Mybatis之动态SQL

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

• 映射文件 UserMapper.xml

   <select id="findUserByUserNameAndSex" parameterType="cn.zst.domain.User" resultType="cn.zst.domain.User">
         SELECT * from `user` WHERE 1=1  and username like '$$' and sex=
    </select>

• 接口

    public List<User> findUserByUserNameAndSex(User user);

• 测试方法

    @Test
    public void testFindUserbyUserNameAndSex() throws  Exception{
        SqlSession openSession = factory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        User user = new User();
        user.setSex("2");
        user.setUsername("李");
        List<User> list = mapper.findUserByUserNameAndSex(user);
        System.out.println(list);
    }

if

    <select id="findUserByUserNameAndSex" parameterType="cn.zst.domain.User" resultType="cn.zst.domain.User">
        SELECT * from `user` WHERE 1=1 
        <if test="username != null and username != ''">
            and username LIKE '%${username}%'
        </if>
        <if test="sex != null and sex != ''">
            and sex=#{sex}
        </if>
    </select>

where

    <select id="findUserByUserNameAndSex" parameterType="cn.zst.domain.User" resultType="cn.zst.domain.User">
        <!--SELECT * from `user` WHERE 1=1-->
        SELECT * from `user`
        <!--where标签的作用
            会自动像SQL语句中添加where关键字，
            会去掉第一个条件的and关键字
        -->
        <where>
        <if test="username != null and username != ''">
            and username LIKE '%${username}%'
        </if>
        <if test="sex != null and sex != ''">
            and sex=#{sex}
        </if>
        </where>
    </select>

sql 片段

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

• 将where条件抽取出来

  <!--封装SQL条件，封装后该SQL语句可以重用
        id :该SQL语句的唯一标识
    -->
    <sql id="user_where">
        <!--where标签的作用
         会自动像SQL语句中添加where关键字，
         会去掉第一个条件的and关键字
     -->
        <where>
            <if test="username != null and username != ''">
                and username LIKE '%${username}%'
            </if>
            <if test="sex != null and sex != ''">
                and sex=#{sex}
            </if>
        </where>
    </sql>

• 使用include引用

    <select id="findUserByUserNameAndSex" parameterType="cn.zst.domain.User" resultType="cn.zst.domain.User">
       SELECT * from `user`
        <!--调用SQL条件-->
        <include refid="user_where"></include>
    </select>

foreach（向sql传递数组或List，mybatis使用foreach解析）

foreach标签的作用是遍历（集合），如果传入的参数是一个集合（比如查用户表中id为1 23 28的用户信息，则条件（id）需要作为一个集合传入）.

• 在POJO中定义list属性ids存储多个用户id

public class QueryVo {
    private User user;
    private List<Integer> ids;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}

• 映射文件 mapper.xml

  <select id="findUserByIds" parameterType="cn.zst.domain.QueryVo" resultType="cn.zst.domain.User">
        <!--SELECT * FROM `user` WHERE id in(1,16,28,22)-->
        SELECT * FROM `user`
        <where>
            <if test="ids != null">
                <!--
                foreach:循环传入的集合参数
                collection:传入的集合的变量名称
                item:每次循环，将循环出的数据放入这个变量中(随意写)
                open：循环开始拼接的字符串
                close：循环结束拼接的字符串
                separator:循环中拼接的分隔符



                -->
                <foreach collection="ids" item="id" open="id in(" close=")" separator=",">
                  #{id}
                </foreach>
            </if>
        </where>

    </select>

• 接口

public List<User> findUserByIds(QueryVo vo

• 测试方法

    @Test
    public void testFindUserCount() throws Exception{
        SqlSession openSession = factory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        Integer count = mapper.findUserCount();
        System.out.println(count);
    }
    @Test
    public void testFindUserbyUserNameAndSex() throws  Exception{
        SqlSession openSession = factory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);
        User user = new User();
        user.setSex("2");
        user.setUsername("李");
        List<User> list = mapper.findUserByUserNameAndSex(user);


        System.out.println(list);
    }

    @Test
    public void testFindUserByIds() throws Exception{
        SqlSession openSession = factory.openSession();
        UserMapper mapper = openSession.getMapper(UserMapper.class);

        QueryVo vo = new QueryVo();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(18);
        ids.add(22);

        vo.setIds(ids);

        List<User> list = mapper.findUserByIds(vo);
        System.out.println(list);

    }