动态SQL
mybatis的强大特性之一就是它的动态SQL。以下是mybatis的动态SQL在XML中支持的几种标签：
- if
- choose（when,otherwise）
- trim,where,set
- foreach
本文，在上一篇文章的基础上进行功能添加修改：mybatis复习02,简单的增删改查，@Param注解多个参数，resultType与resultMap的区别，#{}预编译参数
初始的数据库表
if标签
if标签的结构
<if test="判断条件为true|false">
sql语句块
</if>
if标签中，test属性值为必填，其值为判断条件为真或者假，然后标签中就是sql语句。
根据输入条件动态查询
现在有一个需求，就是根据用户的输入不同来进行不同的条件查询：
用户只输入用户名时，根据用户名进行查询（模糊查询），当用户输入地址时，根据地址去匹配查询，同时输入用户名和地址时进行查询对应匹配的用户。
接着我们来实现，上面的需求，首先在mapper接口中编写对应的方法：
/**
* 多条件查询，支持用户的输入不同，查询出不同的信息
* @param userInfo
* @return
*/
List<UserInfo> selectByUserInfo(UserInfo userInfo);
然后在Mapper接口对应的xml文件中，编写相应的sql：
这里使用了resultMap标签作为返回值映射，也可以使用resultType标签，就不多赘述。
<resultMap id="selectByUserInfo" type="UserInfo">
<id property="id" column="id"></id>
<result property="userName" column="user_name"></result>
<result property="password" column="password"></result>
<result property="userSex" column="user_sex"></result>
<result property="userAddress" column="user_address"></result>
</resultMap>
<!--List<UserInfo> selectByUserInfo(UserInfo userInfo);-->
<select id="selectByUserInfo" resultMap="selectByUserInfo">
select id,
user_name,
password,
user_sex,
user_address
from user_info
where 1=1
<if test="userName != null and userName !=''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userAddress != null and userAddress !=''">
and user_address = #{userAddress}
</if>
</select>
观察完代码，注意到 where 1=1 ，这个的作用是，即使所有的if都不满足，也会执行。
所有的if都不满足即 select * from user_info where 1=1,可以想想一下不在where后面加 1=1,最终的sql会变成什么样，必然会出错，而且为了匹配多条件也必须需要有这样一个条件，因为每个查询中的and，你并不确定用户最后的输入查询条件是什么。（当然，后面的trim,where标签会解决这个问题，那样更优雅）。
测试用例编写：
@Test
public void testSelectByUserInfo(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
//伪造一个用户输入，只输入名称
UserInfo userInfo1 = new UserInfo();
userInfo1.setUserName("白");
List<UserInfo> userInfos1 = mapper.selectByUserInfo(userInfo1);
userInfos1.forEach(System.out::println);
//伪造用户输入。只输入地址
UserInfo userInfo2 = new UserInfo();
userInfo2.setUserAddress("东河龙王庙");
List<UserInfo> userInfos2 = mapper.selectByUserInfo(userInfo2);
userInfos2.forEach(System.out::println);
//伪造用户输入，输入姓名和地址
UserInfo userInfo3 = new UserInfo();
userInfo3.setUserName("叶问");
userInfo3.setUserAddress("佛山街道办事处");
List<UserInfo> userInfos3 = mapper.selectByUserInfo(userInfo3);
userInfos3.forEach(System.out::println);
//伪造一个啥也不输入的用户输入，返回应该是所有的数据
UserInfo userInfo4 = new UserInfo();
List<UserInfo> userInfos4 = mapper.selectByUserInfo(userInfo4);
userInfos4.forEach(System.out::println);
}
测试用例输出：
动态更新中使用if
需求：选择性更新，只更新用户所输入的数据。
编写接口中的方法：
/**
* 根据用户输入来更新，（只为了说明功能）
* @param userInfo
* @return
*/
int updateByUserInfo(UserInfo userInfo);
编写对应xml文件中的sql语句：
<!--int updateByUserInfo(UserInfo userInfo);-->
<update id="updateByUserInfo">
update user_info
set
<if test="userName != null and userName !=''">
user_name =#{userName},
</if>
<if test="userSex != null and userSex !=''">
user_sex =#{userSex},
</if>
<if test="userAddress != null and userAddress !=''">
user_address =#{userAddress},
</if>
id = #{id}
where id =#{id}
</update>
同理，这里的 id=#{id}与上一个where 1=1异曲同工之妙。同样是，因为在如果if都为假的话，不加id=#{id}，sql就会是一个错误的sql，并且set中每个以逗号分隔，你也不确定哪一个条件会成立，所以，最后面还是得补id=#{id}。
测试用例编写：
@Test
public void testUpdateUserInfos(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
UserInfo userInfo1 =new UserInfo();
userInfo1.setId(2);
mapper.updateByUserInfo(userInfo1);
UserInfo userInfo2 =new UserInfo();
userInfo2.setId(2);
userInfo2.setUserName("叶问二");
mapper.updateByUserInfo(userInfo2);
UserInfo userInfo3 =new UserInfo();
userInfo3.setId(2);
userInfo3.setUserName("叶问");
userInfo3.setUserAddress("佛山武馆");
mapper.updateByUserInfo(userInfo3);
}
测试用例输出：
choose标签
choose标签的结构
choose标签其实就是对if标签的一种补充，为了实现if…else这样的逻辑。
choose标签中包括两个子标签（when和otherwise）。一个choose标签中应至少包含一个when标签和0个或者1个otherwise标签。
<choose>
<when test=""></when>
<otherwise></otherwise>
</choose>
choose用例
需求：根据用户名或者id输入来查询对应用户，如果二者都为空，则不执行任何查询。
首先，编写Mapper接口中的方法：
/**
* 根据用户名或者id来查询对应用户
* @param userInfo
* @return
*/
UserInfo selectByIdOrUserName(UserInfo userInfo);
编写对应xml中的sql语句:
<!--UserInfo selectByIdOrUserName(UserInfo userInfo);-->
<select id="selectByIdOrUserName" resultType="UserInfo">
select id id,
user_name userName,
password password,
user_sex userSex,
user_address userAddress
from user_info
where 1=1
<choose>
<when test="id !=null">
and id =#{id}
</when>
<when test="userName !=null and userName !=''">
and user_name = #{userName}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
看到这里，结合之前上面的代码是不是也明白了otherwise 中的 and 1=2的含义了，我就不过多解释了。
测试用例编写:
@Test
public void testSelectUsersByIdOrUserName(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
//先伪造一个数据都为空的用户
UserInfo userInfo1 = new UserInfo();
mapper.selectByIdOrUserName(userInfo1);//不会执行sql
//伪造一个只含id和只含用户名的数据
UserInfo userInfo2 = new UserInfo();
userInfo2.setId(1);
mapper.selectByIdOrUserName(userInfo2);
UserInfo userInfo3 = new UserInfo();
userInfo3.setUserName("叶问");
mapper.selectByIdOrUserName(userInfo3);
}
测试用例输出:
where、set、trim标签
where标签的结构
<where>
可嵌套其他标签使用
</where>
where标签的作用就是，如果where标签中包含的元素有返回值，就在sql中插入一个where，并且如果where后面的字符串是以and或者or开头的,会自动剔除掉and和or。(这不就是上面说的完美解决，以后我们就不再需要where 1=1,这样的代码了).
where用例
就是根据用户的输入不同来进行不同的条件查询：
用户只输入用户名时，根据用户名进行查询（模糊查询），当用户输入地址时，根据地址去匹配查询，同时输入用户名和地址时进行查询对应匹配的用户。
还是上面的同一个需求，这次换个方法来实现，使用where标签。
在Mapper中编写，对应的方法（也可使用之前的方法，我为了不修改，就重新写一个）:
/**
* 多条件查询，支持用户的输入不同，查询出不同的信息 使用where标签来实现
* @param userInfo
* @return
*/
List<UserInfo> selectByUserInfoTwo(UserInfo userInfo);
编写对应xml中的sql:
<!-- List<UserInfo> selectByUserInfoTwo(UserInfo userInfo);-->
<select id="selectByUserInfoTwo" resultType="UserInfo">
select id id,
user_name userName,
password password,
user_sex userSex,
user_address userAddress
from user_info
<where>
<if test="userName!=null and userName!=''">
and user_name like concat('%',#{userName},'%')
</if>
<if test="userAddress!=null and userAddress!=''">
and user_address = #{userAddress}
</if>
</where>
</select>
一下子感觉清爽了许多！
测试用例编写:
@Test
public void testselectByUserInfoTwo(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
//伪造一个用户输入，只输入名称
UserInfo userInfo1 = new UserInfo();
userInfo1.setUserName("白");
List<UserInfo> userInfos1 = mapper.selectByUserInfo(userInfo1);
userInfos1.forEach(System.out::println);
//伪造用户输入。只输入地址
UserInfo userInfo2 = new UserInfo();
userInfo2.setUserAddress("东河龙王庙");
List<UserInfo> userInfos2 = mapper.selectByUserInfo(userInfo2);
userInfos2.forEach(System.out::println);
//伪造用户输入，输入姓名和地址
UserInfo userInfo3 = new UserInfo();
userInfo3.setUserName("叶问");
userInfo3.setUserAddress("佛山街道办事处");
List<UserInfo> userInfos3 = mapper.selectByUserInfo(userInfo3);
userInfos3.forEach(System.out::println);
//伪造一个啥也不输入的用户输入，返回应该是所有的数据
UserInfo userInfo4 = new UserInfo();
List<UserInfo> userInfos4 = mapper.selectByUserInfo(userInfo4);
userInfos4.forEach(System.out::println);
}
测试用例输出：
set标签的结构
<set>
同样也可嵌入其他标签
</set>
set标签的作用与where基本类似，如果set标签元素中有返回值就插入一个set，同时如果set后面的sql是以逗号,结尾的，则将这个逗号剔除。
set用例
需求：选择性更新，只更新用户所输入的数据。（和上面的if第二个例子一样，这次是为了改进）。
编写Mapper接口中的方法:
/**
* 根据用户输入来更新，（只为了说明功能）-->使用set标签来做
* @param userInfo
* @return
*/
int updateByUserInfoTwo(UserInfo userInfo);
编写对应xml文件中的sql：
<!-- int updateByUserInfoTwo(UserInfo userInfo);-->
<update id="updateByUserInfoTwo">
update user_info
<set>
<if test="userName != null and userName !=''">
user_name =#{userName},
</if>
<if test="userSex != null and userSex !=''">
user_sex =#{userSex},
</if>
<if test="userAddress != null and userAddress !=''">
user_address =#{userAddress},
</if>
id = #{id},
</set>
where id =#{id}
</update>
测试用例编写：
@Test
public void testUpdateUserInfosTwo(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
UserInfo userInfo1 =new UserInfo();
userInfo1.setId(2);
mapper.updateByUserInfoTwo(userInfo1);
UserInfo userInfo2 =new UserInfo();
userInfo2.setId(2);
userInfo2.setUserName("叶问二");
mapper.updateByUserInfoTwo(userInfo2);
UserInfo userInfo3 =new UserInfo();
userInfo3.setId(2);
userInfo3.setUserName("叶问");
userInfo3.setUserAddress("佛山武馆");
mapper.updateByUserInfoTwo(userInfo3);
}
测试用例输出：
trim标签的结构
trim标签其实就是where和set标签的整合并且扩充了更多的功能，变得更灵活，trim通过其名字直译也就是剔除.
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
....
</trim>
- prefix:当trim元素内包含内容时，会给内容增加prefix 指定的前缀。
- prefixOverrides:当trim元素内包含内容时，会内容中匹配的的前缀字符串去掉（where标签）。
- suffix:当trim元素内包含内容时，会给内容增加prefix 指定的前缀。
- suffixOverrides:当trim元素内包含内容时，会内容中匹配的的前缀字符串去掉（where标签）。
trim标签用例
然后我们就就近原则吧，用上面的例子（需求：选择性更新，只更新用户所输入的数据。）来编写一个使用trim标签的代码。
Mapper接口中的方法编写：
/**
* 根据用户输入来更新，（只为了说明功能）-->使用trim标签来做
* @param userInfo
* @return
*/
int updateByUserInfoThree(UserInfo userInfo);
对应xml文件的sql：
<!-- int updateByUserInfoThree(UserInfo userInfo);-->
<update id="updateByUserInfoThree">
update user_info
<trim prefix="set" suffixOverrides=",">
<if test="userName != null and userName !=''">
user_name =#{userName},
</if>
<if test="userSex != null and userSex !=''">
user_sex =#{userSex},
</if>
<if test="userAddress != null and userAddress !=''">
user_address =#{userAddress},
</if>
id = #{id},
</trim>
where id =#{id}
</update>
测试代码：
@Test
public void testUpdateUserInfosThree(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
UserInfo userInfo1 =new UserInfo();
userInfo1.setId(2);
mapper.updateByUserInfoThree(userInfo1);
UserInfo userInfo2 =new UserInfo();
userInfo2.setId(2);
userInfo2.setUserName("叶问二");
mapper.updateByUserInfoThree(userInfo2);
UserInfo userInfo3 =new UserInfo();
userInfo3.setId(2);
userInfo3.setUserName("叶问");
userInfo3.setUserAddress("佛山武馆");
mapper.updateByUserInfoThree(userInfo3);
}
测试用例输出:
foreach标签
foreach标签的结构
foreach与java中的基本一致，就不过多解释。foreach可以对数组，Map,实现类迭代器接口的对象进行遍历，数组遍历时会转换为list.
<foreach collection="" separator="" item="" close="" open="" index="">
......
</foreach>
foreach标签的属性：
- collection:必填，值为要迭代的属性名
- item:变量名，每次迭代出来的值
- index:索引的属性名，迭代时候集合数组为当前的索引值，而Map为对应的key值
- open:整个循环内容开头的字符串
- close:整个循环内容末尾的字符串
- separator:每次循环的分隔符
foreach标签的用例
用foreach实现in集合
第一个例子就是用foreach实现in集合，select * from user_info where id in (1,3,5) 类似这样的sql。
编写mapper接口中的方法：
/**
* 根据用户id集合查询
* @param idList
* @return
*/
List<UserInfo> selectByIdList(List<Integer> idList);
对应xml中的sql:
<!-- List<UserInfo> selectByIdList(int id);-->
<select id="selectByIdList" resultType="UserInfo">
select id id,
user_name userName,
password password,
user_sex userSex,
user_address userAddress
from user_info
where id in
<foreach collection="list" separator="," item="idList" open="(" close=")" index="i">
#{idList}
</foreach>
</select>
测试用例：
@Test
public void testSelectByIdList(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
List<Integer> idList = new ArrayList<>();
idList.add(1);
idList.add(2);
idList.add(4);
List<UserInfo> userInfos = mapper.selectByIdList(idList);
userInfos.forEach(System.out::println);
}
测试输出：
foreach实现批量插入
编写mapper接口中的方法：
/**
* 批量插入用户信息
* @param userInfos
* @return
*/
int insertUserInfoList(List<UserInfo> userInfos);
对应xml中的sql:
<!--int insertUserInfoList(List<UserInfo> userInfos);-->
<insert id="insertUserInfoList" >
insert into user_info
(id,user_name,password,user_sex,user_address)
values
<foreach collection="list" separator="," item="userInfos">
(#{userInfos.id},#{userInfos.userName},#{userInfos.password},#{userInfos.userSex},#{userInfos.userAddress})
</foreach>
</insert>
测试用例：
@Test
public void testInsertUserInfoList(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
List<UserInfo> userInfoList = new ArrayList<>();
userInfoList.add(new UserInfo(5,"张三","1238968anc","男","青城土地庙"));
userInfoList.add(new UserInfo(6,"lisi","bootstrap123","男","青城土地庙"));
userInfoList.add(new UserInfo(7,"王孙","vuejavajs221","男","萨拉齐街道办事处"));
mapper.insertUserInfoList(userInfoList);
}
测试输出：
好嘞！！！以上就是今天复习内容！！！陆续复习中…
