Spring Boot集成MyBatis与MySQL

Spring Boot集成MyBatis与MySQL开发全攻略

一、前言：现代Java持久层开发的选择

在微服务架构盛行的今天，Spring Boot以其"约定优于配置"的理念成为Java开发的事实标准。结合MyBatis这一灵活高效的ORM框架和MySQL这一成熟稳定的关系型数据库，构成了企业级应用开发的黄金三角组合。本文将深入探讨三者的整合之道，通过实战案例揭示其核心技术要点。

二、核心技术解析

2.1 Spring Boot的自动配置魔法

• 启动原理：通过@SpringBootApplication注解触发自动配置
• 依赖管理：starter机制简化依赖版本管理
• 嵌入式容器：默认集成Tomcat/Jetty等Web容器

2.2 MyBatis的核心竞争力

• SQL可见性：完整掌控SQL语句编写
• 动态SQL：支持if/choose/when等条件语句
• 插件扩展：支持分页、性能分析等插件

// 动态SQL示例
public interface UserMapper {@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByCriteria")List<User> getByCriteria(UserCriteria criteria);class UserSqlBuilder {public String buildGetUsersByCriteria(UserCriteria criteria) {return new SQL(){{SELECT("*");FROM("users");if (criteria.getUsername() != null) {WHERE("username = #{username}");}if (criteria.getEmail() != null) {WHERE("email LIKE CONCAT(#{email}, '%')");}ORDER_BY("create_time DESC");}}.toString();}}
}

2.3 MySQL的工程实践优势

• 事务支持：完整ACID特性实现
• 存储引擎：InnoDB与MyISAM的选型策略
• 性能优化：索引策略与查询优化器

三、系统集成架构

3.1 分层架构设计

┌─────────────┐
│   Client    │
└──────┬──────┘│
┌──────▼──────┐
│ Controller  │
└──────┬──────┘│
┌──────▼──────┐
│   Service   │
└──────┬──────┘│
┌──────▼──────┐
│   Mapper    │
└──────┬──────┘│
┌──────▼──────┐
│   MySQL     │
└─────────────┘

3.2 数据流向示意图

四、详细实现步骤

4.1 项目初始化（IntelliJ IDEA版）

1. 创建Spring Boot项目（2.7.x+）
2. 选择依赖：Web、MyBatis、MySQL Driver
3. 配置pom.xml验证依赖项

<!-- 关键依赖示例 -->
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency>
<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><scope>runtime</scope>
</dependency>

4.2 数据库配置进阶

spring:datasource:url: jdbc:mysql://localhost:3306/app_db?characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=trueusername: app_userpassword: s3cr3tP@sshikari:connection-timeout: 3000maximum-pool-size: 20mybatis:configuration:default-fetch-size: 100default-statement-timeout: 30map-underscore-to-camel-case: truetype-aliases-package: com.example.domain

4.3 领域模型设计

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {private Long id;private String username;private String encryptedPassword;private String email;private UserStatus status;private LocalDateTime createTime;private LocalDateTime updateTime;
}public enum UserStatus {ACTIVE, INACTIVE, LOCKED
}

4.4 数据访问层实现

注解方式

@Mapper
public interface UserMapper {@Select("SELECT * FROM users WHERE id = #{id}")@Results({@Result(property = "createTime", column = "create_time"),@Result(property = "updateTime", column = "update_time")})User selectById(Long id);
}

XML映射方式

<!-- resources/mapper/UserMapper.xml -->
<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="encrypted_password"/><result property="email" column="email"/><result property="status" column="status" typeHandler="org.apache.ibatis.type.EnumTypeHandler"/><result property="createTime" column="create_time"/><result property="updateTime" column="update_time"/></resultMap><select id="selectAll" resultMap="userResultMap">SELECT * FROM users</select>
</mapper>

4.5 事务管理实践

@Service
@RequiredArgsConstructor
public class UserRegistrationService {private final UserMapper userMapper;private final AuditLogMapper auditLogMapper;@Transactional(rollbackFor = Exception.class)public void registerUser(User user) {userMapper.insert(user);auditLogMapper.logEvent("USER_REGISTER", user.getId());// 其他业务操作...}
}

五、高级特性应用

5.1 MyBatis插件开发

实现SQL执行时间监控：

@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class SqlCostInterceptor implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {long start = System.currentTimeMillis();try {return invocation.proceed();} finally {long cost = System.currentTimeMillis() - start;System.out.println("SQL执行耗时：" + cost + "ms");}}
}

5.2 多数据源配置

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

六、性能优化策略

6.1 查询优化方案

优化手段	实施方法	预期效果
二级缓存	配置标签	减少数据库访问
批量插入	使用foreach标签	提升写入性能
延迟加载	配置lazyLoadingEnabled=true	减少内存消耗
分页插件	集成PageHelper	简化分页操作

6.2 索引优化示例

-- 创建复合索引
CREATE INDEX idx_users_username_email ON users(username, email);-- 执行计划分析
EXPLAIN SELECT * FROM users WHERE username = 'john' AND email LIKE '%@example.com';

七、测试验证方案

7.1 单元测试示例

@SpringBootTest
class UserMapperTest {@Autowiredprivate UserMapper userMapper;@Test@Sql(scripts = "/test-data.sql")void testSelectById() {User user = userMapper.selectById(1L);assertThat(user.getUsername()).isEqualTo("testuser");}
}

7.2 API测试脚本

# 创建测试用户
curl -X POST -H "Content-Type: application/json" \
-d '{"username":"devuser","email":"dev@example.com"}' \
http://localhost:8080/api/users# 查询用户信息
curl -X GET http://localhost:8080/api/users/1

八、生产环境建议

8.1 安全配置

spring:datasource:password: ${DB_PASSWORD} # 从环境变量读取hikari:connection-init-sql: SELECT 1 FROM DUAL # 连接验证

8.2 监控配置

@Bean
public MeterRegistryCustomizer<MeterRegistry> metricsCommonTags() {return registry -> registry.config().commonTags("application", "user-service","datasource", "mysql");
}

附完整项目结构：

src/main/java
├── com.example
│   ├── config       # 配置类
│   ├── controller   # Web层
│   ├── service      # 业务层
│   ├── mapper       # 数据访问层
│   └── domain       # 领域模型
resources/
├── application.yml  # 主配置文件
└── mapper/          # XML映射文件
生产级应用的优化策略未来可探索方向：
- 结合Redis实现缓存层
- 集成MyBatis-Plus增强功能
- 实现多租户数据隔离
- 探索分布式事务解决方案附完整项目结构：

src/main/java
├── com.example
│ ├── config # 配置类
│ ├── controller # Web层
│ ├── service # 业务层
│ ├── mapper # 数据访问层
│ └── domain # 领域模型
resources/
├── application.yml # 主配置文件
└── mapper/ # XML映射文件