前言
动态SQL是MyBatis的一个强大特性,它在一定程度上简化了我们对查询条件的各种情况的处理,什么意思呢?比如查询用户列表,什么参数都不传的情况下查询所有,传入性别参数就按性别查询,传入用户角色就按角色查询,那么这种情况下如果是在传统JDBC访问数据库的场景中实现,我们要写几个判断分支,进行SQL语句的拼接,还有确保SQL语句各关键词之间空格隔开、不能多或少括号、逗号什么的,这是一个比较复杂的过程。MyBatis提供了动态SQL机制,虽然也是根据条件拼接SQL语句,但是更直观、更智能,甚至可以通过foreach拼接固定结构的查询条件。接下来,我们就一起学习MyBatis中的动态SQL。
新建数据库表UserInfo,实体类User,数据层接口UserMapper,SQL映射文件UserMapper.xml,服务层接口UserService,服务层接口实现类UserServiceImpl,测试类Test。(此文章是默认读者的项目中已经搭建好MyBatis开发环境,并且创建了核心配置文件mybatis-config.xml,没有做这两项准备工作的朋友请自行搭建,新手朋友可参考之前的两篇博文进行环境搭建。)
首先列出来所需要的代码
数据库脚本
DROP TABLE IF EXISTS `UserInfo`;
CREATE TABLE `UserInfo` (
`ID` int(6) NOT NULL AUTO_INCREMENT,
`UserCode` varchar(255) DEFAULT NULL,
`UserName` varchar(25) DEFAULT NULL,
`UserPwd` varchar(25) DEFAULT NULL,
`UserGender` varchar(5) DEFAULT NULL,
`UserBirthday` datetime DEFAULT NULL,
`UserPhone` varchar(25) DEFAULT NULL,
`UserAddress` varchar(255) DEFAULT NULL,
`UserRole` bigint(20) DEFAULT NULL,
`UserCreateBy` bigint(20) DEFAULT NULL,
`UserCreateTime` datetime DEFAULT NULL,
`UserModifyBy` bigint(20) DEFAULT NULL,
`UserModifyTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`),
KEY `fk_user_role` (`UserRole`),
CONSTRAINT `fk_user_role` FOREIGN KEY (`UserRole`) REFERENCES `RoleInfo` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of UserInfo
-- ----------------------------
INSERT INTO `UserInfo` VALUES ('1', '10001', '张三', '12345', '男', '1992-03-15 00:00:00', '18210825918', '北京市海淀区', '1', '1', '2018-08-20 11:50:38', '1', '2018-08-24 10:12:57');
INSERT INTO `UserInfo` VALUES ('2', '10002', '李四', '123456', '女', '1996-08-16 00:00:00', '15939305781', '北京市朝阳区', '2', '1', '2018-08-20 11:52:25', '2', '2018-08-24 10:25:02');
INSERT INTO `UserInfo` VALUES ('3', '10001', '王五', '12345', '男', '1992-03-15 00:00:00', '18210825918', '北京市海淀区', '1', '1', '2018-08-20 11:50:38', '1', '2018-08-24 10:12:57');
INSERT INTO `UserInfo` VALUES ('4', '10002', '赵六', '123456', '女', '1996-08-16 00:00:00', '15939305781', '北京市朝阳区', '2', '1', '2018-08-20 11:52:25', '2', '2018-08-24 10:25:02');
INSERT INTO `UserInfo` VALUES ('5', '10001', '刘七', '12345', '男', '1992-03-15 00:00:00', '18210825918', '北京市海淀区', '1', '1', '2018-08-20 11:50:38', '1', '2018-08-24 10:12:57');
INSERT INTO `UserInfo` VALUES ('6', '10002', '孙八', '123456', '女', '1996-08-16 00:00:00', '15939305781', '北京市朝阳区', '2', '1', '2018-08-20 11:52:25', '2', '2018-08-24 10:25:02');
User实体类
package Entity;
public class User {
private int id;
private String userCode;
private String userName;
private String userPwd;
private String userGender;
private String userBirthday;
private String userPhone;
private String userAddress;
private int userRole;
private int userCreateBy;
private String userCreateTime;
private int userModifyBy;
private String userModifyTime;
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
private int age;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPwd() {
return userPwd;
}
public void setUserPwd(String userPwd) {
this.userPwd = userPwd;
}
public String getUserGender() {
return userGender;
}
public void setUserGender(String userGender) {
this.userGender = userGender;
}
public String getUserBirthday() {
return userBirthday;
}
public void setUserBirthday(String userBirthday) {
this.userBirthday = userBirthday;
}
public String getUserPhone() {
return userPhone;
}
public void setUserPhone(String userPhone) {
this.userPhone = userPhone;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
public int getUserRole() {
return userRole;
}
public void setUserRole(int userRole) {
this.userRole = userRole;
}
public int getUserCreateBy() {
return userCreateBy;
}
public void setUserCreateBy(int userCreateBy) {
this.userCreateBy = userCreateBy;
}
public String getUserCreateTime() {
return userCreateTime;
}
public void setUserCreateTime(String userCreateTime) {
this.userCreateTime = userCreateTime;
}
public int getUserModifyBy() {
return userModifyBy;
}
public void setUserModifyBy(int userModifyBy) {
this.userModifyBy = userModifyBy;
}
public String getUserModifyTime() {
return userModifyTime;
}
public void setUserModifyTime(String userModifyTime) {
this.userModifyTime = userModifyTime;
}
}
数据层接口UserMapper
package Dao;
public interface UserMapper {
}
SQL映射文件UserMapper.xml
<?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指定了命名空间-->
<mapper namespace="Dao.UserMapper">
</mapper>
服务层接口UserService
package Service;
public interface UserService {
}
服务层接口实现类UserServiceImpl
package ServiceImpl;
/**
* 用户服务接口实现类
* */
public class UserServiceImpl implements UserService {
}
SqlSession连接工具类
package BaseUtil;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
private static SqlSessionFactory factory;
/**
* 静态块,创建SqlSessionFactory对象
* */
static {
InputStream inputStream=null;
try {
inputStream= Resources.getResourceAsStream("mybatis-config.xml");
factory= new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
inputStream.close();
} catch (IOException e) {
}
}
}
/**
* 返回连接
* */
public static SqlSession getSqlSession(){
return factory.openSession(false); //开启事务控制
}
/**
* 关闭连接
* */
public static void close(SqlSession sqlSession){
if (sqlSession!=null){
sqlSession.close();
}
}
}
一、使用if实现动态SQL
见文知义,if标签就是做简单的条件判断,它有一个test属性指定一个表达式,若满足表达式的结果就将标签内的内容拼接到SQL语句后面,是最简单的动态SQL。假定现在有一个需求,查询用户,默认无where条件,如果传入的用户名不是null并且不是空字符串,则按用户名查找。
在数据层接口UserMapper定义方法如下
public List<User> getUserByIf(@Param("userName")String name); //使用if创建动态SQL语句
在SQL映射文件UserMapper.xml添加如下SQL语句映射块
<select id="getUserByIf" resultType="User">
SELECT * FROM UserInfo WHERE 1=1
<if test="userName!=null and userName!=''">
AND UserName=#{userName}
</if>
</select>
在服务层接口UserService定义方法如下(其实是跟数据层接口一样的,只不过它的实现类要获取连接、管理事务,调用数据层)
public List<User> getUserByIf(@Param("userName")String name); //使用if创建动态SQL语句
在服务层实现类UserService实现上一步定义的方法
@Override
public List<User> getUserByIf(@Param("userName") String name) {
SqlSession sqlSession=MyBatisUtil.getSqlSession();
List<User> userList=sqlSession.getMapper(UserMapper.class).getUserByIf(name);
MyBatisUtil.close(sqlSession);
return userList;
}
在测试类Test中添加如下代码
List<User> userList=new ArrayList<>();
System.out.println("传入参数为null的情况");
userList=new UserServiceImpl().getUserByIf(null);
for (int i = 0; i <userList.size() ; i++) {
System.out.println("用户名:"+userList.get(i).getUserName());
}
System.out.println();
System.out.println("传入参数为空的情况");
userList=new UserServiceImpl().getUserByIf("");
for (int i = 0; i <userList.size() ; i++) {
System.out.println("用户名:"+userList.get(i).getUserName());
}
System.out.println();
System.out.println("传入参数‘张三’");
userList=new UserServiceImpl().getUserByIf("张三");
for (int i = 0; i <userList.size() ; i++) {
System.out.println("用户名:"+userList.get(i).getUserName());
}
运行结果
可以 看到,只有我们传入的参数不为null而且不是空字符串的情况下,才会拼接上查询条件,因为我们用户名是张三的只有一条记录,所以只查出一条。注意在if标签test属性中写判断式时,userName是传入的参数名,而且这里不用加#{},and前后要有空格。
二、使用where+if实现动态SQL
在上面的例子中,我们使用if实现动态SQL拼接语句,细心的朋友可以发现,我们在if标签之前,也就是固定的SQL语句最后加了一个“where 1=1”,这样不是多此一举吗?为什么要加它呢?上面我们介绍了if标签是在满足test表达式的情况下,在原来的SQL语句基础上再拼接一段语句,可以看到我们的if标签中追加的语句都是以AND开头,如果没有“where 1=1”,那么如果在满足了test表达式的情况下拼接SQL语句,此时的语句就变成了“select * from UserInfo and UserName='xxx';”,这条语句执行起来将是错误的SQL语句,因为不符合语法结构。那么难道我们非要加一句像“where 1=1”这样的废话吗?当然不是,MyBatis提供了更智能的解决方案,where标签和if标签的结合。
先看一下它的结构
<where>
<if test="表达式">AND xxx</if>
<if test="表达式">AND xxx</if>
<if test="表达式">OR xxx</if>
</where>
where标签并不会直接拼接到SQL语句后面,只有当它里面的任意一个if标签有返回结果(即满足任意一个)的时候,才会拼上一个where关键字,然后后面接上满足条件的if标签中的语句内容(一个或多个)。where会自动剔除返回的语句字符串中开头的and或者or关键字,比如上面这个where+if结构中三个if标签均满足条件,那么返回的语句字符串是AND xxx AND xxx OR xxx,此时where标签会为我们剔除语句开头的AND,最后的结果就是“SQL语句 where xxx AND xxx OR xxx”,这样就解决了上面出现的问题,使得动态SQL的衔接更加方便。如果所有的if都不满足,将不会出现where语句。
下面依然以上一个需求为例,只需要把UserMapper.xml中刚才的那个SQL映射块改成以下内容,其他的什么都不用动
<select id="getUserByIf" resultType="User">
SELECT * FROM UserInfo
<where>
<if test="userName!=null and userName!=''">
AND UserName=#{userName}
</if>
</where>
</select>
运行依然是没问题的,很简单。
三、使用trim+if实现动态SQL
MyBatis还提供了另外一种更为灵活的元素trim,完全可以取代上一步使用where+if的做法。trim元素也会识别其标签内的if元素是否有返回值,只要有一个可以匹配上(当然并不仅限于一个,这点与where元素一样),就会在自己包含的内容前加一个前缀,也可以在包含的内容后面加一个后缀,也可以指定在内容开头删除什么元素、在内容最后删除什么元素。
trim元素主要用到四个属性
prefix:前缀,如果所包含的if标签有返回内容,则在返回的内容前追加指定的前缀,比如where;
suffix:后缀,同上,只不过是在返回的内容后追加指定的后缀,比如另一个固定条件;
prefixOverrides:指定返回的内容首部进行删除(忽略)的内容,比如and、or这种;
suffixOverrides:指定返回的内容尾部进行删除(忽略)的内容,比如update语句中set元素“列名=值”后面的逗号。
来一个例子就能彻底明白,依然更改上一步的需求,只改映射文件,其他的不用动
<select id="getUserByIf" resultType="User">
SELECT * FROM UserInfo
<trim prefix="where" suffix="and UserGender='男'" prefixOverrides="and | or">
<if test="userName!=null and userName!=''">
AND UserName=#{userName}
</if>
</trim>
</select>
此时我们传入null、空字符串的时候,结果跟之前是一样的,因为此时trim包含的if标签不满足条件,所以不返回内容,也就不会加上where前缀、追加后续内容等,所以SQL语句就是“select * from UserInfo”。我们把数据库中张三的性别改为女,传入参数“张三”,发现差不多张三的用户名了,因为此时trim标签中的if标签有返回内容,我们又指定了忽略返回内容开头的and或者or,所以trim标签将返回内容开头的“and”去除,然后加上一个前缀where,加上一个后缀“and UserGender='男'”,最终的SQL语句是
select * from UserInfo where UserName='张三' and UserGender='男';
跟我们之前使用传统JDBC操作数据库的方式相比,是不是很强大、方便呢?
四、使用set+if实现动态SQL(用于更新操作)
set元素与where元素的用法可以说是一模一样,只不过set元素是用在更新语句中的。它的功能与where差不多,在包含的语句内容前加上一个set关键字,并且如果所包含的内容是以逗号结尾的,会自动剔除,再配合if标签,只更新满足条件的字段。
只贴出结构,不再进行代码测试,因为与where是一模一样的。
<update id="updateDemo">
UPDATE UserInfo
<set>
<if test="userName!=null and userName!=''">UserName=#{userName},</if>
略……
</set>
</update>
五、使用trim+if实现动态SQL(更新操作)
与改造where+if是一样的,只不过把前缀换成set,忽略尾部内容是“,”就可以了。
只贴出语法结构,不再测试。只要你学会了一个,都可以灵活变通。
<update id="updateDemo">
UPDATE UserInfo
<trim prefix="set" suffixOverrides=",">
<if test="userName!=null and userName!=''">UserName=#{userName},</if>
略……
</trim>
</update>
六、使用foreach迭代实现动态SQL(传入数组)
foreach元素主要用于拼接固定格式的语句部分,比如这样的一条语句
select * from UserInfo where ID in (1,2,3,4,5);
这条语句的in以后的内容,完全可以换成foreach元素进行迭代的方式进行拼接,具体什么意思呢?通过示例讲解。
在数据层接口UserMapper中定义如下方法
public List<User> getUserByArray(Integer[] userIds); //传入一组用户ID,进行查询
在SQL映射文件中添加如下内容
<select id="getUserByArray" resultType="User">
SELECT * FROM UserInfo WHERE ID IN
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
在服务层接口定义如下方法
public List<User> getUserByArray(Integer[] userIds); //传入一组用户ID,进行查询
在服务层实现类实现上面定义的方法
@Override
public List<User> getUserByArray(Integer[] userIds) {
SqlSession sqlSession=MyBatisUtil.getSqlSession();
List<User> userList=sqlSession.getMapper(UserMapper.class).getUserByArray(userIds);
MyBatisUtil.close(sqlSession);
return userList;
}
在测试类添加如下代码
System.out.println();
System.out.println("传入数组类型进行迭代拼接语句");
Integer[] userIds=new Integer[]{1,2,3,4};
userList=new UserServiceImpl().getUserByArray(userIds);
for (int i = 0; i <userList.size() ; i++) {
System.out.println("用户名:"+userList.get(i).getUserName());
}
运行结果会查出ID在1、2、3、4其中的用户
那么这个过程发生了什么样的一件事呢?当我们将数组作为参数传到SQL映射文件中的语句块时,我们在未完成的SQL语句后面加了一个foreach元素,collection属性是指要遍历的元素的类型,数组是array,集合是list;item属性是集合中当前元素的别名,代指当前迭代到的元素内容;open属性指foreach元素所包含的内容以什么开始,这个结合场景决定,这里是in语句,后面当然要紧跟左括号了,所以指定open为"(";close属性指foreach元素包含的内容以什么结尾,这里肯定是右括号")";separator属性指每迭代一个元素,以什么分隔,既然是in语句那么当然是以逗号隔开了。通过上述过程,我们的foreach元素先来一个左括号,然后迭代到数组中的第一个元素1,紧接着拼一个逗号,再迭代第二个元素2……最后以一个右括号结尾,最终语句如下
select * from UserInfo where ID in (1,2,3,4)
七、使用foreach迭代实现动态SQL(传入集合)
与传入数组一模一样,只需要将collection属性指定为list,其余的完全一样,不作多余的介绍。
八、使用foreach迭代实现动态SQL(传入Map)
假如这种情形,要查询ID在1、2、3、4中,并且性别是男的用户,按照以往的方式可能在方法中传入两个参数,一个数组/集合和一个性别。其实这可以封装成一个Map,put一个性别,put一个List,那么我们怎样遍历到Map中的List呢?看示例。
在数据层接口UserMapper中定义如下方法
public List<User> getUserBy_Map(Map map); //传入Map迭代其中的List
在SQL映射文件中添加如下内容
<select id="getUserBy_Map" resultType="User">
SELECT * FROM UserInfo WHERE UserGender=#{gender} AND ID IN
<foreach collection="idList" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
在服务层接口定义同样的方法,在其实现类调用数据层,省略。
在测试类添加如下代码
System.out.println();
System.out.println("传入Map集合进行迭代");
Map<String,Object> map=new HashMap<>();
List<Integer> idList=new ArrayList<>();
idList.add(1);
idList.add(2);
idList.add(3);
idList.add(4);
map.put("gender","男");
map.put("idList",idList);
userList=new UserServiceImpl().getUserBy_Map(map);
for (int i = 0; i <userList.size() ; i++) {
System.out.println("用户名:"+userList.get(i).getUserName());
}
运行结果会查出ID是1、2、3、4其中任意一个数字的、并且性别是男的用户
s上述示例中,我们传入了一个Map作为参数,Map中有一个String类型的性别,一个List类型的ID集合,性别我们可以直接拿过去用。我们对这个ID集合进行遍历的时候,只需要把collection属性指定为此集合在Map中对应的Key,即idList,而不能指定为array或者list,其余的都一样。
其实,MyBatis在进行参数入参的时候,都会把它封装成一个Map,不管是单个字符串参数、还是对象参数、数组参数、集合参数等。封装后的Map,Key就是参数名,Value就是这个参数的值。
MyBatis对于入参情况的总结:
1、入参为基本类型:变量名作为Key,值作为Value;
2、入参为对象:对象的属性名作为Key,值作为Value;
3、入参为List:默认以“list”作为Key,此List作为Value;
4、入参为数组:默认“array”作为Key,此数组作为Value;
5、入参为Map:键值不变。
九、使用choose-when-otherwise实现动态SQL
这个结构同Java中的switch一样,跟JSTL中的choose标签是一样的,之匹配一个结果就跳出当前choose。这里不做过多的介绍了。
结尾
以上示例,有的情况在真正的开发中并不是最好的方案,只是为了让大家理解这个东西、知道有这么个东西,具体的用法还是要根据需求灵活变通。