Spring 整合 MyBatis

1. MyBatis 概述

1.1 什么是 MyBatis

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。

1.2 MyBatis vs JPA

特性	MyBatis	JPA/Hibernate
SQL 控制	完全控制	自动生成
学习曲线	较低	较高
灵活性	高	中等
复杂查询	简单	较复杂
数据库迁移	需修改 SQL	相对容易

2. 快速入门

2.1 添加依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.15</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

2.2 数据源配置

@Configuration
public class DataSourceConfig {
    
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC");
        config.setUsername("root");
        config.setPassword("password");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000);
        config.setConnectionTimeout(20000);
        return new HikariDataSource(config);
    }
    
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setTypeAliasesPackage("com.example.entity");
        factory.setMapperLocations(new PathMatchingResourcePatternResolver()
            .getResources("classpath:mapper/*.xml"));
        
        org.apache.ibatis.session.Configuration configuration = 
            new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultFetchSize(100);
        configuration.setDefaultStatementTimeout(30);
        factory.setConfiguration(configuration);
        
        return factory.getObject();
    }
    
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer configurer = new MapperScannerConfigurer();
        configurer.setBasePackage("com.example.mapper");
        configurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        return configurer;
    }
}

2.3 实体类

public class User {
    private Long id;
    private String username;
    private String password;
    private String email;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;
    
    // getters and setters
}

2.4 Mapper 接口

public interface UserMapper {
    
    User findById(Long id);
    
    List<User> findAll();
    
    List<User> findByCondition(UserQuery query);
    
    int insert(User user);
    
    int update(User user);
    
    int deleteById(Long id);
}

2.5 Mapper XML

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.mapper.UserMapper">
    
    <resultMap id="userResultMap" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
        <result property="createdAt" column="created_at"/>
        <result property="updatedAt" column="updated_at"/>
    </resultMap>
    
    <select id="findById" resultMap="userResultMap">
        SELECT * FROM users WHERE id = #{id}
    </select>
    
    <select id="findAll" resultMap="userResultMap">
        SELECT * FROM users ORDER BY created_at DESC
    </select>
    
    <select id="findByCondition" resultMap="userResultMap">
        SELECT * FROM users
        <where>
            <if test="username != null and username != ''">
                AND username LIKE CONCAT('%', #{username}, '%')
            </if>
            <if test="email != null and email != ''">
                AND email = #{email}
            </if>
        </where>
    </select>
    
    <insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO users (username, password, email, created_at, updated_at)
        VALUES (#{username}, #{password}, #{email}, #{createdAt}, #{updatedAt})
    </insert>
    
    <update id="update" parameterType="User">
        UPDATE users
        <set>
            <if test="username != null">username = #{username},</if>
            <if test="password != null">password = #{password},</if>
            <if test="email != null">email = #{email},</if>
            updated_at = #{updatedAt}
        </set>
        WHERE id = #{id}
    </update>
    
    <delete id="deleteById">
        DELETE FROM users WHERE id = #{id}
    </delete>
</mapper>

3. 动态 SQL

3.1 if 标签

<select id="search" resultMap="userResultMap">
    SELECT * FROM users
    <where>
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

3.2 choose/when/otherwise

<select id="findByType" resultMap="userResultMap">
    SELECT * FROM users
    <where>
        <choose>
            <when test="type == 'admin'">
                AND role = 'ADMIN'
            </when>
            <when test="type == 'user'">
                AND role = 'USER'
            </when>
            <otherwise>
                AND role = 'GUEST'
            </otherwise>
        </choose>
    </where>
</select>

3.3 foreach 标签

<select id="findByIds" resultMap="userResultMap">
    SELECT * FROM users WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<insert id="batchInsert">
    INSERT INTO users (username, email) VALUES
    <foreach collection="users" item="user" separator=",">
        (#{user.username}, #{user.email})
    </foreach>
</insert>

3.4 sql 片段

<sql id="userColumns">
    id, username, password, email, created_at, updated_at
</sql>

<sql id="userWhere">
    <where>
        <if test="username != null">AND username = #{username}</if>
        <if test="email != null">AND email = #{email}</if>
    </where>
</sql>

<select id="findByCondition" resultMap="userResultMap">
    SELECT <include refid="userColumns"/>
    FROM users
    <include refid="userWhere"/>
</select>

3.5 set 标签

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

4. 注解方式

4.1 基本注解

public interface UserMapper {
    
    @Select("SELECT * FROM users WHERE id = #{id}")
    User findById(Long id);
    
    @Select("SELECT * FROM users ORDER BY created_at DESC")
    List<User> findAll();
    
    @Insert("INSERT INTO users (username, password, email) " +
            "VALUES (#{username}, #{password}, #{email})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
    
    @Update("UPDATE users SET username = #{username}, email = #{email} " +
            "WHERE id = #{id}")
    int update(User user);
    
    @Delete("DELETE FROM users WHERE id = #{id}")
    int deleteById(Long id);
}

4.2 结果映射

@Results(id = "userResult", value = {
    @Result(property = "id", column = "id", id = true),
    @Result(property = "username", column = "username"),
    @Result(property = "email", column = "email"),
    @Result(property = "createdAt", column = "created_at"),
    @Result(property = "roles", column = "id", 
            many = @Many(select = "findRolesByUserId"))
})
@Select("SELECT * FROM users WHERE id = #{id}")
User findByIdWithRoles(Long id);

@ResultMap("userResult")
@Select("SELECT * FROM users")
List<User> findAllWithResultMap();

4.3 动态 SQL 注解

public interface UserMapper {
    
    @SelectProvider(type = UserSqlProvider.class, method = "findByCondition")
    List<User> findByCondition(UserQuery query);
    
    @InsertProvider(type = UserSqlProvider.class, method = "insertSelective")
    int insertSelective(User user);
    
    @UpdateProvider(type = UserSqlProvider.class, method = "updateSelective")
    int updateSelective(User user);
}

public class UserSqlProvider {
    
    public String findByCondition(UserQuery query) {
        return new SQL() {{
            SELECT("*");
            FROM("users");
            if (query.getUsername() != null) {
                WHERE("username LIKE CONCAT('%', #{username}, '%')");
            }
            if (query.getEmail() != null) {
                WHERE("email = #{email}");
            }
            ORDER_BY("created_at DESC");
        }}.toString();
    }
    
    public String insertSelective(User user) {
        return new SQL() {{
            INSERT_INTO("users");
            if (user.getUsername() != null) {
                VALUES("username", "#{username}");
            }
            if (user.getEmail() != null) {
                VALUES("email", "#{email}");
            }
        }}.toString();
    }
    
    public String updateSelective(User user) {
        return new SQL() {{
            UPDATE("users");
            if (user.getUsername() != null) {
                SET("username = #{username}");
            }
            if (user.getEmail() != null) {
                SET("email = #{email}");
            }
            WHERE("id = #{id}");
        }}.toString();
    }
}

5. 关联查询

5.1 一对一关联

<resultMap id="userWithProfileResultMap" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <association property="profile" javaType="UserProfile">
        <id property="id" column="profile_id"/>
        <result property="nickname" column="nickname"/>
        <result property="avatar" column="avatar"/>
    </association>
</resultMap>

<select id="findUserWithProfile" resultMap="userWithProfileResultMap">
    SELECT u.*, p.id as profile_id, p.nickname, p.avatar
    FROM users u
    LEFT JOIN user_profiles p ON u.id = p.user_id
    WHERE u.id = #{id}
</select>

5.2 一对多关联

<resultMap id="userWithOrdersResultMap" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="orderNo" column="order_no"/>
        <result property="amount" column="amount"/>
    </collection>
</resultMap>

<select id="findUserWithOrders" resultMap="userWithOrdersResultMap">
    SELECT u.*, o.id as order_id, o.order_no, o.amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>

5.3 多对多关联

<resultMap id="userWithRolesResultMap" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <collection property="roles" ofType="Role">
        <id property="id" column="role_id"/>
        <result property="name" column="role_name"/>
        <collection property="permissions" ofType="Permission">
            <id property="id" column="permission_id"/>
            <result property="name" column="permission_name"/>
        </collection>
    </collection>
</resultMap>

<select id="findUserWithRolesAndPermissions" resultMap="userWithRolesResultMap">
    SELECT u.*, r.id as role_id, r.name as role_name,
           p.id as permission_id, p.name as permission_name
    FROM users u
    LEFT JOIN user_roles ur ON u.id = ur.user_id
    LEFT JOIN roles r ON ur.role_id = r.id
    LEFT JOIN role_permissions rp ON r.id = rp.role_id
    LEFT JOIN permissions p ON rp.permission_id = p.id
    WHERE u.id = #{id}
</select>

5.4 嵌套查询

<resultMap id="userWithRolesNestedResultMap" type="User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <collection property="roles" 
                column="id" 
                select="findRolesByUserId"/>
</resultMap>

<select id="findUserWithRolesNested" resultMap="userWithRolesNestedResultMap">
    SELECT * FROM users WHERE id = #{id}
</select>

<select id="findRolesByUserId" resultType="Role">
    SELECT r.* FROM roles r
    JOIN user_roles ur ON r.id = ur.role_id
    WHERE ur.user_id = #{userId}
</select>

6. 分页查询

6.1 PageHelper 插件

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>6.1.0</version>
</dependency>

@Configuration
public class MyBatisConfig {
    
    @Bean
    public PageInterceptor pageInterceptor() {
        PageInterceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "mysql");
        properties.setProperty("reasonable", "true");
        interceptor.setProperties(properties);
        return interceptor;
    }
}

@Service
public class UserService {
    
    public PageInfo<User> findByPage(int pageNum, int pageSize) {
        PageHelper.startPage(pageNum, pageSize);
        List<User> users = userMapper.findAll();
        return new PageInfo<>(users);
    }
    
    public PageInfo<User> findByPageWithSort(int pageNum, int pageSize, String orderBy) {
        PageHelper.startPage(pageNum, pageSize, orderBy);
        List<User> users = userMapper.findAll();
        return new PageInfo<>(users);
    }
}

6.2 自定义分页

public record PageRequest(
    int pageNum,
    int pageSize,
    String orderBy
) {
    public int getOffset() {
        return (pageNum - 1) * pageSize;
    }
}

public record PageResult<T>(
    List<T> data,
    long total,
    int pageNum,
    int pageSize,
    int totalPages
) {
    public static <T> PageResult<T> of(List<T> data, long total, PageRequest request) {
        int totalPages = (int) Math.ceil((double) total / request.pageSize());
        return new PageResult<>(data, total, request.pageNum(), request.pageSize(), totalPages);
    }
}

<select id="countAll" resultType="long">
    SELECT COUNT(*) FROM users
</select>

<select id="findByPage" resultMap="userResultMap">
    SELECT * FROM users
    ORDER BY ${orderBy}
    LIMIT #{offset}, #{pageSize}
</select>

7. 批量操作

7.1 批量插入

<insert id="batchInsert">
    INSERT INTO users (username, email, created_at)
    VALUES
    <foreach collection="users" item="user" separator=",">
        (#{user.username}, #{user.email}, #{user.createdAt})
    </foreach>
</insert>

7.2 批量更新

<update id="batchUpdate">
    <foreach collection="users" item="user" separator=";">
        UPDATE users SET
        username = #{user.username},
        email = #{user.email}
        WHERE id = #{user.id}
    </foreach>
</update>

7.3 使用 Batch Executor

@Service
public class BatchService {
    
    private final SqlSessionFactory sqlSessionFactory;
    
    public void batchInsert(List<User> users) {
        try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            for (User user : users) {
                mapper.insert(user);
            }
            session.commit();
        }
    }
}

8. 缓存机制

8.1 一级缓存

MyBatis 默认开启一级缓存（SqlSession 级别）：

SqlSession session = sqlSessionFactory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);

User user1 = mapper.findById(1L); // 查询数据库
User user2 = mapper.findById(1L); // 从缓存获取

session.clearCache(); // 清除缓存
User user3 = mapper.findById(1L); // 重新查询数据库

8.2 二级缓存

<!-- Mapper XML 中启用 -->
<mapper namespace="com.example.mapper.UserMapper">
    <cache/>
    
    <select id="findById" resultMap="userResultMap" useCache="true">
        SELECT * FROM users WHERE id = #{id}
    </select>
</mapper>

// 或使用注解
@CacheNamespace
public interface UserMapper {
    
    @CacheNamespaceRef(UserMapper.class)
    User findById(Long id);
}

8.3 整合 Redis 缓存

<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-redis</artifactId>
    <version>1.0.0-beta2</version>
</dependency>

<cache type="org.mybatis.caches.redis.RedisCache"/>

9. 插件开发

9.1 自定义插件

@Intercepts({
    @Signature(type = Executor.class, method = "update", 
               args = {MappedStatement.class, Object.class})
})
public class SqlLogPlugin implements Interceptor {
    
    private static final Logger logger = LoggerFactory.getLogger(SqlLogPlugin.class);
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        
        long start = System.currentTimeMillis();
        Object result = invocation.proceed();
        long duration = System.currentTimeMillis() - start;
        
        logger.info("SQL: {} - {} ms", ms.getId(), duration);
        
        return result;
    }
}

9.2 注册插件

@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setDataSource(dataSource);
    factory.setPlugins(new SqlLogPlugin());
    return factory.getObject();
}

10. 最佳实践

10.1 命名规范

类型	命名规范	示例
Mapper 接口	XxxMapper	UserMapper
XML 文件	XxxMapper.xml	UserMapper.xml
实体类	驼峰命名	User, UserOrder
表名	下划线命名	user, user_order

10.2 代码分层

com.example
├── controller    // 控制器层
├── service       // 业务逻辑层
│   └── impl
├── mapper        // 数据访问层
├── entity        // 实体类
├── dto           // 数据传输对象
└── config        // 配置类

10.3 事务管理

@Service
@Transactional
public class UserServiceImpl implements UserService {
    
    @Transactional(readOnly = true)
    public User findById(Long id) {
        return userMapper.findById(id);
    }
    
    @Transactional(rollbackFor = Exception.class)
    public void createUser(User user) {
        userMapper.insert(user);
    }
}

11. 小结

本章学习了 Spring 整合 MyBatis 的核心内容：

内容	要点
基础配置	数据源、SqlSessionFactory、Mapper 扫描
动态 SQL	if、choose、foreach、set、sql 片段
注解方式	@Select、@Insert、@Update、@Delete
关联查询	一对一、一对多、多对多
分页查询	PageHelper、自定义分页
批量操作	foreach、Batch Executor
缓存机制	一级缓存、二级缓存、Redis
插件开发	Interceptor、自定义插件

下一章将学习 Spring Boot 4 的快速入门。