Detailed explanation of Mybatis optimization search method

Mapping

• MyBatis allows developers to write native SQL or dynamic SQL directly, rather than relying on SQL generated by the ORM framework.
• This method can accurately control SQL execution and optimize the performance of query statements, especially when handling complex queries
• Example：
• 1-Database table structure
• Suppose there are two tables:userandrole, and an association tableuser_role

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE role (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(255)
);

CREATE TABLE user_role (
    user_id INT,
    role_id INT,
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (role_id) REFERENCES role(id)
);

• 2-MyBatis configuration file ()
• This is the global configuration file of MyBatis, which sets up some basic configuration and environment information.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-////DTD Config 3.0//EN"
  "/dtd/">
<configuration>
    <environments default="development">
        <environment >
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value=""/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_demo"/>
                <property name="username" value="root"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource=""/>
    </mappers>
</configuration>

• 3-Mapper XML file ()
• This file defines how SQL maps and operates databases

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-////DTD Mapper 3.0//EN"
  "/dtd/">
<mapper namespace="">
    <select  resultType="">
        SELECT * FROM user WHERE id = #{id}
    </select>

    <resultMap  type="">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <collection property="roles" ofType="">
            <id column="role_id" property="id"/>
            <result column="role_name" property="roleName"/>
        </collection>
    </resultMap>

    <select  resultMap="userRoleMap">
        SELECT , ,  AS role_id, r.role_name
        FROM user u
        LEFT JOIN user_role ur ON  = ur.user_id
        LEFT JOIN role r ON ur.role_id = 
        WHERE  = #{id}
    </select>
</mapper>

2. Result Mapping

• MyBatis provides a highly flexible result mapping mechanism that can map database results directly into complex object models.
• It allows you to map the result set returned by SQL queries into Java objects
• This reduces the overhead of data conversion and processing, making data loading more efficient
• Example：
• Configure a simple result map in the Mapper XML of MyBatis as follows:

<!-- Mapper XML Configuration -->
<mapper namespace="">
    <!-- Define the result map -->
    <resultMap  type="">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="email" property="email" />
    </resultMap>

    <!-- Query the user using the defined result map -->
    <select  resultMap="BaseResultMap">
        SELECT id, username, email FROM user WHERE id = #{id}
    </select>
</mapper>

• In this example
• BaseResultMapDefines how touserColumns of tableid、username,andemailMapping to Java objectsUserIn the corresponding properties of

3. Delay loading

• MyBatis supports lazy loading technology, allowing associated objects to be loaded on demand
• This means that the corresponding SQL query will be executed only when an associated object is used
• This strategy can significantly reduce the number of database accesses and network latency when initializing an object
• MyBatis supports lazy loading configurations inSet in  for example:

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

• This configuration ensures that the role data will be loaded in the database only when the user's role information is actually needed to use, so
• This delayed loading can improve the performance of the application, especially when the data structure is complex or the data volume is large

4. Cache support

• MyBatis has a powerful cache mechanism built-in to store the results of the query that has been executed.
• In order to quickly return the results in the same subsequent query request without the need to execute the actual database query again
• Includes first-level cache (session cache) and second-level cache (global cache)
• Level 1 cache is enabled by default, ensuring that the results of the same query in the same session can be reused
• The secondary cache can reuse query results across sessions, reduce the number of database queries and improve application performance

Level 1 Cache：

• Range of action: Level 1 cache is based on SQL sessions, and it is only valid within the current session
• Level 1 cache is also cleared when the session is closed or submitted
• Implementation method: By default, each SQL session of MyBatis has its own level 1 cache
• Whenever a query is executed in a session, MyBatis first checks whether the corresponding result already exists in the cache.
• If there is, the cache result will be returned directly; if there is no, the database query will be executed and the query result will be placed in the cache
• Cache content: The cached content includes query result objects and executed query statements

Level 2 cache (Global Cache)：

• Range of action: Level 2 cache is cross-session, and it can be shared by different sessions
• The scope of the secondary cache is usually at the mapper level, and different mappers hold their respective secondary caches.
• Configuration method: Level 2 cache needs to be explicitly enabled in the MyBatis configuration file or mapping file
• After turning on, query results can be reused across sessions, thereby reducing the number of queries to the database and improving efficiency.
• Implementation technology: Level 2 cache can use the simple implementation provided by MyBatis, or integrate more powerful cache solutions, such as Ehcache, Redis, etc.

Sample configuration：

• An example of enabling Level 2 cache in MyBatis' Mapper XML:

<!-- Turn on the current Mapper Level 2 cache -->
<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>

Cache Policy：

• eviction: Cache recycling strategy
• Commonly used areLRU(Least Recently Used Minimum policy usage),FIFO(First In First Out First Out strategy) etc.
• flushInterval: Cache refresh interval, unit is milliseconds
• After setting, the cache will be automatically cleared after the specified time interval.
• size: The number of references, set how many objects can be stored in the cache
• readOnly: Read only
• Iftrue, then all retrieved cache objects are read-only, which can provide some performance advantages

5. Batch operation optimization

• MyBatis provides batch processing support, allowing multiple update operations to be performed in one session
• This can reduce the number of database interactions, reduce network overhead and database pressure

6. Dynamic SQL

• MyBatis' dynamic SQL function allows the construction of SQL statements evaluated at runtime and assemble different SQL fragments according to different conditions.
• This not only improves SQL flexibility, but also avoids unnecessary query conditions, thereby optimizing query efficiency
• Key Components of Dynamic SQL：
• 1-<if>Label：
• Decide whether to include a SQL fragment based on whether the condition is true
• This can be used to add optional query conditions or modify other SQL constructs
• 2-<choose>, <when>, <otherwise>Label：
• Similar to the one in JavaswitchStatement, selectively including SQL fragments based on multiple conditions
• 3-<where>Label：
• Automatically process SQL queriesWHEREclause, if its internal condition is true, insertWHEREKeywords
• At the same time, it also intelligently handles unnecessaryANDorORKeywords
• 4-<set>Label：
• For dynamic constructionUPDATEStatement, automatically process the list of column assignments, and intelligently insert commas when needed
• 5-<foreach>Label：
• Used to build duplicate SQL fragments in SQL statements, such as batch insertion of data or buildingINClause
• 6-<sql>and<include>Label：
• Used for SQL fragment extraction
• <sql>: Extract the tags of SQL statements
• <sql >Extracted SQL statements</sql>
• <include>:Introduce SQL fragment tags
• <include ref />
• Example：
• Here are some examples of dynamic SQL that show how to use them in MyBatis' Mapper XML

<!-- Mapper XML Examples in the file -->
<mapper namespace="">
    <!-- Dynamic generation SELECT Query -->
    <select  resultType="User">
        <where>
            <if test="username != null">
                AND username = #{username}
            </if>
            <if test="email != null">
                AND email = #{email}
            </if>
        </where>
    </select>

    <!-- Dynamic generation UPDATE Query -->
    <update >
        UPDATE users
        <set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="email != null">
                email = #{email},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- use foreach generate IN Clause -->
    <select  resultType="User">
        SELECT * FROM users
        WHERE id IN
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>

7. Plug-ins and interceptors

• MyBatis allows plugins to intercept and modify query processes
• Developers can add or rewrite the behavior of certain operations through plugins
• If the parameter processing before the query or the result processing after the execution, further optimize the query performance
• The MyBatis plugin is essentially implemented through an interceptor.
• MyBatis interceptor is a component that implements the Interceptor interface, which is used to insert custom logic in key links of SQL execution.

The above is the detailed explanation of the Mybatis optimization search method. For more information about Mybatis optimization search, please pay attention to my other related articles!