Dynamic SQL

introduce

In the mapping file of Mybatis, our SQL is relatively simple. Sometimes when the business logic is complex, our SQL changes dynamically. At this time, our SQL can not meet the requirements in the previous study.

The official document on dynamic SQL reference is described as follows:

Previous problems

Example of multi criteria query:

How to write our SQL syntax at this time?

If you still use the previous method, you need to write two SQL statements. If you have more, the amount of code will become larger and larger!

So we need to use dynamic SQL

We just need to learn if and foreach.

< if > of dynamic SQL

introduce

< where >: condition label. If there are dynamic conditions, use this tag instead of the where keyword

< if >: condition judgment label

grammar

< if test = "condition judgment" >

Query condition splicing

</if>

For example, the above case:

We just put studentmapper XML, write as follows!!!

Note: Yes #{}, which means ${}!!! I made a mistake just now. I've been looking for bug s for a long time!!!

<select id="selectCondition" resultType="student" parameterType="student">
    SELECT * FROM student
    <where>
        <if test="id != null">
            id = #{id}
        </if>
        <if test="name != null">
            AND name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

< foreach > of dynamic SQL

introduce

< foreach >: loop through labels. Applies to relationships with multiple parameters or

grammar

Collection: parameter container type, (list - collection, array - array)

open: start SQL statement

close: end SQL statement

item: variable name of the parameter

Separator: separator

<foreach collection=""open=""close=""item=""separator="">
Get parameters
</foreach>

Perform sql splicing operations circularly, for example: SELECT * FROM student WHERE id IN (1,2,5).

<select id="selectByIds" resultType="student" parameterType="list">
    <!-- SELECT * FROM student WHERE id IN (1,2,5) -->
    SELECT * FROM student
    <where>
        <foreach collection="list" open="id IN (" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

The parameterType here is list, indicating that the parameter passed in is a collection type; item is id, which means that a variable named id is used to receive each element in the list.

For the SQL statement: SELECT * FROM student WHERE id IN (1,2,5), the above xml will not write "dead" in SQL! list passes several IDS, and there are several values in "()"!

SQL fragment extraction

introduce

In SQL, duplicate SQL can be extracted and referenced with include when used, so as to achieve the purpose of SQL reuse.

< SQL >: extract SQL statement labels.  
< include >: introduce SQL fragment label.  

grammar

< SQL id = "fragment unique ID" > extracted SQL statement < / SQL >

< include refid = "fragment unique identifier" / >

For example, in studentmapper There are many SELECT * FROM student * statements in XML! We can extract them

 

In studentmapper Write under the < mapper > root tag in the XML file:

<sql id="select">SELECT * FROM student</sql>

Then replace it with the < include > tag where the statement is used

<select id="selectAll" resultType="student">
    <include refid="select"/>
</select>

Modified complete studentmapper The XML is as follows

<?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: Core root label
    namespace Attributes: namespaces
-->

<mapper namespace="com.itheima.mapper.StudentMapper">

    <sql id="select">SELECT * FROM student</sql>

    <!--
    select: Label of query function
    id Attribute: unique identification
    resultType Property: Specifies the result mapping object type
    parameterType Property: Specifies the type of parameter mapping object
    -->
    <select id="selectAll" resultType="student">
        <include refid="select"/>
    </select>
    
    <select id="selectById" resultType="student" parameterType="int">
        <include refid="select"/> WHERE id = #{id}
    </select>

    <!--
        Returns a int Number of rows of type! Therefore, it can be omitted resultType
        But, SQL Parameters of the statement id,name,age It comes from students, so there should be parameterType
    -->
    <insert id="insert" parameterType="student">
        INSERT INTO student VALUES (#{id},#{name},#{age})
    </insert>

    <update id="update" parameterType="student">
        UPDATE student SET name = #{name},age = #{age} WHERE id = #{id}
    </update>

    <!-- java.lang.Integer -> int-->
    <delete id="delete" parameterType="int">
        DELETE FROM student WHERE id = #{id}
    </delete>

    <select id="selectCondition" resultType="student" parameterType="student">
        <include refid="select"/>
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="name != null">
                AND name = #{name}
            </if>
            <if test="age != null">
                AND age = #{age}
            </if>
        </where>
    </select>

    <select id="selectByIds" resultType="student" parameterType="list">
        <include refid="select"/>
        <where>
            <foreach collection="list" open="id IN (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

Paging plug-in

introduce