The label used to loop the container forEach, see the example
The properties of foreach element mainly include item, index, collection, open, separator, close.
- item: the alias of the element iteration in the collection,
- Index: the index when the elements in the collection iterate
- open: in the common language where statement, it indicates what to start with, such as' (')
- Separator: the separator for each iteration,
- In the where statement, it indicates what to end with,
When using foreach, the most critical and error prone property is the collection property, which must be specified. However, in different situations, the value of the property is different, mainly in the following three situations:
- If a single parameter is passed in and the parameter type is a list, the collection property value is list
- If a single parameter is passed in and the parameter type is an array array, the property value of collection is array
- If there are multiple incoming parameters, we need to encapsulate them into a map. Of course, a single parameter can also be encapsulated into a map. In fact, if you are passing in parameters, it will also be encapsulated into a map in MyBatis. The key of the map is the parameter name, so the value of the collection property is the map of the incoming List or array object in its own encapsulation The key in it
For the last one, let's take a look at the official statement:
Note that you can pass a list instance or array as a parameter object to MyBatis. When you do this, MyBatis will automatically wrap it in a Map and use the name as the key. The list instance will take "list" as the key, and the key of the array instance will be "array".
Therefore, no matter the list or array type of multi parameter or single parameter can be encapsulated as a map for transmission. If a list is passed, mybatis will be encapsulated as a map with list as key and list value as object. If it is array, it will be encapsulated as a map with array as key and array value as object. If it is encapsulated by itself, it will be encapsulated as the key value in the map encapsulated by itself
//In mapper, what we want to pass for this method is a container. We need to transfer the elements in the container one by one
//This forEach tag is used in the method of splicing to xml
public List<Entity> queryById(List<String> userids);
//The corresponding xml is as follows
<select id="queryById" resultMap="BaseReslutMap" >
select * FROM entity
where id in
<foreach collection="userids" item="userid" index="index" open="(" separator="," close=")">
#{userid}
</foreach>
</select>concat fuzzy query
//For example, we want to query conditions, but several conditions are not used every time, so we can
//By judging whether to splice into sql
<select id="queryById" resultMap="BascResultMap" parameterType="entity">
SELECT * from entity
<where>
<if test="name!=null">
name like concat('%',concat(#{name},'%'))
</if>
</where>
</select>choose (when, otherwise) label
The choose tag is used to determine whether the test conditions in its internal when tag are true or not in order. If one is true, the choose tag ends. When all the conditions of when in choose are not satisfied, the sql in other wise is executed. Similar to the switch statement in Java, choose is switch, when is case, and other is default.
For example, in the following example, all the conditions that can be restricted are also written down for convenience. Choose will select a sql execution whose test is true from top to bottom. For security reasons, we use where to package choose and place more keywords than errors.
<!-- choose(Judgment parameters) - Sequence entity classes User The first non empty property is: where condition -->
<select id="getUserList_choose" resultMap="resultMap_user" parameterType="com.yiibai.pojo.User">
SELECT *
FROM User u
<where>
<choose>
<when test="username !=null ">
u.username LIKE CONCAT(CONCAT('%', #{username, jdbcType=VARCHAR}),'%')
</when >
<when test="sex != null and sex != '' ">
AND u.sex = #{sex, jdbcType=INTEGER}
</when >
<when test="birthday != null ">
AND u.birthday = #{birthday, jdbcType=DATE}
</when >
<otherwise>
</otherwise>
</choose>
</where>
</select> selectKey label
In the insert statement, the sequence is often used in Oracle and the function is used in MySQL to automatically generate the primary key of the inserted table, and the method is required to return the generated primary key. This can be achieved by using the selectKey tag of myBatis.
The following example uses the mysql database custom function nextval('student ') to generate a key and set it to the studentId property in the passed entity class. So after executing this method, you can get the generated key through this entity class.
<!-- Insert student Automatic primary key-->
<insert id="createStudentAutoKey" parameterType="liming.student.manager.data.model.StudentEntity" keyProperty="studentId">
<selectKey keyProperty="studentId" resultType="String" order="BEFORE">
select nextval('student')
</selectKey>
INSERT INTO STUDENT_TBL(STUDENT_ID,
STUDENT_NAME,
STUDENT_SEX,
STUDENT_BIRTHDAY,
STUDENT_PHOTO,
CLASS_ID,
PLACE_ID)
VALUES (#{studentId},
#{studentName},
#{studentSex},
#{studentBirthday},
#{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},
#{classId},
#{placeId})
</insert> Call the interface method and get the key generated automatically
StudentEntity entity = new StudentEntity();
entity.setStudentName("Hello, Liming");
entity.setStudentSex(1);
entity.setStudentBirthday(DateUtil.parse("1985-05-28"));
entity.setClassId("20000001");
entity.setPlaceId("70000001");
this.dynamicSqlMapper.createStudentAutoKey(entity);
System.out.println("New students ID: " + entity.getStudentId()); if tag
The if tag can be used in many types of sql statements. Let's take a query as an example. First, let's look at a very common query:
<!-- Query students list，like full name -->
<select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</select> However, if studentName is null at this time, this statement is likely to report an error or the query result is empty. At this time, we use if dynamic sql statement to judge first. If the value is null or equal to empty string, we will not judge this condition and increase flexibility.
Parameter is entity class StudentEntity. Judge all the attributes in the entity class. If it is not empty, the judgment condition will be executed.
<!-- 2 if(Judgment parameters) - Attribute whose entity class is not empty as where condition -->
<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
SELECT ST.STUDENT_ID,
ST.STUDENT_NAME,
ST.STUDENT_SEX,
ST.STUDENT_BIRTHDAY,
ST.STUDENT_PHOTO,
ST.CLASS_ID,
ST.PLACE_ID
FROM STUDENT_TBL ST
WHERE
<if test="studentName !=null ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
</if>
<if test="studentSex != null and studentSex != '' ">
AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}
</if>
<if test="studentBirthday != null ">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}
</if>
<if test="classId != null and classId!= '' ">
AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}
</if>
<if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">
AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}
</if>
<if test="placeId != null and placeId != '' ">
AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}
</if>
<if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">
AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
</if>
<if test="studentId != null and studentId != '' ">
AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
</if>
</select> It's flexible to use. For an entity class like new, we need to limit that condition. We just need to attach the corresponding value to the condition where. On the contrary, we can not judge where without assigning.
public void select_test_2_1() {
StudentEntity entity = new StudentEntity();
entity.setStudentName("");
entity.setStudentSex(1);
entity.setStudentBirthday(DateUtil.parse("1985-05-28"));
entity.setClassId("20000001");
//entity.setPlaceId("70000001");
List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity);
for (StudentEntity e : list) {
System.out.println(e.toString());
}
}Conditional judgment of if + where
Such a combination can lead to errors when more if tags are used for conditions in where. We take the query statement in 3.1 as an example, when java code is called as follows:
@Test
public void select_test_2_1() {
StudentEntity entity = new StudentEntity();
entity.setStudentName(null);
entity.setStudentSex(1);
List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity);
for (StudentEntity e : list) {
System.out.println(e.toString());
}
} If the parameter studentName is null in the above example, no studentwill be performed_ The judgment of the name column will directly import the redundant error SQL of the "WHERE AND" keyword.
At this time, we can use where dynamic statement to solve this problem. The "where" tag knows that if it contains a return value in the tag, it inserts a "where". In addition, if the content returned by the tag starts with AND OR, it will be eliminated.
The above example is modified as follows:
<!-- 3 select - where/if(Judgment parameters) - Attribute whose entity class is not empty as where condition -->
<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity">
SELECT ST.STUDENT_ID,
ST.STUDENT_NAME,
ST.STUDENT_SEX,
ST.STUDENT_BIRTHDAY,
ST.STUDENT_PHOTO,
ST.CLASS_ID,
ST.PLACE_ID
FROM STUDENT_TBL ST
<where>
<if test="studentName !=null ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
</if>
<if test="studentSex != null and studentSex != '' ">
AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}
</if>
<if test="studentBirthday != null ">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}
</if>
<if test="classId != null and classId!= '' ">
AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}
</if>
<if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">
AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}
</if>
<if test="placeId != null and placeId != '' ">
AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}
</if>
<if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">
AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
</if>
<if test="studentId != null and studentId != '' ">
AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
</if>
</where>
</select> if + set to modify statements
When the if tag is not used in the update statement, a null parameter will cause an error.
When the if tag is used in the update statement, if the previous if is not executed, it may result in redundant comma errors. The SET tag allows you to dynamically configure the SET keyword and weed out any unrelated commas appended to the end of the condition. After modification with if+set tag, if an item is null, it will not be updated, but the original database value will be maintained.
Here is an example:
<!-- 4 if/set(Judgment parameters) - Update the attribute whose entity class is not empty -->
<update id="updateStudent_if_set" parameterType="liming.student.manager.data.model.StudentEntity">
UPDATE STUDENT_TBL
<set>
<if test="studentName != null and studentName != '' ">
STUDENT_TBL.STUDENT_NAME = #{studentName},
</if>
<if test="studentSex != null and studentSex != '' ">
STUDENT_TBL.STUDENT_SEX = #{studentSex},
</if>
<if test="studentBirthday != null ">
STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
</if>
<if test="studentPhoto != null ">
STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},
</if>
<if test="classId != '' ">
STUDENT_TBL.CLASS_ID = #{classId}
</if>
<if test="placeId != '' ">
STUDENT_TBL.PLACE_ID = #{placeId}
</if>
</set>
WHERE STUDENT_TBL.STUDENT_ID = #{studentId};
</update> if + trim instead of where/set tag
trim is a more flexible tag for removing redundant keywords. It can practice the effect of where and set.
trim instead of where
<!-- 5.1if/trim replace where(Judgment parameters) -Attribute whose entity class is not empty as where condition-->
<select id="getStudentList_if_trim" resultMap="resultMap_studentEntity">
SELECT ST.STUDENT_ID,
ST.STUDENT_NAME,
ST.STUDENT_SEX,
ST.STUDENT_BIRTHDAY,
ST.STUDENT_PHOTO,
ST.CLASS_ID,
ST.PLACE_ID
FROM STUDENT_TBL ST
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="studentName !=null ">
ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')
</if>
<if test="studentSex != null and studentSex != '' ">
AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}
</if>
<if test="studentBirthday != null ">
AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}
</if>
<if test="classId != null and classId!= '' ">
AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}
</if>
<if test="classEntity != null and classEntity.classId !=null and classEntity.classId !=' ' ">
AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}
</if>
<if test="placeId != null and placeId != '' ">
AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}
</if>
<if test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' ">
AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
</if>
<if test="studentId != null and studentId != '' ">
AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
</if>
</trim>
</select> trim instead of set
<!-- 5.2 if/trim replace set(Judgment parameters) - Update the attribute whose entity class is not empty -->
<update id="updateStudent_if_trim" parameterType="liming.student.manager.data.model.StudentEntity">
UPDATE STUDENT_TBL
<trim prefix="SET" suffixOverrides=",">
<if test="studentName != null and studentName != '' ">
STUDENT_TBL.STUDENT_NAME = #{studentName},
</if>
<if test="studentSex != null and studentSex != '' ">
STUDENT_TBL.STUDENT_SEX = #{studentSex},
</if>
<if test="studentBirthday != null ">
STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},
</if>
<if test="studentPhoto != null ">
STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},
</if>
<if test="classId != '' ">
STUDENT_TBL.CLASS_ID = #{classId},
</if>
<if test="placeId != '' ">
STUDENT_TBL.PLACE_ID = #{placeId}
</if>
</trim>
WHERE STUDENT_TBL.STUDENT_ID = #{studentId}
</update>foreach
For dynamic SQL, it is necessary to iterate over a collection, usually for IN conditions. The list instance will use "list" as the key, and the array instance will use "array" as the key.
foreach element is very powerful, it allows you to specify a collection, declare collection items and index variables, which can be used in the element body. It also allows you to specify open and closed strings, placing separators between iterations. This element is smart, and it doesn't accidentally append extra delimiters.
Note: you can pass a list instance or an array to MyBatis as a parameter object. When you do, MyBatis will automatically wrap it in a Map with the name as the key. The list instance will use "list" as the key, and the array instance will use "array" as the key.
This section is about XML configuration files and XML mapping files. The next section discusses the Java API in detail, so you can get the most efficient mapping you've created.
1. The parameter is the writing method of array example
Method declaration for interface:
public List<StudentEntity> getStudentListByClassIds_foreach_array(String[] classIds);
Dynamic SQL statement:
<!-- 7.1 foreach(loop array parameter) - As where in in Conditions of -->
<select id="getStudentListByClassIds_foreach_array" resultMap="resultMap_studentEntity">
SELECT ST.STUDENT_ID,
ST.STUDENT_NAME,
ST.STUDENT_SEX,
ST.STUDENT_BIRTHDAY,
ST.STUDENT_PHOTO,
ST.CLASS_ID,
ST.PLACE_ID
FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
<foreach collection="array" item="classIds" open="(" separator="," close=")">
#{classIds}
</foreach>
</select> Test code, query the students in 20000001 and 20000002 classes:
@Test
public void test7_foreach() {
String[] classIds = { "20000001", "20000002" };
List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_array(classIds);
for (StudentEntity e : list) {
System.out.println(e.toString());
}
}2. The parameter is a list example
Method declaration for interface:
public List<StudentEntity> getStudentListByClassIds_foreach_list(List<String> classIdList);
Dynamic SQL statement:
<!-- 7.2 foreach(loop List<String>parameter) - As where in in Conditions of -->
<select id="getStudentListByClassIds_foreach_list" resultMap="resultMap_studentEntity">
SELECT ST.STUDENT_ID,
ST.STUDENT_NAME,
ST.STUDENT_SEX,
ST.STUDENT_BIRTHDAY,
ST.STUDENT_PHOTO,
ST.CLASS_ID,
ST.PLACE_ID
FROM STUDENT_TBL ST
WHERE ST.CLASS_ID IN
<foreach collection="list" item="classIdList" open="(" separator="," close=")">
#{classIdList}
</foreach>
</select>Test code, query the students in 20000001 and 20000002 classes:
@Test
public void test7_2_foreach() {
ArrayList<String> classIdList = new ArrayList<String>();
classIdList.add("20000001");
classIdList.add("20000002");
List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_list(classIdList);
for (StudentEntity e : list) {
System.out.println(e.toString());
}
} sql fragment label < sql >: through this label, you can define reusable sql statement fragments, which can be directly referenced in the execution sql statement label. This can not only improve the coding efficiency, but also effectively simplify the code and improve readability
The attribute to be configured: id = "" > > indicates the unique ID of the sql statement fragment to be modified
Reference: through the < include refid = "" / > tag reference, the value in refid = "" points to the id = "" attribute in the < SQL > to be referenced
<!--definition sql fragment-->
<sql id="orderAndItem">
o.order_id,o.cid,o.address,o.create_date,o.orderitem_id,i.orderitem_id,i.product_id,i.count
</sql>
<select id="findOrderAndItemsByOid" parameterType="java.lang.String" resultMap="BaseResultMap">
select
<!--quote sql fragment-->
<include refid="orderAndItem" />
from ordertable o
join orderitem i on o.orderitem_id = i.orderitem_id
where o.order_id = #{orderId}
</select>