前言
在实际项目开发中,经常需要根据不同的条件去做不同的CRUD。总不能根据有一个条件就创建一个查询方法吧,再或者,要删除多条数据的时候,也总不能循环去调用删除方法,比如像这样
for(Integer id : ids) {
userDao.deleteUserById(id);
}
这样会不停的获取数据库连接,资源消耗是很大的(第六篇深入对比了这种方式的问题,总之比较严重)。
在Mybatis中就针对这个问题有一个特性,就是动态SQL,可以很简单的解决这个问题。
动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似,Mybatis采用功能强大的基于OGNL的表达式来完成动态SQL。OGNL的表达式可以被用在任意的SQL映射语句中。
在Mybatis中,常用的动态SQL元素包括:
>if
>choose(when、otherwise)
>where
>set
>foreach
>bind
下面就根据每个标签依依做一个示例。
首先搭建Mybatis环境和日志环境搭建mybatis环境和日志环境(参考第一篇和第二篇),并创建如下一张表tb_emp(id、姓名、地址和状态)和相应的持久层对象,插入几条数据:
if标签
if的逻辑就和java语言的条件分支语句if的逻辑是一样的,代表判断,同样也只能接受true或false作为运算结果。
现在就根据地址和状态去做一个员工查询,但是状态是一个可变条件。
mapper文件、dao层接口
<mapper namespace="com.zepal.mybatis.dao.EmpDao">
<resultMap type="com.zepal.mybatis.domain.Emp" id="empMap">
<id column="emp_id" property="empId"/>
<result column="emp_name" property="empName"/>
<result column="emp_addr" property="empAddr"/>
<result column="emp_status" property="empStatus"/>
</resultMap>
<select id="getEmpByAddrAndStatus" parameterType="map" resultMap="empMap">
SELECT * FROM tb_emp
WHERE emp_addr = #{empAddr}
<!-- 注意这里 -->
<if test="empStatus != null">
AND emp_status = #{empStatus}
</if>
;
</select>
</mapper>
public interface EmpDao {
List<Emp> getEmpByAddrAndStatus(Map<String, Object> paramMap);
}
因为typeAliases(参照第二篇配置)的关系,Mybatis可以接收任何java对象和数据类型作为参数,这里以Map作为参数传递,typeAliases已经为常用的java对象作了处理,所以这里parameterType不用全路径。
上面的<if>标签,作为可选条件,如果传进mapper中的empStatus不为null则带上emp_status条件查询。
测试代码及结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("empAddr", "上海");
paramMap.put("empStatus", "ACTIVE");
List<Emp> emps = empDao.getEmpByAddrAndStatus(paramMap);
System.out.println(emps.toString());
sqlSession.close();
}
}
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE emp_addr = ? AND emp_status = ? ;
DEBUG [main] - ==> Parameters: 上海(String), ACTIVE(String)
DEBUG [main] - <== Total: 1
[Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE]]
注释掉empStaus条件或设置为null的结果
//paramMap.put("empStatus", "ACTIVE");
paramMap.put("empStatus", null);
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE emp_addr = ? ;
DEBUG [main] - ==> Parameters: 上海(String)
DEBUG [main] - <== Total: 2
[Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE], Emp [empId=3, empName=王五, empAddr=上海, empStatus=LEAVE]]
很明显,没有带上emp_status条件查询。
if标签中,只有一个test属性,只支持布尔表达式,也就是运算结果只能是true或false,常用的逻辑运算符
运算符 | 说明 |
== | 等于 |
!= | 不等于 |
and | 且 |
or | 或者 |
> | 大于 |
>= | 大于且等于 |
lt | 小于,注意不能是<符号 |
lte | 小于且等于,不能是<=符号 |
choose(when、otherwise)标签
Mybatis提供的choose标签,使用逻辑类似于java中switch语句。原谅我词穷,还真不知道该怎么去假设性的描述这个场景。直接上示例吧。
mapper文件和dao层接口
<?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.zepal.mybatis.dao.EmpDao">
<resultMap type="com.zepal.mybatis.domain.Emp" id="empMap">
<id column="emp_id" property="empId"/>
<result column="emp_name" property="empName"/>
<result column="emp_addr" property="empAddr"/>
<result column="emp_status" property="empStatus"/>
</resultMap>
<select id="listEmpByNameOrAddrOrStatus" parameterType="map" resultMap="empMap">
SELECT * FROM tb_emp
WHERE emp_addr = #{empAddr}
<!-- 注意这里 -->
<choose>
<when test="empName != null">
AND emp_name = #{empName}
</when>
<otherwise>
AND emp_status = 'ACTIVE'
</otherwise>
</choose>
;
</select>
</mapper>
public interface EmpDao {
List<Emp> listEmpByNameOrAddrOrStatus(Map<String, Object> paramMap);
}
测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
//参数
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("empAddr", "上海");
paramMap.put("empName", "李四");
List<Emp> emps = empDao.listEmpByNameOrAddrOrStatus(paramMap);
System.out.println(emps.toString());
sqlSession.close();
}
}
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE emp_addr = ? AND emp_name = ? ;
DEBUG [main] - ==> Parameters: 上海(String), 李四(String)
DEBUG [main] - <== Total: 1
[Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE]]
可以从结果看到执行了<when>标签里面的SQL。支持多个<when>标签哦。
那么我们将empName参数注释掉或设为NULL。看看结果
//paramMap.put("empName", "李四");
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE emp_addr = ? AND emp_status = 'ACTIVE' ;
DEBUG [main] - ==> Parameters: 上海(String)
DEBUG [main] - <== Total: 1
[Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE]]
可以看到执行了<otherwise>标签里面的SQL。执行逻辑完全类似java中的switch语句。<when>标签作条件选择(类似case),如果都为false,则执行<otherwise>(类似default)。otherwise标签可以没有,但是只能有一个<otherwise>,<when>可以有多个。
<when>标签同<if>标签一样,只有一个test属性,只支持布尔表达式,也就是运算结果只能是true或false,常用的逻辑运算符也和上面的if标签一样,这里不赘述。
where标签
在mybatis的映射文件中,where标签可以替代SQL语句的where关键字。下面用一个具体的示例来说明。
mapper文件和dao层接口
<select id="listEmpByStatus" parameterType="string" resultMap="empMap">
SELECT * FROM tb_emp WHERE
<if test="empStatus != null">
emp_status = #{empStatus}
</if>
;
</select>
List<Emp> listEmpByStatus(@Param("empStatus")String empStatus);
说明:注意dao层接口中的注解@Param,它是mybatis中的参数绑定注解。意思是将传递到dao层接口的参数绑定一个参数名叫empStaus(注意区分String后面的empStatus,String后面的参数名可以随便起,加上注解之后,都会为该参数绑定一个参数名叫empStatus)。在没有用Map、List、javabeandeng之外的数据类型作为参数传递时,都请加上这个注解,不然Mybatis无法识别(参数的类型能识别,参数名无法识别)。
测试代码和结果
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
String empStatus = "ACTIVE";
List<Emp> emps = empDao.listEmpByStatus(empStatus);
System.out.println(emps.toString());
sqlSession.close();
}
}
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE emp_status = ? ;
DEBUG [main] - ==> Parameters: ACTIVE(String)
DEBUG [main] - <== Total: 3
[Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE], Emp [empId=4, empName=赵六, empAddr=深圳, empStatus=ACTIVE], Emp [empId=5, empName=杨二, empAddr=深圳, empStatus=ACTIVE]]
这里明显是运行了if标签的。现在我们将参数注释掉或设置为null再看。
//String empStatus = "ACTIVE";
List<Emp> emps = empDao.listEmpByStatus(null);
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE ;
DEBUG [main] - ==> Parameters:
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
<!--注意这里,SQL语法错误-->
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;
由于传递的参数为null,那么if标签里面的预算结果是false,即不执行if标签里面的内容。但是在执行SQL的时候,却把where执行进去了。
注意:不要想着把where关键字加到if标签里面,单个if标签可以解决这个问题,但是多个if标签呢?
那么就该where标签登场了,看法宝:
<select id="listEmpByStatus" parameterType="string" resultMap="empMap">
SELECT * FROM tb_emp
<!-- 注意这里 -->
<where>
<if test="empStatus != null">
emp_status = #{empStatus}
</if>
</where>
;
</select>
测试代码和运行结果
//String empStatus = "ACTIVE";
List<Emp> emps = empDao.listEmpByStatus(null);
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp ;
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 5
[Emp [empId=1, empName=张三, empAddr=北京, empStatus=LEAVE], Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE], Emp [empId=3, empName=王五, empAddr=上海, empStatus=LEAVE], Emp [empId=4, empName=赵六, empAddr=深圳, empStatus=ACTIVE], Emp [empId=5, empName=杨二, empAddr=深圳, empStatus=ACTIVE]]
很明显where关键字没有被拼接到SQL语句上。这就是where的用法。
where标签就是个独立的标签,没有相关属性。建议在以后的mapper文件中,只要用到了动态SQL语句,都要把<where>标签加上。
set标签
set标签同where标签一样,都是可以前置替换SQL中的set关键字。
看下面一个简单的示例
<update id="updateEmp" parameterType="com.zepal.mybatis.domain.Emp">
UPDATE tb_emp SET
<if test="empName != null">
emp_name = #{empName},
</if>
<if test="empAddr != null">
emp_addr = #{empAddr},
</if>
<if test="empStatus != null">
emp_status = #{empStatus}
</if>
WHERE emp_id = 5;
</update>
同where一样,假如说,所有的if标签计算结果都为false,那么SQL语句肯定会执行失败,假如说第二个和第三个if标签判断为false,那么将第一个if标签里的sql语句拼接上之后,又会多一个逗号(,),当然,除非全部if标签都判断为true。
那么现在就可以用set标签来解决这个问题。
<update id="updateEmp" parameterType="com.zepal.mybatis.domain.Emp">
UPDATE tb_emp
<set>
<if test="empName != null">
emp_name = #{empName},
</if>
<if test="empAddr != null">
emp_addr = #{empAddr},
</if>
<if test="empStatus != null">
emp_status = #{empStatus}
</if>
</set>
WHERE emp_id = 5;
</update>
int updateEmp(Emp emp);
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
Emp emp = new Emp();
emp.setEmpName("张三丰");
emp.setEmpAddr("武当山");
//emp.setEmpStatus("ACTIVE");这里是被注释掉了
int result = empDao.updateEmp(emp);
System.out.println("执行update受影响的行 :" + result);
sqlSession.commit();
sqlSession.close();
}
}
DEBUG [main] - ==> Preparing: UPDATE tb_emp SET emp_name = ?, emp_addr = ? WHERE emp_id = 5;
DEBUG [main] - ==> Parameters: 张三丰(String), 武当山(String)
DEBUG [main] - <== Updates: 1
执行update受影响的行 :1
很明显,在第三个if标签判断为false的时候,第二个if标签里多的逗号(,)被set标签消除掉了。
foreach标签
上面的几个标签出现了条件判断中,那么当我们面临一个集合参数的时候,就该foreach登场了。foreach通常用在构建in条件语句的时候。
看下面的示例
<delete id="deleteEmp" parameterType="list">
DELETE FROM tb_emp WHERE emp_id in
<foreach collection="list" index="index" item="item"
open="(" separator="," close=");">
#{item}
</foreach>
</delete>
int deleteEmp(List<Integer> empIds);
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
ids.add(5);
int result = empDao.deleteEmp(ids);
System.out.println("执行delete受影响的行 : " + result);
sqlSession.commit();
sqlSession.close();
}
}
DEBUG [main] - ==> Preparing: DELETE FROM tb_emp WHERE emp_id in ( ? , ? , ? );
DEBUG [main] - ==> Parameters: 1(Integer), 3(Integer), 5(Integer)
DEBUG [main] - <== Updates: 3
执行delete受影响的行 : 3
从上面可以看到,执行的sql语句和执行结果。
foreach标签功能特别智能,可以配合其它标签做出特别多不可思议的组合,所以需要多加练习和思考(操作务必要够骚^_^)。
foreach标签紧允许指定一个集合作为参数。那么,假如说在dao层向mapper文件传递了多个参数,就需要用@Param绑定参数
了。如下所示:
int deleteEmp(@Param("list")List<Integer> empIds, @Param("empName")String empName);
说明:这里的list绑定并不是一定要叫list,可以是其它任意名字,但是要和foreach中的collection属性配对。
foreach标签中的属性说明
属性名 | 说明 |
collection | 用于标识传入的集合类型参数,即获取循环体,当只有一个参数时,可以是默认list不用绑定,当多个参数时一定要用@Param注解绑定参数 |
index | 在遍历循环体的时候,为元素的索引,从0开始 |
item | 在遍历循环体的时候,单个元素,可以理解为循环体中单个元素的别名 |
open | 遍历之前的分隔符,如上面示例的"(" |
separator | 在遍历循环体时,每个元素会被这个属性分割,如(1,3,5)被逗号分割 |
close | 在遍历完成之后,结束时的分隔符,如上面示例的")" |
上面所列的属性按需取用,但是collection是一定要有的。
bind标签(这个感觉不怎么常用)
bind元素可以从OGNL表达式中国创建一个变量并将其绑定到上下文。
如下所示:
<select id="getEmpByLikeName" parameterType="string" resultMap="empMap">
<bind name="pattern" value=" '%' + _parameter + '%' "/>
SELECT * FROM tb_emp WHERE emp_name LIKE #{pattern};
</select>
Emp getEmpByLikeName(String empName);
public class Test {
public static void main(String[] args) {
InputStream is = Test.class.getResourceAsStream("/mybatis/mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new
SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession = sqlSessionFactory.openSession();
EmpDao empDao = sqlSession.getMapper(EmpDao.class);
Emp emp = empDao.getEmpByLikeName("李");
System.out.println(emp.toString());
sqlSession.close();
}
}
DEBUG [main] - ==> Preparing: SELECT * FROM tb_emp WHERE emp_name LIKE ?;
DEBUG [main] - ==> Parameters: %李%(String)
DEBUG [main] - <== Total: 1
Emp [empId=2, empName=李四, empAddr=上海, empStatus=ACTIVE]
用法就是在当前标签中的上下文中,可以定义一段SQL,并可以获取在dao层传递的参数,通过name属性标识,被当前上下文引用。
到此,Mybatis的动态SQL所有的标签就介绍完了,动态SQL为Mybatis的操作提供了很多智能的操作,因此操作一定要骚。
此篇完结