SpringBoot整合MyBatis操作MySQL全流程解析

1. SpringBoot整合MyBatis操作MySQL全流程解析

作为Java开发者最常用的技术组合之一，SpringBoot+MyBatis+MySQL的黄金三角搭配几乎出现在80%的中小型企业级项目中。这种组合之所以流行，是因为它完美平衡了开发效率与运行性能——SpringBoot的自动化配置让开发者从繁琐的XML中解放，MyBatis的SQL灵活性保留了传统JDBC的优势，而MySQL作为最成熟的开源关系型数据库，提供了稳定可靠的数据存储方案。

我在实际企业项目开发中发现，虽然这个技术栈入门简单，但要真正发挥其威力，需要特别注意几个关键点：MyBatis的Mapper接口与XML文件的映射规则、SpringBoot自动配置的数据源参数优先级、以及MySQL连接池的性能调优。下面我就结合一个电商用户模块的案例，详细拆解从环境搭建到查询优化的完整实现过程。

2. 环境准备与项目初始化

2.1 基础环境配置

推荐使用以下环境组合（经多个生产项目验证稳定）：

• JDK 17（LTS长期支持版本）
• SpringBoot 3.1.5（注意GroupId已改为org.springframework.boot）
• MyBatis 3.0.2（与SpringBoot 3.x版本匹配）
• MySQL 8.0.33（社区版）

在IDEA中创建项目时，除了选择Spring Web和MySQL Driver这两个基础依赖外，必须显式添加MyBatis Starter：

<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>3.0.2</version> </dependency>

特别注意：SpringBoot 3.x开始使用Jakarta EE 9+规范，包路径从javax变更为jakarta，若混用会导致ClassNotFound异常。

2.2 数据库连接配置

在application.yml中配置数据源时，建议采用以下优化参数：

spring: datasource: url: jdbc:mysql://localhost:3306/ecommerce?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 加密密码建议使用Jasypt driver-class-name: com.mysql.cj.jdbc.Driver hikari: maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000

其中HikariCP的连接池参数需要根据实际业务QPS调整：

• 常规Web应用：maxPoolSize ≈ (CPU核心数 * 2) + 有效磁盘数
• 高并发场景：需要配合压测工具确定最优值

3. MyBatis核心组件配置

3.1 实体类与Mapper设计

以用户表为例，实体类设计应遵循JPA规范：

@Data @NoArgsConstructor @AllArgsConstructor public class User { private Long id; private String username; private String encryptedPassword; private LocalDateTime createTime; private Integer status; // 枚举转换示例 public enum Status { NORMAL(1), LOCKED(0); private final int code; } }

Mapper接口建议采用以下最佳实践：

@Mapper public interface UserMapper { @Select("SELECT * FROM user WHERE id = #{id}") User selectById(@Param("id") Long id); @Results({ @Result(property = "status", column = "status", javaType = User.Status.class, typeHandler = EnumOrdinalTypeHandler.class) }) @Select("SELECT * FROM user WHERE username = #{name}") User selectByUsername(String name); }

3.2 XML映射文件要点

在resources/mapper目录下创建UserMapper.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="encryptedPassword" column="password"/> <result property="createTime" column="create_time"/> <result property="status" column="status" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/> </resultMap> <select id="selectUsersByCondition" resultMap="userResultMap"> SELECT * FROM user <where> <if test="username != null"> AND username LIKE CONCAT('%', #{username}, '%') </if> <if test="status != null"> AND status = #{status.code} </if> </where> ORDER BY create_time DESC </select> </mapper>

关键配置技巧：

1. 使用<where>标签自动处理AND前缀
2. 枚举字段通过typeHandler实现自动转换
3. 模糊查询使用CONCAT避免SQL注入

4. 高级查询与性能优化

4.1 分页查询实现

SpringBoot整合PageHelper的推荐方式：

1. 添加依赖：

<dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency>

2. 服务层实现：

public PageInfo<User> queryUsers(int pageNum, int pageSize, String username) { PageHelper.startPage(pageNum, pageSize); List<User> users = userMapper.selectByUsername(username); return new PageInfo<>(users); }

警告：PageHelper的startPage()必须紧挨Mapper调用，中间不能有其它查询

4.2 二级缓存配置

在application.yml中启用MyBatis缓存：

mybatis: configuration: cache-enabled: true local-cache-scope: statement

实体类需要实现Serializable接口，然后在Mapper接口添加注解：

@CacheNamespace(implementation = MybatisRedisCache.class, eviction = MybatisRedisCache.class) public interface UserMapper { //... }

自定义Redis缓存实现类示例：

public class MybatisRedisCache implements Cache { private final ReadWriteLock lock = new ReentrantReadWriteLock(); private final String id; private final RedisTemplate<String, Object> redisTemplate; public MybatisRedisCache(String id) { this.id = id; this.redisTemplate = (RedisTemplate<String, Object>) SpringContextHolder.getBean("redisTemplate"); } // 实现Cache接口所有方法... }

5. 生产环境常见问题排查

5.1 连接泄露检测

在应用关闭时添加以下检查：

@PreDestroy public void checkConnectionLeaks() { HikariDataSource dataSource = (HikariDataSource) applicationContext.getBean(DataSource.class); if (dataSource.getHikariPoolMXBean().getActiveConnections() > 0) { logger.warn("存在数据库连接未关闭: {}", dataSource.getHikariPoolMXBean().getActiveConnections()); } }

5.2 SQL性能监控

配置p6spy打印真实SQL：

spring: datasource: url: jdbc:p6spy:mysql://localhost:3306/db driver-class-name: com.p6spy.engine.spy.P6SpyDriver

在spy.properties中配置：

module.log=com.p6spy.engine.logging.P6LogFactory appender=com.p6spy.engine.spy.appender.Slf4JLogger logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat customLogMessageFormat=%(executionTime)ms | %(category) | connection %(connectionId) | %(sqlSingleLine)

5.3 事务管理要点

声明式事务的隔离级别选择建议：

• 读操作：@Transactional(readOnly = true, isolation = Isolation.READ_COMMITTED)
• 写操作：@Transactional(isolation = Isolation.REPEATABLE_READ)

特别注意：MyBatis的ExecutorType.BATCH模式需要手动控制session提交：

@Autowired private SqlSessionTemplate sqlSessionTemplate; public void batchInsert(List<User> users) { SqlSession session = sqlSessionTemplate.getSqlSessionFactory() .openSession(ExecutorType.BATCH, false); try { UserMapper mapper = session.getMapper(UserMapper.class); for (User user : users) { mapper.insert(user); } session.commit(); } finally { session.close(); } }

6. 扩展功能实现

6.1 多数据源配置

1. 主数据源配置类：

@Configuration @MapperScan(basePackages = "com.example.mapper.primary", sqlSessionTemplateRef = "primarySqlSessionTemplate") public class PrimaryDataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.primary") public DataSource primaryDataSource() { return DataSourceBuilder.create().build(); } @Bean public SqlSessionFactory primarySqlSessionFactory( @Qualifier("primaryDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations( new PathMatchingResourcePatternResolver() .getResources("classpath:mapper/primary/*.xml")); return bean.getObject(); } @Bean public DataSourceTransactionManager primaryTransactionManager( @Qualifier("primaryDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean public SqlSessionTemplate primarySqlSessionTemplate( @Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } }

6.2 动态表名处理

使用MyBatis拦截器实现：

@Intercepts({ @Signature(type= StatementHandler.class, method="prepare", args={Connection.class, Integer.class}) }) public class DynamicTableInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler handler = (StatementHandler) invocation.getTarget(); MetaObject metaObject = SystemMetaObject.forObject(handler); MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement"); if (mappedStatement.getId().contains("DynamicTable")) { BoundSql boundSql = handler.getBoundSql(); String sql = boundSql.getSql(); sql = sql.replace("#{dynamicTable}", getActualTableName()); metaObject.setValue("delegate.boundSql.sql", sql); } return invocation.proceed(); } private String getActualTableName() { // 根据业务规则返回真实表名 return "user_" + LocalDate.now().getYear(); } }

7. 监控与调优

7.1 Druid监控面板

若使用阿里Druid连接池，添加以下配置：

spring: datasource: druid: stat-view-servlet: enabled: true url-pattern: /druid/* login-username: admin login-password: 加密密码 web-stat-filter: enabled: true exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" filter: stat: log-slow-sql: true slow-sql-millis: 1000

访问/druid路径即可查看：

• SQL执行频次与耗时
• 连接池状态
• URI访问统计

7.2 MyBatis性能分析

使用MyBatis-Plus的性能分析插件：

@Bean public PerformanceInterceptor performanceInterceptor() { PerformanceInterceptor interceptor = new PerformanceInterceptor(); interceptor.setFormat(true); interceptor.setMaxTime(1000); return interceptor; }

输出示例：

Time：2 ms - ID：com.example.mapper.UserMapper.selectById Execute SQL： SELECT id,username,password FROM user WHERE id=1

8. 安全加固措施

8.1 SQL注入防护

1. 永远不要使用字符串拼接SQL：

// 错误示范 @Select("SELECT * FROM user WHERE id = " + id) User selectById(String id); // 正确做法 @Select("SELECT * FROM user WHERE id = #{id}") User selectById(@Param("id") Long id);

2. 使用MyBatis的<bind>标签处理模糊查询：

<select id="searchUsers"> <bind name="pattern" value="'%' + keyword + '%'" /> SELECT * FROM user WHERE username LIKE #{pattern} </select>

8.2 敏感数据加密

实现TypeHandler处理加密字段：

public class EncryptedStringTypeHandler extends BaseTypeHandler<String> { private final Encryptor encryptor = new AESEncryptor(); @Override public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) { ps.setString(i, encryptor.encrypt(parameter)); } @Override public String getNullableResult(ResultSet rs, String columnName) { return encryptor.decrypt(rs.getString(columnName)); } // 其他重载方法... }

在Mapper XML中指定typeHandler：

<result column="password" property="encryptedPassword" typeHandler="com.example.handler.EncryptedStringTypeHandler"/>

9. 测试策略

9.1 单元测试配置

使用MyBatis-Spring的测试支持：

@MybatisTest @AutoConfigureTestDatabase(replace = Replace.NONE) @Rollback class UserMapperTest { @Autowired private UserMapper userMapper; @Test void testSelectById() { User user = userMapper.selectById(1L); assertNotNull(user); assertEquals("admin", user.getUsername()); } }

测试配置要点：

1. @MybatisTest会自动配置内存数据库（需排除@DataJpaTest）
2. 生产数据库测试需设置replace = Replace.NONE
3. @Rollback确保测试后回滚事务

9.2 集成测试方案

使用Testcontainers进行真实MySQL测试：

@Testcontainers @SpringBootTest class UserIntegrationTest { @Container static MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0"); @DynamicPropertySource static void configureProperties(DynamicPropertyRegistry registry) { registry.add("spring.datasource.url", mysql::getJdbcUrl); registry.add("spring.datasource.username", mysql::getUsername); registry.add("spring.datasource.password", mysql::getPassword); } @Test void testUserFlow() { // 测试完整业务流程 } }

10. 部署注意事项

10.1 多环境配置

使用Spring Profile管理环境差异：

# application-dev.yml spring: datasource: url: jdbc:mysql://dev-db:3306/app username: devuser # application-prod.yml spring: datasource: url: jdbc:mysql://prod-cluster:3306/app?useSSL=true username: produser hikari: maximum-pool-size: 50

激活方式：

• 启动参数：--spring.profiles.active=prod
• 环境变量：SPRING_PROFILES_ACTIVE=prod

10.2 数据库迁移方案

推荐使用Flyway管理DDL变更：

1. 添加依赖：

<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>

2. 在resources/db/migration目录放置SQL脚本：

• V1__Initial_schema.sql
• V2__Add_user_status_column.sql

3. 配置基线版本（适用于已有数据库）：

spring: flyway: baseline-on-migrate: true baseline-version: 1

11. 性能调优实战

11.1 连接池参数优化

针对不同业务场景的HikariCP配置建议：

1. 高并发查询场景：

spring: datasource: hikari: maximum-pool-size: 50 minimum-idle: 10 connection-timeout: 1000 idle-timeout: 60000 max-lifetime: 120000

2. 批量处理场景：

spring: datasource: hikari: maximum-pool-size: 10 minimum-idle: 2 connection-timeout: 3000 idle-timeout: 300000 max-lifetime: 1800000

11.2 MyBatis执行器选择

在application.yml中配置执行器类型：

mybatis: executor-type: batch # 可选simple/reuse/batch

各执行器适用场景：

• Simple：默认模式，每次执行都会创建新的PreparedStatement
• Reuse：复用预处理语句，适合OLTP场景
• Batch：批量模式，适合数据导入等批量操作

12. 最新特性适配

12.1 SpringBoot 3.x新特性

1. 记录SQL执行日志的新方式：

logging: level: org.springframework.jdbc.core.JdbcTemplate: DEBUG org.springframework.jdbc.core.StatementCreatorUtils: TRACE

2. 构造函数注入的简化写法：

@Mapper public interface UserMapper { @ConstructorArgs({ @Arg(column="id", javaType=Long.class), @Arg(column="username", javaType=String.class) }) @Select("SELECT id, username FROM user WHERE id = #{id}") User selectById(Long id); }

12.2 MyBatis 3.5+特性

1. 注解动态SQL：

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName") List<User> getUsersByName(String name); class UserSqlBuilder { public static String buildGetUsersByName(final String name) { return new SQL(){{ SELECT("*"); FROM("user"); if (name != null) { WHERE("username like #{name} || '%'"); } ORDER_BY("id"); }}.toString(); } }

2. 结果映射简化：

@Results(id = "userResult", value = { @Result(property = "id", column = "id", id = true), @Result(property = "username", column = "username") }) @Select("SELECT * FROM user WHERE id = #{id}") User selectById(Long id); @ResultMap("userResult") @Select("SELECT * FROM user WHERE username = #{name}") User selectByUsername(String name);

13. 异常处理机制

13.1 常见异常排查

1. BindingException检查点：

• Mapper接口与XML的namespace是否一致
• 方法名是否与XML中的id匹配
• 参数类型是否一致

2. TooManyResultsException处理：

// 使用Optional避免NPE @Select("SELECT * FROM user WHERE username = #{name}") Optional<User> selectByUsername(String name); // 或者限制结果集 @Select("SELECT * FROM user WHERE username = #{name} LIMIT 1") User selectOneByUsername(String name);

13.2 自定义异常转换

实现MyBatis的ExceptionTranslator：

@Bean public PersistenceExceptionTranslationPostProcessor persistenceExceptionTranslationPostProcessor() { return new PersistenceExceptionTranslationPostProcessor(); } @ExceptionHandler(DataAccessException.class) public ResponseEntity<ErrorResponse> handleDataAccessException(DataAccessException ex) { if (ex.getCause() instanceof SQLException) { SQLException sqlEx = (SQLException) ex.getCause(); return ResponseEntity.status(500) .body(new ErrorResponse("SQL_ERROR", sqlEx.getMessage())); } return ResponseEntity.status(500) .body(new ErrorResponse("DATABASE_ERROR", ex.getMessage())); }

14. 项目结构最佳实践

推荐的分层架构：

src/main/java ├── com.example │ ├── config/ # 配置类 │ ├── controller/ # 表现层 │ ├── service/ # 业务逻辑层 │ │ ├── impl/ # 实现类 │ ├── mapper/ # MyBatis Mapper接口 │ ├── model/ # 实体类 │ │ ├── dto/ # 数据传输对象 │ │ ├── vo/ # 视图对象 │ │ ├── enums/ # 枚举定义 │ ├── util/ # 工具类 │ ├── exception/ # 异常类 │ ├── Application.java src/main/resources ├── mapper/ # XML映射文件 │ ├── primary/ # 主数据源Mapper │ ├── secondary/ # 从数据源Mapper ├── application.yml ├── application-dev.yml ├── application-prod.yml

关键原则：

1. Mapper接口与XML文件同名且同包结构
2. 业务逻辑集中在Service层
3. 实体类保持纯净（不包含业务逻辑）

15. 代码生成技巧

使用MyBatis Generator定制：

1. 配置generatorConfig.xml：

<table tableName="user" domainObjectName="User"> <generatedKey column="id" sqlStatement="MySQL" identity="true"/> <columnOverride column="create_time" property="createTime" javaType="java.time.LocalDateTime"/> <columnOverride column="status" javaType="com.example.model.enums.User$Status" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/> </table>

2. 自定义注释生成器：

public class CustomCommentGenerator extends DefaultCommentGenerator { @Override public void addModelClassComment(TopLevelClass topLevelClass, IntrospectedTable introspectedTable) { topLevelClass.addJavaDocLine("/**"); topLevelClass.addJavaDocLine(" * 表名: " + introspectedTable.getFullyQualifiedTable()); topLevelClass.addJavaDocLine(" */"); } }

3. 运行生成命令：

mvn mybatis-generator:generate -Dconfigfile=src/main/resources/generatorConfig.xml

16. 复杂查询优化案例

16.1 一对多关联查询

使用嵌套结果映射实现：

<resultMap id="userWithOrdersMap" type="User"> <id property="id" column="user_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="selectUserWithOrders" resultMap="userWithOrdersMap"> SELECT u.id as user_id, u.username, o.id as order_id, o.order_no, o.amount FROM user u LEFT JOIN orders o ON u.id = o.user_id WHERE u.id = #{userId} </select>

16.2 动态条件查询

使用<script>标签实现复杂逻辑：

<select id="searchUsers" resultType="User"> <![CDATA[ SELECT * FROM user ]]> <script> <where> <if test="username != null and username != ''"> AND username LIKE CONCAT('%', #{username}, '%') </if> <if test="statusList != null and statusList.size() > 0"> AND status IN <foreach collection="statusList" item="status" open="(" separator="," close=")"> #{status.code} </foreach> </if> <if test="startTime != null"> AND create_time >= #{startTime} </if> <if test="endTime != null"> AND create_time <= #{endTime} </if> </where> ORDER BY <choose> <when test="orderBy == 'createTime'">create_time</when> <otherwise>id</otherwise> </choose> <if test="ascending != null and ascending"> ASC </if> <if test="ascending != null and !ascending"> DESC </if> </script> </select>

17. 存储过程调用

17.1 基本调用方式

Mapper接口定义：

@Options(statementType = StatementType.CALLABLE) @Select("{call sp_get_user_statistics(#{userId, mode=IN}, #{orderCount, mode=OUT, jdbcType=INTEGER})}") void callUserStatsProcedure(@Param("userId") Long userId, @Param("orderCount") Integer orderCount);

17.2 结果集处理

处理存储过程返回的多个结果集：

<select id="callComplexProcedure" statementType="CALLABLE"> {call sp_complex_query( #{param1, mode=IN}, #{param2, mode=INOUT, jdbcType=VARCHAR}, #{result1, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=userMap}, #{result2, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=orderMap} )} </select>

对应的Java方法签名：

void callComplexProcedure( @Param("param1") String param1, @Param("param2") String[] param2, @Param("result1") List<User> users, @Param("result2") List<Order> orders);

18. 批量操作优化

18.1 批量插入方案

使用MyBatis的foreach标签：

<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id"> INSERT INTO user (username, password) VALUES <foreach collection="list" item="item" separator=","> (#{item.username}, #{item.encryptedPassword}) </foreach> </insert>

注意：MySQL的批量插入语句有长度限制（max_allowed_packet）

18.2 批量更新技巧

使用case-when实现单条SQL批量更新：

<update id="batchUpdateStatus"> UPDATE user SET status = CASE id <foreach collection="list" item="item"> WHEN #{item.id} THEN #{item.status.code} </foreach> END WHERE id IN <foreach collection="list" item="item" open="(" separator="," close=")"> #{item.id} </foreach> </update>

19. 类型处理器进阶

19.1 JSON字段处理

自定义Jackson类型处理器：

public class JsonTypeHandler<T> extends BaseTypeHandler<T> { private final Class<T> type; private final ObjectMapper objectMapper = new ObjectMapper(); public JsonTypeHandler(Class<T> type) { this.type = type; } @Override public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) { try { ps.setString(i, objectMapper.writeValueAsString(parameter)); } catch (JsonProcessingException e) { throw new RuntimeException(e); } } @Override public T getNullableResult(ResultSet rs, String columnName) { return parse(rs.getString(columnName)); } private T parse(String json) { try { return json != null ? objectMapper.readValue(json, type) : null; } catch (IOException e) { throw new RuntimeException(e); } } }

注册处理器：

@MappedTypes(Address.class) @MappedJdbcTypes(JdbcType.VARCHAR) public class AddressTypeHandler extends JsonTypeHandler<Address> { public AddressTypeHandler() { super(Address.class); } }

19.2 自定义类型转换

实现TypeHandler处理特殊格式：

public class BitSetTypeHandler extends BaseTypeHandler<BitSet> { @Override public void setNonNullParameter(PreparedStatement ps, int i, BitSet parameter, JdbcType jdbcType) { ps.setString(i, bitSetToString(parameter)); } @Override public BitSet getNullableResult(ResultSet rs, String columnName) { return stringToBitSet(rs.getString(columnName)); } private String bitSetToString(BitSet bitSet) { return Long.toString(bitSet.toLongArray()[0], 36); } private BitSet stringToBitSet(String s) { return BitSet.valueOf(new long[]{Long.parseLong(s, 36)}); } }

20. 插件开发实战

20.1 SQL执行时间统计插件

@Intercepts({ @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}), @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}), @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class}) }) public class SqlCostTimeInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler handler = (StatementHandler) invocation.getTarget(); long start = System.currentTimeMillis(); try { return invocation.proceed(); } finally { long cost = System.currentTimeMillis() - start; BoundSql boundSql = handler.getBoundSql(); String sql = boundSql.getSql(); if (cost > 100) { Logger.warn("Slow SQL [{}ms]: {}", cost, StringUtils.abbreviate(sql.replaceAll("\\s+", " "), 100)); } } } }

20.2 分页插件原理

自定义分页拦截器核心逻辑：

@Override public Object intercept(Invocation invocation) throws Throwable { if (autoDialect) { initDialect(); } MappedStatement ms = (MappedStatement) invocation.getArgs()[0]; Object parameter = invocation.getArgs()[1]; RowBounds rowBounds = (RowBounds) invocation.getArgs()[2]; if (!dialect.skip(ms, parameter, rowBounds)) { ResultHandler resultHandler = (ResultHandler) invocation.getArgs()[3]; BoundSql boundSql = ms.getBoundSql(parameter); String pageSql = dialect.getPageSql(boundSql.getSql(), rowBounds, parameter); invocation.getArgs()[2] = RowBounds.DEFAULT; BoundSql pageBoundSql = new BoundSql( ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); // 处理参数映射... return executor.query(ms, wrapCount(parameter), RowBounds.DEFAULT, resultHandler, pageKey, pageBoundSql); } return invocation.proceed(); }

21. 多租户实现方案

21.1 基于拦截器的方案

@Intercepts({ @Signature(type= StatementHandler.class, method="prepare", args={Connection.class, Integer.class}) }) public class TenantInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler handler = (StatementHandler) invocation.getTarget(); MetaObject metaObject = SystemMetaObject.forObject(handler); MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement"); if (mappedStatement.getId().matches(".*(insert|update|delete|select).*")) { BoundSql boundSql = handler.getBoundSql(); String sql = boundSql.getSql(); String tenantId = TenantContext.getCurrentTenant(); if (StringUtils.isNotBlank(tenantId) && !sql.contains(TenantContext.IGNORE_TENANT_MARKER)) { String modifiedSql = TenantSqlParser.process(sql, tenantId); metaObject.setValue("delegate.boundSql.sql", modifiedSql); } } return invocation.proceed(); } }

21.2 动态表名处理器

public class DynamicTableNameHandler implements SqlParser { @Override public String process(String sql, String tenantId) { if (StringUtils.isBlank(tenantId)) { return sql; } Pattern pattern = Pattern.compile("(from|into|update)\\s+(\\w+)", Pattern.CASE_INSENSITIVE); Matcher matcher = pattern.matcher(sql); StringBuffer sb = new StringBuffer(); while (matcher.find()) { String tableName = matcher.group(2); if (!tableName.startsWith("sys_")) { // 系统表不处理 matcher.appendReplacement(sb, matcher.group(1) + " " + tableName + "_" + tenantId); } } matcher.appendTail(sb); return sb.toString(); } }

22. 分布式ID生成集成

22.1 雪花算法集成

自定义TypeHandler处理分布式ID：

public class SnowflakeIdTypeHandler extends BaseTypeHandler<Long> { private final Snowflake snowflake = new Snowflake(1, 1); @Override public void setNonNullParameter(PreparedStatement ps, int i, Long parameter, JdbcType jdbcType) { if (parameter == null || parameter == 0L) { parameter = snowflake.nextId(); } ps.setLong(i, parameter); } @Override public Long getNullableResult(ResultSet rs, String columnName) { return rs.getLong(columnName); } }

在实体类中使用：

public class Order { @Id @Column(name = "id") @TypeHandler(SnowflakeIdTypeHandler.class) private Long id; }

22.2 数据库序列方案

Oracle序列适配器：

<insert id="insertUser" parameterType="User"> <selectKey keyProperty="id" resultType="long" order="BEFORE"> SELECT seq_user.NEXTVAL FROM dual </selectKey> INSERT INTO user(id, username) VALUES(#{id}, #{username}) </insert>

23. 读写分离实现

23.1 基于AbstractRoutingDataSource

1. 定义数据源路由器：

public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "read" :