动态SQL
介绍
- 官方文档中这样写到:
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。 - 需要学习的元素:
if元素
当我们查询员工时,完整得sql语句是这样的:
select * from tbl_employee where id=#{id} and user_name like #{userName} and email=#{email} and gender=#{gender}
但是我们想传入什么字段,sql语句就带上这个字段的值,即当我们只传入一个id时,sql语句就变为:
select * from tbl_employee where id=#{id}
当我们只传入一个id和user_name时,语句就变为:
select * from tbl_employee where id=#{id} and user_name like #{userName}
这种动态的sql该如何实现?
创建一个接口EmployeeMapperDynamicSQL,写上一个getEmpsByConditionIf()方法:
package com.jackma.mybatis.dao;
import com.jackma.mybatis.bean.Employee;
import java.util.List;
public interface EmployeeMapperDynamicSQL {
List<Employee> getEmpsByConditionIf(Employee employee);
}
创建一个EmployeeMapperDynamicSQL.xml文件,写上sql映射语句:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jackma.mybatis.dao.EmployeeMapperDynamicSQL">
<!-- List<Employee> getEmpsByConditionIf(Employee employee);-->
<select id="getEmpsByConditionIf" resultType="emp">
select * from tbl_employee where
<if test="id!=null">
id=#{id}
</if>
-- "表示双引号
<if test="userName!=null and userName != """>
and user_name like #{userName}
</if>
<if test="email!=null">
and email=#{email}
</if>
<if test="gender != null">
and gender=#{gender}
</if>
</select>
</mapper>
其中,要在test属性中写上判断表达式(OGNL),并且遇到特殊的符号要转义。
测试,假设只传入id和user_Name:
@Test
public void test7() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(3, "%o%", null, null);
List<Employee> empsByConditionIf = mapper.getEmpsByConditionIf(employee);
for (Employee emp: empsByConditionIf
) {
System.out.println(emp);
}
} finally {
sqlSession.commit();
}
}
结果:
trim标签(where,set)
where
但是上面这种查询会有一个问题,如果我们查询时只传入user_Name而不传入id,则会报错:
会发现sql语句把后面的and也拼接上了,解决方法有两种,一种是在where后面加上1=1,使得上面的sql语句变为:
select * from tbl_employee where 1=1 and user_name like ?
第二种方法是Mybatis推荐使用的,把原来的where去掉,然后把所有要拼接的动态条件放在一对where标签里,它就会自动把sql语句中的第一个and或者or去掉:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jackma.mybatis.dao.EmployeeMapperDynamicSQL">
<!-- List<Employee> getEmpsByConditionIf(Employee employee);-->
<select id="getEmpsByConditionIf" resultType="emp">
select * from tbl_employee
<where>
<if test="id!=null">
id=#{id}
</if>
<if test="userName!=null and userName != """>
and user_name like #{userName}
</if>
<if test="email!=null">
and email=#{email}
</if>
<if test="gender != null">
and gender=#{gender}
</if>
</where>
</select>
</mapper>
测试:
怎么理解自动把sql语句中的第一个and或者or去掉?让我们调整一下and的顺序:
然后测试中依旧只传入userName,结果:
会发现最后的and并没有删掉,所以以后使用where标签时要注意把and或者or写在前面。
使用trim来实现
如何解决上面使用where标签时把and或or拼在后面的问题?我们可以使用trim标签来实现。先在接口中创建一个方法:
List<Employee> getEmpsByConditionTrim(Employee employee);
trim标签有四个属性,来看一下分别代表什么含义:
- prefix:给拼接好后的整个sql语句添加前缀
- prefixOverrides:把拼接好后的整个sql语句前面多余的字符去掉
- suffix:给拼接好后的整个sql语句添加后缀
- suffixOverrides:把拼接好后的整个sql语句后面多余的字符去掉
sql映射文件,我们使用prefix在前面加个where,使用suffixOverrides来把后尾的and去掉:
<!-- List<Employee> getEmpsByConditionTrim(Employee employee);-->
<select id="getEmpsByConditionTrim" resultType="emp">
select * from tbl_employee
<!-- prefix:给拼接好后的整个sql语句添加前缀
prefixOverrides:把拼接好后的整个sql语句前面多余的字符去掉
suffix:给拼接好后的整个sql语句添加后缀
suffixOverrides:把拼接好后的整个sql语句后面多余的字符去掉-->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} and
</if>
<if test="userName!=null and userName != """>
user_name like #{userName} and
</if>
<if test="email!=null">
email=#{email} and
</if>
<if test="gender != null">
gender=#{gender}
</if>
</trim>
</select>
测试,依旧是仅传入userName,结果:
set
假如我们现在需要更新员工信息,以前在更新的时候需要把全部值都传入,但是现在想实现传入哪些值就更新哪些值。写一个方法updateEmp():
void updateEmp(Employee employee);
sql映射文件:
<!-- void updateEmp(Employee employee);-->
<select id="updateEmp">
update tbl_employee
set
<if test="userName != null">
user_name = #{userName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="gender != null">
gender = #{gender},
</if>
where id = #{id}
</select>
测试:
@Test
public void test9() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(3, "admin", null, null);
mapper.updateEmp(employee);
sqlSession.commit();
} finally {
sqlSession.commit();
}
}
此时会报错:
会发现sql语句中user_name后面多了一个逗号,如何去掉?可以使用set标签把所有if包起来:
<!-- void updateEmp(Employee employee);-->
<update id="updateEmp">
update tbl_employee
<set>
<if test="userName != null">
user_name = #{userName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="gender != null">
gender = #{gender},
</if>
</set>
where id = #{id}
</update>
使用trim来实现
当然上面使用的set我们也可以使用trim来实现:
<!-- void updateEmp(Employee employee);-->
<update id="updateEmp">
update tbl_employee
<trim prefix="set" suffixOverrides=",">
<if test="userName != null">
user_name = #{userName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="gender != null">
gender = #{gender},
</if>
</trim>
where id = #{id}
</update>
choose标签
分支选择,里面有when和otherwise两个标签,与switch case类似,when就相当于case,otherwise就相当于default如果传入了id则使用id来查询,如果传入了userName则使用userName来查询,只会进入其中一个。在接口中写一个方法:
List<Employee> getEmpsByConditionChoose(Employee employee);
sql映射文件:
<!-- List<Employee> getEmpsByConditionChoose(Employee employee);-->
<select id="getEmpsByConditionChoose" resultType="emp">
select * from tbl_employee
<where>
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="userName != null">
user_name = #{userName}
</when>
<when test="email != null">
email = #{email}
</when>
<!-- 如果都不传入,输出男生的员工信息-->
<otherwise>
gender = "男"
</otherwise>
</choose>
</where>
</select>
测试,什么都不传入:
@Test
public void test8() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(null, null, null, null);
List<Employee> empsByConditionChoose = mapper.getEmpsByConditionChoose(employee);
for (Employee emp: empsByConditionChoose
) {
System.out.println(emp);
}
} finally {
sqlSession.commit();
}
}
foreach标签
批量查询
假如我们现在要查询id为1,2,3的员工信息,写一个方法:
List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
sql映射文件:
<!-- List<Employee> getEmpsByConditionForeach(List<Integer> ids);-->
<select id="getEmpsByConditionForeach" resultType="emp">
select * from tbl_employee where id in
<!-- collection:指定要遍历的集合-->
<!-- item:当前遍历的元素赋给哪个值,然后使用#{}就能取出元素-->
<!-- separator:每个元素直接的分隔符-->
<!-- open:遍历的整体拼接一个开始的字符(where id in(1,2,3),遍历整体是123,在前面拼一个"(" )-->
<!-- close:遍历的整体拼接一个结束的字符-->
<foreach collection="ids" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
测试:
@Test
public void test10() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3));
for (Employee emp :
list) {
System.out.println(emp);
}
} finally {
sqlSession.commit();
}
}
批量插入
我们可以使用foreach来实现员工的批量插入,写一个方法addEmps():
void addEmps(@Param("emps")List<Employee> emps);
sql映射文件:
<!-- void addEmps(@Param("emps")List<Integer> emps);-->
<insert id="addEmps">
insert into tbl_employee(user_name,email,gender,d_id)
values
<foreach collection="emps" separator="," item="emp">
(#{emp.userName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
测试:
@Test
public void test11() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emps = new ArrayList<>();
emps.add(new Employee(null, "smith", "女","smith@qq.com", new Department(1)));
emps.add(new Employee(null, "leo", "男","leo@qq.com", new Department(2)));
mapper.addEmps(emps);
} finally {
sqlSession.commit();
}
}
结果:
上面这种方法我们是使用foreach来遍历多个values,当然我们也可以来遍历多个插入语句:
<!-- 需要在数据库连接属性allowMultiQueries设置为true-->
<insert id="addEmps">
<foreach collection="emps" separator=";" item="emp">
insert into tbl_employee(user_name,email,gender,d_id)
values(#{emp.userName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
这种方法适用于批量删除、修改。但是需要在数据库连接属性allowMultiQueries设置为true:
bind绑定
bind可以将OGNL表达式绑定到一个变量中,方便后面引用这个变量的值。拿前面的一个例子举例,通过模糊查询来查找userName中带有o的员工:
此时我们传入的是%o%,若我们此时想只传入o也能实现模糊查询,就可以使用bind:
测试:
结果:
sql和include
使用sql标签可以抽取重用的sql片段,然后使用include标签来引用。拿插入方法来举例,我们来抽取红框中的部分:
<!-- void addEmps(@Param("emps")List<Integer> emps);-->
<!-- 抽取sql-->
<sql id="insertColumn">
user_name,email,gender,d_id
</sql>
<insert id="addEmps">
insert into tbl_employee(
<!-- 引用外部定义的sql-->
<include refid="insertColumn"></include>
)
values
<foreach collection="emps" separator="," item="emp">
(#{emp.userName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>