Mybatis中的动态sql

语句：if 标签、where标签、foreach标签、sql标签，这些SQL语句都是跟查询相关的。

在映射配置文件中，前面我们的SQL都是比较简单的，有时候业务逻辑复杂时，我们的SQL是动态变化的，

一、if标签：

动态SQL之<if>标签：查询条件有可能有，有可能没有，要根据不同的情况拼接查询条件。

我们根据实体类的不同取值，使用不同的SQL语句来进行查询。比如在id如果不为空时可以根据id查询，如果username不为空时，还要加入用户名作为条件，这种情况在我们的多条件组合查询中经常会碰到。

查询一个用户，参数为一个user对象，对象中有可能传了地址，有可能传了名称，还有可能传了性别，但是都不确定，这个时候该怎么做呢？

1、 在IUserDao接口中添加一个方法findUserByCondition，传入的参数为user对象，user对象中有可能有用户名，有可能有性别，也有可能有地址，还有可能都有，还有可能都没有。

public interface IUserDao {
    List<User> findUserByCondition(User user);
}

2、在映射配置文件中添加根据条件查询配置：查询用select标签

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

用if标签来做条件的判断，if标签的test属性的值为要判定的条件，注意不等于用！=，两个条件的话用and拼接，而不是java中的&&

3、测试根据条件查询操作：

public class MybatisTest {
    private InputStream in ;
    private SqlSession session;
    private IUserDao userDao;
    @Before
    public void init() throws Exception{
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3.使用工厂生产SqlSession对象
        session = factory.openSession(true);
        //4.使用SqlSession创建Dao接口的代理对象
        userDao = session.getMapper(IUserDao.class);
    }
    @After
    public void destroy() throws IOException {
        //session.commit();
        //6.释放资源
        session.close();
        in.close();
    }
    @Test
    public void testFindUserByCondition(){
        User user=new User();
        user.setUsername("小二王");
        user.setSex("女");
        List<User> users = userDao.findUserByCondition(user);
        for (User u : users) {
            System.out.println(u);
        }
    }
}

结果如下：

if标签判断字符串存在的问题

注意：使用if标签来做条件判断时，如果test属性中是根据单个字符串（如type）来进行判断的话，则要注意type的值要用双引号，而不能用单引号。

如果用单引号，则<if>标签判断不会生效，如下所示：

select * 
<if test="type == '1'">
    from user a
</if>
<if test="type == '2'">
    from app-user a
</if>

原因分析：mybatis是用OGNL表达式来解析的，在OGNL的表达式中，'1'或'2'这种类型的都会被解析成字符，而java又是强类型的，字符和单个字符串是不相等的，故会导致两边的类型不等，因此<if>标签中的sql不会被解析。要想相等，必须两边都是相同的类型。

方法一、type的值改为双引号

select * 
<if test='type == "1"'>
    from user a
</if>
<if test='type == "2"'>
    from app-user a
</if>

方法二、如果用了单引号则要通过toString()方法转成字符串。

select * 
<if test="type == '1'.toString()">
    from user a
</if>
<if test="type == '2'.toString()">
    from app-user a
</if>

重点注意：if标签在判断是否为空时，一定要使用and，不能使用or

<if test="null != indexName and indexName != ''">
    and s.index_name like concat('%', #{indexName},'%')
</if>

如果使用or，当indexName值为null，会出现如下的情况：

AND s.index_name LIKE concat( '%', null, '%' ) 

从而导致无法查询到数据。

二、where标签

为了简化上面where 1=1的条件拼装，我们可以采用where标签来简化开发，将所有的if标签加在where标签内部，此时就可以不用写where 1=1。

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

可以通过where标签去控制到底是加where还是加and

Where标签的使用可以避免我们在查询语句后面都加上一个永远为真的条件，从而使我们的sql语句看起来更清晰更简洁。

三、foreach标签：用于遍历集合，

在实际开发中可能有这样的需求：select * from user where id in （41、42、43、46）；可以把这些id放入list集合中， 在将集合包装在QueryVo对象中。参数类型为包装对象。

1、在QueryVo类中定义List<Integer> ids

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

2、在IUserDao接口中添加一个方法findUserInIds，传入的参数为queryVo对象

public interface IUserDao {
    List<User> findUserInids(QueryVo vo);
}

3、在映射配置文件中添加根据QueryVo对象对象中的id集合ids实现查询列表配置：查询用select标签

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

注意：#{}中的id要与item的属性值保持一致。

Foreach标签的写法：foreach标签的collection属性表示要遍历的集合元素，注意编写时不要写#{}，open表示语句的开始部分，close代表语句的结束部分。可以通过where标签去控制到底是加where还是and,item表示遍历集合的每个元素生成的变量名，Sperator代表分隔符。

4、测试foreach标签的使用：

public class MybatisTest {
    private InputStream in ;
    private SqlSession session;
    private IUserDao userDao;
    @Before
    public void init() throws Exception{
        //1.读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3.使用工厂生产SqlSession对象
        session = factory.openSession(true);
        //4.使用SqlSession创建Dao接口的代理对象
        userDao = session.getMapper(IUserDao.class);
    }
    @After
    public void destroy() throws IOException {
        //session.commit();
        //6.释放资源
        session.close();
        in.close();
    }
    @Test
    public void testFindUserInids(){
        QueryVo vo=new QueryVo();
        List<Integer> list=new ArrayList<Integer>();
        list.add(42);
        list.add(43);
        vo.setIds(list);
        List<User> users = userDao.findUserInids(vo);
        for (User u : users) {
            System.out.println(u);
        }
    }

}

结果如下：

四、sql标签：抽取重复的sql语句

Sql标签的id属性值为标签的唯一标识，

<sql id="defaultUser">
        SELECT * from user
    </sql>

再通过include标签引入：include标签的refid属性的值为sql标签的id属性值。

    <select id="findUserInids" resultType="user" parameterType="QueryVo">
        <include refid="defaultUser"></include>
        <where>
            <if test="ids!=null and ids.size()>0">
                <foreach collection="ids" open=" and id in( " close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

案例二

<sql id="prepareInfoList">
        <if test="">
           ...</if>
        <if test="">
           ...</if>
        <if test="">
           ...</if>
        <if test="">
            ...</if>
        <if test="">
           ...</if>
    </sql>

案例三

<!-- 基本列 -->
    <sql id="sys_audit_log">
    ID,TABLE_NAME,OLD_STR,TYPE,NEW_STR,SQL_STR,CREATE_TIME,UPDATE_TIME,CREATE_NAME
  </sql>

 

使用

<select id="xxx" resultMap="BaseResultMap">
        select <include refid="sys_audit_log"></include> from ...
    </select>

五、trim标签

trim标签的属性如下表：

属性名	说明
prefix	在sql语句前面拼接的字符串，即添加前缀
suffix	在sql语句后面拼接的字符串，即添加后缀
prefixOverrides	去除sql语句前面指定的字符或者关键字,即去除前缀，如and或or
suffixOverrides	去除sql语句后面指定的字符或者关键字，即去除后缀，如逗号

trim标签可以用来控制添加或去除前缀或后缀，

<update id="updateBatch">
        update t_zd_pri
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="WD =case" suffix="end,">
                <foreach collection="sitePrices" item="item" index="index">
                    <if test="item.unitPrice != null ">
                        when ID = #{item.id} then #{item.unitPrice}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="sitePrices" item="item" index="index" separator="," open="(" close=")">
            #{item.id}
        </foreach>
    </update>

其中：<trim prefix="set" suffixOverrides=",">表示添加set前缀set，去除后缀逗号。<trim prefix="WD =case" suffix="end,">表示添加前缀WD =case,添加后缀end,

六、choose标签

　　choose 标签是按顺序判断其内部 when 标签中的 test 条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。otherwise 也可以不写。

案例一

<choose>
                <when test="batchDate != null">
                    ...</when>
                <otherwise>
                    ...</otherwise>
            </choose>

案例二

<choose>
            <when test="rectifyStatus==0">
                ...</when>
            <when test="rectifyStatus==1">
                ...
            </when>
            <when test="rectifyStatus==2">
                ...
            </when>
            <when test="rectifyStatus==3">
                ...
            </when>
            <when test="rectifyStatus==4">
                ...
            </when>
        </choose>