动态sql
1 .什么是动态sql
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
mybatis核心 对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接、组装。
2. 需求
用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态sql。
对查询条件进行判断,如果输入参数不为空才进行查询条件拼接。
3. mapper.xml
<!--多条件查询用户信息 -->
<select id="findUserList" parameterType="com.ren.mybatis.po.UserQueryVo" resultType="com.ren.mybatis.po.UserCustom">
select * from user
<where>
<include refid="query_user_where"></include>
</where>
</select>
<!--查询总记录条数 -->
<select id="findTotalCount" parameterType="com.ren.mybatis.po.UserQueryVo" resultType="int">
select count(*) from user
<where>
<if test="userCustom != null">
<if test="userCustom.sex != null and userCustom.username != ''">
user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username != null and userCustom.username != ''">
and user.username like '%${userCustom.username}%'
</if>
</if>
</where>
</select>
4. 测试代码
//查询总记录条数
@Test
public void testfindCount() throws Exception
{
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到Mapper接口的代理类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserCustom userCustom = new UserCustom();
UserQueryVo userQueryVo = new UserQueryVo();
userCustom.setSex("1");
userCustom.setUsername("小明");
userQueryVo.setUserCustom(userCustom);
int totalCount = userMapper.findTotalCount(userQueryVo);
System.out.println(totalCount);
}
5. sql片段
需求
将上边实现的动态sql判断代码块抽取出来,组成一个sql片段。其它的statement中就可以引用sql片段。方便程序员进行开发。
(1)定义sql片段
<!--定义sql片段 -->
<!--id是sql片段的唯一标识;一般基于单表定义,灵活;一般不包含where条件 -->
<sql id="query_user_where">
<if test="userCustom != null">
<if test="userCustom.sex != null and userCustom.username != ''">
user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username != null and userCustom.username != ''">
and user.username like '%${userCustom.username}%'
</if>
</if>
</sql>
(2) 引用sql片段
在mapper.xml中定义的statement中引用sql片段:
<include refid="query_user_where"></include>
6. foreach
向sql传递数组或List,mybatis使用foreach解析;
(1) 需求
在用户查询列表和查询总数的statement中增加多个id输入查询。
sql语句如下:
两种方法:
SELECT * FROM USER WHERE id=1 OR id=10 OR id=16
SELECT * FROM USER WHERE id IN(1,10,16)
(2)在输入参数类型中添加List<Integer> ids传入多个id
package com.ren.mybatis.po;
import java.util.List;
/**
* POJO的包装类型:
* 用于包装所需要的查询条件:
* @author 任志燕
* 2017年4月19日
*
*/
public class UserQueryVo {
//用户查询条件
private UserCustom userCustom;
//包含其他的查询条件:订单、商品..
private List
ids;//定义一个查询id集合,用于如果查询id在某一个范围内时使用
public void setIds(List
ids) {
this.ids = ids;
}
public List
getIds() {
return ids;
}
public void setUserCustom(UserCustom userCustom) {
this.userCustom = userCustom;
}
public UserCustom getUserCustom() {
return userCustom;
}
}
(3) 修改mapper.xml: WHERE id=1 OR id=10 OR id=16:在查询条件中,查询条件定义成一个sql片段,需要修改sql片段。
<!--
forech的使用:用于遍历接受的list类型的参数:(userQueryVo中的ids属性)
collection:是指定输入对象中集合属性的名字即ids
open:指定开始遍历时拼接串(即你要拼接的sql语句的开始字符串)你可以先写出你要拼接的sql语句串来判断开始字符串和结束字符串
close:指定结束遍历时拼接串(即你要拼接的sql语句的结束字符串)
item:每次遍历时生成的对象(自己起名)
separator:写遍历的两个对象之间需要拼接的字符串
-->
<!--
目的:如果为了实现拼接sql语句:and (id=16 or id =22 or id = 42)
注意:多写and连接符也不怕,因为使用where标签会将sql语句中多余的and去掉
-->
<!-- <if test="ids != null">
<foreach collection="ids" item="item_id" open="and (" close=")" separator="or">
这里写每次遍历需要拼接的内容
id =#{item_id}
</foreach>
</if> -->
<!--
目的:如果为了实现拼接sql语句:and id in (16,22,42)
注意:多写and连接符也不怕,因为使用where标签会将sql语句中多余的and去掉
-->
<if test="ids != null">
<foreach collection="ids" item="item_id" open="and id in(" close=")" separator=",">
<!--每个遍历需要拼接的字符串 -->
#{item_id}
</foreach>
</if>
(4)测试代码:
//多条件查询:使用POJO包装模型
@Test
public void testfindUserList() throws Exception
{
SqlSession sqlSession = sqlSessionFactory.openSession();
//得到Mapper接口的代理类对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserCustom userCustom = new UserCustom();
UserQueryVo userQueryVo = new UserQueryVo();
userCustom.setSex("1");
userCustom.setUsername("小明");
userQueryVo.setUserCustom(userCustom);
List<Integer>ids = new ArrayList<>();
ids.add(16);
ids.add(22);
ids.add(42);
userQueryVo.setIds(ids);
List<UserCustom> list = userMapper.findUserList(userQueryVo);
for (UserCustom userCustom2 : list) {
System.out.println(userCustom2.getUsername());
}
}