mybatis tutorial dynamic sql statement _ Power node Java College collation
- 2020-10-31 21:46:14
- OfStack
Sometimes, sql statement in where condition, some security judgment is required, for example, if the parameter passed in according to a certain condition is empty, the result of the query is likely to be empty, maybe we need to find out all the information when the parameter is empty. Id is generated using Oracle's sequences and mysql's functions. In this case, we can use dynamic sql.
The following paragraphs use the mysql syntax and functions (such as the string linking function CONCAT).
selectKey label
In insert statements, sequences are often used in Oracle, functions are used in MySQL to automatically generate the primary key inserted into the table, and methods are required to return this 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 the entity class.
<!-- Insert the students Automatic primary key -->
<insert id="createStudentAutoKey" parameterType="bjpowernodestudentmanagerdatamodelStudentEntity" 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=orgapacheibatistypeBlobTypeHandler},
#{classId},
#{placeId})
</insert>
Call the interface method, and get the automatic generation of 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 property 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, such as queries. Let's start with 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 at this point if studentName or studentSex for null , the statement is likely to report an error or the query result is empty. At this point we use if dynamic sql Statement to determine if the value is null Or equal to an empty string, we do not make this condition judgment, increase flexibility.
The parameter is an entity class StudentEntity . All attributes in the entity class are judged, and if they are not null, the judgment condition is executed.
<!-- 2 if( To determine parameters ) - Attribute that does not have an entity class as empty where conditions -->
<select id="getStudentList_if" resultMap="resultMap_studentEntity" parameterType="bjpowernode.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 classEntityclassId !=null and classEntityclassId !=' ' ">
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 placeEntityplaceId != null and placeEntityplaceId != '' ">
AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}
</if>
<if test="studentId != null and studentId != '' ">
AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}
</if>
</select>
new1 such an entity class, we need to limit that condition, only need to attach the corresponding value will be where this condition, on the contrary, no assignment 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());
}
}
if + where condition judgment
This combination can cause errors when there are many if tags used conditionally in where. Let's take the query 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 judgment of STUDENT_NAME column will not be performed, and the "WHERE AND" keyword redundant error SQL will be directed.
In this case, we can use the where dynamic statement to solve. The "where" tag will know that if it contains a tag with a return value, it inserts 1 'where'. In addition, if the tag returns something that starts with AND or OR, it will be removed.
The above example is modified to:
<!-- 3 select - where/if( To determine parameters ) - Attribute that does not have an entity class as empty where conditions -->
<select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="bjpowernode.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 classEntityclassId !=null and classEntityclassId !=' ' ">
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 placeEntityplaceId != null and placeEntityplaceId != '' ">
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, having one argument of null results in an error.
When using the if tag in an update statement, if the preceding if is not executed, this may result in an redundant comma error. Using the set tag, you can dynamically configure the SET keyword and weed out any irrelevant commas appended to the end of the condition.
When the if+set tag is modified, if an item is null, it is not updated, but the original value of the database is kept. Here are some examples:
<!-- 4 if/set( To determine parameters ) - Update an attribute whose entity class is not empty -->
<update id="updateStudent_if_set" parameterType="bjpowernode.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 tags
trim is a more flexible way to remove redundant keyword tags, and can practice the effects of where and set.
trim where instead
<!-- 5.1 if/trim Instead of where( To determine parameters ) - Attribute that does not have an entity class as empty 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 classEntityclassId !=null and classEntityclassId !=' ' ">
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 set instead
<!-- 2 if/trim Instead of set( To determine parameters ) - Update an attribute whose entity class is not empty -->
<update id="updateStudent_if_trim" parameterType="bjpowernode.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_TBLSTUDENT_ID = #{studentId}
</update>
choose (when, otherwise)
Sometimes we don't want to apply all the conditions and just want to choose one from a number of 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 associated with (and), while choose is associated with (or).
choose tag judges 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. Similar to Java, choose is switch, when is case, and otherwise is default.
For example, in the following example, all the conditions that can be restricted are also written down. choose selects one when tag test from top to bottom to execute sql of true. To be safe, we use where to wrap choose, placing more keywords than errors.
<!-- 6 choose( To determine parameters ) - List the entity classes in order 1 An attribute that is not null where conditions -->
<select id="getStudentList_choose" resultMap="resultMap_studentEntity" parameterType="bjpowernode.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 !=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
For dynamic SQL it is essential 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 in that it allows you to specify a collection, declare collection items, and index variables that 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, and it doesn't accidentally append redundant delimiters.
Note: You can pass an instance of List or an array as an argument object to MyBatis. When you do this, MyBatis will automatically wrap it in 1 Map with the name as the key. An instance of List will have "list" as the key, and an 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 ever created.
The parameter is array example
Interface method declaration:
public List<StudentEntity> getStudentListByClassIds_foreach_array(String[] classIds);
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 the code and 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());
The parameter is list example
Interface method declaration:
public List<StudentEntity> getStudentListByClassIds_foreach_list(List<String> classIdList);
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 the code and query the 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());
}
}