(一)动态SQL简介
使用JDBC对数据库进行操作,通常需要根据需求手动的拼接SQL或重新编写SQL语句,这是一项非常无聊和麻烦的操作,但是Mybatis提供了对SQL语句动态组装的功能,恰好解决这一项麻烦的操作。
参考: Mybatis官方文档
Mybatis动态SQL语句中的主要元素
元素 | 说明 |
---|---|
<if> | 用于单条件分支判断 |
<choose> <when> <otherwise> | 用于多条件分支判断,类似Java中的switch语句 |
<where> <trim><set> | 辅助元素用于处理sql拼装、特殊字符等问题 |
<foreach> | 循环语句用于in关键字列举条件中 |
<bind> | 从OGNL表达式中创建一个变量将其绑定到上下文,用于模糊查询 |
项目结构:
1.<if>元素
如果有多个<if test=”表达式”>元素,且test右边的表达式为true,则SQL语句进行动态组装。
实例演示
比如查询客户信息,可以按用户名或职位单字段查询,也可以用户名和职位组合查询(SQL动态组装条件)
01.在CustomerMapper.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">
<!-- namespace+id确定一条SQL语句 -->
<mapper namespace="com.wang.mapper.CustomerMapper">
<select id="findCustomerByNameAndJobs"
parameterType="com.wang.po.Customer"
resultType="com.wang.po.Customer">
select*from t_customer where 1=1
<if test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</if>
<!--jobs表示Customer对象的属性不为空-->
<if test="jobs!=null and jobs!=''">
and jobs=#{jobs}
</if>
</select>
</mapper>
02.在mybatis-config.xml文件中引入CustomerMapper.xml文件
<mappers>
<mapper resource="com/wang/mapper/CustomerMapper.xml" />
</mappers>
03.创建测试类MybatisTest.java
@Test
public void findCustomersByNameAndJobs() {
// 第一次查询会话
SqlSession sqlSession = MybatisUtils.getSession();
Customer customer = new Customer();
// 为customer对象属性设值,Mapper.xml使用OGNL可以检测username是否设值
customer.setUsername("李四");
customer.setJobs("程序员");
List<Customer> customers = sqlSession.selectList("com.wang.mapper.CustomerMapper.findCustomerByNameAndJobs", customer);
for (Customer customer1 : customers) {
System.out.println(customer1);
}
sqlSession.close();
}
结果
01.当只在用户名输入框中输入李四时:SQL语句只组装username字段条件
DEBUG [main] - ==> Preparing: select*from t_customer where 1=1 and
username like concat('%',?,'%')
DEBUG [main] - ==> Parameters: 李四(String)
DEBUG [main] - <== Total: 1
Customer [id=2, username=李四, jobs=程序员, phone=183666666]
02.当只在职业文本框输入程序员时:SQL语句只组装jobs字段条件
DEBUG [main] - ==> Preparing: select*from t_customer where 1=1 and jobs=?
DEBUG [main] - ==> Parameters: 程序员(String)
DEBUG [main] - <== Total: 1
Customer [id=2, username=李四, jobs=程序员, phone=183666666]
03.同时在用户和职业文本框输入李四和程序员时:SQL组装username和jobs两个条件
DEBUG [main] - ==> Preparing: select*from t_customer where 1=1
and username like concat('%',?,'%') and jobs=?
DEBUG [main] - ==> Parameters: 李四(String), 程序员(String)
DEBUG [main] - <== Total: 1
Customer [id=2, username=李四, jobs=程序员, phone=183666666]
2.<choose><when><otherwise>元素
<choose><when><otherwise>元素类似Java中switch语句,满足其中的一个条件即可实现SQL动态组装。
实例演示
比如:01.用户名不为空只能使用用户名查询。02.职业不为空只能使用职业来查询,03.用户名和职业都为空,那么只能按电话号码查询。
01.在CustomerMapper.xml中编写动态SQL语句
<select id="findCustomerByNameOrJobs"
parameterType="com.wang.po.Customer"
resultType="com.wang.po.Customer">
select*from t_customer where 1=1
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<!--jobs表示Customer对象的属性不为空 -->
<when test="jobs!=null and jobs!=''">
and jobs=#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
02.在mybatis-config.xml文件中引入CustomerMapper.xml文件
<mappers>
<mapper resource="com/wang/mapper/CustomerMapper.xml" />
</mappers>
03.创建测试类MybatisTest.java
@Test
public void findCustomersByNameOrJobs() {
// 第一次查询会话
SqlSession sqlSession = MybatisUtils.getSession();
Customer customer = new Customer();
// 为customer对象属性设值,Mapper.xml使用OGNL可以检测username是否设值
customer.setUsername("李四");
customer.setJobs("程序员");
List<Customer> customers = sqlSession.selectList("com.wang.mapper.CustomerMapper.findCustomerByNameOrJobs", customer);
for (Customer customer1 : customers) {
System.out.println(customer1);
}
sqlSession.close();
}
结果:
注意:假设同时输入李四和程序员,SQL语句根据CustomerMapper.xml中
username和jobs配置先后决定动态组装哪个字段。
比如CustomerMapper.xml中先配置
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>所以先先判断username,即先判断用户名
01.出入用户名为李四,职业为程序员SQL语句动态组装为以下形式:
DEBUG [main] - ==> Preparing: select*from t_customer
where 1=1 and username like concat('%',?,'%')
DEBUG [main] - ==> Parameters: 李四(String)
DEBUG [main] - <== Total: 1
Customer [id=2, username=李四, jobs=程序员, phone=183666666]
02.用户名和职业都不输入SQL语句动态组装为以下形式:
DEBUG [main] - ==> Preparing: select*from t_customer where 1=1
and phone is not null
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
Customer [id=1, username=张三, jobs=学生, phone=183999999]
Customer [id=2, username=李四, jobs=程序员, phone=183666666]
Customer [id=3, username=王五, jobs=医生, phone=183888888]
3.<where>、<trim> 元素用来代替”where 1=1”
01.<where> 的使用
<select id="findCustomerByNameOrJobs"
parameterType="com.wang.po.Customer"
resultType="com.wang.po.Customer">
select*from t_customer
<where>
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<!--jobs表示Customer对象的属性不为空 -->
<when test="jobs!=null and jobs!=''">
and jobs=#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</where>
</select>
02.<trim> 的使用
属性 | 说明 |
---|---|
prefix | 表示使用where连接后面的sql |
prefixOverrides | 去除特殊字符。比如sql中的and和or等 |
<select id="findCustomerByNameOrJobs"
parameterType="com.wang.po.Customer"
resultType="com.wang.po.Customer">
select*from t_customer
<trim prefix="where" prefixOverrides="and">
<choose>
<when test="username!=null and username!=''">
and username like concat('%',#{username},'%')
</when>
<!--jobs表示Customer对象的属性不为空 -->
<when test="jobs!=null and jobs!=''">
and jobs=#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</trim>
</select>
4.<set> 元素代替SQL语句中的关键字set
<set> 元素的作用:动态的修改数据表中的字段。比如Hebernate更新某个对象需要发送所有字段给持久层对象,把持久层的所有都更新了一遍。在开发过程中往往不需要把数据库中的字段全部更新一遍,因此我们使用Mybatis动态SQL语句更新需要更新的数据,这样增强了程序执行效率。
01.<set> 的使用(一条动态SQL可以在多个场景引用)
实例演示
<update id="updateCustomer" parameterType="com.wang.po.Customer">
update t_customer
<set>
<if test="username !=null and username!=''">
username=#{username},
</if>
<if test="jobs !=null and jobs!=''">
jobs=#{jobs},
</if>
<if test="phone !=null and phone!=''">
phone=#{phone}
</if>
<if test="address !=null and address!=''">
address=#{address}
</if>
</set>
where id=#{id}
</update>
@Test
public void updateCustomersByNameOrJobsOrAddress() {
// 第一次查询会话
SqlSession sqlSession = MybatisUtils.getSession();
Customer customer = new Customer();
// 为customer对象属性设值,Mapper.xml使用OGNL可以检测username是否设值
customer.setId(3);
//修改id为3的电话号码
customer.setPhone("183121212");
customer.setJobs("程序员");
int rows=sqlSession.update("com.wang.mapper.CustomerMapper.updateCustomer", customer);
if(rows>0) {
System.out.println("更新成功!");
}else {
System.out.println("更新失败!");
}
sqlSession.commit();
sqlSession.close();
}
结果
注:修改两个字段
DEBUG [main] - ==> Preparing: update t_customer SET jobs=?, phone=?
where id=?
DEBUG [main] - ==> Parameters: 程序员(String), 183121212(String), 3(Integer)
DEBUG [main] - <== Updates: 1
更新成功!
5.<foreach> 元素用于查询一个指定范围类数据
<foreach> 元素主要属性
属性 | 说明 |
---|---|
item | 表示循环获取的元素 |
index | 表示当前元素在集合中的位置(即集合中的索引) |
collection | 配置查询参数的类型(注:参数类型要小写) |
open和close | 配置的是以什么符号将这些集合元素包装起来 |
separator | 使用逗号分割元素 |
比如:查询t_customer表中id小于或等于2的用户信息
01.CustomerMapper.xml
<select id="findCustomerByIds" parameterType="List"
resultType="com.wang.po.Customer">
select*from t_customer where id in
<foreach item="id" index="index" collection="list" open="("
separator="," close=")">#{id}</foreach>
</select>
02.MybatisTest.java
@Test
public void findCustomerByIds() {
SqlSession sqlSession = MybatisUtils.getSession();
// 创建List集合对象用来保存id
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
List<Customer> customers = sqlSession.selectList("com.wang.mapper.CustomerMapper" + ".findCustomerByIds", ids);
for (Customer customer : customers) {
System.out.println(customer);
}
sqlSession.close();
}
结果
DEBUG [main] - ==> Preparing: select*from t_customer where id in ( ? , ? )
DEBUG [main] - ==> Parameters: 1(Integer), 2(Integer)
DEBUG [main] - <== Total: 2
Customer [id=1, username=张三, jobs=学生, phone=183999999]
Customer [id=2, username=李四, jobs=程序员, phone=183666666]
6.<bind> 元素等价于concat()函数都是用于模糊查询
比如:模糊查询t_customer数据表用户名为“李”的客户信息
01.CustomerMapper.xml
<select id="findCustomerByIds" parameterType="com.wang.po.Customer"
resultType="com.wang.po.Customer">
<!-- value:填写Customer中的username属性 -->
<bind name="name" value="'%'+username+'%'"/>
select*from t_customer where username like #{name}
</select>
02.MybatisTest.java
@Test
public void findCustomerByName() {
SqlSession sqlSession = MybatisUtils.getSession();
Customer customer=new Customer();
customer.setUsername("李");
List<Customer> customers = sqlSession.selectList("com.wang.mapper."
+ "CustomerMapper" + ".findCustomerByIds", customer);
for (Customer customer1 : customers) {
System.out.println(customer1);
}
sqlSession.close();
}
结果
DEBUG [main] - ==> Preparing: select*from t_customer where username like ?
DEBUG [main] - ==> Parameters: %李%(String)
DEBUG [main] - <== Total: 1
Customer [id=2, username=李四, jobs=程序员, phone=183666666]