mybatis动态SQL

---

条件查询if标签

---

IUserDao

 /**
     * 根据传入参数条件查询
     * @return 查询条件：可能有用户名，有可能有性别，也有可能有地址，还有可能是都有
     */
     List<User> findUserByCondition(User user);

IUserDao.xml

<select id="findUserByCondition" resultType="user" parameterType="user">
    select * from user where 1=1
    <if test="username != null">
        and username = #{username}
    </if>
    <if test="usersex != null">
        and sex = ${sex}
    </if>
</select>

test

没有设置sex时的查询

   /**
         * 测试条件查询
         */
        @Test
        public void testFindByCondition(){
    
            User u = new User();
            u.setUsername("老王");
            List<User> users = userDao.findUserByCondition(u);
            for (User user : users) {
                System.out.println(user);
            }
    }

	结果：
    User{id=41, username='老王', address='北京', sex='男', birthday=Tue Feb 27 17:47:08 CST 2018}
    User{id=46, username='老王', address='北京', sex='女', birthday=Wed Mar 07 17:37:26 CST 2018}

设置sex时的查询

  @Test
    public void testFindByCondition(){

        User u = new User();
        u.setUsername("老王");
        u.setSex("女");
        List<User> users = userDao.findUserByCondition(u);
        for (User user : users) {
            System.out.println(user);
        }

    }

    结果：
    User{id=46, username='老王', address='北京', sex='女', birthday=Wed Mar 07 17:37:26 CST 2018}

---

条件查询where标签

---

加入where标签后 select * from user where 1=1就可以改为select * from user，where会自动连接

<!--根据条件查询-->
    <select id="findUserByCondition" resultType="user" parameterType="user">
        select * from user
        <where>
        <if test="username != null">
            and username = #{username}
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
        </where>
    </select>

---

条件查询foreach标签

---

创建一个QueryVo实体类，存储list集合

public class QueryVo {

    //定义一个id集合
    private List<Integer> ids;


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

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

IUserDao.xml中

<!--根据QueryVo中提供的id集合实现查询用户列表-->
    <select id="findUserByIds" resultType="user" parameterType="queryvo">
        select * from user 
        <where>
            <if test="ids != null and ids.size()>0">
                <!--collection:集合名称 item：遍历出来的值叫id separator：用，分割  最后要加上值id:#{id}-->
                <foreach collection="ids" open="and id in (" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

test

 /**
     * 测试foreach标签的使用
     */
    @Test
    public void findUserByIds(){

        QueryVo vo =  new QueryVo();
        List<Integer> list = new LinkedList<Integer>();
        list.add(41);
        list.add(43);
        list.add(48);
        vo.setIds(list);

        List<User> users = userDao.findUserByIds(vo);
        for (User user : users) {
            System.out.println(user);
        }
    }