MyBatis动态SQL教程
动态SQL是 MyBatis 中非常强大且灵活的功能,允许你根据不同的条件构建SQL查询。
这主要通过 <if>、<choose>、<when>、<otherwise>、<foreach>等标签实现。
查询场景
/**
* 根据条件查询员工信息
* @param emp
* @return
*/
List<Emp> getEmpCondition(Emp emp);
if标签的使用
<if> 标签:该标签用于根据条件判断是否包含某段SQL片段。
<select id="getEmpCondition" resultType="com.evan.mybatis.entity.Emp">
select * from t_emp where 1=1
<if test="empName != null and empName != ''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender != ''">
and gender = #{gender}
</if>
</select>
测试
@Test
public void test1() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setEmpName("李四");
List<Emp> emps = mapper.getEmpCondition(emp);
emps.forEach(System.out::println);
sqlSession.close();
}
结论:if标签可通过test属性的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行
where标签的使用
- 若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
- 若where标签中的if条件满足,则where标签会自动添加where关键字,并将条件前面多余的and或or去掉
注意:where标签会自动去掉条件前面多余的and或or
<select id="getEmpCondition" resultType="com.evan.mybatis.entity.Emp">
select * from t_emp
<where>
<if test="empName != null and empName !=''">
and emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="gender != null and gender !=''">
or gender = #{gender}
</if>
</where>
</select>
测试
@Test
public void test1() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp();
emp.setEmpName("李四");
emp.setAge(19);
emp.setGender("女");
List<Emp> emps = mapper.getEmpCondition(emp);
emps.forEach(System.out::println);
sqlSession.close();
}
trim标签的使用
trim用于去掉或添加标签中的内容,常用属性:
- prefix:在trim标签中内容的前面添加指定内容
- prefixOverrides:在trim标签中内容的前面去掉指定内容
- suffix:在trim标签中内容的后面添加指定内容
- suffixOverrides:在trim标签中内容的后面去掉指定内容
<select id="getEmpCondition" resultType="com.evan.mybatis.entity.Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and|or">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age} or
</if>
<if test="gender != null and gender != ''">
gender = #{gender}
</if>
</trim>
</select>
测试
@Test
public void test1() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp(0,"李四",19,"男",null);
List<Emp> emps = mapper.getEmpCondition(emp);
emps.forEach(System.out::println);
sqlSession.close();
}
choose、when、otherwise标签的使用
<choose>、<when>、<otherwise> 标签:这些标签类似于Java中的switch-case-default结构。
<choose>标签中的<when>标签表达式结果满足则条件拼接查询,否则在读多个<when>标签中继续判断,如都不满足,则执行<otherwise>标签内容。
注意:when至少有一个,otherwise最多设置一个
<select id="getEmpCondition" resultType="com.evan.mybatis.entity.Emp">
select * from t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="gender != null and gender != ''">
gender = #{gender}
</when>
<otherwise>
gender = '女'
</otherwise>
</choose>
</where>
</select>
测试
@Test
public void test1() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp = new Emp(0,null,0,"男",null);
List<Emp> emps = mapper.getEmpCondition(emp);
emps.forEach(System.out::println);
sqlSession.close();
}
foreach标签的使用
场景1
方法形参为集合,实现批量添加操作。
/**
* 批量添加
* @param emps
* @return
*/
int addEmp(@Param("emps") List<Emp> emps);
<!--
collection属性:要遍历的集合
item属性:遍历集合的过程中能得到每一个具体对象,在item属性中设置一个名字,将来通过这个名字引用遍历出来的对象
separator属性:指定当foreach标签的标签体重复拼接字符串时,各个标签体字符串之间的分隔符
open属性:指定整个循环把字符串拼好后,字符串整体的前面要添加的字符串
close属性:指定整个循环把字符串拼好后,字符串整体的后面要添加的字符串
index属性:这里起一个名字,便于后面引用
遍历List集合,这里能够得到List集合的索引值
遍历Map集合,这里能够得到Map集合的key
-->
<insert id="addEmp">
insert into t_emp(emp_name,age,gender,dept_id) values
<foreach collection="emps" item="e" open="values" separator="," index="myIndex">
<!-- 在foreach标签内部如果需要引用遍历得到的具体的一个对象,需要使用item属性声明的名称 -->
(#{e.empName},#{e.age},#{e.gender},null)
</foreach>
</insert>
测试
@Test
public void test2() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp1 = new Emp(0, "evan", 22, "女", null);
Emp emp2 = new Emp(0, "allen", 23, "男", null);
Emp emp3 = new Emp(0, "john", 24, "女", null);
int result = mapper.addEmp(Arrays.asList(emp1, emp2, emp3));
System.out.println("结果:" + result);
sqlSession.close();
}
场景2
形参为List集合,实现批量更新操作。
/**
* 批量更新
* @param users
* @return
*/
Integer updateUserBatch(@Param("users") List<User> users);
<update id="updateUserBatch" parameterType="User">
<foreach collection="users" item="u" separator=";">
update t_user
<set>
username = #{u.username},password = #{u.password}
</set>
<where>
id = #{u.id}
</where>
</foreach>
</update>
或
<update id="updateEmpBatch" parameterType="Emp">
<foreach collection="emps" item="e" separator=";">
update t_emp set emp_name=#{e.empName} where emp_id=#{e.empId}
</foreach>
</update>
测试
@Test
public void test5() {
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
Emp emp1 = new User(90, "华华", "890", 10, "男", "hh@sian.com");
Emp emp2 = new User(91, "睇睇", "890", 10, "男", "hh@sian.com");
Emp emp3 = new User(92, "倩倩", "890", 10, "男", "hh@sian.com");
List<Emp> list = Arrays.asList(emp1, emp2, emp3);
Integer result = mapper.updateEmpBatch(list);
System.out.println(result);
}
批量更新时需要注意
上面批量插入的例子本质上是一条SQL语句,而实现批量更新则需要多条SQL语句拼起来,用分号分开。也就是一次性发送多条SQL语句让数据库执行。此时需要在数据库连接信息的URL地址中设置:
jdbc.url=jdbc:mysql:///mybatis-example?allowMultiQueries=true
例如:
jdbc.url=jdbc:mysql://localhost:3306/dbtest1?useUnicode=true
&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
&allowMultiQueries=true
关于foreach标签的collection属性
如果没有给接口中List类型的参数使用@Param
注解指定一个具体的名字,那么在collection属性中默认可以使用collection或list来引用这个list集合。这一点可以通过异常信息看出来:
Parameter 'emps' not found. Available parameters are [arg0, collection, list]
在实际开发中,为了避免隐晦的表达造成一定的误会,建议使用@Param
注解明确声明变量的名称,然后在foreach标签的collection属性中按照@Param
注解指定的名称来引用传入的参数。
场景3
形参为数组,实现批量删除操作。
/**
* 批量删除
* @param empNames
* @return
*/
int removeEmp(@Param("empNames") String[] empNames);
- 方式1: 通过形参数组并且where条件为in()方式条件进行批量删除
<delete id="removeEmp">
delete from t_emp where emp_name in
(
<foreach collection="empNames" item="e" separator=",">
#{e}
</foreach>
)
</delete>
或
<delete id="removeEmp">
delete from t_emp where emp_name in
<foreach collection="empNames" separator="," item="e" open="(" close=")">
#{e}
</foreach>
</delete>
- 方式2: 通过形参数组并且where条件为or方式的模糊条件遍历
<delete id="removeEmp" parameterType="java.lang.String">
delete from t_emp
<where>
<foreach collection="empNames" item="e" separator="or">
emp_name like '%${e}%'
</foreach>
</where>
</delete>
测试
@Test
public void test3() {
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
String[] args = {
"evan",
"allen",
"john"};
int result = mapper.removeEmp(args);
System.out.println("结果:" + result);
sqlSession.close();
}
sql标签的使用
sql标签,可以记录一段公共sql片段,在使用的地方通过include标签进行引入。
<!-- 公共sql语句 -->
<sql id="empColumns">emp_id,emp_name,age,gender,dept_id</sql>
<select id="getEmpCondition" resultType="com.evan.mybatis.entity.Emp">
select <include refid="empColumns"/> from t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="gender != null and gender != ''">
gender = #{gender}
</when>
<otherwise>
gender = '女'
</otherwise>
</choose>
</where>
</select>