AI-reviewed An AI proof-read this post, but it still deserves a careful read.

A complete guide to MyBatis’s complete knowledge system

This document comprehensively covers MyBatis ‘theoretical knowledge and command operations, from basic concepts to advanced actual combat, without omission.

MyBatis Overview

What is MyBatis

MyBatis is an SQL mapping and persistence layer framework. It allows developers to write their own SQL and is responsible for repetitive tasks such as parameter binding, result mapping, and transaction collaboration. MyBatis is often referred to as “semi-automated ORM”, but it does not automatically generate all SQL based on entity relationships like the fully automated ORM framework does.

MyBatis, formerly known as iBatis, was migrated from Apache to Google Code in 2010 and renamed MyBatis, and later migrated to GitHub.

MyBatis ‘positioning

characteristics	JDBC	MyBatis	Hibernate (JPA)
SQL control	fully manual	Semi-automatic (handwritten SQL, automatic mapping)	Fully automatic (automatic generation SQL）
learning cost	High (a lot of boilerplate code)	in	high
flexibility	highest	high	lower
controllable performance	is	is	Difficult (needs to be adjusted)
Database portability	difference	in	high
Suitable for the scene	—	complex SQL, high performance requirements	Rapid development and simple CRUD

Core features of MyBatis

 ┌────────────────────────────────────────────────────┐
 │              MyBatis 核心特性                        │
 ├──────────────┬──────────────┬─────────────────────┤
 │   SQL 映射    │  动态 SQL    │  高级映射            │
 ├──────────────┼──────────────┼─────────────────────┤
 │ XML/注解配置  │ 条件拼接      │ 一对一/一对多/多对多  │
 │ 参数自动绑定  │ 循环遍历      │ 嵌套结果/嵌套查询     │
 │ 结果自动映射  │ SQL 片段复用  │ 鉴别器（discriminator）│
 ├──────────────┼──────────────┼─────────────────────┤
│   缓存机制    │  插件机制     │  扩展能力            │
├──────────────┼──────────────┼─────────────────────┤
│ 一级缓存      │ 拦截器        │ TypeHandler          │
│ 二级缓存      │ 分页插件      │ ObjectFactory        │
│ 第三方缓存    │ SQL 审计      │ 语言驱动             │
└──────────────┴──────────────┴─────────────────────┘

MyBatis Architecture

                     ┌─────────────────┐
                     │   应用程序层      │
                     │  (Service/DAO)   │
                     └────────┬────────┘
                              │
                     ┌────────▼────────┐
                     │  SqlSession 接口  │
                     │ (selectOne/list  │
                     │  insert/update   │
                    │  delete/commit)  │
                    └────────┬────────┘
                             │
                    ┌────────▼────────┐
                    │  Executor 执行器  │
                    │  (Simple/Reuse   │
                    │   Batch)         │
                    └────────┬────────┘
                             │
              ┌──────────────┼──────────────┐
              │              │              │
     ┌────────▼───┐  ┌──────▼─────┐  ┌────▼────────┐
     │ Statement  │  │ Parameter  │  │  ResultSet  │
     │ Handler    │  │ Handler    │  │  Handler    │
     │(SQL预处理)  │  │(参数绑定)   │  │(结果映射)    │
     └────────┬───┘  └────────────┘  └─────────────┘
              │
     ┌────────▼───┐
     │   JDBC     │
     │  数据库     │
     └────────────┘

MyBatis version

MyBatis 3 is currently a commonly used main version series in courses and practical projects. Example use in this article 3.5.15, the version number is only used to ensure that the example is reproducible; the actual project should be based on the version determined in dependency management, and the release notes of the corresponding version should be consulted when upgrading.

quick start

sample database

This document uses the following example databases uniformly:

 -- 部门表
 CREATE TABLE dept (
     dept_id   INT PRIMARY KEY AUTO_INCREMENT,
     dept_name VARCHAR(50) NOT NULL,
     location  VARCHAR(100)
 );
 -- 员工表
 CREATE TABLE emp (
    emp_id     INT PRIMARY KEY AUTO_INCREMENT,
    emp_name   VARCHAR(50) NOT NULL,
    email      VARCHAR(100),
    salary     DECIMAL(10,2),
    dept_id    INT,
    hire_date  DATE,
    status     TINYINT DEFAULT 1,  -- 1:在职 0:离职
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);
-- 项目表（多对多）
CREATE TABLE project (
    project_id   INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    budget       DECIMAL(12,2)
);
-- 员工-项目关联表（多对多）
CREATE TABLE emp_project (
    emp_id     INT,
    project_id INT,
    role       VARCHAR(50),
    PRIMARY KEY (emp_id, project_id),
    FOREIGN KEY (emp_id) REFERENCES emp(emp_id),
    FOREIGN KEY (project_id) REFERENCES project(project_id)
);

Maven Depends

 <!-- MyBatis 核心 -->
 <dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis</artifactId>
     <version>3.5.15</version>
 </dependency>
 <!-- MySQL 驱动 -->
 <dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>
<!-- 日志 -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>2.0.12</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.4.14</version>
</dependency>
<!-- Lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.30</version>
    <scope>provided</scope>
</dependency>

entity class

 package com.example.entity;
 import lombok.Data;
 import java.math.BigDecimal;
 import java.time.LocalDate;
 import java.util.List;
 @Data
 public class Emp {
    private Integer empId;
    private String empName;
    private String email;
    private BigDecimal salary;
    private Integer deptId;
    private LocalDate hireDate;
    private Integer status;
    // 关联对象
    private Dept dept;
    // 关联集合（多对多）
    private List<Project> projects;
}
@Data
public class Dept {
    private Integer deptId;
    private String deptName;
    private String location;
    // 关联集合（一对多）
    private List<Emp> emps;
}
@Data
public class Project {
    private Integer projectId;
    private String projectName;
    private BigDecimal budget;
    private List<Emp> emps;
}

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
     <!-- 属性配置 -->
     <properties resource="db.properties"/>
     <!-- 设置 -->
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="cacheEnabled" value="true"/>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="defaultExecutorType" value="REUSE"/>
        <setting name="logImpl" value="SLF4J"/>
    </settings>
    <!-- 别名 -->
    <typeAliases>
        <package name="com.example.entity"/>
    </typeAliases>
    <!-- 环境配置 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 映射器 -->
    <mappers>
        <package name="com.example.mapper"/>
    </mappers>
</configuration>

db.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mydb?
    useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=root123

Mapper interface

 package com.example.mapper;
 import com.example.entity.Emp;
 import java.util.List;
 public interface EmpMapper {
     // 查询所有员工
     List<Emp> selectAll();
    // 根据 ID 查询
    Emp selectById(Integer empId);
    // 插入
    int insert(Emp emp);
    // 更新
    int update(Emp emp);
    // 删除
    int deleteById(Integer empId);
}

Mapper XML

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
         "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <mapper namespace="com.example.mapper.EmpMapper">
     <select id="selectAll" resultType="Emp">
         SELECT * FROM emp
     </select>
    <select id="selectById" resultType="Emp" parameterType="int">
        SELECT * FROM emp WHERE emp_id = #{empId}
    </select>
    <insert id="insert" parameterType="Emp">
        INSERT INTO emp (emp_name, email, salary, dept_id, hire_date, status)
        VALUES (#{empName}, #{email}, #{salary}, #{deptId}, #{hireDate}, #{status})
    </insert>
    <update id="update" parameterType="Emp">
        UPDATE emp SET
            emp_name = #{empName},
            email = #{email},
            salary = #{salary},
            dept_id = #{deptId},
            status = #{status}
        WHERE emp_id = #{empId}
    </update>
    <delete id="deleteById" parameterType="int">
        DELETE FROM emp WHERE emp_id = #{empId}
    </delete>
</mapper>

tool class

 package com.example.util;
 import org.apache.ibatis.io.Resources;
 import org.apache.ibatis.session.SqlSession;
 import org.apache.ibatis.session.SqlSessionFactory;
 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 import java.io.InputStream;
 public class MyBatisUtil {
    private static final SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception e) {
            throw new RuntimeException("MyBatis 初始化失败", e);
        }
    }
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
    public static SqlSession getSqlSession(boolean autoCommit) {
        return sqlSessionFactory.openSession(autoCommit);
    }
}

test

public class MyBatisTest {
    @Test
    public void testSelectAll() {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            EmpMapper mapper = session.getMapper(EmpMapper.class);
             List<Emp> emps = mapper.selectAll();
             emps.forEach(System.out::println);
         }
    }
    @Test
    public void testInsert() {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            EmpMapper mapper = session.getMapper(EmpMapper.class);
            Emp emp = new Emp();
            emp.setEmpName("张三");
            emp.setEmail("zhangsan@example.com");
            emp.setSalary(new BigDecimal("15000"));
            emp.setDeptId(1);
            emp.setHireDate(LocalDate.now());
            emp.setStatus(1);
            int rows = mapper.insert(emp);
            session.commit();  // 必须手动提交
            System.out.println("插入行数: " + rows + ", 主键: " + emp.getEmpId());
        }
    }
}

Core configuration file (mybatis-config.xml)

Complete configuration structure

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
         "https://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
     <!-- 1. 属性：引入外部 properties 文件 -->
     <properties resource="db.properties">
         <!-- 也可以在这里定义默认属性 -->
         <property name="username" value="root"/>
    </properties>
    <!-- 2. 设置：全局行为配置 -->
    <settings>
        <!-- 驼峰命名自动映射（emp_name → empName） -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 二级缓存开关 -->
        <setting name="cacheEnabled" value="true"/>
        <!-- 延迟加载开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 是否按需加载（3.4.2+默认true） -->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!-- 延迟加载触发方法 -->
        <setting name="lazyLoadTriggerMethods"
     value="equals,clone,hashCode,toString"/>
        <!-- 默认执行器类型 -->
        <setting name="defaultExecutorType" value="REUSE"/>
        <!-- 默认语句超时（秒） -->
        <setting name="defaultStatementTimeout" value="30"/>
        <!-- 默认获取大小 -->
        <setting name="defaultFetchSize" value="100"/>
        <!-- 本地缓存范围 -->
        <setting name="localCacheScope" value="SESSION"/>
        <!-- 默认事务隔离级别 -->
        <setting name="defaultTransactionIsolationLevel" value="READ_COMMITTED"/>
        <!-- 日志实现 -->
        <setting name="logImpl" value="SLF4J"/>
        <!-- 代理工厂（CGLIB/JAVASSIST） -->
        <setting name="proxyFactory" value="JAVASSIST"/>
        <!-- 多结果集 -->
        <setting name="multipleResultSetsEnabled" value="true"/>
        <!-- 列名标签前缀 -->
        <setting name="useColumnLabel" value="true"/>
        <!-- 使用生成的主键 -->
        <setting name="useGeneratedKeys" value="false"/>
        <!-- 自动映射行为 -->
        <setting name="autoMappingBehavior" value="PARTIAL"/>
        <!-- 自动映射未知列行为 -->
        <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
        <!-- 返回行数上限 -->
        <setting name="safeRowBoundsEnabled" value="false"/>
        <!-- 安全的 ResultHandler -->
        <setting name="safeResultHandlerEnabled" value="true"/>
        <!-- 指定 MyBatis 语言驱动 -->
        <setting name="defaultScriptingLanguage"
     value="org.apache.ibatis.scripting.xmltags.XMLLanguageDriver"/>
        <!-- 枚举类型默认处理器 -->
        <setting name="defaultEnumTypeHandler"
     value="org.apache.ibatis.type.EnumTypeHandler"/>
        <!-- NULL 值的 JDBC 类型 -->
        <setting name="jdbcTypeForNull" value="OTHER"/>
    </settings>
    <!-- 3. 类型别名 -->
    <typeAliases>
        <!-- 单个类配置 -->
        <typeAlias type="com.example.entity.Emp" alias="Emp"/>
        <!-- 包扫描（默认用类名首字母小写） -->
        <package name="com.example.entity"/>
    </typeAliases>
    <!-- 4. 类型处理器 -->
    <typeHandlers>
        <typeHandler handler="com.example.handler.StringListTypeHandler"/>
        <package name="com.example.handler"/>
     </typeHandlers>
     <!-- 5. 对象工厂 -->
     <objectFactory type="com.example.factory.CustomObjectFactory">
         <property name="someProperty" value="100"/>
     </objectFactory>
     <!-- 6. 插件（拦截器） -->
     <plugins>
         <plugin interceptor="com.example.plugin.MyPagePlugin">
             <property name="dialect" value="mysql"/>
         </plugin>
     </plugins>
     <!-- 7. 环境配置 -->
     <environments default="development">
         <environment id="development">
             <transactionManager type="JDBC"/>
             <dataSource type="POOLED">
                 <property name="driver" value="${jdbc.driver}"/>
                 <property name="url" value="${jdbc.url}"/>
                 <property name="username" value="${jdbc.username}"/>
                 <property name="password" value="${jdbc.password}"/>
                 <!-- 连接池配置 -->
                 <property name="poolMaximumActiveConnections" value="10"/>
                 <property name="poolMaximumIdleConnections" value="5"/>
                 <property name="poolMaximumCheckoutTime" value="20000"/>
                 <property name="poolTimeToWait" value="20000"/>
                <property name="poolPingEnabled" value="true"/>
                <property name="poolPingQuery" value="SELECT 1"/>
                <property name="poolPingConnectionsNotUsedFor" value="3600000"/>
            </dataSource>
        </environment>
        <environment id="production">
            <transactionManager type="MANAGED"/>
            <dataSource type="JNDI">
                <property name="initialContext" value="java:comp/env"/>
                <property name="dataSource" value="jdbc/myDataSource"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 8. 数据库厂商标识 -->
    <databaseIdProvider type="DB_VENDOR">
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle"/>
        <property name="PostgreSQL" value="pg"/>
    </databaseIdProvider>
    <!-- 9. 映射器 -->
    <mappers>
        <!-- 方式一：资源路径 -->
        <mapper resource="mapper/EmpMapper.xml"/>
        <!-- 方式二：URL -->
        <mapper url="file:///var/mappers/EmpMapper.xml"/>
        <!-- 方式三：接口类 -->
        <mapper class="com.example.mapper.EmpMapper"/>
        <!-- 方式四：包扫描 -->
        <package name="com.example.mapper"/>
    </mappers>
</configuration>

Configure element order

mybatis-config.xml The top-level elements of must appear in the following order. Elements that are not used can be omitted, but the order cannot be exchanged at will.

1. properties
2. settings
3. typeAliases
4. typeHandlers
5. objectFactory
6. objectWrapperFactory
7. reflectorFactory
8. plugins
9. environments
10. databaseIdProvider
11. mappers

Detailed explanation of common Settings

setting item	optional value	default value	description
`cacheEnabled`	`true`、`false`	`true`	Whether to globally enable mapper L2 caching
`lazyLoadingEnabled`	`true`、`false`	`false`	Whether to enable delayed loading
`aggressiveLazyLoading`	`true`、`false`	`false`	Whether any method call triggers the loading of all deferred properties of the object
`mapUnderscoreToCamelCase`	`true`、`false`	`false`	Whether to automatically map underscore naming to hump naming
`useGeneratedKeys`	`true`、`false`	`false`	Whether to allow JDBC to use automatic generation of primary keys
`defaultExecutorType`	`SIMPLE`、`REUSE`、`BATCH`	`SIMPLE`	Default actuator type
`defaultStatementTimeout`	positive integer	not set	SQL execution timeout in seconds
`localCacheScope`	`SESSION`、`STATEMENT`	`SESSION`	Level 1 cache scope
`jdbcTypeForNull`	JDBC type	`OTHER`	for `null` Parameter specifies the JDBC type
`logImpl`	Log implementation class	automatic detection	Specify the logging implementation used by MyBatis
`autoMappingBehavior`	`NONE`、`PARTIAL`、`FULL`	`PARTIAL`	Automatic mapping levels

Three types of data sources

type	description	applicable scenarios
UNPOOLED	Open/close connection with each request	simple application
POOLED	connection pool	Most apps (recommended)
JNDI	Get data sources from JNDI	Application servers (Tomcat, etc.)

Two transaction managers

type	description	applicable scenarios
JDBC	Direct use of JDBC commit/rollback	standalone application
MANAGED	Container manages transactions (no commit/roll back)	Application server, Spring

type alias

Common aliases built into MyBatis:

alias	Java type
_int	int
int	Integer
_long	long
long	Long
_boolean	boolean
boolean	Boolean
string	String
integer	Integer
date	Date
decimal	BigDecimal
object	Object
map	Map
hashmap	HashMap
list	List
arraylist	ArrayList

SqlSession and Mapper Interface

SqlSession life cycle

 SqlSessionFactoryBuilder  →  一次创建后丢弃（方法局部变量）
         │
         ▼
 SqlSessionFactory          →  应用级别（单例，全局唯一）
         │
         ▼
 SqlSession                →  方法/请求级别（非线程安全）
         │
         ▼
Mapper 接口实例            →  SqlSession 级别

SqlSession core method

 // 查询
 <T> T selectOne(String statement);
 <T> T selectOne(String statement, Object parameter);
 <E> List<E> selectList(String statement);
 <E> List<E> selectList(String statement, Object parameter);
 <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds);
 <K, V> Map<K, V> selectMap(String statement, String mapKey);
 <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey);
// 游标查询
<T> Cursor<T> selectCursor(String statement);
// 插入
int insert(String statement);
int insert(String statement, Object parameter);
// 更新
int update(String statement);
int update(String statement, Object parameter);
// 删除
int delete(String statement);
int delete(String statement, Object parameter);
// 事务
void commit();
void commit(boolean force);
void rollback();
void rollback(boolean force);
// 批量
void flushStatements();
// 关闭
void close();
// 清除缓存
void clearCache();
// 获取配置
Configuration getConfiguration();
// 获取映射器
<T> T getMapper(Class<T> type);
// 获取连接
Connection getConnection();

Two ways of using

Method 1: Call directly through namespace

SqlSession session = MyBatisUtil.getSqlSession();
List<Emp> emps = session.selectList("com.example.mapper.EmpMapper.selectAll");
session.close();

Method 2: Call through the Mapper interface (recommended)

SqlSession session = MyBatisUtil.getSqlSession();
EmpMapper mapper = session.getMapper(EmpMapper.class);
List<Emp> emps = mapper.selectAll();
session.close();

Recommendation Method 2: Type safety, IDE prompts, compile-time checks.

Three actuators

effector	description	applicable scenarios
Simple	Create a new Statement for each execution	default
Reuse	Reuse Statement	Same SQL multiple times
Batch	batch execution	Batch INSERT/UPDATE

// 指定执行器
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

Affairs management

 // 手动提交（默认）
 SqlSession session = sqlSessionFactory.openSession();  // autoCommit = false
 try {
     EmpMapper mapper = session.getMapper(EmpMapper.class);
     mapper.insert(emp);
     session.commit();   // 手动提交
 } catch (Exception e) {
     session.rollback(); // 回滚
 } finally {
    session.close();
}
// 自动提交
SqlSession session = sqlSessionFactory.openSession(true);  // autoCommit = true
// try-with-resources（推荐）
try (SqlSession session = sqlSessionFactory.openSession()) {
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    mapper.insert(emp);
    session.commit();
}

Mapper file (Mapper XML)

complete structure

 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
         "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!-- namespace 必须与 Mapper 接口全限定名一致 -->
 <mapper namespace="com.example.mapper.EmpMapper">
     <!-- ============ 1. 缓存配置 ============ -->
     <cache/>                    <!-- 开启二级缓存（默认） -->
    <cache-ref namespace="com.example.mapper.DeptMapper"/>  <!-- 引用其他命名空间的缓存 -->
    <!-- ============ 2. 结果映射 ============ -->
    <resultMap id="empResultMap" type="Emp">
        <id property="empId" column="emp_id"/>
        <result property="empName" column="emp_name"/>
        <result property="email" column="email"/>
    </resultMap>
    <!-- ============ 3. SQL 片段 ============ -->
    <sql id="empColumns">
        emp_id, emp_name, email, salary, dept_id, hire_date, status
    </sql>
    <sql id="empWhere">
        <where>
            <if test="empName != null and empName != ''">
                AND emp_name LIKE CONCAT('%', #{empName}, '%')
            </if>
            <if test="deptId != null">
                AND dept_id = #{deptId}
            </if>
        </where>
    </sql>
    <!-- ============ 4. 查询 ============ -->
    <select id="selectAll" resultType="Emp">
        SELECT <include refid="empColumns"/> FROM emp
    </select>
    <!-- ============ 5. 插入 ============ -->
    <insert id="insert" parameterType="Emp"
            useGeneratedKeys="true" keyProperty="empId">
        INSERT INTO emp (emp_name, email, salary, dept_id, hire_date, status)
        VALUES (#{empName}, #{email}, #{salary}, #{deptId}, #{hireDate}, #{status})
    </insert>
    <!-- ============ 6. 更新 ============ -->
    <update id="update" parameterType="Emp">
        UPDATE emp SET
            emp_name = #{empName},
            salary = #{salary}
        WHERE emp_id = #{empId}
    </update>
    <!-- ============ 7. 删除 ============ -->
    <delete id="deleteById" parameterType="int">
        DELETE FROM emp WHERE emp_id = #{empId}
    </delete>
</mapper>

Detailed explanation of select element attributes

 <select
     id="selectEmp"              <!-- 必须与接口方法名一致 -->
     parameterType="map"         <!-- 参数类型（可省略，自动推断） -->
     resultType="Emp"            <!-- 返回类型 -->
     resultMap="empResultMap"    <!-- 结果映射（与 resultType 二选一） -->
     flushCache="false"          <!-- 是否清空缓存 -->
     useCache="true"             <!-- 是否使用二级缓存 -->
     timeout="30"                <!-- 超时秒数 -->
     fetchSize="100"             <!-- 批量获取大小 -->
    statementType="PREPARED"    <!-- STATEMENT/PREPARED/CALLABLE -->
    resultSetType="FORWARD_ONLY" <!-- FORWARD_ONLY/SCROLL_INSENSITIVE/SCROLL_SENSITIVE
     -->
    databaseId="mysql"          <!-- 数据库厂商标识 -->
    resultOrdered="false"       <!-- 嵌套结果是否按顺序 -->
    resultSets="emp,dept"       <!-- 多结果集名 -->
    >
    SELECT * FROM emp WHERE emp_id = #{empId}
</select>

The difference between #{} and ${}

characteristics	#{}	${}
treatment	Precompiled parameters (for PreparedStatement?)	String splicing
SQL injection	Prevent	Risk
performance	Pre-compiled caching can be utilized	Different every time
usage scenarios	parameter values	Table Name /Column Name/ORDER BY

 <!-- #{}：安全，用于传值 -->
 <select id="selectById" resultType="Emp">
     SELECT * FROM emp WHERE emp_id = #{empId}  <!-- → WHERE emp_id = ? -->
 </select>
 <!-- ${}：不安全，用于动态表名/列名 -->
 <select id="selectByColumn" resultType="Emp">
     SELECT * FROM emp ORDER BY ${columnName} ${order}
     <!-- → SELECT * FROM emp ORDER BY emp_id DESC -->
</select>
<!-- 必须对 ${} 的输入做校验 -->

parameter passing

single parameter

// 接口
Emp selectById(Integer empId);

<!-- XML：#{任意名} -->
<select id="selectById" resultType="Emp">
    SELECT * FROM emp WHERE emp_id = #{empId}
    <!-- 也可写成 #{id} 或 #{任意名}，单参数不限制 -->
</select>

Multiple parameters (using @Param)

// 接口
List<Emp> selectByCondition(@Param("empName") String empName,
                             @Param("deptId") Integer deptId);

<select id="selectByCondition" resultType="Emp">
    SELECT * FROM emp
    WHERE emp_name LIKE CONCAT('%', #{empName}, '%')
      AND dept_id = #{deptId}
</select>

Multiple parameters (using Map /Objects)

// Map
List<Emp> selectByMap(Map<String, Object> params);
// 对象
List<Emp> selectByEmp(Emp query);

 <!-- Map：#{key} -->
 <select id="selectByMap" resultType="Emp">
     SELECT * FROM emp
     WHERE emp_name = #{name} AND dept_id = #{deptId}
 </select>
 <!-- 对象：#{属性名} -->
 <select id="selectByEmp" resultType="Emp" parameterType="Emp">
     SELECT * FROM emp
    WHERE emp_name = #{empName} AND dept_id = #{deptId}
</select>

Parameter is List / Array

1List<Emp> selectByIds(@Param(“ids”) List<Integer> ids);

<select id="selectByIds" resultType="Emp">
    SELECT * FROM emp WHERE emp_id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Detailed explanation of insert elements

 <!-- 普通插入 -->
 <insert id="insert" parameterType="Emp">
     INSERT INTO emp (emp_name, email, salary)
     VALUES (#{empName}, #{email}, #{salary})
 </insert>
 <!-- 主键回填（自增主键） -->
 <insert id="insert" parameterType="Emp"
         useGeneratedKeys="true" keyProperty="empId" keyColumn="emp_id">
    INSERT INTO emp (emp_name, email)
    VALUES (#{empName}, #{email})
</insert>
<!-- 插入后 emp.getEmpId() 可获取生成的主键 -->
<!-- 主键回填（非自增主键，如 Oracle 序列） -->
<insert id="insert" parameterType="Emp">
    <selectKey keyProperty="empId" resultType="int" order="BEFORE">
        SELECT SEQ_EMP.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO emp (emp_id, emp_name)
    VALUES (#{empId}, #{empName})
</insert>
<!-- MySQL/PostgreSQL 使用 UUID -->
<insert id="insert" parameterType="Emp">
    <selectKey keyProperty="empId" resultType="string" order="BEFORE">
        SELECT UUID()
    </selectKey>
    INSERT INTO emp (emp_id, emp_name)
    VALUES (#{empId}, #{empName})
</insert>

`selectKey` attribute	description
`keyProperty`	Java property to write the primary key value
`keyColumn`	Primary key column name in the database, which can be specified when there are multiple columns of primary keys
`resultType`	Java type of primary key value
`order`	`BEFORE` Represents the query primary key before insertion,`AFTER` Represents the query primary key after insertion

SQL fragment

<!-- 定义 SQL 片段 -->
<sql id="empColumns">
    emp_id, emp_name, email, salary, dept_id, hire_date, status
</sql>
 <sql id="empColumnsWithAlias">
     e.emp_id, e.emp_name, e.email, e.salary, e.dept_id, e.hire_date, e.status
 </sql>
<!-- 引用 SQL 片段 -->
<select id="selectAll" resultType="Emp">
    SELECT <include refid="empColumns"/> FROM emp
</select>
<!-- 带参数的 SQL 片段 -->
<sql id="tableName">
    ${prefix}_emp
</sql>
<select id="selectAll" resultType="Emp">
    SELECT * FROM <include refid="tableName">
        <property name="prefix" value="t"/>
    </include>
</select>

Detailed explanation of ResultsMap

Why you need a ResultsMap

When database column names are inconsistent with Java attribute names, mapping needs to be carried out through ResultMap:

数据库列名: emp_id, emp_name, hire_date
Java 属性名: empId, empName, hireDate
→ 开启 mapUnderscoreToCamelCase 可自动映射
→ 但复杂映射（关联、集合、鉴别器）必须用 ResultMap

Basic structure of ResultMap

 <resultMap id="empResultMap" type="Emp">
     <!-- id：主键映射（MyBatis 用它判断对象唯一性） -->
     <id property="empId" column="emp_id"/>
     <!-- result：普通字段映射 -->
     <result property="empName" column="emp_name"/>
     <result property="email" column="email"/>
     <result property="salary" column="salary"/>
     <result property="hireDate" column="hire_date"/>
</resultMap>

constructor map

 // 实体类有带参构造器
 public class Emp {
     private Integer empId;
     private String empName;
     public Emp(Integer empId, String empName) {
         this.empId = empId;
         this.empName = empName;
     }
}

<resultMap id="empResultMap" type="Emp">
    <constructor>
        <idArg javaType="int" column="emp_id"/>
        <arg javaType="string" column="emp_name"/>
    </constructor>
</resultMap>

autoMapping automatic mapping

 <!-- 全局设置 -->
 <settings>
     <!-- NONE: 禁用自动映射 -->
     <!-- PARTIAL(默认): 自动映射非嵌套结果 -->
     <!-- FULL: 自动映射所有（含嵌套） -->
     <setting name="autoMappingBehavior" value="PARTIAL"/>
 </settings>
 <!-- ResultMap 级别 -->
<resultMap id="empResultMap" type="Emp" autoMapping="true">
    <id property="empId" column="emp_id"/>
    <result property="empName" column="emp_name"/>
    <!-- 其他字段自动映射 -->
</resultMap>

ResultMap inheritance

 <!-- 基础 ResultMap -->
 <resultMap id="baseResultMap" type="Emp">
     <id property="empId" column="emp_id"/>
     <result property="empName" column="emp_name"/>
     <result property="email" column="email"/>
     <result property="salary" column="salary"/>
 </resultMap>
 <!-- 继承并扩展 -->
<resultMap id="empWithDeptResultMap" type="Emp" extends="baseResultMap">
    <association property="dept" javaType="Dept">
        <id property="deptId" column="d_dept_id"/>
        <result property="deptName" column="dept_name"/>
    </association>
</resultMap>

Discriminator

Decide to use a different ResultMap based on the value of a column:

 <resultMap id="employeeResultMap" type="Emp">
     <id property="empId" column="emp_id"/>
     <result property="empName" column="emp_name"/>
     <!-- 鉴别器：根据 status 的值决定映射方式 -->
     <discriminator javaType="int" column="status">
         <!-- status = 1：在职员工，映射部门 -->
         <case value="1" resultType="Emp">
             <association property="dept" javaType="Dept">
                <id property="deptId" column="dept_id"/>
                <result property="deptName" column="dept_name"/>
            </association>
        </case>
        <!-- status = 0：离职员工，映射离职信息 -->
        <case value="0" resultType="Emp" resultMap="resignedEmpResultMap"/>
    </discriminator>
</resultMap>

dynamic SQL

Overview of dynamic SQL elements

elements	description	similar to
<if>	condition judgment	if
<choose>/<when>/<otherwise>	multi-conditional selection	switch/case/default
<where>	Smart WHERE clause	—
<set>	smart SET clause	—
<trim>	Custom prefix/suffix clipping	—
<foreach>	ergodic set	for
<bind>	bind variables	—
<sql>/<include>	SQL fragment reuse	—

if element

 <select id="selectByCondition" resultType="Emp">
     SELECT * FROM emp
     WHERE 1=1
     <if test="empName != null and empName != ''">
         AND emp_name LIKE CONCAT('%', #{empName}, '%')
     </if>
     <if test="deptId != null">
         AND dept_id = #{deptId}
     </if>
    <if test="minSalary != null">
        AND salary &gt;= #{minSalary}
    </if>
    <if test="maxSalary != null">
        AND salary &lt;= #{maxSalary}
    </if>
    <if test="status != null">
        AND status = #{status}
    </if>
</select>

note:<and> in XML needs to be escaped to and, or use <! [CDATA[…]]>Package.

where element

<where>Will be automatically processed:

Remove the first AND or OR
If there are no conditions, no WHERE is added

 <select id="selectByCondition" resultType="Emp">
     SELECT * FROM emp
     <where>
         <if test="empName != null and empName != ''">
             AND emp_name LIKE CONCAT('%', #{empName}, '%')
         </if>
         <if test="deptId != null">
             AND dept_id = #{deptId}
         </if>
        <if test="minSalary != null">
            AND salary &gt;= #{minSalary}
        </if>
    </where>
</select>

choose / when / otherwise

 <select id="selectByChoice" resultType="Emp">
     SELECT * FROM emp
     <where>
         <choose>
             <when test="empName != null and empName != ''">
                 emp_name = #{empName}
             </when>
             <when test="email != null and email != ''">
                 email = #{email}
            </when>
            <otherwise>
                status = 1
            </otherwise>
        </choose>
    </where>
</select>

set element

<set>Will be automatically processed:

Remove unnecessary commas at the end
If there are no conditions, no SET will be added (an error will be reported)

 <update id="updateSelective" parameterType="Emp">
     UPDATE emp
     <set>
         <if test="empName != null">emp_name = #{empName},</if>
         <if test="email != null">email = #{email},</if>
         <if test="salary != null">salary = #{salary},</if>
         <if test="deptId != null">dept_id = #{deptId},</if>
         <if test="status != null">status = #{status},</if>
     </set>
    WHERE emp_id = #{empId}
</update>

trim element

<trim> is <where> and <set> One of the basic capabilities of elements such as elements, you can customize prefixes, suffixes, and content that needs to be removed.

<!-- 等价于 <where> 的常见写法 -->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
    ...
</trim>

<!-- 等价于 <set> 的常见写法 -->
<trim prefix="SET" suffixOverrides=",">
    ...
</trim>

attribute	description
`prefix`	Add prefix to final content
`suffix`	Add suffix to final content
`prefixOverrides`	Remove matching strings at the beginning of content
`suffixOverrides`	Remove matching strings at the end of content

foreach element

 <!-- IN 查询 -->
 <select id="selectByIds" resultType="Emp">
     SELECT * FROM emp WHERE emp_id IN
     <foreach collection="ids" item="id" open="(" separator="," close=")">
         #{id}
     </foreach>
 </select>
 <!-- 生成：SELECT * FROM emp WHERE emp_id IN (1, 2, 3) -->
<!-- 批量插入 -->
<insert id="batchInsert" parameterType="list">
    INSERT INTO emp (emp_name, email, salary)
    VALUES
    <foreach collection="list" item="emp" separator=",">
        (#{emp.empName}, #{emp.email}, #{emp.salary})
    </foreach>
</insert>
<!-- 生成：INSERT INTO emp ... VALUES ('张三','a@b.com',10000),('李四','c@d.com',12000) -
     ->
<!-- 批量更新（CASE WHEN 方式） -->
<update id="batchUpdate" parameterType="list">
    UPDATE emp SET
    salary =
    <foreach collection="list" item="emp" open="CASE emp_id" close="END">
        WHEN #{emp.empId} THEN #{emp.salary}
    </foreach>
    WHERE emp_id IN
    <foreach collection="list" item="emp" open="(" separator="," close=")">
        #{emp.empId}
    </foreach>
</update>
<!-- OR 条件 -->
<select id="selectByNames" resultType="Emp">
    SELECT * FROM emp WHERE
    <foreach collection="names" item="name" separator=" OR ">
        emp_name = #{name}
    </foreach>
</select>

attribute	description
`collection`	Name of the collection to traverse. single `List` The parameter is usually `list`, arrays are usually `array`, can also be used `@Param` specified name
`item`	Variable name of the current element
`index`	The key of the current index or Map
`open`	Starting string for the entire paragraph
`close`	The ending string of the entire paragraph
`separator`	Separators between adjacent elements

bind element

 <!-- 模糊查询拼接 -->
 <select id="selectByName" resultType="Emp">
     <bind name="likeName" value="'%' + empName + '%'" />
     SELECT * FROM emp WHERE emp_name LIKE #{likeName}
 </select>
 <!-- 绑定多个变量 -->
 <select id="selectByCondition" resultType="Emp">
     <bind name="minSalaryValue" value="minSalary != null ? minSalary : 0" />
    <bind name="maxSalaryValue" value="maxSalary != null ? maxSalary : 999999" />
    SELECT * FROM emp
    WHERE salary BETWEEN #{minSalaryValue} AND #{maxSalaryValue}
</select>

Complete dynamic SQL example

<select id="selectByDynamicCondition" resultType="Emp">
    SELECT
    <include refid="empColumns"/>
    FROM emp
    <where>
        <if test="empName != null and empName != ''">
            <bind name="likeName" value="'%' + empName + '%'"/>
            AND emp_name LIKE #{likeName}
         </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="deptId != null">
            AND dept_id = #{deptId}
        </if>
        <if test="deptIds != null and deptIds.size() > 0">
            AND dept_id IN
            <foreach collection="deptIds" item="did" open="(" separator="," close=")">
                #{did}
            </foreach>
        </if>
        <if test="minSalary != null or maxSalary != null">
            <choose>
                <when test="minSalary != null and maxSalary != null">
                    AND salary BETWEEN #{minSalary} AND #{maxSalary}
                </when>
                <when test="minSalary != null">
                    AND salary &gt;= #{minSalary}
                </when>
                <otherwise>
                    AND salary &lt;= #{maxSalary}
                </otherwise>
            </choose>
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    ORDER BY emp_id DESC
</select>

relational query

One-to-one association

Method 1: Nested results (join query, once SQL)

public class Emp {
    private Integer empId;
    private String empName;
    private Dept dept;  // 关联的部门对象
}

<resultMap id="empWithDeptResultMap" type="Emp">
    <id property="empId" column="emp_id"/>
    <result property="empName" column="emp_name"/>
    <result property="email" column="email"/>
     <!-- 一对一关联 -->
     <association property="dept" javaType="Dept">
         <id property="deptId" column="d_dept_id"/>
         <result property="deptName" column="d_dept_name"/>
        <result property="location" column="d_location"/>
    </association>
</resultMap>
<select id="selectEmpWithDept" resultMap="empWithDeptResultMap">
    SELECT
        e.emp_id,
        e.emp_name,
        e.email,
        d.dept_id   AS d_dept_id,
        d.dept_name AS d_dept_name,
        d.location  AS d_location
    FROM emp e
    LEFT JOIN dept d ON e.dept_id = d.dept_id
    WHERE e.emp_id = #{empId}
</select>

Method 2: Nested query (step-by-step query, N+1 questions)

 <resultMap id="empWithDeptStepResultMap" type="Emp">
     <id property="empId" column="emp_id"/>
     <result property="empName" column="emp_name"/>
     <!-- column: 传递给子查询的列名; select: 子查询的方法全限定名 -->
     <association property="dept"
                  javaType="Dept"
                  column="dept_id"
                  select="com.example.mapper.DeptMapper.selectById"
                 fetchType="lazy"/>  <!-- lazy: 延迟加载; eager: 立即加载 -->
</resultMap>
<select id="selectEmpWithDeptStep" resultMap="empWithDeptStepResultMap">
    SELECT * FROM emp WHERE emp_id = #{empId}
</select>

<!-- DeptMapper.xml -->
<select id="selectById" resultType="Dept">
    SELECT * FROM dept WHERE dept_id = #{deptId}
</select>

Implementation process:

Select * FROM emp WHERE emp_id = ?
Remove dept_id and execute SELECT * FROM dept WHERE dept_id = ?

Method 3: Nested query passes multiple parameters

<association property="dept"
             javaType="Dept"
             column="{deptId=dept_id, status=status}"
             select="com.example.mapper.DeptMapper.selectByIdAndStatus"
             fetchType="lazy"/>

One-to-many association (collection)

public class Dept {
    private Integer deptId;
    private String deptName;
    private List<Emp> emps;  // 关联的员工列表
}

Method 1: Nested results

 <resultMap id="deptWithEmpsResultMap" type="Dept">
     <id property="deptId" column="dept_id"/>
     <result property="deptName" column="dept_name"/>
     <result property="location" column="location"/>
     <!-- 一对多关联 -->
     <collection property="emps" ofType="Emp">
         <id property="empId" column="e_emp_id"/>
         <result property="empName" column="e_emp_name"/>
        <result property="email" column="e_email"/>
        <result property="salary" column="e_salary"/>
    </collection>
</resultMap>
<select id="selectDeptWithEmps" resultMap="deptWithEmpsResultMap">
    SELECT
        d.dept_id,
        d.dept_name,
        d.location,
        e.emp_id   AS e_emp_id,
        e.emp_name AS e_emp_name,
        e.email    AS e_email,
        e.salary   AS e_salary
    FROM dept d
    LEFT JOIN emp e ON d.dept_id = e.dept_id
    WHERE d.dept_id = #{deptId}
</select>

Method 2: Nested query

 <resultMap id="deptWithEmpsStepResultMap" type="Dept">
     <id property="deptId" column="dept_id"/>
     <result property="deptName" column="dept_name"/>
     <collection property="emps"
                 ofType="Emp"
                 column="dept_id"
                 select="com.example.mapper.EmpMapper.selectByDeptId"
                 fetchType="lazy"/>
</resultMap>
<select id="selectDeptWithEmpsStep" resultMap="deptWithEmpsStepResultMap">
    SELECT * FROM dept WHERE dept_id = #{deptId}
</select>

many-to-many correlation

public class Emp {
    private Integer empId;
    private String empName;
    private List<Project> projects;  // 参与的项目
}

 <resultMap id="empWithProjectsResultMap" type="Emp">
     <id property="empId" column="emp_id"/>
     <result property="empName" column="emp_name"/>
     <collection property="projects" ofType="Project">
         <id property="projectId" column="p_project_id"/>
         <result property="projectName" column="p_project_name"/>
         <result property="budget" column="p_budget"/>
     </collection>
</resultMap>
<select id="selectEmpWithProjects" resultMap="empWithProjectsResultMap">
    SELECT
        e.emp_id,
        e.emp_name,
        p.project_id   AS p_project_id,
        p.project_name AS p_project_name,
        p.budget       AS p_budget
    FROM emp e
    LEFT JOIN emp_project ep ON e.emp_id = ep.emp_id
    LEFT JOIN project p ON ep.project_id = p.project_id
    WHERE e.emp_id = #{empId}
</select>

Nested query vs nested result comparison

characteristics	Nested results (join）	Nested query (step by step )
Number of SQL	1 time (multi-table JOIN)	N+1 times
performance	High (single query )	Low (N+1 problem, but can delay loading )
lazy loading	No support	Support
SQL complexity	High (multi-table JOIN)	Low (simple query )
amount of data	large (Cartesian product )	Step-by-step acquisition
recommend	Less associated data	Associated data multiple + delayed loading

Delayed loading configuration

<!-- mybatis-config.xml -->
<settings>
    <!-- 全局开启延迟加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- false: 按需加载（仅访问关联属性时才加载） -->
    <setting name="aggressiveLazyLoading" value="false"/>
    <!-- 延迟加载触发方法 -->
    <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

<!-- 单个 association/collection 级别 -->
<association property="dept" fetchType="lazy" .../>
<collection property="emps" fetchType="eager" .../>

caching mechanism

cache system

 ┌──────────────────────────────────────────────┐
 │             MyBatis 缓存体系                    │
 │                                               │
 │  ┌─────────────┐    ┌─────────────────────┐  │
 │  │  一级缓存     │    │    二级缓存           │  │
 │  │  (SqlSession)│    │  (Mapper namespace)  │  │
 │  │              │    │                      │  │
 │  │  默认开启     │    │  需手动开启           │  │
 │  │  Session级   │    │  跨SqlSession共享     │  │
│  │  HashMap     │    │  可集成第三方          │  │
│  └──────┬──────┘    └──────────┬───────────┘  │
│         │                      │              │
│         └──────────┬───────────┘              │
│                    ▼                          │
│              ┌───────────┐                    │
│              │   数据库    │                   │
│              └───────────┘                    │
└──────────────────────────────────────────────┘
查询顺序：二级缓存 → 一级缓存 → 数据库

a level one cache

Level 1 cache is SqlSession levelis turned on by default and cannot be turned off.

working principle

同一次 SqlSession 中：
. 第一次查询 emp_id=1 → 查数据库 → 存入一级缓存
. 第二次查询 emp_id=1 → 直接从一级缓存获取
. 执行 insert/update/delete → 清空一级缓存
. session.commit() 或 session.close() → 一级缓存消失

Level 1 cache invalidation conditions

conditions	description
Different SqlSessions	Cache is not shared for different sessions
Insert/update/delete was executed	Clear all caches for the current session
Executed session.clearCache()	manual clearing
Executed session.commit()	empty the cache
A query with flashCache =“true” was executed	This query clears the cache

Level 1 cache range

<!-- 默认：SESSION（SqlSession 范围） -->
<setting name="localCacheScope" value="SESSION"/>
<!-- STATEMENT：每条语句执行后清空（相当于关闭一级缓存） -->
<setting name="localCacheScope" value="STATEMENT"/>

second level cache

Level 2 cache Yes Mapper namespace levelcache of, shared across SqlSessions.

opening method

<!-- 1. 全局开启（mybatis-config.xml） -->
<settings>
    <setting name="cacheEnabled" value="true"/>
</settings>
<!-- 2. Mapper XML 中声明 -->
<mapper namespace="com.example.mapper.EmpMapper">
    <cache/>  <!-- 简单声明 -->
</mapper>

cache element attributes

<cache
    type="org.apache.ibatis.cache.impl.PerpetualCache"   <!-- 缓存实现类 -->
    eviction="LRU"       <!-- 淘汰策略 -->
    flushInterval="60000" <!-- 刷新间隔（毫秒） -->
    size="1024"          <!-- 最大对象数 -->
    readOnly="false"     <!-- 是否只读 -->
/>

elimination strategydescription

LRU (default) least recently used

FIFO First in First Out

SOFT soft references (recycled when memory is low)

WEAK weak reference (recycled during GC)

readOnly	description
true	All threads share the same object (high performance, unsafe)
false (default)	Returns a copy of the cached object each time (serialized, safe)

L2 cache usage conditions

Entity classes must implement Serializable interface (when readOnly=false)
Commit or close must be written to the secondary cache after select
Executing insert/update/delete will automatically clear the secondary cache of the namespace

@Test
public void testSecondLevelCache() {
    // Session 1
    try (SqlSession session1 = factory.openSession()) {
        EmpMapper mapper1 = session1.getMapper(EmpMapper.class);
        Emp emp1 = mapper1.selectById(1);
         session1.commit();  // 提交后写入二级缓存
     }
    // Session 2
    try (SqlSession session2 = factory.openSession()) {
        EmpMapper mapper2 = session2.getMapper(EmpMapper.class);
        Emp emp2 = mapper2.selectById(1);  // 从二级缓存获取
    }
}

Select useCache and flashCache

 <!-- 该查询不使用二级缓存 -->
 <select id="selectCount" resultType="int" useCache="false">
     SELECT COUNT(*) FROM emp
 </select>
 <!-- 该查询执行后清空缓存 -->
 <select id="selectFreshData" resultType="Emp" flushCache="true">
     SELECT * FROM emp
 </select>
<!-- insert/update/delete 默认 flushCache="true" -->

cache-ref references other namespace caches

<!-- 多个 Mapper 共享同一个缓存 -->
<mapper namespace="com.example.mapper.EmpMapper">
    <cache/>
</mapper>
<mapper namespace="com.example.mapper.EmpDeptMapper">
    <!-- 引用 EmpMapper 的缓存 -->
    <cache-ref namespace="com.example.mapper.EmpMapper"/>
</mapper>

Integrated third-party cache (EhCache)

<!-- 依赖 -->
<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.2.1</version>
</dependency>
 <!-- Mapper XML -->
 <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
 <!-- 或带配置 -->
 <cache type="org.mybatis.caches.ehcache.EhcacheCache">
     <property name="timeToIdleSeconds" value="3600"/>
     <property name="timeToLiveSeconds" value="7200"/>
     <property name="maxEntriesLocalHeap" value="1000"/>
     <property name="maxEntriesLocalDisk" value="10000000"/>
    <property name="memoryStoreEvictionPolicy" value="LRU"/>
</cache>

Level 1 cache vs Level 2 cache

characteristics	a level one cache	second level cache
range	SqlSession	Mapper namespace
shared	not share	Sharing across SqlSessions
default	open	closed
closable	STATEMENT can be set	cacheEnabled=false can be set
failure	Add, delete, modify, commit, close	Additions, deletions and changes to the same namespace
storage	HashMap	Customizable (EhCache/Redis)

Primary key backfilling and batch operation

Primary key backfilling

Self-added PK Backfill

<insert id="insert" parameterType="Emp"
        useGeneratedKeys="true"
        keyProperty="empId"
        keyColumn="emp_id">
    INSERT INTO emp (emp_name, email, salary)
    VALUES (#{empName}, #{email}, #{salary})
</insert>

Emp emp = new Emp();
emp.setEmpName("张三");
mapper.insert(emp);
System.out.println(emp.getEmpId());  // 自动回填的主键

Non-self-increasing primary key (selectKey)

 <!-- Oracle 序列 -->
 <insert id="insert" parameterType="Emp">
     <selectKey keyProperty="empId" resultType="int" order="BEFORE">
         SELECT SEQ_EMP.NEXTVAL FROM DUAL
     </selectKey>
     INSERT INTO emp (emp_id, emp_name) VALUES (#{empId}, #{empName})
 </insert>
 <!-- MySQL UUID -->
<insert id="insert" parameterType="Emp">
    <selectKey keyProperty="empId" resultType="string" order="BEFORE">
        SELECT REPLACE(UUID(), '-', '')
    </selectKey>
    INSERT INTO emp (emp_id, emp_name) VALUES (#{empId}, #{empName})
</insert>

bulk inserts

Method 1: foreach splicing VALUES

<insert id="batchInsert" parameterType="list">
    INSERT INTO emp (emp_name, email, salary, dept_id, hire_date, status)
    VALUES
    <foreach collection="list" item="emp" separator=",">
        (#{emp.empName}, #{emp.email}, #{emp.salary}, #{emp.deptId}, #{emp.hireDate}, #
    {emp.status})
    </foreach>
</insert>

List<Emp> emps = new ArrayList<>();
emps.add(emp1);
emps.add(emp2);
mapper.batchInsert(emps);

Method 2: Batch actuator

 try (SqlSession session = factory.openSession(ExecutorType.BATCH, false)) {
     EmpMapper mapper = session.getMapper(EmpMapper.class);
     for (int i = 0; i < 10000; i++) {
         Emp emp = new Emp();
         emp.setEmpName("emp_" + i);
         mapper.insert(emp);
         if (i % 1000 == 0) {
            session.flushStatements();  // 每 1000 条执行一次
        }
    }
    session.commit();
}

Method 3: ON DUPLICATE KEY UPDATE (MySQL batch upsert)

 <insert id="batchUpsert" parameterType="list">
     INSERT INTO emp (emp_id, emp_name, email, salary)
     VALUES
     <foreach collection="list" item="emp" separator=",">
         (#{emp.empId}, #{emp.empName}, #{emp.email}, #{emp.salary})
     </foreach>
     ON DUPLICATE KEY UPDATE
         emp_name = VALUES(emp_name),
         email = VALUES(email),
        salary = VALUES(salary)
</insert>

batch update

Method 1: Case When

 <update id="batchUpdate" parameterType="list">
     UPDATE emp SET
     salary =
     <foreach collection="list" item="emp" open="CASE emp_id" close="END">
         WHEN #{emp.empId} THEN #{emp.salary}
     </foreach>
     WHERE emp_id IN
     <foreach collection="list" item="emp" open="(" separator="," close=")">
         #{emp.empId}
    </foreach>
</update>

Method 2: Batch actuator

try (SqlSession session = factory.openSession(ExecutorType.BATCH)) {
    EmpMapper mapper = session.getMapper(EmpMapper.class);
    for (Emp emp : empList) {
        mapper.update(emp);
    }
    session.commit();
}

TypeHandler type processor

What is TypeHandler

TypeHandler is used for conversion between Java types and JDBC types:

Java 类型  ←→  JDBC 类型
String    ←→  VARCHAR
Integer   ←→  INT
Date      ←→  TIMESTAMP
List      ←→  VARCHAR (自定义)

Built-in TypeHandler

MyBatis has built-in converters between commonly used Java types and JDBC types.

TypeHandler	Java types	JDBC type
`BooleanTypeHandler`	`Boolean`、`boolean`	`BOOLEAN`
`IntegerTypeHandler`	`Integer`、`int`	`INTEGER`
`LongTypeHandler`	`Long`、`long`	`BIGINT`
`StringTypeHandler`	`String`	`VARCHAR`、`CHAR`、`LONGVARCHAR`
`DateTypeHandler`	`Date`	`TIMESTAMP`
`BigDecimalTypeHandler`	`BigDecimal`	`DECIMAL`、`NUMERIC`
`ByteArrayTypeHandler`	`byte[]`	`BLOB`、`LONGVARBINARY`
`EnumTypeHandler`	`Enum`	is typically `VARCHAR`
`EnumOrdinalTypeHandler`	`Enum` the sequence number of	`INTEGER`
`LocalDateTypeHandler`	`LocalDate`	`DATE`
`LocalDateTimeTypeHandler`	`LocalDateTime`	`TIMESTAMP`

Custom TypeHandler

Scenario: Save the List as a JSON string

package com.example.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
@MappedTypes(List.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class StringListTypeHandler extends BaseTypeHandler<List<String>> {
    // 将 Java 类型 → JDBC 类型（存入数据库）
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    List<String> parameter, JdbcType jdbcType) throws SQLException {
        // List → "a,b,c"
        String value = String.join(",", parameter);
        ps.setString(i, value);
    }
    // 将 JDBC 类型 → Java 类型（从数据库读取）
    @Override
    public List<String> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String value = rs.getString(columnName);
        return toList(value);
    }
    @Override
    public List<String> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String value = rs.getString(columnIndex);
        return toList(value);
    }
    @Override
    public List<String> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String value = cs.getString(columnIndex);
        return toList(value);
    }
    private List<String> toList(String value) {
        if (value == null || value.isEmpty()) {
            return new ArrayList<>();
        }
        return Arrays.asList(value.split(","));
    }
}

Register TypeHandler

<!-- mybatis-config.xml -->
<typeHandlers>
    <!-- 方式一：单个注册 -->
    <typeHandler handler="com.example.handler.StringListTypeHandler"/>
    <!-- 方式二：包扫描 -->
    <package name="com.example.handler"/>
</typeHandlers>

Using TypeHandler

 <!-- 在映射中指定 typeHandler -->
 <resultMap id="empResultMap" type="Emp">
     <id property="empId" column="emp_id"/>
     <result property="empName" column="emp_name"/>
     <result property="tags" column="tags"
             typeHandler="com.example.handler.StringListTypeHandler"/>
 </resultMap>
 <!-- 在参数中指定 -->
<insert id="insert" parameterType="Emp">
    INSERT INTO emp (emp_name, tags)
    VALUES (#{empName}, #{tags, typeHandler=com.example.handler.StringListTypeHandler})
</insert>

Enumeration type handling

 public enum EmpStatus {
     ACTIVE(1, "在职"),
     RESIGNED(0, "离职");
     private int code;
     private String desc;
     EmpStatus(int code, String desc) {
         this.code = code;
        this.desc = desc;
    }
    // getter...
}

<!-- 方式一：按名称存储（默认） -->
<!-- 存入 "ACTIVE"，读取时 Enum.valueOf("ACTIVE") -->
<setting name="defaultEnumTypeHandler" value="org.apache.ibatis.type.EnumTypeHandler"/>
<!-- 方式二：按序号存储 -->
<!-- 存入 0（ACTIVE 的序号） -->
<setting name="defaultEnumTypeHandler"
    value="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>

Custom enumeration TypeHandler (stored by code)

 @MappedTypes(EmpStatus.class)
 public class EmpStatusTypeHandler extends BaseTypeHandler<EmpStatus> {
     @Override
     public void setNonNullParameter(PreparedStatement ps, int i,
                                     EmpStatus parameter, JdbcType jdbcType) throws SQLException {
         ps.setInt(i, parameter.getCode());
     }
     @Override
    public EmpStatus getNullableResult(ResultSet rs, String columnName) throws SQLException {
        int code = rs.getInt(columnName);
        return EmpStatus.fromCode(code);
    }
    // ... 其他方法
}

ObjectFactory Object Factory

What is ObjectFactory

ObjectFactory is responsible for creating instances of the resulting object. The default implementation is DefaultObjectFactory.

Customize ObjectFactory

 package com.example.factory;
 import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
 import java.util.List;
 import java.util.Properties;
 public class CustomObjectFactory extends DefaultObjectFactory {
     @Override
    public <T> T create(Class<T> type) {
        T object = super.create(type);
        // 可以在这里做初始化
        return object;
    }
    @Override
    public <T> T create(Class<T> type, List<Class<?>> constructorArgTypes, List<Object>
     constructorArgs) {
        T object = super.create(type, constructorArgTypes, constructorArgs);
        // 自定义初始化逻辑
        return object;
    }
    @Override
    public void setProperties(Properties properties) {
        super.setProperties(properties);
    }
    @Override
    public <T> boolean isCollection(Class<T> type) {
        return super.isCollection(type);
    }
}
<objectFactory type="com.example.factory.CustomObjectFactory">
    <property name="someProperty" value="100"/>
</objectFactory>

Plugin plug-in mechanism (interceptor)

Plug-in principle

MyBatis allows plug-ins (interceptors) to intercept method calls on the following four major objects:

object	Methods of interception	description
Executor	update, query, commit, rollback, createStatement, etc.	effector
ParameterHandler	setParameters	parameter processing
ResultSetHandler	handleResultSets	Result set processing
StatementHandler	prepare, parameterize, batch	SQL statement processing

SqlSession.selectList()
    → Executor.query()
        → StatementHandler.prepare()      ← 可拦截（修改 SQL）
        → StatementHandler.parameterize()
            → ParameterHandler.setParameters()  ← 可拦截（修改参数）
        → StatementHandler.query()
            → ResultSetHandler.handleResultSets()  ← 可拦截（修改结果）

Write custom plug-ins

Example paging interceptor

package com.example.plugin;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
 import org.apache.ibatis.plugin.*;
 import org.apache.ibatis.session.ResultHandler;
 import org.apache.ibatis.session.RowBounds;
 import java.util.Properties;
@Intercepts(@Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
))
public class PagePlugin implements Interceptor {
    private Properties properties;
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 获取参数
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        // 执行原查询（获取总数）
        // ... 分页逻辑
        // 修改 RowBounds
        args[2] = new RowBounds(offset, limit);
        return invocation.proceed();
    }
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    @Override
    public void setProperties(Properties properties) {
        this.properties = properties;
    }
}
<!-- 注册插件 -->
<plugins>
    <plugin interceptor="com.example.plugin.PagePlugin">
        <property name="dialect" value="mysql"/>
    </plugin>
</plugins>

SQL Audit Interceptor Example

 @Intercepts({
     @Signature(type = Executor.class, method = "update",
                args = {MappedStatement.class, Object.class})
 })
 public class SqlAuditPlugin implements Interceptor {
     @Override
     public Object intercept(Invocation invocation) throws Throwable {
         MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        // 获取 SQL ID
        String sqlId = ms.getId();
        // 获取 SQL 语句
        BoundSql boundSql = ms.getBoundSql(parameter);
        String sql = boundSql.getSql();
        // 记录审计日志
        long start = System.currentTimeMillis();
        Object result = invocation.proceed();
        long elapsed = System.currentTimeMillis() - start;
        System.out.println("[SQL审计] " + sqlId + " | 耗时: " + elapsed + "ms");
        System.out.println("[SQL] " + sql);
        return result;
    }
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    @Override
    public void setProperties(Properties properties) {}
}

paging

RowBounds paging (memory paging)

// 内存分页（不推荐大数据量）
int offset = 0;
int limit = 10;
RowBounds rowBounds = new RowBounds(offset, limit);
List<Emp> emps = session.selectList("com.example.mapper.EmpMapper.selectAll", null,
    rowBounds);

note: RowBounds is a memory paging that first detects all data and then extracts a subset. It is not recommended.

SQL paging (recommended)

1List<Emp> selectByPage(@Param(“offset”) int offset, @Param(“limit”) int limit);

<select id="selectByPage" resultType="Emp">
    SELECT * FROM emp
    ORDER BY emp_id
    LIMIT #{offset}, #{limit}
</select>

PageHelper paging plug-in (most commonly used)

 <!-- 依赖 -->
 <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.3.3</version>
 </dependency>
 <!-- 或 MyBatis 专用版 -->
 <dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>

 <!-- mybatis-config.xml 配置 -->
 <plugins>
     <plugin interceptor="com.github.pagehelper.PageInterceptor">
         <!-- 数据库方言 -->
         <property name="helperDialect" value="mysql"/>
         <!-- 分页合理化：页码 < 1 查第一页，> 最大页查最后一页 -->
         <property name="reasonable" value="true"/>
         <!-- 支持通过 Mapper 接口参数传递分页参数 -->
         <property name="supportMethodsArguments" value="true"/>
        <!-- 总数统计 -->
        <property name="params" value="count=countSql"/>
    </plugin>
</plugins>

Using PageHelper

 // 方式一：静态方法调用
 PageHelper.startPage(1, 10);  // 第 1 页，每页 10 条
 List<Emp> emps = mapper.selectAll();
 // PageHelper 会自动追加 LIMIT 0, 10
 // 获取分页信息
 PageInfo<Emp> pageInfo = new PageInfo<>(emps);
 System.out.println("总记录数: " + pageInfo.getTotal());
 System.out.println("总页数: " + pageInfo.getPages());
System.out.println("当前页: " + pageInfo.getPageNum());
System.out.println("每页大小: " + pageInfo.getPageSize());
System.out.println("是否有下一页: " + pageInfo.isHasNextPage());
// 方式二：通过参数传递
List<Emp> emps = mapper.selectByParams(1, 10);
// PageHelper 自动处理
// 方式三：Lambda 方式
PageInfo<Emp> pageInfo = PageHelper.startPage(1, 10)
    .doSelectPageInfo(() -> mapper.selectAll());

PageInfo attribute

 public class PageInfo<T> {
     private int pageNum;        // 当前页
     private int pageSize;       // 每页大小
     private long total;         // 总记录数
     private int pages;          // 总页数
     private List<T> list;       // 数据列表
     private int prePage;        // 上一页
     private int nextPage;       // 下一页
     private boolean isFirstPage;    // 是否首页
    private boolean isLastPage;     // 是否末页
    private boolean hasPreviousPage; // 是否有上一页
    private boolean hasNextPage;     // 是否有下一页
    private int navigateFirstPage;   // 导航首页
    private int navigateLastPage;    // 导航末页
    private int[] navigatepageNums;  // 导航页码数组
}

annotation development

basic annotation

public interface EmpMapper {
    // 查询
    @Select("SELECT * FROM emp WHERE emp_id = #{empId}")
     Emp selectById(Integer empId);
     // 查询列表
     @Select("SELECT * FROM emp WHERE dept_id = #{deptId}")
     List<Emp> selectByDeptId(Integer deptId);
    // 插入（主键回填）
    @Insert("INSERT INTO emp (emp_name, email, salary) VALUES (#{empName}, #{email}, #
     {salary})")
    @Options(useGeneratedKeys = true, keyProperty = "empId")
    int insert(Emp emp);
    // 更新
    @Update("UPDATE emp SET emp_name = #{empName}, salary = #{salary} WHERE emp_id = #
     {empId}")
    int update(Emp emp);
    // 删除
    @Delete("DELETE FROM emp WHERE emp_id = #{empId}")
    int deleteById(Integer empId);
    // 查询数量
    @Select("SELECT COUNT(*) FROM emp")
    int selectCount();
}

Results and Result annotations

 @Results({
     @Result(property = "empId", column = "emp_id", id = true),
     @Result(property = "empName", column = "emp_name"),
     @Result(property = "hireDate", column = "hire_date"),
     @Result(property = "dept", column = "dept_id",
             one = @One(select = "com.example.mapper.DeptMapper.selectById",
                        fetchType = FetchType.LAZY))
 })
 @Select("SELECT * FROM emp WHERE emp_id = #{empId}")
Emp selectByIdWithDept(Integer empId);

one-to-many annotation

@Results({
    @Result(property = "deptId", column = "dept_id", id = true),
    @Result(property = "deptName", column = "dept_name"),
    @Result(property = "emps", column = "dept_id",
            many = @Many(select = "com.example.mapper.EmpMapper.selectByDeptId",
                         fetchType = FetchType.LAZY))
})
@Select("SELECT * FROM dept WHERE dept_id = #{deptId}")
Dept selectDeptWithEmps(Integer deptId);

Dynamic SQL annotations (@SelectProvider, etc.)

 // Provider 方式
 public class EmpSqlProvider {
     public String selectByCondition(final Emp emp) {
         return new SQL() {{
             SELECT("*");
             FROM("emp");
             if (emp.getEmpName() != null) {
                 WHERE("emp_name LIKE CONCAT('%', #{empName}, '%')");
            }
            if (emp.getDeptId() != null) {
                WHERE("dept_id = #{deptId}");
            }
            if (emp.getStatus() != null) {
                WHERE("status = #{status}");
            }
            ORDER_BY("emp_id DESC");
        }}.toString();
    }
}
// 接口
public interface EmpMapper {
    @SelectProvider(type = EmpSqlProvider.class, method = "selectByCondition")
    List<Emp> selectByCondition(Emp emp);
    @InsertProvider(type = EmpSqlProvider.class, method = "insert")
    int insert(Emp emp);
    @UpdateProvider(type = EmpSqlProvider.class, method = "update")
    int update(Emp emp);
    @DeleteProvider(type = EmpSqlProvider.class, method = "delete")
    int delete(Integer empId);
}

Comparison of annotations and XML

characteristics	XML mapping	annotation mapping
SQL complexity	Suitable for complex SQL and large dynamic SQL	Suitable for simple SQL, complex scenarios usually require a Provider
dynamic SQL	Complete function and good readability	Can be implemented, but the code is usually more decentralized
readability	Separation of SQL and Java code	Simple sentences are intuitive
maintainability	Complex queries are easier to maintain centrally	Simple CRUD low maintenance costs
recommended scene	Complex queries, multi-person collaboration projects	Simple CRUD, a small amount of fixed SQL

Spring Integration

dependent

 <dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis-spring</artifactId>
     <version>3.0.3</version>
 </dependency>
 <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-jdbc</artifactId>
    <version>6.1.5</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>6.1.5</version>
</dependency>
<!-- 数据源 -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

Spring configuration (XML)

 <!-- 数据源 -->
 <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
     <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
     <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mydb"/>
     <property name="username" value="root"/>
     <property name="password" value="root123"/>
     <property name="maximumPoolSize" value="10"/>
     <property name="connectionTimeout" value="30000"/>
 </bean>
<!-- SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="configLocation" value="classpath:mybatis-config.xml"/>
    <property name="mapperLocations" value="classpath:mapper/*.xml"/>
    <property name="typeAliasesPackage" value="com.example.entity"/>
</bean>
<!-- Mapper 扫描 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.example.mapper"/>
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 事务管理 -->
<bean id="transactionManager"
     class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>

Spring configuration (Java configuration method)

 @Configuration
 @MapperScan("com.example.mapper")
 @EnableTransactionManagement
 public class MyBatisConfig {
     @Bean
     public DataSource dataSource() {
         HikariConfig config = new HikariConfig();
         config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("root123");
        config.setMaximumPoolSize(10);
        return new HikariDataSource(config);
    }
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception
     {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        factory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));
        factory.setTypeAliasesPackage("com.example.entity");
        return factory.getObject();
    }
    @Bean
    public DataSourceTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

Service layer usage

 @Service
 @Transactional
 public class EmpServiceImpl implements EmpService {
     @Autowired
     private EmpMapper empMapper;
     @Override
     public Emp getEmpById(Integer empId) {
        return empMapper.selectById(empId);
    }
    @Override
    public void transferSalary(Integer fromId, Integer toId, BigDecimal amount) {
        Emp from = empMapper.selectById(fromId);
        Emp to = empMapper.selectById(toId);
        from.setSalary(from.getSalary().subtract(amount));
        to.setSalary(to.getSalary().add(amount));
        empMapper.update(from);
        // 模拟异常
        // int i = 1 / 0;
        empMapper.update(to);  // 异常时自动回滚
    }
}

Spring Boot integration

dependent

 <dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>3.0.3</version>
 </dependency>
 <dependency>
     <groupId>com.mysql</groupId>
     <artifactId>mysql-connector-j</artifactId>
</dependency>

application.yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: root123
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
mybatis:
  # 配置文件位置
  config-location: classpath:mybatis-config.xml
  # Mapper XML 位置
  mapper-locations: classpath:mapper/*.xml
  # 实体类包
  type-aliases-package: com.example.entity
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazy-loading-enabled: true
    default-executor-type: reuse
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
# PageHelper
pagehelper:
  helper-dialect: mysql
  reasonable: true
  support-methods-arguments: true

startup class

@SpringBootApplication
@MapperScan("com.example.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Complete project structure

src/main/
├── java/com/example/
│   ├── Application.java          ← 启动类
│   ├── config/
│   │   └── MyBatisConfig.java    ← 可选：自定义配置
 │   ├── controller/
 │   │   └── EmpController.java
 │   ├── service/
 │   │   ├── EmpService.java
│   │   └── impl/EmpServiceImpl.java
│   ├── mapper/
│   │   ├── EmpMapper.java        ← Mapper 接口
│   │   └── DeptMapper.java
│   └── entity/
│       ├── Emp.java
│       └── Dept.java
└── resources/
    ├── application.yml
    ├── mybatis-config.xml        ← 可选
    ├── mapper/
    │   ├── EmpMapper.xml         ← Mapper XML
    │   └── DeptMapper.xml
    └── logback.xml

MyBatis-Plus

Introduction to MyBatis-Plus

MyBatis-Plus (MP for short) is an enhanced tool for MyBatis, based on MyBatisOnly enhance and no change, created to simplify development and improve efficiency.

core features

 ┌─────────────────────────────────────────────┐
 │           MyBatis-Plus 核心特性               │
 ├──────────────┬─────────────────────────────┤
 │  无侵入       │ 只做增强不做改变              │
 │  损耗小       │ 启动即注入基本 CURD           │
 │  强大的 CRUD  │ 内置通用 Mapper/Service      │
 │  Lambda 表达式│ 编译期检查字段名              │
 │  主键自动生成  │ 支持多种主键策略              │
 │  代码生成器    │ 快速生成代码                 │
│  分页插件      │ 内置分页                    │
│  逻辑删除      │ 内置逻辑删除                 │
│  乐观锁        │ 内置乐观锁                   │
│  自动填充      │ 自动填充创建/更新时间         │
└──────────────┴─────────────────────────────┘

dependent

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
    <version>3.5.5</version>
</dependency>

Entity class annotation

 @TableName("emp")   // 指定表名
 public class Emp {
     @TableId(type = IdType.AUTO)   // 主键策略
     private Integer empId;
     @TableField("emp_name")        // 指定列名（驼峰自动映射时可省略）
     private String empName;
    @TableField("email")
    private String email;
    @TableField(exist = false)     // 非数据库字段
    private String tempField;
    @TableField(fill = FieldFill.INSERT)  // 插入时自动填充
    private LocalDateTime createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)  // 插入和更新时自动填充
    private LocalDateTime updateTime;
    @TableLogic                    // 逻辑删除字段
    @TableField("status")
    private Integer deleted;
    @Version                       // 乐观锁版本号
    private Integer version;
}

`IdType`	description
`AUTO`	Use the database self-added primary key
`NONE`	No primary key policy is specified, which is decided by the global configuration or developer
`INPUT`	The developer manually sets the primary key before insertion
`ASSIGN_ID`	Assign the ID using the default identifier generator, usually the long integer value generated by the snowflake algorithm
`ASSIGN_UUID`	Assign a UUID string without hyphen

BaseMapper Universal CRUD

public interface EmpMapper extends BaseMapper<Emp> {
    // 自动拥有以下方法，无需编写 XML
}
// 使用
EmpMapper mapper = ...;
 // 插入
 mapper.insert(emp);
// 根据 ID 删除
mapper.deleteById(1);
// 根据 ID 更新
mapper.updateById(emp);
// 根据 ID 查询
Emp emp = mapper.selectById(1);
// 查询所有
List<Emp> list = mapper.selectList(null);
// 条件查询
List<Emp> list = mapper.selectList(
    new QueryWrapper<Emp>()
        .eq("dept_id", 1)
        .ge("salary", 10000)
        .like("emp_name", "张")
        .orderByDesc("salary")
);
// Lambda 条件查询（推荐）
List<Emp> list = mapper.selectList(
    new LambdaQueryWrapper<Emp>()
        .eq(Emp::getDeptId, 1)
        .ge(Emp::getSalary, new BigDecimal("10000"))
        .like(Emp::getEmpName, "张")
        .orderByDesc(Emp::getSalary)
);
// 查询数量
Long count = mapper.selectCount(null);
// 分页查询
Page<Emp> page = mapper.selectPage(
    new Page<>(1, 10),  // 第1页，每页10条
    new LambdaQueryWrapper<Emp>().eq(Emp::getStatus, 1)
);
List<Emp> records = page.getRecords();
long total = page.getTotal();

IService General Service

// Service 接口
public interface EmpService extends IService<Emp> {}
// Service 实现
@Service
 public class EmpServiceImpl extends ServiceImpl<EmpMapper, Emp> implements EmpService
     {}
 // 使用
 @Autowired
private EmpService empService;
// 保存
empService.save(emp);
// 批量保存
empService.saveBatch(empList);
// 批量保存（每100条提交一次）
empService.saveBatch(empList, 100);
// 根据 ID 更新
empService.updateById(emp);
// 保存或更新
empService.saveOrUpdate(emp);
// 根据 ID 查询
Emp emp = empService.getById(1);
// 查询列表
List<Emp> list = empService.list();
// 条件查询
List<Emp> list = empService.list(
    new LambdaQueryWrapper<Emp>().eq(Emp::getDeptId, 1)
);
// 分页
Page<Emp> page = empService.page(
    new Page<>(1, 10),
    new LambdaQueryWrapper<Emp>().eq(Emp::getStatus, 1)
);
// 链式查询
List<Emp> list = empService.lambdaQuery()
    .eq(Emp::getDeptId, 1)
    .ge(Emp::getSalary, new BigDecimal("10000"))
    .list();
// 链式更新
empService.lambdaUpdate()
    .eq(Emp::getDeptId, 1)
    .set(Emp::getSalary, new BigDecimal("20000"))
    .update();
// 链式删除
empService.lambdaUpdate()
    .eq(Emp::getStatus, 0)
    .remove();

Common methods for condition builders

method	role
`eq(column, value)`	equal to
`ne(column, value)`	not equal to
`gt(column, value)`	greater than
`ge(column, value)`	greater than or equal to
`lt(column, value)`	less than
`le(column, value)`	less than or equal to
`like(column, value)`	Fuzzy matching, adding wildcards on both sides of the value
`likeLeft(column, value)`	left fuzzy matching
`likeRight(column, value)`	right fuzzy matching
`notLike(column, value)`	non-fuzzy matching
`in(column, list)`	collection contains
`notIn(column, list)`	collection does not contain
`isNull(column)`	Judgment is null
`isNotNull(column)`	Judgment is not null
`between(column, v1, v2)`	interval matching
`notBetween(column, v1, v2)`	non-interval matching
`orderByDesc(column)`	descending sort
`orderByAsc(column)`	ascending sort
`groupBy(column)`	packet
`having(condition)`	added `HAVING` conditions
`last(sql)`	When splicing SQL at the end of a statement, you must ensure that the content is trustworthy
`select(columns...)`	Specify query column

paging plug-in configuration

 @Configuration
 public class MybatisPlusConfig {
     @Bean
     public MybatisPlusInterceptor mybatisPlusInterceptor() {
         MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
         // 分页插件
         interceptor.addInnerInterceptor(
            new PaginationInnerInterceptor(DbType.MYSQL)
        );
        // 乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

Logical delete configuration

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: deleted   # 逻辑删除字段
      logic-delete-value: 1         # 已删除值
      logic-not-delete-value: 0     # 未删除值

Automatic fill configuration

 @Component
 public class MyMetaObjectHandler implements MetaObjectHandler {
     @Override
     public void insertFill(MetaObject metaObject) {
         this.strictInsertFill(metaObject, "createTime", LocalDateTime.class,
     LocalDateTime.now());
         this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class,
     LocalDateTime.now());
     }
    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class,
     LocalDateTime.now());
    }
}

code generator

MyBatis Generator (MBG)

 <!-- pom.xml -->
 <build>
     <plugins>
         <plugin>
             <groupId>org.mybatis.generator</groupId>
             <artifactId>mybatis-generator-maven-plugin</artifactId>
             <version>3.0.4</version>
             <configuration>
     <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
                <overwrite>true</overwrite>
                <verbose>true</verbose>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>com.mysql</groupId>
                    <artifactId>mysql-connector-j</artifactId>
                    <version>8.3.0</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

 <!-- src/main/resources/generatorConfig.xml -->
 <?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE generatorConfiguration PUBLIC
     "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
     "https://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
 <generatorConfiguration>
     <context id="default" targetRuntime="MyBatis3">
        <!-- 不生成注释 -->
        <commentGenerator>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!-- 数据库连接 -->
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/mydb"
                        userId="root"
                        password="root123"/>
        <!-- 实体类生成路径 -->
        <javaModelGenerator targetPackage="com.example.entity"
                            targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!-- Mapper XML 生成路径 -->
        <sqlMapGenerator targetPackage="mapper"
                         targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!-- Mapper 接口生成路径 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.example.mapper"
                             targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!-- 要生成的表 -->
        <table tableName="emp" domainObjectName="Emp"
               enableCountByExample="false"
               enableUpdateByExample="false"
               enableDeleteByExample="false"
               enableSelectByExample="false"
               selectByExampleQueryId="false"/>
        <table tableName="dept" domainObjectName="Dept"/>
    </context>
</generatorConfiguration>

1mvn mybatis-generator

MyBatis-Plus code generator

 // 依赖
 // mybatis-plus-generator 3.5.5
 // velocity-engine-core 2.3
 public class CodeGenerator {
     public static void main(String[] args) {
         FastAutoGenerator.create(
                 "jdbc:mysql://localhost:3306/mydb",
                 "root",
                "root123"
        )
        .globalConfig(builder -> {
            builder.author("example")
                   .outputDir(System.getProperty("user.dir") + "/src/main/java")
                   .commentDate("yyyy-MM-dd");
        })
        .packageConfig(builder -> {
            builder.parent("com.example")
                   .entity("entity")
                   .mapper("mapper")
                   .service("service")
                   .serviceImpl("service.impl")
                   .controller("controller")
                   .pathInfo(Collections.singletonMap(
                       OutputFile.xml,
                       System.getProperty("user.dir") + "/src/main/resources/mapper"));
        })
        .strategyConfig(builder -> {
            builder.addInclude("emp", "dept")     // 表名
                   .addTablePrefix("t_", "sys_")  // 去除表前缀
                   .entityBuilder()
                       .enableLombok()
                       .enableTableFieldAnnotation()
                       .logicDeleteColumnName("deleted")
                       .versionColumnName("version")
                   .mapperBuilder()
                       .enableMapperAnnotation()
                   .serviceBuilder()
                       .formatServiceFileName("%sService")
                       .formatServiceImplFileName("%sServiceImpl");
        })
        .templateEngine(new VelocityTemplateEngine())
        .execute();
    }
}

performance optimization

SQL Tuning

use #{} rather than ${}(Prevent SQL injection + utilize pre-compiled caching)
Avoid SELECT *, only check the required columns **
Fair use index(Refer to MySQL Index Guide)
Avoid N+1 issues(Use nested results instead of nested queries, or use deferred loading)
Use Batch actuators for large batch operations

cache optimization

Reasonable use of secondary cache(Scenes where reading more and writing less)
Cache granularity control（useCache / flushCache）
Integrate distributed caches such as Redis(Replace local cache)

lazy loading

<!-- 开启延迟加载，减少不必要的关联查询 -->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

Connection pool optimization

<!-- 使用 HikariCP 高性能连接池 -->
<dataSource type="POOLED">
    <property name="poolMaximumActiveConnections" value="20"/>
    <property name="poolMaximumIdleConnections" value="10"/>
    <property name="poolMaximumCheckoutTime" value="20000"/>
    <property name="poolPingEnabled" value="true"/>
    <property name="poolPingQuery" value="SELECT 1"/>
</dataSource>

Batch operation optimization

 // 使用 Batch 执行器
 try (SqlSession session = factory.openSession(ExecutorType.BATCH)) {
     EmpMapper mapper = session.getMapper(EmpMapper.class);
     for (int i = 0; i < 10000; i++) {
         mapper.insert(emp);
         if (i % 1000 == 0) {
             session.flushStatements();  // 分批 flush
         }
     }
    session.commit();
}

fetchSize optimization

<!-- 大数据量查询时设置 fetchSize，减少网络交互 -->
<select id="selectLargeData" resultType="Emp" fetchSize="1000">
    SELECT * FROM emp
</select>

Open the log to view SQL (development environment)

<settings>
    <setting name="logImpl" value="SLF4J"/>
</settings>

Performance optimization checklist

optimization term	description	priority
SQL Tuning	Use indexes rationally and avoid full table scanning	⭐⭐⭐
Avoid nested queries N+1	Use join or delayed loading	⭐⭐⭐
bulk operation	Batch actuator	⭐⭐⭐
Connection pool tuning	Reasonably set the number of connections	⭐⭐
second level cache	Read more and write less scenes	⭐⭐
lazy loading	Reduce unnecessary inquiries	⭐⭐
fetchSize	Large data query	⭐
mapping optimization	Map only required fields	⭐

Frequently Asked Questions and Troubleshooting

`BindingException`: Unable to find mapping statement

symptoms：org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)

reason: Mapper interface method does not match statement id in XML

solve：

Check whether the namespace is consistent with the fully qualified name of the interface
Check whether the id is consistent with the method name
Check that the XML file is loaded correctly (mapper-locations configuration)

Parameter binding error

symptoms：There is no getter for property named ‘xxx’

reason: MyBatis rules for handling single parameters

solve：

// 方式一：加 @Param
List<Emp> select(@Param("empName") String empName);
// 方式二：用对象包装
List<Emp> select(Emp query);
// 方式三：用 Map
List<Emp> select(Map<String, Object> params);

Chinese garbled code

solve：

Database URL Plus characterEncoding= UTF-8
Set XML file encoding to UTF-8
Configure to ensure correct logging

Level 1 caching causes data inconsistency

scene: In the same SqlSession, other transactions modified data, but the current session still returns old data

solve：

<!-- 关闭一级缓存 -->
<setting name="localCacheScope" value="STATEMENT"/>
<!-- 或在查询时刷新 -->
<select id="selectFresh" flushCache="true">...</select>

L2 cache serialization error

symptoms：NotSerializableException

solve: Entity classes implement Serializable interfaces

Nested query N+1 questions

solve：

Use nested results (joins) instead of nested queries
Enable delayed loading
Batch query using MyBatis-Plus

${} causes SQL injection

solve：

Try to use #{}
When ${} must be used (table name/column name/sorting), perform input verification

Automatic mapping does not take effect

check：

Whether mapUnderscoreToCamelCase is set to true
Whether autoMappingBehavior is PARTIAL or FULL
Whether the autoMapping of ResultMap is true

Comparison symbol issues in dynamic SQL

solve：

<!-- 方式一：转义 -->
<if test="salary &gt; 10000">
<!-- 方式二：CDATA -->
<if test="salary <![CDATA[ > ]]> 10000">

PageHelper paging does not take effect

check：

PageHelper.startPage() must be used in the query methodthe previous rowcall
Ensure that only the first query that follows is valid
Check whether the plug-in configuration is correct

Practical case collection

 <!-- 查询部门→员工→项目（三级嵌套） -->
 <resultMap id="deptEmpProjectResultMap" type="Dept">
     <id property="deptId" column="dept_id"/>
     <result property="deptName" column="dept_name"/>
     <collection property="emps" ofType="Emp">
         <id property="empId" column="emp_id"/>
         <result property="empName" column="emp_name"/>
         <result property="salary" column="salary"/>
        <collection property="projects" ofType="Project">
            <id property="projectId" column="project_id"/>
            <result property="projectName" column="project_name"/>
            <result property="budget" column="budget"/>
        </collection>
    </collection>
</resultMap>
<select id="selectDeptEmpProject" resultMap="deptEmpProjectResultMap">
    SELECT
        d.dept_id, d.dept_name,
        e.emp_id, e.emp_name, e.salary,
        p.project_id, p.project_name, p.budget
    FROM dept d
    LEFT JOIN emp e ON d.dept_id = e.dept_id
    LEFT JOIN emp_project ep ON e.emp_id = ep.emp_id
    LEFT JOIN project p ON ep.project_id = p.project_id
    WHERE d.dept_id = #{deptId}
</select>

Case 2: General conditional paging query

 // DTO
 @Data
 public class EmpQueryDTO {
     private String empName;
     private Integer deptId;
     private BigDecimal minSalary;
     private BigDecimal maxSalary;
     private Integer status;
     private Integer pageNum = 1;
    private Integer pageSize = 10;
}

 <select id="selectByPage" resultType="Emp">
     SELECT
     <include refid="empColumns"/>
     FROM emp
     <where>
         <if test="empName != null and empName != ''">
             <bind name="likeName" value="'%' + empName + '%'"/>
             AND emp_name LIKE #{likeName}
         </if>
        <if test="deptId != null">
            AND dept_id = #{deptId}
        </if>
        <if test="minSalary != null">
            AND salary &gt;= #{minSalary}
        </if>
        <if test="maxSalary != null">
            AND salary &lt;= #{maxSalary}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    ORDER BY emp_id DESC
</select>

// Service
public PageInfo<Emp> getEmpPage(EmpQueryDTO dto) {
    PageHelper.startPage(dto.getPageNum(), dto.getPageSize());
    List<Emp> list = empMapper.selectByPage(dto);
    return new PageInfo<>(list);
}

Case 3: Batch import (Excel data import)

 @Transactional
 public void batchImport(List<EmpDTO> dtoList) {
     List<Emp> empList = dtoList.stream().map(dto -> {
         Emp emp = new Emp();
         emp.setEmpName(dto.getName());
         emp.setEmail(dto.getEmail());
         emp.setSalary(dto.getSalary());
         emp.setDeptId(dto.getDeptId());
         emp.setHireDate(dto.getHireDate());
        emp.setStatus(1);
        return emp;
    }).collect(Collectors.toList());
    // 分批插入，每 500 条一批
    int batchSize = 500;
    for (int i = 0; i < empList.size(); i += batchSize) {
        int end = Math.min(i + batchSize, empList.size());
        List<Emp> batch = empList.subList(i, end);
        empMapper.batchInsert(batch);
    }
}

Case 4: Custom TypeHandler (JSON storage)

// 实体类
@Data
public class Emp {
    private Integer empId;
    private String empName;
    // JSON 格式存储到数据库
    private Map<String, Object> extInfo;
}

 @MappedTypes(Map.class)
 @MappedJdbcTypes(JdbcType.VARCHAR)
 public class JsonTypeHandler extends BaseTypeHandler<Map<String, Object>> {
     private static final ObjectMapper mapper = new ObjectMapper();
     @Override
     public void setNonNullParameter(PreparedStatement ps, int i,
                                     Map<String, Object> parameter, JdbcType jdbcType)
     throws SQLException {
        try {
            ps.setString(i, mapper.writeValueAsString(parameter));
        } catch (JsonProcessingException e) {
            throw new SQLException("JSON 序列化失败", e);
        }
    }
    @Override
    public Map<String, Object> getNullableResult(ResultSet rs, String columnName)
     throws SQLException {
        return parse(rs.getString(columnName));
    }
    @Override
    public Map<String, Object> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parse(rs.getString(columnIndex));
    }
    @Override
    public Map<String, Object> getNullableResult(CallableStatement cs, int columnIndex)
     throws SQLException {
        return parse(cs.getString(columnIndex));
    }
    private Map<String, Object> parse(String json) {
        if (json == null || json.isEmpty()) return null;
        try {
            return mapper.readValue(json, new TypeReference<Map<String, Object>>() {});
        } catch (JsonProcessingException e) {
            return null;
        }
    }
}

Case 5: SQL Audit Interceptor

 @Intercepts({
     @Signature(type = Executor.class, method = "query",
                args = {MappedStatement.class, Object.class, RowBounds.class,
     ResultHandler.class}),
     @Signature(type = Executor.class, method = "update",
                args = {MappedStatement.class, Object.class})
 })
 public class SlowSqlInterceptor implements Interceptor {
     private static final long THRESHOLD = 1000;  // 1秒
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        BoundSql boundSql = ms.getBoundSql(parameter);
        String sql = boundSql.getSql().replaceAll("\\s+", " ").trim();
        long start = System.currentTimeMillis();
        Object result;
        try {
            result = invocation.proceed();
        } finally {
            long elapsed = System.currentTimeMillis() - start;
            if (elapsed > THRESHOLD) {
                log.warn("[慢SQL] {}ms | {} | {}", elapsed, ms.getId(), sql);
            } else {
                log.debug("[SQL] {}ms | {} | {}", elapsed, ms.getId(), sql);
            }
        }
        return result;
    }
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    @Override
    public void setProperties(Properties properties) {}
}

Case 6: MyBatis-Plus Complete CRUD

 // 实体类
 @TableName("emp")
 @Data
 public class Emp {
     @TableId(type = IdType.AUTO)
     private Long empId;
     @TableField("emp_name")
     private String empName;
    private String email;
    private BigDecimal salary;
    private Integer deptId;
    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;
    @TableLogic
    private Integer deleted;
}
// Mapper
public interface EmpMapper extends BaseMapper<Emp> {
    // 自定义查询
    @Select("SELECT e.* FROM emp e WHERE e.salary > #{salary}")
    List<Emp> selectHighSalary(@Param("salary") BigDecimal salary);
}
// Service
@Service
public class EmpServiceImpl extends ServiceImpl<EmpMapper, Emp> implements EmpService {
    public Page<Emp> getEmpPage(int pageNum, int pageSize, EmpQueryDTO query) {
        return this.page(
            new Page<>(pageNum, pageSize),
            new LambdaQueryWrapper<Emp>()
                .like(StrUtil.isNotBlank(query.getEmpName()), Emp::getEmpName,
     query.getEmpName())
                .eq(query.getDeptId() != null, Emp::getDeptId, query.getDeptId())
                .ge(query.getMinSalary() != null, Emp::getSalary, query.getMinSalary())
                .le(query.getMaxSalary() != null, Emp::getSalary, query.getMaxSalary())
                .eq(Emp::getDeleted, 0)
                .orderByDesc(Emp::getCreateTime)
        );
    }
    public List<Emp> batchUpsert(List<Emp> empList) {
        this.saveOrUpdateBatch(empList, 500);
        return empList;
    }
}
// Controller
@RestController
@RequestMapping("/emp")
public class EmpController {
    @Autowired
    private EmpService empService;
    @GetMapping("/page")
    public Result<Page<Emp>> page(@RequestParam(defaultValue = "1") int pageNum,
                                   @RequestParam(defaultValue = "10") int pageSize,
                                   EmpQueryDTO query) {
        return Result.ok(empService.getEmpPage(pageNum, pageSize, query));
    }
    @PostMapping
    public Result<Void> save(@RequestBody Emp emp) {
        empService.save(emp);
        return Result.ok();
    }
    @PutMapping
    public Result<Void> update(@RequestBody Emp emp) {
        empService.updateById(emp);
        return Result.ok();
    }
    @DeleteMapping("/{id}")
    public Result<Void> delete(@PathVariable Long id) {
        empService.removeById(id);  // 逻辑删除
        return Result.ok();
    }
}

Quick check manual

Quick check of core configuration

configuration items	default value	description
`mapUnderscoreToCamelCase`	`false`	Underline naming is automatically mapped to hump naming
`cacheEnabled`	`true`	Whether to allow mapper L2 caching
`lazyLoadingEnabled`	`false`	Whether to enable delayed loading
`defaultExecutorType`	`SIMPLE`	Default actuator type
`localCacheScope`	`SESSION`	Level 1 cache scope
`jdbcTypeForNull`	`OTHER`	`null` The JDBC type used for the parameter
`useGeneratedKeys`	`false`	Whether to allow JDBC to automatically generate primary keys
`autoMappingBehavior`	`PARTIAL`	Automatic mapping levels

Dynamic SQL quick check

elements	use	description
`<if>`	condition judgment	Joining SQL fragments when conditions are met
`<where>`	generated `WHERE`	Automatically remove unnecessary at the beginning `AND` or `OR`
`<set>`	generated `SET`	Automatically removes unnecessary commas at the end
`<choose>`	multi-branch selection	with `<when>`、`<otherwise>` mating
`<trim>`	Custom clipping	Adjust SQL using prefixes, suffixes, and override rules
`<foreach>`	ergodic set	commonly used in `IN` Conditions and batch writes
`<bind>`	Binding expression result	Variables can be pre-constructed for subsequent reference
`<sql>`	Defining SQL fragments	by `<include>` multiplexing

Quick check of association mapping

elements	use	commonly used attributes
`<association>`	one-to-one correlation	`property`、`javaType`、`column`、`select`、`fetchType`
`<collection>`	one-to-many correlation	`property`、`ofType`、`column`、`select`、`fetchType`
`<discriminator>`	Select mapping based on column values	`column`、`javaType`、`case`

Quick check of parameter placeholders

characteristics	`#{}`	`${}`
treatment	Precompiled parameter placeholders	String direct substitution
SQL injection risk	lower	higher
typical uses	parameter values	Table names, column names, or sorting fragments that have been whitelisted

Cache quick check

characteristics	a level one cache	second level cache
scope	`SqlSession`	Mapper namespace
default state	open	Need to be configured in the mapping file
Common failure opportunities	Update, commit, roll back, close session, or clear cache	Usually refreshed when executing update statements with the same namespace

Quick check of annotations

annotation	description
`@Select`	declaration query statement
`@Insert`	declaration insert statement
`@Update`	declare update statement
`@Delete`	declare delete statement
`@Results`、`@Result`	Declare result mapping
`@One`、`@Many`	Statement related query
`@Options`	Configure options such as primary key backfilling and caching
`@Param`	Name method parameters

Quick check of common mistakes

phenomenon	common cause	Investigation direction
`BindingException`	Mapping statement is not registered	Check namespace, statement ID, and mapping file scan path
Unable to find getter for property	Parameter names or expressions do not match	check `@Param`Entity attributes and OGNL expressions
`NotSerializableException`	Secondary cache objects are not serializable	Let cached objects implement `Serializable`, or adjust cache implementation
N+1 query	Nested queries are executed one by one	Evaluation joint table query, batch query or delayed loading
Chinese garbled code	Inconsistent client, connection, or database encoding	Unified Character Set Configuration

Appendix: Panorama of MyBatis Knowledge System

 MyBatis 知识体系
 ├── 基础
 │   ├── 概述与架构
 │   ├── 快速入门
 │   ├── 核心配置文件
 │   └── SqlSession 与映射器
 │
 ├── SQL 映射
 │   ├── Mapper XML（select/insert/update/delete）
│   ├── #{} vs ${}
│   ├── 参数传递（@Param/Map/对象/List）
│   ├── SQL 片段（<sql>/<include>）
│   └── 主键回填（useGeneratedKeys/selectKey）
│
├── 结果映射
│   ├── ResultMap（id/result/constructor）
│   ├── 自动映射（autoMappingBehavior）
│   ├── ResultMap 继承（extends）
│   └── 鉴别器（discriminator）
│
├── 动态 SQL
│   ├── if / where / set
│   ├── choose / when / otherwise
│   ├── trim / foreach / bind
│   └── SQL 片段复用
│
├── 关联查询
│   ├── 一对一（association）
│   ├── 一对多（collection）
│   ├── 多对多
│   ├── 嵌套结果 vs 嵌套查询
│   └── 延迟加载
│
├── 缓存
│   ├── 一级缓存（SqlSession）
│   ├── 二级缓存（namespace）
│   ├── 第三方缓存（EhCache/Redis）
│   └── cache-ref
│
├── 高级特性
│   ├── TypeHandler 类型处理器
│   ├── ObjectFactory 对象工厂
│   ├── Plugin 插件机制
│   ├── 分页（RowBounds/PageHelper）
│   └── 批量操作（foreach/Batch执行器）
│
├── 注解开发
│   ├── 基本 CRUD 注解
│   ├── @Results/@Result
│   ├── @One/@Many
│   └── @SelectProvider 等
│
├── 框架集成
│   ├── Spring 集成
│   ├── Spring Boot 集成
│   └── MyBatis-Plus
│
├── 工具
│   ├── 代码生成器（MBG/MP Generator）
│   └── 分页插件（PageHelper）
│
├── 性能优化
│   ├── SQL 优化
│   ├── 缓存策略
│   ├── 延迟加载
│   ├── 批量操作
│   ├── 连接池调优
│   └── fetchSize
│
└── 排错
    ├── BindingException
    ├── 参数绑定错误
    ├── 编码问题
    ├── 缓存问题
    └── N+1 问题

document explains: This document comprehensively covers all knowledge points of MyBatis, from basic concepts to advanced practical combat, from theory to command operations, without omission. It is suitable for beginners to learn systematically, and it is also suitable for experienced developers as a reference manual. Used in conjunction with previous MySQL (JOIN/Subquery/Transaction/Index) and Maven guides, a complete Java backend knowledge body can be built.

A complete guide to MyBatis's complete knowledge system

A complete guide to MyBatis’s complete knowledge system

MyBatis Overview

What is MyBatis

MyBatis ‘positioning

Core features of MyBatis

MyBatis Architecture

MyBatis version

quick start

sample database

Maven Depends

entity class

mybatis-config.xml

db.properties

Mapper interface

Mapper XML

tool class

test

Core configuration file (mybatis-config.xml)

Complete configuration structure

Configure element order

Detailed explanation of common Settings

Three types of data sources

Two transaction managers

type alias

SqlSession and Mapper Interface

SqlSession life cycle

SqlSession core method

Two ways of using

Method 1: Call directly through namespace

Method 2: Call through the Mapper interface (recommended)

Three actuators

Affairs management

Mapper file (Mapper XML)

complete structure

Detailed explanation of select element attributes

The difference between #{} and ${}

parameter passing

single parameter

Multiple parameters (using @Param)

Multiple parameters (using Map /Objects)

Parameter is List / Array

Detailed explanation of insert elements

SQL fragment

Detailed explanation of ResultsMap

Why you need a ResultsMap

Basic structure of ResultMap

constructor map

autoMapping automatic mapping

ResultMap inheritance

Discriminator

dynamic SQL

Overview of dynamic SQL elements

if element

where element

choose / when / otherwise

set element

trim element

foreach element

bind element

Complete dynamic SQL example

relational query

One-to-one association

Method 1: Nested results (join query, once SQL)

Method 2: Nested query (step-by-step query, N+1 questions)

Method 3: Nested query passes multiple parameters

One-to-many association (collection)

Method 1: Nested results

Method 2: Nested query

many-to-many correlation

Nested query vs nested result comparison

Delayed loading configuration

caching mechanism

cache system

a level one cache

working principle

Level 1 cache invalidation conditions

Level 1 cache range

second level cache

opening method