Detailed explanation of the basic usage of dynamic SQL in Java's MyBatis framework

Sometimes, some security judgment is required in the where conditions of the SQL statement. For example, if the passed parameter is empty when querying according to a certain condition, the query result is likely to be empty at this time. Perhaps when we need the parameter to be empty, we will find all the information. Use Oracle's sequences and mysql functions to generate Ids. At this time we can use dynamic sql. All the following are mysql syntax and functions (such as string link function CONCAT).

selectKey tag
In the insert statement, Oracle often uses sequences and functions in MySQL to automatically generate the primary key of the insert table, and a method is required to return this generated primary key. This effect can be achieved using the selectKey tag of myBatis. The following example is to use 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, the edge can obtain 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 interface methods and obtain automatic key generation

 StudentEntity entity = new StudentEntity(); entity.setStudentName("Hello Dawn"); entity.setStudentSex(1); entity.setStudentBirthday(DateUtil.parse("1985-05-28")); entity.setClassId("20000001"); entity.setPlaceId("70000001"); this.dynamicSqlMapper.createStudentAutoKey(entity); System.out.println("New Student ID: " + entity.getStudentId());

SelectKey statement property configuration details:

if tag

If tags can be used in many types of SQL statements, let's take query as an example. First, let’s look at a very ordinary query:

 <!-- Query student list, like 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 or studentSex is null at this time, this statement is likely to report an error or the query result is empty. At this time, we use the if dynamic SQL statement to make judgments first. If the value is null or equal to an empty string, we will not make judgments on this condition and increase flexibility.
The parameter is the entity class StudentEntity. All attributes in the entity class are judged, and if they are not empty, the judgment condition is executed.

 <!-- 2 if(judgment parameter) - Use the property 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>

When using it, if you want to use it, you need to limit the condition for new such entity class. You only need to attach the corresponding value to where the condition. On the contrary, if you don't assign the value, you can not judge in where.

 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()); } }

If + where conditional judgment
Such combinations may cause errors when conditions in where use more if tags. Let's take the query statement in 3.1 as an example, when the 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 above example is null, the STUDENT_NAME column will not be judged, and the extra error SQL for the "WHERE AND" keyword will be directly derived.

At this time, we can use where dynamic statements to solve the problem. This "where" tag will know that if the tag it contains has a return value, it will insert a 'where'. Additionally, if the content returned by a tag starts with AND or OR, it will be removed.
The above example is modified to:

 <!-- 3 select - where/if (judgment parameter) - Use the property whose entity class is not empty as the 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 !=' ' "> 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>

Update statement for if + set
When the if tag is not used in the update statement, if there is a parameter null, it will cause an error.
When using if tags in update statements, if the previous if is not executed, it may result in a comma redundant error. Use the set tag to dynamically configure SET keywords and to remove any unrelated commas appended to the end of the condition.

After modifying with the if+set tag, if an item is null, it will not be updated, but the original database value will be maintained. The following example:

 <!-- 4 if/set(judgment parameter) - Update attributes 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 place to go to redundant keyword tags, which can practice the effects of where and set.

trim instead of where

 <!-- 5.1 if/trim replaces where (judgment parameter) - Use attributes 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 replaces set (judgment parameter) - Update attributes 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>

Choose (when, otherwise)

Sometimes we don't want to apply all the conditions, but just choose one from multiple options. When using the if tag, as long as the expression in the test is true, the conditions in the if tag will be executed. MyBatis provides the choice element. If tag is a relationship with (and), and choose is a relationship with (or).
The choice tag is to determine whether the test condition in the internal when tag is valid in order. If one is valid, the choice ends. When all the conditions in choose are not satisfied, the SQL in otherwise is executed. Similar to Java's switch statement, choose switch, when case, and otherwise default.
For example, the following examples also write down all the conditions that can be restricted and use them. Choose will select a SQL execution with test true from top to bottom when tag. For security considerations, we use where to wrap up the choice and place more keywords than errors.

 <!-- 6 choose(judgment parameters) - Take the first property of the entity class that is not empty in order as where condition--> <select id="getStudentList_choose" 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> <choose> <when ​​test="studentName !=null "> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%') </when > <when ​​test="studentSex != null and studentSex != '' "> AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} </when > <when test="studentBirthday != null "> AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE} </when > <when ​​test="classId != null and classId!= '' "> AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR} </when > <when ​​test="classEntity != null and classEntity.classId !=' ' "> AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR} </when > <when ​​test="placeId != null and placeId != '' "> AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR} </when > <when ​​test="placeEntity != null and placeEntity.placeId != null and placeEntity.placeId != '' "> AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR} </when > <when ​​test="studentId != null and studentId != '' "> AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR} </when > <otherwise> </otherwise> </choose> </where> </select>

foreach
It is very necessary for dynamic SQL, mainly to iterate over a collection, usually for IN conditions. List instances will use "list" as key, and array instances will use "array" as key.
The foreach element is very powerful, it allows you to specify a collection, declare collection items and index variables, which can be used within the element body. It also allows you to specify open and closed strings, placing separators between iterations. This element is very smart, and it does not append extra separators by chance.
Note: You can pass a List instance or array as parameter object to MyBatis. When you do this, MyBatis will automatically wrap it in a map with the name as the key. List instances will use "list" as key, and array instances will use "array" as key.
This section is discussed about XML configuration files and XML mapping files. The next section will discuss the Java API in detail, so you can get the most efficient mappings you have created.

1. Write the example of the parameter as array

Method declaration of interface:

 public List<StudentEntity> getStudentListByClassIds_foreach_array(String[] classIds);

Dynamic SQL statement:

 <!― 7.1 foreach(loop array parameter) - as the condition for in in where --> <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 the code to query students in the two classes of 20000001 and 20000002:

 @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. Write the example of the parameter list
Method declaration of interface:

 public List<StudentEntity> getStudentListByClassIds_foreach_list(List<String> classIdList);

Dynamic SQL statement:

 <!-- 7.2 foreach(Loop List<String> Parameter) - as a condition for in in where --> <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 the code to query students in the two classes of 20000001 and 20000002:

 @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()); } }

3. Encapsulate the parameters into the type of Map by yourself

 <select id="dynamicForeach3Test" resultType="Blog"> select * from t_blog where title like "%"#{title}"%" and id in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>

The value of the above collection is ids, which is the key of the parameter Map passed in, and the corresponding Mapper code:

 public List<Blog> dynamicForeach3Test(Map<String, Object> params);

Corresponding test code:

 @Test public void dynamicForeach3Test() { SqlSession session = Util.getSqlSessionFactory().openSession(); BlogMapper blogMapper = session.getMapper(BlogMapper.class); final List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); ids.add(6); ids.add(7); ids.add(9); Map<String, Object> params = new HashMap<String, Object>(); params.put("ids", ids); params.put("title", "China"); List<Blog> blogs = blogMapper.dynamicForeach3Test(params); for (Blog blog : blogs) System.out.println(blog); session.close(); }