12.mybatis dynamic splicing sql

1. Demand: query users by gender and name
Query sql:
 

SELECT id, username, birthday, sex, address FROM `user` WHERE sex = 1 AND username LIKE '%Zhang%'

2. Use if tag
Modify UserMapper.xml as follows:

<!-- Query users by criteria -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
    SELECT id, username, birthday, sex, address FROM `user`
    WHERE 1=1
    <if test="sex != null and sex != ''">
        AND sex = #{sex}
    </if>
    <if test="username != null and username != ''">
        AND username LIKE
        '%${username}%'
    </if>
</select>


Note that data of string type needs to be verified not equal to empty string.

This code should be generated in a program (such as Java), and the conditions after 1 = 1 in the where condition are dynamically changed through the if block. For example:
String sql="select * from table_name where 1=1";
if( conditon 1) {
sql=sql+" and var2=value2";
}
if(conditon 2) {
sql=sql+" and var3=value3";
}
where 1=1 is to avoid the syntax error caused by the first word after the where keyword is "and".


3.Where label
The above sql also has the statement where 1=1, which is very troublesome
where tags can be used for modification

Modify UserMapper.xml as follows
<! -- Query users by criteria -- >
 

<select id="queryUserByWhere" parameterType="user" resultType="user">
    SELECT id, username, birthday, sex, address FROM `user`
<!-- where Labels can be added automatically where,Simultaneous processing sql First in statement and Keyword -->
    <where>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
        <if test="username != null and username != ''">
            AND username LIKE
            '%${username}%'
        </if>
    </where>
</select>

4.Sql fragment
In SQL, the repeated SQL can be extracted and used with include reference. Finally, the purpose of SQL reuse can be achieved.

<select id="queryUserByWhere" parameterType="user" resultType="user">
    <!-- SELECT id, username, birthday, sex, address FROM `user` -->
    <!-- Use include Label loading sql Fragments; refid yes sql fragment id -->

    SELECT <include refid="userFields" /> FROM `user`

    <!-- where Labels can be added automatically where Keywords, processing at the same time sql First in statement and Keyword -->
    <where>
        <if test="sex != null">
            AND sex = #{sex}
        </if>
        <if test="username != null and username != ''">
            AND username LIKE
            '%${username}%'
        </if>
    </where>
</select>


<! -- declare sql fragment -- >
 

<sql id="userFields">
    id, username, birthday, sex, address
</sql>

5.foreach label
Pass array or List to sql, and mybatis uses foreach to parse, as follows:

Query user information based on multiple IDS
Query sql:
SELECT * FROM user WHERE id IN (1,10,24)

<!-- according to ids Query users -->
<select id="queryUserByIds" parameterType="queryVo" resultType="user">
    SELECT * FROM `user`
    <where>
        <!-- foreach Tags, traversing -->
        <!-- collection: Traversal set, here is QueryVo Of ids attribute -->
        <!-- item: Traversal of the project, you can write freely, but with the following#Be consistent inside -- >
        <!-- open: Added before sql fragment -->
        <!-- close: Added at the end sql fragment -->
        <!-- separator: Specifies the separator used between traversal elements -->
        <foreach collection="ids" item="item" open="id IN (" close=")"
            separator=",">
            #{item}
        </foreach>
    </where>
</select>

 

Tags: SQL Fragment xml Java

Posted on Thu, 09 Jan 2020 14:08:42 -0500 by refiking