Details the basic usage of dynamic SQL in Java's MyBatis framework
- 2020-05-07 19:49:50
- OfStack
Sometimes, in the sql statement where condition, some security judgment is needed. For example, if the parameter passed is empty when querying according to a certain 1 condition, the result of the query is likely to be empty. Maybe we need to find out all the information when the parameter is empty. Generate Id using the sequence of Oracle and the function of mysql. At this point we can use dynamic sql. The following sections use mysql syntax and functions (such as the string chaining function CONCAT).
selectKey label
often USES sequences in insert statements, Oracle in Oracle, MySQL in MySQL to automatically generate the primary key inserted into the table, and requires methods to return the generated primary key. This can be achieved using the selectKey tag of myBatis. The following example USES the mysql database custom function nextval('student') to generate an key and set it to the studentId attribute in the incoming entity class. So after executing this method, the edge can get the generated key through this entity class.
<!-- Insert the students 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>
Invoke the interface method, and get automatically generated key
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(" The new students ID: " + entity.getStudentId());
selectKey statement properties configuration details:
| 属性 | 描述 | 取值 |
| keyProperty | selectKey 语句生成结果需要设置的属性。 | |
| resultType | 生成结果类型，MyBatis 允许使用基本的数据类型，包括String 、int类型。 | |
| order |
1：BEFORE，会先选择主键，然后设置keyProperty，再执行insert语句； 2：AFTER，就先运行insert 语句再运行selectKey 语句。 |
BEFORE AFTER |
| statementType | MyBatis 支持STATEMENT，PREPARED和CALLABLE 的语句形式， 对应Statement ，PreparedStatement 和CallableStatement 响应 |
STATEMENT PREPARED CALLABLE |
if label
The if tag can be used in many types of sql statements, as an example of a query. First look at a very common query:
<!-- Query students list . like The name -->
<select id="getStudentListLikeName" parameterType="StudentEntity" resultMap="studentResultMap">
SELECT * from STUDENT_TBL ST
WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')
</select>
But if studentName or studentSex are null at this point, this statement is likely to report an error or the query result is null. At this point, we use if dynamic sql statement to judge first, if the value is null or equal to an empty string, we do not judge this condition, increase the flexibility.
The parameter is the entity class StudentEntity. All attributes in the entity class are judged, and if they are not null, a judgment condition is executed.
<!-- 2 if( To determine parameters ) - The attribute that the entity class is not empty is treated as where conditions -->
<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 is more flexible to use, new1 such entity class, we need to limit that condition, only need to attach the corresponding value will where this condition, on the contrary, not to assign the value can not be judged 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());
}
}
Conditional determination of if + where
When conditions in where use a large number of if tags, this combination may cause errors. We 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 in the above example, the parameter studentName is null, the STUDENT_NAME column will not be judged, and the "WHERE AND" keyword is redundant.
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 an 'where'. In addition, if the tag returns something that begins with AND or OR, it will be removed.
The above example is changed to:
<!-- 3 select - where/if( To determine parameters ) - The attribute that the entity class is not empty is treated as where conditions -->
<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>
Update statement for if + set
when the if tag is not used in the update statement, an error occurs if one argument is null.
When using the if tag in an update statement, if the preceding if is not executed, it may cause a comma redundancy error. Use the set tag to dynamically configure the SET keyword, and to remove any irrelevant commas appended to the end of the condition.
After using the if+set tag, if an item is null, it is not updated, but the original value of the database is kept. Here's an example:
<!-- 4 if/set( To determine parameters ) - Updates the properties of an entity class that are 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 replaces where/set label
trim is a more flexible place to put redundant keyword labels, he can practice where and set effect.
trim instead of where
<!-- 5.1 if/trim Instead of where( To determine parameters ) - The attribute that the entity class is not empty is treated as where conditions -->
<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 Instead of set( To determine parameters ) - Updates the properties of an entity class that are 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, we just want to choose one of several options. With the if tag, the condition in the if tag is executed as long as the expression in test is true. MyBatis provides the choose element. The if tag is a relationship to (and), while choose is a relationship to (or).
The choose tag determines whether the test condition in its internal when tag is true or not in order. If one of them is true, choose ends. When all conditions of when in choose are not satisfied, sql in otherwise is executed. switch statements similar to Java, choose is switch, when is case, otherwise is default.
For example, in the following example, all conditions that can be restricted are also written, and aspects are used. choose selects the test of 1 when tag from top to bottom to execute sql of true. For security reasons, we used where to wrap choose, placing more keywords than errors.
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(" The new students ID: " + entity.getStudentId());
foreach
is very necessary for dynamic SQL, the main is to iterate over a set, usually for IN conditions. The List instance will use "list" as the key, and the array instance will use "array" as the key.
The foreach element is very powerful and allows you to specify a collection, declare collection items and index variables, which can be used in the body of the element. It also allows you to specify open and closed strings, placing delimiters between iterations. This element is smart enough not to accidentally append redundant delimiters.
Note: you can pass an List instance or an array to MyBatis as a parameter object. When you do this, MyBatis will automatically wrap it in 1 Map, using the name as the key. The List instance will have "list" as the key, and the array instance will have "array" as the key.
This section discusses the XML configuration file and the XML mapping file. The next section discusses Java API in detail, so you can get the most efficient mapping you've created.
1. The parameter is the array example
Method declaration of interface:
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(" The new students ID: " + entity.getStudentId());
Dynamic SQL statement:
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(" The new students ID: " + entity.getStudentId());
Test code, query the 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. The parameter is the list example
Method declaration of interface:
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(" The new students ID: " + entity.getStudentId());
Dynamic SQL statement:
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(" The new students ID: " + entity.getStudentId());
Test code, query the students in the two classes of 20000001 and 20000002:
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(" The new students ID: " + entity.getStudentId());
3. Encapsulate the parameters into of type Map
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(" The new students ID: " + entity.getStudentId());
The value of collection above is ids, which is key of the parameter Map passed in. The corresponding Mapper code is:
public List<Blog> dynamicForeach3Test(Map<String, Object> params);
corresponding test code:
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(" The new students ID: " + entity.getStudentId());