动态SQL
什么是动态sql:
mybatis核心 对sql语句进行灵活操作,通过表达式进行判断,对sql进行灵活拼接、组装。
需求
- 用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态sql。
- 对查询条件进行判断,如果输入参数不为空才进行查询条件拼接。
基础类:
- 数据库持久层类po(和数据表有映射关系):
public class UserPo {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
包装类vo(接受前台的表单数据从视图层传递到任何层):
public class UserQueryVo {
private UserPo userPo;
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="cn.domarvel.dao.UserMapper">
<!--用户信息综合查询
#{userPo.username}:取出pojo包装对象中姓名值
${userPo.id}:取出pojo包装对象中用户id
-->
<select id="findUserByNamesAndId" parameterType="cn.domarvel.vo.UserQueryVo" resultType="cn.domarvel.po.UserPo">
select * from user
<!--
where 可以自动去掉条件中的第一个and
-->
<where>
<if test="userPo!=null">
<if test="userPo.username!=null and userPo.username!=''">
and username like #{userPo.username}
</if>
<if test="userPo.id!=null">
and id=#{userPo.id}
</if>
</if>
</where>
</select>
<!--用户信息综合查询总数
parameterType:指定输入类型和findUserByNamesAndId一样
resultType:输出结果类型
-->
<select id="findUserCount" parameterType="cn.domarvel.vo.UserQueryVo" resultType="_int">
select count(id) from user
<where>
<if test="userPo!=null">
<if test="userPo.username!=null and userPo.username!=''">
and username like #{userPo.username}
</if>
<if test="userPo.id!=null">
and id=#{userPo.id}
</if>
</if>
</where>
</select>
</mapper>
UserMapper.java
public interface UserMapper {
public List<UserPo> findUserByNamesAndId(UserQueryVo userQueryVo) throws Exception;
public int findUserCount(UserQueryVo userQueryVo) throws Exception;
}
测试代码
public class UserDaoTest {
private SqlSessionFactory SqlSessionFactory;
@Before
public void init(){
try {
SqlSessionFactory=new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapConfig.xml"));
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void findUserByNamesAndId() throws Exception{
SqlSession sqlSession=SqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
//创建封装类
UserQueryVo userQueryVo=new UserQueryVo();
//创建基础类
UserPo userPo=new UserPo();
userPo.setUsername("%小明%");
userQueryVo.setUserPo(userPo);
List<UserPo> results=userMapper.findUserByNamesAndId(userQueryVo);
for(UserPo i:results){
System.out.println(i);
}
}
@Test
public void findUserCount() throws Exception{
SqlSession sqlSession=SqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
//创建封装类
UserQueryVo userQueryVo=new UserQueryVo();
//创建基础类
UserPo userPo=new UserPo();
userPo.setUsername("%小明%");
userQueryVo.setUserPo(userPo);
int userCount=userMapper.findUserCount(userQueryVo);
System.out.println(userCount);
}
}
SQL片段
需求:
- 将上边实现的动态sql判断代码块抽取出来,组成一个sql片段。其它的statement中就可以引用sql片段。
- 方便程序员进行开发。
定义sql片段
在mapper.xml文件中定义
<!-- 定义sql片段
id:sql片段的唯一标识
经验:是基于表单来定义sql片段,这样的话这样sql片段可重用性才高。
在SQL片段中不要包括where
-->
<sql id="query_User_Username_Id">
<if test="userPo!=null">
<if test="userPo.username!=null and userPo.username!=''">
and username like #{userPo.username}
</if>
<if test="userPo.id!=null">
and id=#{userPo.id}
</if>
</if>
</sql>
引用sql片段
在mapper.xml中定义的statement中引用sql片段:
<!--用户信息综合查询
#{userPo.username}:取出pojo包装对象中姓名值
${userPo.id}:取出pojo包装对象中用户id
-->
<select id="findUserByNamesAndId" parameterType="cn.domarvel.vo.UserQueryVo" resultType="cn.domarvel.po.UserPo">
select * from user
<!--
where 可以自动去掉条件中的第一个and
-->
<where>
<!-- 引用sql片段的id,如果refid指定的id不在本mapper文件中,需要前边加namespace -->
<include refid="query_User_Username_Id"></include>
<!-- 在这里还可以引用其它的sql片段,因为这里总是要引入一个到多个id,所以这里不能把where写在sql片段中 -->
</where>
</select>
foreach
向sql传递数组或List,mybatis使用foreach解析
需求:
在用户查询列表中增加多个id输入查询。
sql语句如下:
两种方式:
select * from user where username like '%小明%' and (id=1 or id=2)
select * from user where username like '%小明%' and id in(1,2)
在输入参数类型中添加List<Integer>
ids传入多个id
public class UserQueryVo {
private UserPo userPo;
private List<Integer> ids;
修改mapper.xml
<!-- 定义sql片段
id:sql片段的唯一标识
经验:是基于表单来定义sql片段,这样的话这样sql片段可重用性才高。
在SQL片段中不要包括where
-->
<sql id="query_User_Username_Id">
<if test="userPo!=null">
<if test="userPo.username!=null and userPo.username!=''">
and username like #{userPo.username}
</if>
<if test="ids!=null">
<!-- select * from user where username like '%小明%' and (id=1 or id=2) -->
<foreach collection="ids" item="item_id" open="and (" close=")" separator="or">
<!--使用foreach遍历传入ids
collection:指定输入对象中集合属性
item:每个遍历生成对象
open:开始遍历时拼接的串
close:结束遍历时拼接的串
separator:遍历的两个对象之间需要拼接的串
-->
id=#{item_id}<!-- 每次遍历循环的串 -->
</foreach>
</if>
</if>
</sql>
测试代码
@Test
public void findUserByNamesAndId() throws Exception{
SqlSession sqlSession=SqlSessionFactory.openSession();
UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
//创建封装类
UserQueryVo userQueryVo=new UserQueryVo();
//创建基础类
UserPo userPo=new UserPo();
userPo.setUsername("%小明%");
userQueryVo.setUserPo(userPo);
List<Integer> list=new ArrayList<Integer>();
list.add(1);
list.add(22);
list.add(25);
userQueryVo.setIds(list);
List<UserPo> results=userMapper.findUserByNamesAndId(userQueryVo);
for(UserPo i:results){
System.out.println(i);
}
}
另外一个sql的实现:
<!-- select * from user where username like '%小明%' and id in(1,2) -->
<foreach collection="ids" item="item_id" open="and id in(" close=")" separator=",">
#{item_id}<!-- 每次遍历循环的串 -->
</foreach>