Mybatis动态sql

本节内容：

• 动态sql

一、动态sql

通过mybatis提供的各种标签方法实现动态拼接sql。

需求：根据性别和名字查询用户。查询sql：

SELECT id, username, birthday, sex, address FROM `user` WHERE sex = 1 AND username LIKE '%张%'

1. if标签

（1）Mapper.xml文件
UserMapper.xml配置sql，如下：

    <!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="user" resultType="user">
        SELECT id, username, birthday, sex, address FROM `user`
        WHERE sex = #{sex} AND username LIKE
        '%${username}%'
    </select>

（2）Mapper接口

编写Mapper接口，如下图：

    //根据条件查询用户
    public List<User> queryUserByWhere(User user);

（3）测试方法

在UserMapperTest添加测试方法，如下：

    //根据条件查询用户
    @Test
    public void testUser() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        // 获取sqlSession，和spring整合后由spring管理
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //SqlSEssion会帮我生成一个实现类  （需要我们给接口，它帮我们生成实现类，返回给我们的还是接口）
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = new User();
        user.setSex("1");
        user.setUsername("张");

        List<User> users = userMapper.queryUserByWhere(user);
        for (User u: users) {
            System.out.println(u);
        }

        // 和spring整合后由spring管理
        sqlSession.close();

    }

执行测试方法，查看控制台日志：

DEBUG [main] - ==>  Preparing: SELECT id, username, birthday, sex, address FROM `user` WHERE sex = ? AND username LIKE '%张%' 
DEBUG [main] - ==> Parameters: 1(String)
DEBUG [main] - <==      Total: 4
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
User [id=24, username=张三丰, sex=1, birthday=null, address=河南郑州]
User [id=27, username=张飞, sex=1, birthday=Mon Dec 25 00:00:00 CST 2017, address=蜀国]

假设现在不需要性别这个条件了，注释掉 user.setSex("1")，测试结果如下：

DEBUG [main] - ==>  Preparing: SELECT id, username, birthday, sex, address FROM `user` WHERE sex = ? AND username LIKE '%张%' 
DEBUG [main] - ==> Parameters: null
DEBUG [main] - <==      Total: 0

这显然不合理。

按照之前所学的，要解决这个问题，需要编写多个sql，查询条件越多，需要编写的sql就更多了，显然这样是不靠谱的。

解决方案，使用动态sql的if标签。

（4）使用if标签

    <!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="user" resultType="user">
        SELECT id, username, birthday, sex, address FROM `user`
        WHERE 1=1
        <if test="sex != null and sex !=''">
            AND sex = #{sex}
        </if>
        <if test="username != null and username != ''">
            AND username LIKE '%${username}%'
        </if>
    </select>

注意字符串类型的数据需要要做不等于空字符串校验。另外，为什么where后加上 1=1，因为如果不加这个，当代码中注释掉 user.setSex("1") 时，打印出来的sql语句就是 “where      and username like...”，显然语法错误。

还是注释掉 user.setSex("1")，再次执行测试方法，查看控制台日志：

DEBUG [main] - ==>  Preparing: SELECT id, username, birthday, sex, address FROM `user` WHERE 1=1 AND username LIKE '%张%' 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 4
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
User [id=24, username=张三丰, sex=1, birthday=null, address=河南郑州]
User [id=27, username=张飞, sex=1, birthday=Mon Dec 25 00:00:00 CST 2017, address=蜀国]

2. where标签

上面的sql还有where 1=1 这样的语句，很麻烦。可以使用where标签进行改造。

    <!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="user" resultType="user">
        SELECT id, username, birthday, sex, address FROM `user`
        <where>
            <if test="sex != null and sex !=''">
                AND sex = #{sex}
            </if>
            <if test="username != null and username != ''">
                AND username LIKE '%${username}%'
            </if>
        </where>
    </select>

3. sql片段

Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的。

把上面例子中的id, username, birthday, sex, address提取出来，作为sql片段，如下：

    <!-- sql片段 -->
    <select id="queryUserBySql" parameterType="user" resultType="user">
        <!-- SELECT id, username, birthday, sex, address FROM `user` -->
        <!-- 使用include标签加载sql片段；refid是sql片段id -->
        SELECT <include refid="userFields" /> FROM `user`
        <!-- where标签可以自动添加where关键字，同时处理sql语句中第一个and关键字 -->
        <where>
            <if test="sex != null">
                AND sex = #{sex}
            </if>
            <if test="username != null and username != ''">
                AND username LIKE
                '%${username}%'
            </if>
        </where>
    </select>

    <!-- 声明sql片段 -->
    <sql id="userFields">
        id, username, birthday, sex, address
    </sql>

4. foreach标签

向sql传递数组或List，mybatis使用foreach解析，如下：

根据多个id查询用户信息，查询sql：

SELECT * FROM user WHERE id IN (1,10,24)

（1）UserMapper.java添加方法（存在三种形式）

    //根据多个id查询用户信息
    public List<User> queryUserByIds(Integer[] ids);
    public List<User> queryUserByIds(List<Integer> ids);
    public List<User> queryUserByIds(QueryVo vo);

先来个 public List<User> queryUserByIds(QueryVo vo); 改造QueryVo.java

如下图在pojo中定义list属性ids存储多个用户id，并添加getter/setter方法。

public class QueryVo implements Serializable { //序列化，对象转成二进制进行传输。

	private static final long serialVersionUID = 1L;

	private User user;
	
	List<Integer> idsList;
	
	Integer[] ids;
	
	public List<Integer> getIdsList() {
		return idsList;
	}
	public void setIdsList(List<Integer> idsList) {
		this.idsList = idsList;
	}
	public Integer[] getIds() {
		return ids;
	}
	public void setIds(Integer[] ids) {
		this.ids = ids;
	}
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}

}

（2）Mapper.xml文件

UserMapper.xml添加sql，如下：

    <!--根据多个id查询用户信息 (1,2,3)-->
    <select id="queryUserByIds" parameterType="QueryVo" resultType="user">
        SELECT * FROM `user`
        <where>
            id IN
            <!-- foreach标签，进行遍历 -->
            <!-- collection：遍历的集合，这里是QueryVo的ids属性 -->
            <!-- item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致 -->
            <!-- open：在前面添加的sql片段 -->
            <!-- close：在结尾处添加的sql片段 -->
            <!-- separator：指定遍历的元素之间使用的分隔符 -->
            <foreach collection="idsList" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </where>
    </select>

或者将 id IN 放进 open 中，如下：

<foreach collection="idslist" item="item" separator="," open="id IN (" close=")">

（4）测试方法

    //根据多个ID查询用户
    @Test
    public void testID() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        // 获取sqlSession，和spring整合后由spring管理
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //SqlSEssion会帮我生成一个实现类  （需要我们给接口，它帮我们生成实现类，返回给我们的还是接口）
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        QueryVo vo = new QueryVo();
        List<Integer> ids = new ArrayList<>();
        ids.add(10);
        ids.add(16);
        ids.add(22);
        vo.setIdsList(ids);

        List<User> users = userMapper.queryUserByIds(vo);
        for (User u: users) {
            System.out.println(u);
        }

        // 和spring整合后由spring管理
        sqlSession.close();

    }

执行测试方法，查看控制台日志：

DEBUG [main] - ==>  Preparing: SELECT * FROM `user` WHERE id IN ( ? , ? , ? ) 
DEBUG [main] - ==> Parameters: 10(Integer), 16(Integer), 22(Integer)
DEBUG [main] - <==      Total: 3
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州]

在实现一个 public List<User> queryUserByIds(Integer[] ids);

修改UserMapper.xml，如下：

    <!--根据多个id查询用户信息 (1,2,3)-->
    <select id="queryUserByIds" parameterType="QueryVo" resultType="user">
        SELECT * FROM `user`
        <where>
            id IN
            <foreach collection="array" item="item" separator="," open="(" close=")">
                #{item}
            </foreach>
        </where>
    </select>

【注意】：当传递的参数是数组时，collection的值一定要是array，否则会报错。同样地，当传递的参数是list的时候，collection的值一定要是list。而当传入的参数是个对象(比如QueryVo)时，直接使用对象里面的属性名（比如上例中是idsList）。

测试方法：

    //根据多个ID查询用户
    @Test
    public void testID2() throws Exception {
        //加载核心配置文件
        String resource = "sqlMapConfig.xml";
        InputStream in = Resources.getResourceAsStream(resource);
        //创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        // 获取sqlSession，和spring整合后由spring管理
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //SqlSEssion会帮我生成一个实现类  （需要我们给接口，它帮我们生成实现类，返回给我们的还是接口）
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        Integer[] ids = new Integer[3];
        ids[0] = 16;
        ids[1] = 22;
        ids[2] = 10;

        List<User> users = userMapper.queryUserByIds(ids);
        for (User u: users) {
            System.out.println(u);
        }

        // 和spring整合后由spring管理
        sqlSession.close();

    }

执行测试方法，查看日志：

DEBUG [main] - ==>  Preparing: SELECT * FROM `user` WHERE id IN ( ? , ? , ? ) 
DEBUG [main] - ==> Parameters: 16(Integer), 22(Integer), 10(Integer)
DEBUG [main] - <==      Total: 3
User [id=10, username=张三, sex=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
User [id=16, username=张小明, sex=1, birthday=null, address=河南郑州]
User [id=22, username=陈小明, sex=1, birthday=null, address=河南郑州]

　　　

【注意】：拿到需求，可以先定义接口，然后在写sql。这样比较有头绪。