Detailed explanation of the role and structure of MyBatis SQL mapping file

The MyBatis SQL mapping file defines SQL statements and how to map parameters and results of SQL statements to Java objects.

1. Function (Purpose)

MyBatis SQL mapping file (usually named) The main functions are:

1. Define SQL statements:Write SQL statements in XML mapping files, including SELECT, INSERT, UPDATE, DELETE and other types of SQL statements.
2. Parameter Mapping:Defines how to map parameters of Java methods to parameters in SQL statements. Available#and$Placeholder, and specify parameter type, jdbcType and other information.
3. Result Mapping:Defines how to map query results from SQL statements to Java objects. AvailableresultTypeorresultMap, and define one-to-one, one-to-many relationship mappings.
4. Dynamic SQL (Dynamic SQL):Use dynamic SQL elements provided by MyBatis (for example<if>, <choose>, <when>, <otherwise>, <where>, <set>, <foreach>), generate SQL statements dynamically according to different conditions.
5. Cache Configuration:Configure the cache behavior of SQL statements to improve query performance.
6. Associated with the Mapper interface:Associate the SQL map file with the Java Mapper interface, so that SQL statements can be executed by calling the methods of the Mapper interface.

In summary, SQL mapping files are the key to MyBatis decoupling SQL statements from Java code, which allows developers to focus on writing and optimizing SQL statements without caring about the details of JDBC.

2. Structure

The root element of the MyBatis SQL mapping file is<mapper>, it contains multiple child elements used to define SQL statements, parameter maps, result maps, etc.

Here is a typical SQL map file structure:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-////DTD Mapper 3.0//EN"
        "/dtd/">
<mapper namespace="">

    <!-- 1. cache (cache) -->
    <cache
            eviction="LRU"
            flushInterval="60000"
            readOnly="true"
            size="512"/>

    <!-- 2. resultMap (Result Mapping) -->
    <resultMap  type="">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="email" column="email"/>
        <!-- association (One-to-one) -->
        <association property="address" column="address_id" javaType=""
                     select=""/>
        <!-- collection (One to many) -->
        <collection property="orders" column="id" ofType=""
                    select=""/>
    </resultMap>

    <!-- 3. select (Query) -->
    <select  parameterType="int" resultMap="UserResultMap" useCache="true">
        SELECT * FROM user WHERE id = #{id}
    </select>

    <!-- 4. insert (insert) -->
    <insert  parameterType="" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user (username, email) VALUES (#{username}, #{email})
    </insert>

    <!-- 5. update (renew) -->
    <update  parameterType="">
        UPDATE user SET username = #{username}, email = #{email} WHERE id = #{id}
    </update>

    <!-- 6. delete (delete) -->
    <delete  parameterType="int">
        DELETE FROM user WHERE id = #{id}
    </delete>

    <!-- 7. sql (SQL Fragment) -->
    <sql >
        id, username, email
    </sql>

</mapper>

3. Detailed explanation of each element

<mapper>(root element):

• namespaceProperties: Specifies the fully qualified name of the Mapper interface. MyBatis will search for SQL mapping files based on this namespace and associate SQL statements with methods of the Mapper interface. This is the key to associating SQL mapping files with Java code.
• For example:<mapper namespace="">CorrespondingInterface file

<cache>(cache):

• Configure the second-level cache corresponding to the Mapper.
• evictionAttribute: Specifies the cached elimination policy (LRU, FIFO, SOFT, WEAK).
• flushIntervalProperties: Specifies the refresh interval of cache (milliseconds).
• sizeProperties: Specifies the maximum capacity of the cache.
• readOnlyProperties: Specifies whether the cache is read-only (trueorfalse)。
• <cache type=""/>Using custom cache

<cache-ref>(Cached reference):

• References to the cache configuration of other Mappers.
• namespaceProperties: Specifies the namespace of the Mapper to be referenced.

<resultMap>(Result Map):

effect:Defines how to map query results from SQL statements to Java objects.

• idProperties: SpecifyresultMapunique identifier.
• typeProperties: Specifies the Java type to map.

Sub-elements:

• <id>: Define primary key mapping.
  • propertyAttribute: Specifies the attribute name of the Java object.
  • columnAttribute: Specifies the column name in the database table.
• <result>: Define normal attribute maps.
  • propertyAttribute: Specifies the attribute name of the Java object.
  • columnAttribute: Specifies the column name in the database table.
• <association>: Define one-to-one relationship mapping.
  • propertyAttribute: Specifies the attribute name of the Java object.
  • columnAttribute: Specifies the column name in the database table.
  • javaTypeProperties: Specifies the Java type of the associated object.
  • selectProperties: Specifies the ID of the SQL statement used to query the associated object.
• <collection>: Define one-to-many relationship mapping.
  • propertyAttribute: Specifies the attribute name of the Java object.
  • columnAttribute: Specifies the column name in the database table.
  • ofTypeAttribute: Specifies the Java type of the element in the collection.
  • selectAttribute: Specifies the ID of the SQL statement used to query the collection element.
• <discriminator>: Define the discriminator and select different mapping rules according to different conditions.
  • columnAttribute: Specifies the column name used to authenticate.
  • <case>Element: Define different mapping rules.
    • valueAttribute: Specify the authentication value.
    • resultMapProperties: Specify the one to useresultMap。

Example:

<resultMap  type="">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="email" column="email"/>
    <association property="address" column="address_id" javaType=""
                 select=""/>
    <collection property="orders" column="id" ofType=""
                select=""/>
</resultMap>

<select>(Query):

• effect:Defines a SELECT statement to query data.
• idProperties: Specifies the unique identifier of the SQL statement. Correspond to the method name in the Mapper interface.
• parameterTypeProperties: Specify parameter type.
• resultTypeProperties: Specify the result type (if using a simple type map).
• resultMapAttribute: Specify the result map (if using<resultMap>element).
• parameterMapAttribute: (Original, not recommended)
• flushCacheProperties: If set totrue, then the cache will be refreshed every time the statement is executed.
• useCacheProperties: If set totrue, the result of this statement will be cached.
• timeoutProperties: Set the timeout time (seconds).
• fetchSizeProperties: Sets the number of records obtained from the database each time.
• statementTypeAttribute: Refers to the Statement type, optional values ​​are STATEMENT, PREPARED and CALLABLE.
• STATEMENT, default value, using Statement
• PREPARED, using PreparedStatement, can prevent SQL injection and perform better.
• CALLABLE, mainly used to execute stored procedures

Example:

<select  parameterType="int" resultMap="UserResultMap" useCache="true">
    SELECT * FROM user WHERE id = #{id}
</select>

<insert>(insert):

• effect:Defines an INSERT statement to insert data.
• idProperties: Specifies the unique identifier of the SQL statement.
• parameterTypeProperties: Specify parameter type.
• useGeneratedKeysProperties: Whether MyBatis is allowed to use JDBCgetGeneratedKeysMethod to obtain the autoincrement primary key value.
• keyPropertyAttribute: Specifies which attribute of a Java object is used to receive the autoincrement primary key value.
• keyColumnAttribute: Specifies the primary key column name of the database table.

Example:

<insert  parameterType="" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO user (username, email) VALUES (#{username}, #{email})
</insert>

<update>(renew):

• effect:Defines the UPDATE statement to update data.
• idProperties: Specifies the unique identifier of the SQL statement.
• parameterTypeProperties: Specify parameter type.

Example:

<update  parameterType="">
    UPDATE user SET username = #{username}, email = #{email} WHERE id = #{id}
</update>

<delete>(delete):

• effect:Defines a DELETE statement to delete data.
• idProperties: Specifies the unique identifier of the SQL statement.
• parameterTypeProperties: Specify parameter type.

Example:

<delete  parameterType="int">
    DELETE FROM user WHERE id = #{id}
</delete>

<sql>(SQL snippet):

• effect:Defines a snippet of SQL code that can be reused.
• idProperties: Specifies the unique identity of the SQL fragment.

Example:

<sql >
    id, username, email
</sql>

4. Dynamic SQL Elements

MyBatis provides the following dynamic SQL elements that can dynamically generate SQL statements based on different conditions:

<if>：

• effect:Determine whether a certain segment of SQL code is included based on the conditions.
• testAttribute: Specify the judgment conditions and use the OGNL expression.

Example:

<select  parameterType="" resultMap="UserResultMap">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </where>
</select>

<choose>, <when>, <otherwise>：

• effect:Similar to Javaswitch-caseA statement that selects one of the conditional branches.
• <choose>Element: Contains multiple<when>Element and one<otherwise>element.
• <when>Element: Specify the condition and the corresponding SQL code.
• <otherwise>Element: Specify the default SQL code (when all<when>Execute if none of the conditions are met).
• testProperties: Specify<when>The judgment conditions of elements are used, using OGNL expression.

Example:

<select  parameterType="" resultMap="UserResultMap">
    SELECT * FROM user
    <where>
        <choose>
            <when test="username != null and username != ''">
                AND username = #{username}
            </when>
            <when test="email != null and email != ''">
                AND email = #{email}
            </when>
            <otherwise>
                AND 1 = 1
            </otherwise>
        </choose>
    </where>
</select>

<where>：

• effect:Dynamic generationWHEREclauses and automatically processedANDorORKeywords.
• if<where>There is anything inside the element and it will be added automaticallyWHEREKeywords.
• if<where>The content inside the element isANDorORAt the beginning, it automatically removes these keywords.

Example:

<select  parameterType="" resultMap="UserResultMap">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </where>
</select>

<set>：

• effect:Dynamic generationSETclause, used in UPDATE statements and automatically handle commas,。
• if<set>There is anything inside the element and it will be added automaticallySETKeywords.
• if<set>The content inside the element is comma,At the beginning, it will automatically remove the comma.

Example:

<update  parameterType="">
    UPDATE user
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </set>
    WHERE id = #{id}
</update>

<trim>：

• effect:More general dynamic SQL elements that customize prefixes, suffixes, and what prefixes and suffixes are to be removed.
• prefixAttribute: Specify the prefix.
• suffixProperties: Specify the suffix.
• prefixOverridesProperties: Specifies the prefix to remove.
• suffixOverridesProperties: Specifies the suffix to remove.

Example:

<select  parameterType="" resultMap="UserResultMap">
    SELECT * FROM user
    <trim prefix="WHERE" prefixOverrides="AND |OR">
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </trim>
</select>

<update  parameterType="">
    UPDATE user
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null">
            username = #{username},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </trim>
    WHERE id = #{id}
</update>

<foreach>：

• effect:Used to traverse the collection and generate duplicate SQL code.
• collectionProperties: Specifies the collection to traverse.
• itemAttribute: Specifies the alias of the element in the collection.
• indexAttribute: Specifies the alias for the index of the current element.
• openProperties: Specifies the string at the beginning of the loop.
• closeProperties: Specifies the string at the end of the loop.
• separatorAttribute: Specifies the separator between elements.

Example:

<select  parameterType="list" resultMap="UserResultMap">
    SELECT * FROM user
    WHERE id IN
    <foreach item="id" collection="list" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<insert  parameterType="list">
    INSERT INTO user (username, email) VALUES
    <foreach item="user" collection="list" separator="," >
        (#{}, #{})
    </foreach>
</insert>

<bind>：

• effect:Create a variable and bind it to the result of an OGNL expression.
• nameAttribute: Specify the variable name
• valueAttribute: Specify an OGNL expression

Example:

<select  parameterType="string" resultMap="UserResultMap">
    <bind name="pattern" value="'%' + searchTerm + '%'" />
    SELECT * FROM user
    WHERE username LIKE #{pattern} OR email LIKE #{pattern}
</select>

5. DOCTYPE (Document Type Definition)

MyBatis SQL mapping files need to follow specific DTD constraints.

DTD Statement:

<!DOCTYPE mapper
        PUBLIC "-////DTD Mapper 3.0//EN"
        "/dtd/">

DTD file:

• The file defines the structure of the MyBatis SQL map file. The file can be found on the official MyBatis website.
• In development, we usually do not need to manually modify the DTD file, we only need to declare DTD in the XML configuration file.

6. Summary

The MyBatis SQL mapping file is the core of the MyBatis framework, which defines SQL statements and how to map parameters and results of SQL statements to Java objects.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.