mybatis 动态SQL的使用

一、if的使用

持久层Dao接口

public interface IUserDao {
    /**
     * 根据条件查找
     * @param user
     * @return
     */
    List<User> findUserByCondition(User user);
}

持久层映射配置
当为多条件的时候 可以使用and
如： < if test="username != null and username != ‘老王’ ">
双引号“ 内使用单引号‘

<?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="com.ect.dao.IUserDao">
    <select id="findUserByCondition" parameterType="com.ect.domain.User" resultType="com.ect.domain.User">
        SELECT * FROM user WHERE 1=1
        <if test="username != null">
            and username=#{username}
        </if>
    </select>
</mapper>

测试：

   @Test
    public void testFindUserByCondition(){
        User user = new User();
        user.setUsername("老王");
        List<User> userByCondition = this.userDao.findUserByCondition(user);
        for (User user1 : userByCondition) {
            System.out.println(user1);
        }
    }

二、where的使用

使用< where>< /where>标签后，就可以不使用 where 1=1了。

<?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="com.ect.dao.IUserDao">
    <select id="findUserByCondition" parameterType="com.ect.domain.User" resultType="com.ect.domain.User">
        SELECT * FROM user
        <where>
            <if test="username != null">
                and username=#{username}
            </if>
            <if test="sex != null">
                and sex=#{sex}
            </if>
        </where>
    </select>
</mapper>

三、foreach的使用

QueryVo.java

public class QueryVo {
    private List<Integer> ids;

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

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

IUserDao.java

public interface IUserDao {
    /**
     * 根据Id查询
     * @param vo
     * @return
     */
    List<User> findUserByIds(QueryVo vo);
}

持久层Dao配置

    <select id="findUserByIds" resultType="com.ect.domain.User" parameterType="com.ect.domain.QueryVo">
        SELECT * FROM user
        <where>
            <if test="ids !=null and ids.size() > 0">
                <foreach collection="ids" open="id in (" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

测试：

    @Test
    public void testFindUserByIds(){
        QueryVo vo = new QueryVo();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(61);
        ids.add(62);
        ids.add(63);
        vo.setIds(ids);

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

        }
    }

四、sql标签（了解就行）

主要是用于处理重复的sql语句
< sql>编写

  <sql id="defaultSql">
        SELECT * FROM user
    </sql>

< include>引入

  <include refid="defaultSql"></include>