可参考mybatis详解
有些时候,sql语句where条件中,需要一些安全判断,例如按性别检索,如果传入的参数是空的,此时查询出的结果很可能是空的,也许我们需要参数为空时,是查出全部的信息。这是我们可以使用动态sql,增加一个判断,当参数不符合要求的时候,我们可以不去判断此查询条件。
准备工作
为了使读者便于区分实体属性和表字段,此处都加student
字段头,实际开发中可以不要。直接定义id,name,sex字段即可。
实体对象StudentEntity
package com.somnus.pojo;
@Data
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = true)
public class StudentEntity {
@Schema(description = "主键,由数据库自动生成")
private Long studentId;
@Schema(description = "学生姓名")
private String studentName;
@Schema(description = "学校性别。1代表男生;0代表女生")
private int studentSex;
}
表结构t_student
字段名 | 字段类型 | 字段描述 |
---|---|---|
student_id | bigint | 主键,数据库自动生成 |
student_name | varchar | 学生姓名 |
student_sex | int | 学校性别。1代表男生;0代表女生 |
if标签
<!-- 查询学生list,like姓名 -->
<select id="getStudentEntityListLikeName" parameterType="com.somnus.pojo.StudentEntity" resultMap="studentResultMap">
SELECT * t_student WHERE student_name LIKE CONCAT('%', #{studentName}),'%')
</select>
但是此时如果studentName为null,此语句很可能报错或查询结果为空。
此时我们使用if动态sql语句先进行判断,如果值为null或等于空字符串,我们就不进行此条件的判断,增加灵活性。
参数为实体类StudentEntity。将实体类中所有的属性均进行判断,如果不为空则执行判断条件。
<!-- 2 if(判断参数) - 将实体类不为空的属性作为where条件 -->
<select id="getStudentList_if" resultMap="studentResultMap" parameterType="com.somnus.pojo.StudentEntity">
SELECT student_id,
student_name,
student_sex
FROM t_student
WHERE
<if test="studentName !=null and studentName != '' ">
student_name LIKE CONCAT('%', #{studentName, jdbcType=VARCHAR},'%')
</if>
<if test="studentSex != null and studentSex != '' ">
AND student_sex = #{studentSex, jdbcType=INTEGER}
</if>
<if test="studentId != null and studentId != '' ">
AND student_id = #{studentId, jdbcType=VARCHAR}
</if>
</select>
使用时比较灵活, new一个这样的实体类,我们需要限制那个条件,只需要附上相应的值就会where这个条件,相反不去赋值就可以不在where中判断。
public void select_test_if() {
StudentEntity entity = new StudentEntity();
entity.setStudentName("zpli");
entity.setStudentSex(1);
List<StudentEntity> list = studentMapper.getStudentList_if(entity);
for (StudentEntity e : list) {
System.out.println(e.toString());
}
}
if + where 的条件判断
当where中的条件使用的if标签较多时,这样的组合可能会导致错误。我们以在 if 标签查询语句为例子,当java代码按如下方法调用时:
@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());
}
}
如果上面例子,参数studentName为null,将不会进行student_name列的判断,则会直接导“WHERE AND”关键字多余的错误SQL。
这时我们可以使用where动态语句来解决。这个where
标签会做两件事:
- 如果它包含的标签中有返回值的话,它就插入一个
where
语句,否则什么也不做 - 如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。
上面例子修改为:
<!-- 3 select - where/if(判断参数) - 将实体类不为空的属性作为where条件 -->
<select id="getStudentList_whereIf" resultMap="studentResultMap" parameterType="com.somnus.pojo.StudentEntity">
SELECT student_id,
student_name,
student_sex
FROM t_student
<where>
<if test="studentName !=null and studentName != '' ">
student_name LIKE CONCAT('%', #{studentName, jdbcType=VARCHAR},'%')
</if>
<if test="studentSex != null and studentSex != '' ">
AND student_sex = #{studentSex, jdbcType=INTEGER}
</if>
<if test="studentId != null and studentId != '' ">
AND student_id = #{studentId, jdbcType=VARCHAR}
</if>
</where>
</select>
if + set 的更新语句
当update语句中没有使用set标签时,如果最后一个if标签的参数为null,就会导致错误。
当在update语句中使用if标签时,如果最后的if没有执行,则或导致逗号多余错误。使用set标签可以将动态的配置set
关键字,和剔除追加到条件末尾的任何不相关的逗号。
如下studentSex为null或者为空串,则最终的语句会变为UPDATE t_student set student_name = 'zpli', WHERE student_id = '7'
。此时set语句最后多了逗号,sql必将报错。
<!-- 4 if/set(判断参数) - 将实体类不为空的属性更新 -->
<update id="updateStudent_set" parameterType="com.somnus.pojo.StudentEntity">
UPDATE t_student set
<if test="studentName != null and studentName != '' ">
student_name = #{studentName},
</if>
<if test="studentSex != null and studentSex != '' ">
student_sex = #{studentSex}
</if>
WHERE student_id = #{studentId};
</update>
使用if+set标签修改后,如果某项为null则不进行更新,而是保持数据库原值。如下示例:
<!-- 4 if/set(判断参数) - 将实体类不为空的属性更新 -->
<update id="updateStudent_if_set" parameterType="com.somnus.pojo.StudentEntity">
UPDATE t_student
<set>
<if test="studentName != null and studentName != '' ">
student_name = #{studentName},
</if>
<if test="studentSex != null and studentSex != '' ">
student_sex = #{studentSex},
</if>
</set>
WHERE student_id = #{studentId};
</update>
if + trim代替where/set标签
trim是更灵活的去处多余关键字的标签,他可以实践where和set的效果。
属性值 | 属性说明 |
---|---|
prefix | 以xx开头 |
prefixOverrides | 覆盖开头的xx |
suffixOverrides | 覆盖结尾的xx |
trim代替where
prefixOverrides 属性会忽略开头中通过管道符分隔的文本序列(注意此例中的空格是必要的)。下述例子会移除开头中 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
<!-- if/trim代替where(判断参数) - 将实体类不为空的属性作为where条件 -->
<select id="getStudentList_if_trim" resultMap="studentResultMap">
SELECT student_id,
student_name,
student_sex
FROM t_student
<trim prefix="WHERE" prefixOverrides="AND| OR">
<if test="studentName !=null " and studentName != '' >
student_name CONCAT('%', #{studentName, jdbcType=VARCHAR},'%')
</if>
<if test="studentSex != null and studentSex != '' ">
AND student_sex = #{studentSex, jdbcType=INTEGER}
</if>
</trim>
</select>
trim代替set
<!-- if/trim代替set(判断参数) - 将实体类不为空的属性更新 -->
<update id="updateStudent_if_trim" parameterType="com.somnus.pojo.StudentEntity">
UPDATE t_student
<trim prefix="SET" suffixOverrides=",">
<if test="studentName != null and studentName != '' ">
student_name = #{studentName},
</if>
<if test="studentSex != null and studentSex != '' ">
student_sex = #{studentSex},
</if>
</trim>
WHERE student_id = #{studentId}
</update>
choose (when, otherwise)
有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。而使用if标签时,只要test中的表达式为true,就会执行if标签中的条件。MyBatis提供了choose 元素。if标签是与(and)的关系,而choose比傲天是或(or)的关系。
choose标签是按顺序判断其内部when标签中的test条件出否成立,如果有一个成立,则choose结束。当choose中所有when的条件都不满则时,则执行otherwise中的sql。类似于Java 的switch 语句,choose为switch,when为case,otherwise则为default。
例如下面例子,同样把所有可以限制的条件都写上,方面使用。choose会从上到下选择一个when标签的test为true的sql执行。安全考虑,我们使用where将choose包起来,放置关键字多于错误。
<!-- choose(判断参数) - 按顺序将实体类第一个不为空的属性作为where条件 -->
<select id="getStudentList_choose" resultMap="studentResultMap" parameterType="com.somnus.pojo.StudentEntity">
SELECT student_id,
student_name,
student_sex
FROM t_student
<where>
<choose>
<when test="studentName !=null ">
student_name LIKE CONCAT('%', #{studentName, jdbcType=VARCHAR},'%')
</when >
<when test="studentSex != null and studentSex != '' ">
AND student_sex = #{studentSex, jdbcType=INTEGER}
</when >
<when test="studentId != null and studentId != '' ">
AND student_id = #{studentId, jdbcType=VARCHAR}
</when >
<otherwise>
</otherwise>
</choose>
</where>
</select>
foreach
属性值 | 属性说明 |
---|---|
collections | 集合参数 |
item | 当使用可迭代对象或者数组(如 List、Set 等)时,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,item 是值 |
index | 当使用可迭代对象或者数组(如 List、Set 等)时,index 是当前迭代的序号。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键 |
open | 语句整体开始,头插入 |
close | 语句整体结束,尾追加 |
separator | 每次迭代结束的分割符 |
对于动态SQL 非常必须的,主是要迭代一个集合,通常是用于IN 条件,或者批量插入,亦或者批量更新。
你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
foreach元素是非常强大的,它允许你指定一个集合,声明集合项和索引变量,它们可以用在元素体内。它也允许你指定开放和关闭的字符串,在迭代之间放置分隔符。这个元素是很智能的,它不会偶然地附加多余的分隔符。
这个部分是对关于XML配置文件和XML映射文件的而讨论的。下一部分将详细讨论Java API,所以你可以得到你已经创建的最有效的映射。
foreach用于in条件
接口的方法声明:
List<StudentEntity> getStudentListByStudentIds_foreach_list(List<String> studentIdList);
动态SQL语句:
<!-- foreach(循环List<String>参数) - 作为where中in的条件 -->
<select id="getStudentListByStudentIds_foreach_list" resultMap="studentResultMap">
SELECT student_id,
student_name,
student_sex
FROM t_student
WHERE student_id IN
<foreach collection="studentIdList" item="studentId" open="(" separator="," close=")">
#{studentId}
</foreach>
</select>
或者
<!-- foreach(循环List<String>参数) - 作为where中in的条件 -->
<select id="getStudentListByStudentIds_foreach_list" resultMap="studentResultMap">
SELECT student_id,
student_name,
student_sex
FROM t_student
WHERE student_id IN (
<foreach collection="studentIdList" item="studentId" separator="," >
#{studentId,jdbcType=BIGINT}
</foreach>
)
</select>
测试代码,查询学生中,学号在20000001、20000002的学生:
@Test
public void test_foreach() {
ArrayList<String> studentIdList = new ArrayList<String>();
studentIdList.add("20000001");
studentIdList.add("20000002");
List<StudentEntity> list = studentMapper.getStudentListByStudentIds_foreach_list(studentIdList);
for (StudentEntity e : list) {
System.out.println(e.toString());
}
}
foreach用于批量插入
接口的方法声明:
void insertStudentEntityBatch(List<StudentEntity> studentEntityList);
动态SQL语句:
<select id="insertStudentEntityBatch">
insert t_student
student_name,
student_sex
values
<foreach collection="studentEntityList" item="studentEntity" separator=","">
(
#{studentEntity.studentName,jdbcType=VARCHAR},
#{studentEntity.studentSex,jdbcType=INTEGER}
)
</foreach>
</select>
测试代码:
@Test
public void test_insertBatch() {
ArrayList<StudentEntity> studentEntityList = new ArrayList<String>();
StudentEntity entity1 = new StudentEntity();
entity1.setStudentName("zhangsan");
entity1.setStudentSex(24);
studentEntityList.add(entity1);
StudentEntity entity2 = new StudentEntity();
entity2.setStudentName("lisi");
entity2.setStudentSex(28);
studentEntityList.add(entity2);
studentMapper.insertStudentEntityBatch(classIdList);
}
foreach用于批量更新
接口的方法声明:
void updateStudentEntityBatch(List<StudentEntity> studentEntityList);
动态SQL语句:
<foreach collection="studentEntityList" item="studentEntity" separator=";")">
update t_student
<set>
<if studentEntity.studentName != null and studentEntity.studentName != ''>
student_name = #{studentEntity.studentName,jdbcType=VARCHAR},
</if>
<if studentEntity.studentSex != null and studentEntity.studentSex != 0>
student_sex = #{studentEntity.studentSex,jdbcType=INTEGER}
</if>
</set>
<where>
student_id = #{studentEntity.studentId,jdbcType=BIGINT}
</where>
</foreach>
注意:Mybatis映射文件中的sql语句默认是不支持以" ;" 结尾,也就是不支持多条sql语句的执行。所以需要在连接mysql的url上加&allowMultiQueries=true 这个才可以执行。
测试代码:
@Test
public void test_updateBatch() {
ArrayList<StudentEntity> studentEntityList = new ArrayList<String>();
StudentEntity entity1 = new StudentEntity();
entity1.setStudentId(20000001);
entity1.setStudentName("zhangsan");
entity1.setStudentSex(24);
studentEntityList.add(entity1);
StudentEntity entity2 = new StudentEntity();
entity2.setStudentId(20000002);
entity2.setStudentName("lisi");
entity2.setStudentSex(28);
studentEntityList.add(entity2);
studentMapper.updateStudentEntityBatch(classIdList);
}
script
script的基本用法
要在带注解的映射器接口类中使用动态 SQL,可以使用 script 元素。比如:
@Mapper
public interface StudentEntityMapper {
@Update({"<script>",
"update t_student",
" <set>",
" <if test=\"studentEntity.studentName != null and studentEntity.studentName != '' \">",
" student_name = #{studentEntity.studentName}",
" </if>",
" <if test=\"studentEntity.studentSex != null and studentEntity.studentSex != '' \">",
" student_sex = #{studentEntity.studentSex}",
" </if>",
" </set>",
" <where>",
" student_id = #{studentEntity.studentId} ",
" </where>",
"</script>"})
void updateStudentEntity(@Params("studentEntity) StudentEntity studentEntity);
script中sql语句片段多次复用
也可以定义String字符串片段,集成到script
标签中,实现sql语句片段多次复用。
注意:在script中换行是通过",“连接,而在String语句段中换行是通过”+"连接。
如下:
@Mapper
public interface StudentEntityMapper {
String TABLE_NAME = "t_student";
String WHERE_CLAUSE = " <where>"
+ " student_id = #{studentEntity.studentId} "
+ " </where>";
@Update({"<script>",
"update t_student",
TABLE_NAME,
" <set>",
" <if test=\"studentEntity.studentName != null and studentEntity.studentName != '' \">",
" student_name = #{studentEntity.studentName}",
" </if>",
" <if test=\"studentEntity.studentSex != null and studentEntity.studentSex != '' \">",
" student_sex = #{studentEntity.studentSex}",
" </if>",
" </set>",
WHERE_CLAUSE,
"</script>"})
void updateStudentEntity(@Params("studentEntity) StudentEntity studentEntity);
最终效果和上面一致,优点是TABLE_NAME和WHERE_CLAUSE可以在多个script中使用。