-
在SQL语句的where条件子句中,往往需要进行一些判断。例如,按名称进行模糊查询,如果传入的参数为空,查询结果可能为空的。而实际上,当参数为空时,通常希望查出全部的信息。使用Mybatis框架提供的动态SQL,就可以轻松解决这一问题。具体来说就是使用动态SQL,增加一个判断,当参数不符合时,就不判断此查询条件。
-
MyBatis的动态SQL是基于OGNL表达式的,MyBatis中用于实现动态SQL的元素主要包括 if、
choose(when, otherwise), trim,where,set, foreach等。
目录
- if 元素
- where, if 元素
- set, if元素
- trim 元素
- choose, when,otherwise元素
- foreach元素
1. if 元素
if 元素是简单的条件判断,可用来实现某些简单的条件选择。 以数据库eshop中数据表user_info为例,要求按用户名模糊查询。如果输入用户名j,则查询用户名包含j的用户信息;如果没有输入,则查询所有用户。
使用 if 元素实现这个示例的步骤如下所示。
(1)创建项目mybatis7
(2)在映射文件UserInfoMapper.xml中,添加一个id为 findUserInfoByUserNameWithIf 的 select 元素。
<?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.mybatis.mapper.UserInfoMapper">
<!-- 动态SQL之if元素 -->
<select id="findUserInfoByUserNameWithIf" parameterType="UserInfo" resultType="userInfo">
select *from user_info ui
<if test="userName!=null and userName!=''">
where ui.userName like CONCAT('%',CONCAT(#{userName},'%'))
</if>
</select>
</mapper>
- 在上述select 元素中,使用 if元素编写了动态SQL语句。if 元素会对userName属性进行非空判断,如果传入的查询条件成立,即userName非空,就会将where 子句拼装到select 语句中,否则就会忽略where 子句。
(3)在com.mybatis.mapper包中,创建接口UserInfoMapper并声明方法。
package com.mybatis.mapper;
import java.util.List;
import com.mybatis.pojo.UserInfo;
public interface UserInfoMapper {
//
public List<UserInfo> findUserInfoByUserNameWithIf(UserInfo ui);
}
(4)在测试类MybatisTest中,添加测试方法testFindUserInfoByUserNameWithIf。
package com.mybatis.test;
import static org.junit.jupiter.api.Assertions.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.mybatis.mapper.UserInfoMapper;
import com.mybatis.pojo.UserInfo;
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory;
private SqlSession sqlSession;
//初始化方法
@Before
public void init() {
//读取mybatis配置文件
String resource="mybatis-config.xml";
InputStream inputStream;
try {
//得到配置文件流
inputStream=Resources.getResourceAsStream(resource);
//根据配置文件信息,创建会话工厂
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过工厂得到session
sqlSession=sqlSessionFactory.openSession();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@After
public void destroy() {
//提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
@Test
public void testFindNameWithIf() {
UserInfo u=new UserInfo();
u.setUserName("j");
UserInfoMapper uiMapper=sqlSession.getMapper(UserInfoMapper.class);
List<UserInfo> uiList=uiMapper.findUserInfoByUserNameWithIf(u);
for(UserInfo ui:uiList) {
System.out.println(ui);
}
}
}
在testFindUserInfoByUserNameWithIf方法中,首先获得UserInfoMapper 接口的代理对象,然后将用户名j 封装到UserInfo对象作为查询条件,最后调用接口UserInfoMapper 中的testFindUserInfoByUserNameWithIf 方法,根据条件查询UserInfo 对象列表。
(5)执行testFindUserInfoByUserNameWithIf方法,观察控制台输出。
在输出结果可以看出,使用 if 元素查询出了用户名包含j 的用户信息。在 testFindUserInfoByUserNameWithIf方法中,如果不设置userName的值,生成的SQL语句中就不会包含 where子句。
此时,查询结果为所有用户的信息,如下所示:
2. where、if 元素
当 if 元素较多时,可能会拼装成where and或者where or之类的关键字多余的错误SQL语句,使用 where元素可以轻松有效地解决这一问题。只有where元素内的条件成立时,才会在拼装SQL语句时加上where关键字。如果出现where and或者where or时,where元素会自动剔除where关键字之后多余的and或or 。
以数据表 user_info 为例,要求按用户名模糊查询,同时查询指定状态的用户列表,使用 where 和 if 元素实现这一示例的过程如下所示 。
(1)在映射文件UserInfoMapper.xml中,添加一个id为findUserInfoByUserNameAndStatus的 select 元素。
<!-- 动态SQL之where if -->
<select id="findUserInfoByUserNameAndStatus" parameterType="UserInfo"
resultType="UserInfo">
select *from user_info ui
<where>
<if test="userName!=null and userName!=''">
ui.userName like CONCAT('%',CONCAT(#{userName},'%'))
</if>
<if test="status>-1">
and ui.status=#{status}
</if>
</where>
</select>
(2)在接口UserInfoMapper中,声明一个findUserInfoByUserNameAndStatus方法。
public List<UserInfo> findUserInfoByUserNameAndStatus(UserInfo ui);
(3)在测试类MybatisTest中,添加一个测试方法。
@Test
public void testFindUserInfoByNameAndStatus() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
UserInfo ui=new UserInfo();
//ui.setUserName("j");
ui.setStatus(1);
List<UserInfo> uiList=uim.findUserInfoByUserNameAndStatus(ui);
for(UserInfo u:uiList){
System.out.println(u);
}
}
(4)执行上述测试方法,观察控制台输出。
3. set、if元素
set 和 if 元素可用来组装update语句,只有当 set 元素内的条件成立时,才会在组装SQL语句时加上set关键字。set元素内包含了 if 子元素,每个 if 元素包含的SQL后面会有一个逗号,拼接好的SQL语句中会包含多余的逗号,从而造成SQL语法错误。不过不用担心,set元素能将SQL语句中多余的逗号剔除。
以数据表user_info为例,要求更新某个用户的用户名和密码,使用 set 和 if 元素实现这一示例的过程如下所示。
(1)在映射文件UserInfoMapper.xml中,添加一个id为updateUserInfo2的update元素。
<!-- 动态SQL之set if -->
<update id="updateUserInfo" parameterType="UserInfo">
update user_info
<set>
<if test="userName!=null and userName!=''">
userName=#{userName},
</if>
<if test="password!=null and password!=''">
password=#{password}
</if>
</set>
where id=#{id}
</update>
(2)在接口UserInfoMapper中,声明一个updateUserInfo2方法。
public void updateUserInfo(UserInfo ui);
(3)在测试类MybatisTest中,添加一个测试方法testUpdateUserInfo2。
@Test
public void testUpdateUserInfo() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
UserInfo ui=new UserInfo();
ui.setId(1);
ui.setUserName("强森");
//ui.setPassword("123");
uim.updateUserInfo(ui);
}
(4)执行测试方法,观察控制台输出。
4. trim元素
使用 trim 元素,可以通过 prefix 属性在要拼装的SQL语句片段前加上前缀,通过 suffix 属性在要拼装的SQL语句片段之后加上后缀,通过 prefixOverrides 属性把要拼装的SQL语句片段首部的某些内容覆盖,通过suffixOverrides属性把要拼装的SQL语句片段尾部的某些内容覆盖。因此 trim 元素可用来替代 where 元素和set元素实现同样的功能。
使用trim元素替代where元素,从数据表user_info中按用户名模糊查询,同时查询指定状态的用户列表,实现步骤如下所示。
(1)在UserInfoMapper.xml中,添加一个id为findUserInfoWithTrim的 select 元素。
<!-- 动态SQL之trim元素替代where元素 -->
<select id="findUserInfoWithTrim" parameterType="UserInfo" resultType="UserInfo">
select *from user_info ui
<!-- prefixOverrides -->
<trim prefix="where" prefixOverrides="and|or">
<if test="userName!=null and userName!=''">
ui.userName like CONCAT('%',CONCAT(#{userName},'%'))
</if>
<if test="status>-1">
and ui.status=#{status}
</if>
</trim>
</select>
在上述select元素中,使用trim 元素编写了动态SQL语句。在trim 元素中,prefix属性设置为 where, 将要拼装的SQL语句的前缀设置为where,即使用where关键字来连接后面的SQL语句片段,prefixOverrides属性设置为 and|or,是将要拼装SQL语句片段首部多余的
(2)在接口UserInfoMapper中,声明一个findUserInfoWithTrim 方法。
public List<UserInfo> findUserInfoWithTrim(UserInfo u);
(3)在测试类MybatisTest中,添加一个测试方法。
@Test
public void testfindUserInfoWithTrim() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
UserInfo ui=new UserInfo();
ui.setStatus(1);
ui.setUserName("j");
List<UserInfo> uList=uim.findUserInfoWithTrim(ui);
for(UserInfo u:uList) {
System.out.println(u);
}
}
(4)执行该测试方法,观察控制台输出。
- 使用 trim 元素还可以替代 set元素,以更新数据表user_info中某个用户的用户名和密码为例,
具体步骤如下所示。
(1)在映射文件UserInfoMapper.xml中,添加一个 id 为 updateUserInfoTrim 的 update 元素。
<!-- 动态SQL之trim元素替代set元素 -->
<update id="updateUserInfoTrim" parameterType="UserInfo">
update user_info
<trim prefix="set" suffixOverrides=",">
<if test="userName!=null and userName!=''">
userName=#{userName},
</if>
<if test="password!=null and password!=''">
password=#{password}
</if>
</trim>
where id=#{id}
</update>
(2)在接口UserInfoMapper中,声明一个updateUserInfoTrim方法。
public void updateUserInfoTrim(UserInfo ui);
(3)在测试类MybatisTest中,添加一个测试方法。
@Test
public void testUpdateUserInfoTrim() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
UserInfo ui=new UserInfo();
ui.setUserName("李白");
ui.setId(1);
ui.setPassword("123456");
uim.updateUserInfoTrim(ui);
}
(4)执行该测试方法,观察控制台输出。
5. choose、when和otherwise元素
在查询中,如果不想使用所有的条件,而只是想从多个选项中选择一个,可以使用MyBatis提供的choose、when和 otherwise 元素来实现。choose 元素会按顺序判断 when 元素中的条件是否成立,如果有一个成立,则不再判断后面 when 元素中的条件是否成立,choose元素执行结束;如果所有 when 的条件都不满足,则执行otherwise元素中的SQL语句。
如果想从数据表user_info中根据userName或status进行查询,当userName不为空时则只按照userName查询,其他条件忽略;否则当status大于-1时,则只按照status查询;当userName和status都为空时,则查询所有用户记录,使用choose、when和otherwise元素实现这个示例的步骤如下所示。
(1)在映射文件UserInfoMapper.xml中,添加一个id为 findUserInfoChoose 的 select 元素。
<!-- 动态SQL之choose when otherwise -->
<select id="findUserInfoChoose" parameterType="UserInfo" resultType="UserInfo">
select *from user_info
<where>
<choose>
<when test="userName!=null and userName !=''">
userName like CONCAT('%',CONCAT(#{userName},'%'))
</when>
<when test="status>-1">
status=#{status}
</when>
<otherwise></otherwise>
</choose>
</where>
</select>
在上述select 元素中,使用choose,when,otherwise 元素编写了动态SQL语句。当第一个when 元素中的条件成立时,只动态拼装第一个when 元素中的SQL语句片段。否则,继续判断下一个when 元素中的条件。当所有的when 元素中的条件都不成立时,则只拼接otherwise 元素内的SQL语句片段。
(2)在接口UserInfoMapper中,声明一个findUserInfo_Choose方法。
public List<UserInfo> findUserInfoChoose(UserInfo ui);
(3)在测试类MybatisTest中,添加一个测试方法。
@Test
public void testFindUserInfoChoose() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
UserInfo ui=new UserInfo();
//ui.setUserName("j");
ui.setStatus(1);
List<UserInfo> uiList=uim.findUserInfoChoose(ui);
for(UserInfo u:uiList) {
System.out.println(u);
}
}
(4) 执行该测试方法,观察控制台输出。
6. foreach元素
foreach 元素主要是迭代一个集合,通常是用于 in 条件。例如SQL中的条件形如:where id in(一大串的id),这时可使用 foreach 元素,而不必去拼接id字符串。
foreach元素可以向SQL语句传递数组、List< E>等实例。List< E>实例使用list做为键,数组实例使用array做为键。
如果想从数据表user_info中查询id为1和3的用户记录,使用 foreach 元素的 List< E> 实例的实现步骤如下所示。
(1)在映射文件UserInfoMapper.xml中,添加一个id为findUserInfoByIds的元素。
<!-- 动态SQL之foreach元素,使用List<E>实例 -->
<select id="findUserInfoByIds" resultType="UserInfo">
select *from user_info where id in
<foreach collection="list" item="ids" open="(" close=")" separator=",">
#{ids}
</foreach>
</select>
foreach元素的主要属性有 item, idnex, collection, open, separator和close 等。item 属性表示集合中每个元素迭代时的别名,index属性指定一个变量名称,表示每次迭代的位置,open表示该语句的开始符号,separator属性表示每次迭代之间的分割符号,close属性表示该语句的结束符号,collection属性需要根据具体情况进行设置,通常有以下两种情况。
-
如果向SQL语句传递的时单参数且参数类型为List< E>,collection属性的值为List.
-
如果向SQL语句传递的是单参数且参数类型为Array 数组,collection属性为Array
(2)在接口UserInfoMapper中,声明一个findUserInfoByIds方法。
public List<UserInfo> findUserInfoByIds(List<Integer> ids);
(3)在测试类MybatisTest中,添加一个测试方法。
@Test
public void testFindUserInfoByIds() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
//创建集合对象ids,保存用户id
List<Integer> ids=new ArrayList<Integer>();
ids.add(1);
ids.add(3);
List<UserInfo> uiList=uim.findUserInfoByIds(ids);
for(UserInfo u:uiList) {
System.out.println(u);
}
}
(4)执行该测试方法,观察控制台输出。
- 除了List< E>实例,使用< foreach>元素的array实例,也能实现从数据表user_info中查询id为1和3的用户信息,具体步骤如下所示。
(1)在映射文件UserInfoMapper.xml中,添加一个id为findUserInfoByIds2的< select>元素 。
<!-- 动态SQL之foreach元素,使用Array实例 -->
<select id="findUserInfoByIds2" resultType="UserInfo">
select *from user_info where id in
<foreach collection="array" item="ids" open="(" close=")" separator=",">
#{ids}
</foreach>
</select>
(2)在接口UserInfoMapper中,声明一个findUserInfoByIds2方法。
public List<UserInfo> findUserInfoByIds2(int[] ids);
(3)在测试类MybatisTest中,添加一个测试方法。
@Test
public void testFindUserByIds2() {
UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
int[] ids=new int[2];
ids[0]=1;
ids[1]=3;
List<UserInfo> uiList=uim.findUserInfoByIds2(ids);
for(UserInfo u:uiList) {
System.out.println(u);
}
}
(4)执行该测试方法,观察控制台输出。