Mybatis framework 5: dynamic SQL

1.if   where

Implement a simple requirement:

Query users by gender and name:

Write normally:

    <select id="selectUserBySexAndUsername" parameterType="pojo.User"
        resultType="pojo.User">
        select * from user where sex = #{sex} and username = #{username}
    </select>

 

Disadvantages: the user of the incoming parameter must have a gender and a name

 

So I made some changes:

    <select id="selectUserBySexAndUsername" parameterType="pojo.User"
        resultType="pojo.User">
        select * from user
        where
        <if test="sex != null and sex != ''">
            sex = #{sex}
        </if>
        <if test="username != null and username != ''">
            and username = #{username}
        </if>
    </select>

 

It seems OK, but if the sex does not exist and the username exists, the SQL statement is wrong

 

In this case, just add a Where tag:

    <select id="selectUserBySexAndUsername" parameterType="pojo.User"
        resultType="pojo.User">
        select * from user
        <where>
            <if test="sex != null and sex != ''">
                sex = #{sex}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
        </where>
    </select>

 

Now you can meet the requirements. As long as there is at least one condition, the query will succeed

 

 

2.SQL fragment:

Many SQL statements in SQL tags are repeated. Can we propose to share them?

Sure:

    <sql id="selector">
        select * from user
    </sql>

    <select id="selectUser">
        <include refid="selector" />
        <where>
            <if test="sex != null and sex != ''">
                sex = #{sex}
            </if>
            <if test="username != null and username != ''">
                and username = #{username}
            </if>
        </where>
    </select>

 

This design idea is worth learning, but it will not be used in actual development

 

3.foreach

Scenario: query users based on multiple ID S:

First write a wrapper class, which contains a List of user ID s

(the way to show the wrapper class first is because there will be holes in the array or List directly, which will be explained below.)

package pojo;

import java.io.Serializable;
import java.util.List;

public class QueryVo implements Serializable {

    private static final long serialVersionUID = 1L;private List<Integer> idsList;public List<Integer> getIdsList() {
        return idsList;
    }

    public void setIdsList(List<Integer> idsList) {
        this.idsList = idsList;
    }
    
}

 

Enquiries:

    <select id="selectUserByIds" parameterType="pojo.QueryVo"
        resultType="pojo.User">
        select * from user
        <where>
            <foreach collection="idsList" item="id" separator="," open="id in ("
                close=")">
                #{id}
            </foreach>
        </where>
    </select>

 

However, special attention should be paid here: if the foreach loop is not a List set, but a simple array:

collection cannot be written as property name (such as idsList here), but as array

 

So it can be seen that if our foreach loop is not a wrapper class property, but a List set,

Collection should also be written as a list.

Tags: Java SQL Fragment

Posted on Sun, 12 Jan 2020 08:02:52 -0800 by pastijalan