Catalog

9. Dynamic SQL of Mybatis Mapping Files

9.1 Label Use

9.2 Label Use

9.3 Label Use

9.4 Simplified SQL Fragments in Mybatis: Use of Labels

9. Dynamic SQL of Mybatis Mapping Files

9.1 Use of <if> Labels

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

9.2 Use of <where> Labels

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

9.3 Use of <foreach> Labels

Here we first create a QueryVo class to encapsulate a List collection:

QueryVo.java

package com.cpz.domain;

import java.util.ArrayList;
import java.util.List;

public class QueryVo {
    private User user;
    private List<Integer> ids = new ArrayList<Integer>();

    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;
    }
}

UserDao.xml

<!--4.3 dynamic SQL Labeled<foreach>Label
    collection: Use ognl read queryVo Collection attribute names in entities
    open: sql Prefix of statement
    close: sql The suffix of a statement
    item: Identification of each value traversing a set
    separator: A delimiter that traverses each value of a set
 -->
<select id="findByCondition2" resultType="user" parameterType="user">
     select * from user
     <where>
        <foreach collection="ids" open=" and id in (" close=")" item="id" separator=",">
            #{id}
        </foreach>
     </where>
</select>

TestUser.java

public void findByCondition2(){
    List<Integer> ids = new ArrayList<Integer>();
    ids.add(41);
    ids.add(42);
    ids.add(43);
    QueryVo queryVo = new QueryVo();
    queryVo.setIds(ids);
    List<User> list = userDao.findByCondition2(queryVo);
    for (User user : list) {
        System.out.println(user);
    }
}

9.4 Simplified SQL Fragment in Mybatis: <sql> Label Use

 

You can extract duplicate parts of sql statements, similar to: select * from user, using include as a reference

<sql id="defaultSql">
    select * from user
</sql> 
  
<select id="findByCondition" parameterType="user" resultType="user">
    <include refid="defaultSql"></include>
    <where>
        <if test="username != null">
            and username like #{username}
        </if>
        <if test="sex != null">
            and sex = #{sex}
        </if>
    </where>
</select>