1.什么是动态sql?
mybatis核心对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接组装。
2.需求
用户综合信息查询和用户综合信息查询总数的查询条件可能为空,那么我们可以增加条件查询条件不为空时才进行查询条件的拼接
3.解决
对其statement使用动态sql
4.具体实现
mapper.xml
<!-- 综合查询用户信息 -->
<select id="findUserList" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="com.ddd.mybatis.pojo.UserCustom">
<!-- SELECT * FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
SELECT * FROM USER
<where>
<!-- 当第一个条件满足时where可以自动去除这个and -->
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username LIKE '%${userCustom.username}%'
</if>
</if>
</where>
</select>
<!-- 综合查询用户信息总数 -->
<select id="findUserCount" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="int">
<!-- SELECT COUNT(*) FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
SELECT COUNT(*) FROM USER
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username LIKE '%${userCustom.username}%'
</if>
</if>
</where>
</select>
测试:
@Test
public void testFindUserList() {
//由于之前是在UserDaoImpl中每次获取sqlSession,所以这里需要我们手动写
SqlSession sqlSession=sqlSessionFactory.openSession();
//mybatis自动生成mapper代理对象,代理对象内部调用selectOne或者selectList
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
//调用userMapper的方法,但这里如果不小心用一个单个user来接收,那会报错
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
//userCustom.setSex("男");
userCustom.setUsername("张伟");
userQueryVo.setUserCustom(userCustom);
List<UserCustom> list=userMapper.findUserList(userQueryVo);
System.out.println(list);
}
这里将性别注释掉,来看看日志打印出来的sql是什么:
SELECT * FROM USER WHERE user.username LIKE '%张伟%'
也就是说,当查询条件为空时就不拼接sql了
但是仔细发现,上面两个的sql查询条件是重复的我们写了两遍,可不可以把它们提取出来呢,当然了!
5.mybatis还有个sql标签可以定义sql片段
<!-- sql片段 id是它的标识,一般是基于单表来定义sql,这样sql可重用性才高 ,一般不用where-->
<sql id="query_user_where">
<!-- 当第一个条件满足时where可以自动去除这个and -->
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username LIKE '%${userCustom.username}%'
</if>
</if>
</sql>
怎么引用呢?
<!-- 综合查询用户信息 -->
<select id="findUserList" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="com.ddd.mybatis.pojo.UserCustom">
<!-- SELECT * FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
SELECT * FROM USER
<where>
<!-- 引用sql片段的id,如果不在本mapper中需要加namespace,在其下边还可以定义其他sql片段,这也就是为什么在sql片段定义中不写where -->
<include refid="query_user_where"></include>
</where>
</select>
<!-- 综合查询用户信息总数 -->
<select id="findUserCount" parameterType="com.ddd.mybatis.pojo.UserQueryVo" resultType="int">
<!-- SELECT COUNT(*) FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}%' -->
SELECT COUNT(*) FROM USER
<where>
<!-- 引用sql片段的id,如果不在本mapper中需要加namespace,在其下边还可以定义其他sql片段,这也就是为什么在sql片段定义中不写where -->
<include refid="query_user_where"></include>
</where>
</select>
测试是一样的效果,在此不多做演示
6.向动态sql中传递数组或者list,mybatis使用foreach解析
例如:这是输入多个id进行查询(两种实现)
SELECT * FROM USER WHERE id=1 OR id=3 OR id=5 OR id=10
SELECT * FROM USER WHERE id IN (1,3,5,10)
那在mybatis中怎么实现呢?
我们可以在输入参数类型中变成List<Integer> ids
这样支持多个id传入
首先在UserQueryVo中添加这样一个属性:
//传入多个id
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
在查询条件中使用foreach遍历:
<sql id="query_user_where">
<!-- 当第一个条件满足时where可以自动去除这个and -->
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username LIKE '%${userCustom.username}%'
</if>
<if test="ids!=null">
<!-- 使用foreach遍历传入ids
collection:指定输入对象中集合的属性名
item:每个遍历生成的对象名
open:开始遍历时要拼接的字符串
例如:SELECT * FROM USER WHERE user.username LIKE '%伟%' AND ( id=10 OR id=9 OR id=3)
如果不加括号,sql就错了
close:结束遍历时拼接的串
separator:遍历的两个对象中需要拼接的串
-->
<foreach collection="ids" item="user_id" open="AND(" close=")" separator="OR">
id=#{user_id}
</foreach>
</if>
</if>
测试代码:
@Test
public void testFindUserList() {
//由于之前是在UserDaoImpl中每次获取sqlSession,所以这里需要我们手动写
SqlSession sqlSession=sqlSessionFactory.openSession();
//mybatis自动生成mapper代理对象,代理对象内部调用selectOne或者selectList
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
//调用userMapper的方法,但这里如果不小心用一个单个user来接收,那会报错
UserQueryVo userQueryVo=new UserQueryVo();
UserCustom userCustom=new UserCustom();
List<Integer> ids=new ArrayList<>();
ids.add(1);
ids.add(3);
userQueryVo.setIds(ids);
userCustom.setSex("男");
//userCustom.setUsername("张伟");
userQueryVo.setUserCustom(userCustom);
List<UserCustom> list=userMapper.findUserList(userQueryVo);
System.out.println(list);
}
控制台查询结果:
执行的sql:
Preparing: SELECT * FROM USER WHERE user.sex=? AND( id=? OR id=? )
2017-07-18 09:53:02,244 [main] [com.ddd.mybatis.mapper.UserMapper.findUserList]-[DEBUG] ==> Parameters: 男(String), 1(Integer), 3(Integer)
结果:
[User [id=1, username=小明, sex=男, birthday=Fri Sep 09 00:00:00 CST 2011, address=北京市海淀区]]
<!-- 实现
SELECT * FROM USER WHERE user.username LIKE '%伟%' AND id IN (1,3,5,10)
将open和close和separator更改即可
<foreach collection="ids" item="user_id" open="AND id IN(" close=")" separator=",">
#{user_id}
</foreach>
-->