Spring Boot + MyBatis Plus 最佳实践：从代码生成到数据库连接池优化全攻略

引言

在现代Java企业级应用开发中，Spring Boot与MyBatis Plus的组合已经成为主流技术栈。Spring Boot凭借其自动配置和快速开发特性，而MyBatis Plus则提供了强大的ORM功能和便捷的代码生成工具。本文将从实际项目出发，系统梳理Spring Boot与MyBatis Plus集成的最佳实践，涵盖从代码生成到数据库连接池优化的完整技术链路。

一、项目环境搭建与基础配置

1.1 项目依赖配置

在开始开发之前，我们需要配置好项目的基础依赖。以下是一个典型的Spring Boot + MyBatis Plus项目配置：

<dependencies>
    <!-- Spring Boot Web Starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- MyBatis Plus Starter -->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.3.1</version>
    </dependency>
    
    <!-- MySQL驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <!-- 数据库连接池 -->
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
    </dependency>
    
    <!-- Lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

1.2 核心配置文件

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: password
    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
      pool-name: MyHikariCP
      validation-timeout: 5000

mybatis-plus:
  # 配置Mapper扫描路径
  mapper-locations: classpath*:/mapper/**/*.xml
  # 配置实体类别名
  type-aliases-package: com.example.entity
  # 全局配置
  global-config:
    db-config:
      id-type: auto
      # 表名前缀
      table-prefix: t_
  # 配置日志
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

二、代码自动生成实践

2.1 MyBatis Plus代码生成器

MyBatis Plus提供了强大的代码生成器，可以大大提高开发效率。以下是完整的代码生成配置：

@Configuration
public class CodeGenerator {
    
    public static void main(String[] args) {
        // 代码生成器
        AutoGenerator mpg = new AutoGenerator();
        
        // 全局配置
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("作者名");
        gc.setOpen(false);
        gc.setSwagger2(true); // 实体属性 Swagger2 注解
        mpg.setGlobalConfig(gc);
        
        // 数据源配置
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8");
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername("root");
        dsc.setPassword("password");
        mpg.setDataSource(dsc);
        
        // 包配置
        PackageConfig pc = new PackageConfig();
        pc.setModuleName("user");
        pc.setParent("com.example");
        mpg.setPackageInfo(pc);
        
        // 策略配置
        StrategyConfig strategy = new StrategyConfig();
        strategy.setNaming(NamingStrategy.underline_to_camel);
        strategy.setColumnNaming(NamingStrategy.underline_to_camel);
        strategy.setEntityLombokModel(true);
        strategy.setRestControllerStyle(true);
        strategy.setInclude("user_info"); // 需要生成的表名
        strategy.setControllerMappingHyphenStyle(true);
        strategy.setTablePrefix(pc.getModuleName() + "_");
        mpg.setStrategy(strategy);
        
        // 执行生成
        mpg.execute();
    }
}

2.2 生成的实体类示例

通过代码生成器生成的实体类：

@TableName("t_user_info")
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="UserInfo对象", description="用户信息表")
public class UserInfo extends Model<UserInfo> {
    
    private static final long serialVersionUID = 1L;
    
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    
    @ApiModelProperty(value = "用户名")
    private String username;
    
    @ApiModelProperty(value = "邮箱")
    private String email;
    
    @ApiModelProperty(value = "创建时间")
    private Date createTime;
    
    @ApiModelProperty(value = "更新时间")
    private Date updateTime;
    
    @TableLogic
    @ApiModelProperty(value = "逻辑删除标识")
    private Integer deleted;
    
    @Override
    protected Serializable pkVal() {
        return this.id;
    }
}

三、数据库连接池优化

3.1 HikariCP连接池配置

HikariCP是目前性能最好的数据库连接池之一。以下是详细的配置优化：

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      # 连接池名称
      pool-name: MyHikariCP
      # 最小空闲连接数
      minimum-idle: 5
      # 最大连接数
      maximum-pool-size: 20
      # 连接超时时间
      connection-timeout: 30000
      # 空闲连接超时时间
      idle-timeout: 600000
      # 连接最大存活时间
      max-lifetime: 1800000
      # 验证连接是否有效的超时时间
      validation-timeout: 5000
      # 连接测试查询
      connection-test-query: SELECT 1
      # 自动提交
      auto-commit: true
      # 连接池配置
      leak-detection-threshold: 60000

3.2 连接池监控配置

为了更好地监控连接池性能，可以添加以下配置：

@Component
public class DataSourceMonitor {
    
    @Autowired
    private HikariDataSource dataSource;
    
    @Scheduled(fixedRate = 60000)
    public void monitor() {
        HikariPoolMXBean poolBean = dataSource.getHikariPoolMXBean();
        System.out.println("Active connections: " + poolBean.getActiveConnections());
        System.out.println("Idle connections: " + poolBean.getIdleConnections());
        System.out.println("Total connections: " + poolBean.getTotalConnections());
        System.out.println("Waiting connections: " + poolBean.getThreadsAwaitingConnection());
    }
}

四、MyBatis Plus核心功能详解

4.1 基础CRUD操作

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    /**
     * 根据ID查询用户
     */
    public UserInfo getUserById(Long id) {
        return this.getById(id);
    }
    
    /**
     * 查询所有用户
     */
    public List<UserInfo> getAllUsers() {
        return this.list();
    }
    
    /**
     * 插入用户
     */
    public boolean insertUser(UserInfo user) {
        return this.save(user);
    }
    
    /**
     * 更新用户
     */
    public boolean updateUser(UserInfo user) {
        return this.updateById(user);
    }
    
    /**
     * 删除用户
     */
    public boolean deleteUser(Long id) {
        return this.removeById(id);
    }
}

4.2 条件构造器使用

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    /**
     * 复杂条件查询
     */
    public List<UserInfo> searchUsers(String username, String email, Date startTime, Date endTime) {
        QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
        wrapper.like(StringUtils.isNotBlank(username), "username", username)
               .like(StringUtils.isNotBlank(email), "email", email)
               .ge(startTime != null, "create_time", startTime)
               .le(endTime != null, "create_time", endTime)
               .orderByDesc("create_time");
        
        return this.list(wrapper);
    }
    
    /**
     * 统计查询
     */
    public Long countUsers() {
        return this.count();
    }
    
    public Long countUsersByCondition(String username) {
        QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
        wrapper.like("username", username);
        return this.count(wrapper);
    }
}

4.3 分页查询优化

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    /**
     * 分页查询用户
     */
    public IPage<UserInfo> getUserPage(int current, int size, String username) {
        Page<UserInfo> page = new Page<>(current, size);
        QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
        wrapper.like(StringUtils.isNotBlank(username), "username", username)
               .orderByDesc("create_time");
        
        return this.page(page, wrapper);
    }
    
    /**
     * 复杂分页查询
     */
    public IPage<UserInfo> getUserPageWithJoin(int current, int size, String username, String email) {
        Page<UserInfo> page = new Page<>(current, size);
        QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
        wrapper.select("u.id", "u.username", "u.email", "u.create_time")
               .from("t_user_info u")
               .where("u.username like ? or u.email like ?", 
                      "%" + username + "%", "%" + email + "%")
               .orderByDesc("u.create_time");
        
        return this.page(page, wrapper);
    }
}

五、SQL优化策略

5.1 索引优化实践

-- 创建用户表索引
CREATE TABLE `t_user_info` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `email` varchar(100) NOT NULL COMMENT '邮箱',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint NOT NULL DEFAULT '0' COMMENT '逻辑删除标识',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_email` (`email`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_deleted_create_time` (`deleted`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

5.2 MyBatis Plus SQL优化

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    /**
     * 优化的查询方法 - 使用selectFields
     */
    public List<UserInfo> getUsersWithFields() {
        QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
        wrapper.select("id", "username", "email") // 只查询需要的字段
               .eq("deleted", 0)
               .orderByDesc("create_time");
        
        return this.list(wrapper);
    }
    
    /**
     * 批量操作优化
     */
    public boolean batchInsertUsers(List<UserInfo> users) {
        return this.saveBatch(users, 1000); // 分批插入，避免内存溢出
    }
    
    /**
     * 原生SQL优化
     */
    public List<UserInfo> getUsersByNativeSQL(String username) {
        String sql = "SELECT id, username, email, create_time FROM t_user_info WHERE username LIKE ? AND deleted = 0";
        return this.baseMapper.selectListBySQL(sql, username);
    }
}

5.3 缓存策略实现

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    private static final String USER_CACHE_KEY = "user_info:";
    
    /**
     * 带缓存的用户查询
     */
    public UserInfo getUserWithCache(Long id) {
        String cacheKey = USER_CACHE_KEY + id;
        UserInfo user = (UserInfo) redisTemplate.opsForValue().get(cacheKey);
        
        if (user == null) {
            user = this.getById(id);
            if (user != null) {
                redisTemplate.opsForValue().set(cacheKey, user, 30, TimeUnit.MINUTES);
            }
        }
        
        return user;
    }
    
    /**
     * 缓存更新
     */
    public boolean updateUserWithCache(UserInfo user) {
        boolean result = this.updateById(user);
        if (result) {
            String cacheKey = USER_CACHE_KEY + user.getId();
            redisTemplate.opsForValue().set(cacheKey, user, 30, TimeUnit.MINUTES);
        }
        return result;
    }
    
    /**
     * 缓存删除
     */
    public boolean deleteUserWithCache(Long id) {
        boolean result = this.removeById(id);
        if (result) {
            String cacheKey = USER_CACHE_KEY + id;
            redisTemplate.delete(cacheKey);
        }
        return result;
    }
}

六、事务管理最佳实践

6.1 事务配置

@Configuration
@EnableTransactionManagement
public class TransactionConfig {
    
    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager();
        transactionManager.setDataSource(dataSource);
        return transactionManager;
    }
}

6.2 事务使用示例

@Service
@Transactional(rollbackFor = Exception.class)
public class UserService {
    
    @Autowired
    private UserInfoMapper userInfoMapper;
    
    @Autowired
    private OrderMapper orderMapper;
    
    /**
     * 用户注册事务
     */
    public boolean registerUser(UserInfo user, Order order) {
        try {
            // 保存用户
            userInfoMapper.insert(user);
            
            // 保存订单
            orderMapper.insert(order);
            
            // 模拟业务逻辑
            if (user.getUsername().equals("error")) {
                throw new RuntimeException("模拟异常");
            }
            
            return true;
        } catch (Exception e) {
            // 事务会自动回滚
            throw new RuntimeException("注册失败", e);
        }
    }
    
    /**
     * 事务传播行为示例
     */
    @Transactional(propagation = Propagation.REQUIRED)
    public void requiredTransaction() {
        // 业务逻辑
    }
    
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void requiresNewTransaction() {
        // 新事务
    }
}

七、性能监控与调优

7.1 SQL执行监控

@Component
public class SqlMonitor {
    
    @EventListener
    public void handleSqlExecution(SqlExecutionEvent event) {
        long executionTime = event.getExecutionTime();
        String sql = event.getSql();
        
        if (executionTime > 1000) { // 超过1秒的SQL记录日志
            log.warn("Slow SQL executed: {} with time: {}ms", sql, executionTime);
        }
    }
}

7.2 数据库连接池监控

@Component
public class ConnectionPoolMonitor {
    
    @Autowired
    private HikariDataSource dataSource;
    
    @Scheduled(fixedRate = 30000)
    public void monitorConnectionPool() {
        HikariPoolMXBean poolBean = dataSource.getHikariPoolMXBean();
        
        log.info("=== Connection Pool Status ===");
        log.info("Active Connections: {}", poolBean.getActiveConnections());
        log.info("Idle Connections: {}", poolBean.getIdleConnections());
        log.info("Total Connections: {}", poolBean.getTotalConnections());
        log.info("Threads Awaiting Connection: {}", poolBean.getThreadsAwaitingConnection());
        log.info("Connection Timeout Count: {}", poolBean.getConnectionTimeoutCount());
        log.info("Leak Detection Count: {}", poolBean.getLeakDetectionCount());
    }
}

八、常见问题与解决方案

8.1 数据库连接超时问题

# 配置连接超时时间
spring:
  datasource:
    hikari:
      connection-timeout: 30000
      validation-timeout: 5000
      idle-timeout: 600000

8.2 大数据量查询优化

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    /**
     * 分批查询大数据量
     */
    public void processLargeData() {
        int pageSize = 1000;
        int currentPage = 1;
        boolean hasNext = true;
        
        while (hasNext) {
            Page<UserInfo> page = new Page<>(currentPage, pageSize);
            QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
            wrapper.select("id", "username", "email")
                   .eq("deleted", 0)
                   .orderByAsc("id");
            
            IPage<UserInfo> result = this.page(page, wrapper);
            
            // 处理当前页数据
            List<UserInfo> users = result.getRecords();
            processUsers(users);
            
            hasNext = result.getPages() > currentPage;
            currentPage++;
        }
    }
    
    private void processUsers(List<UserInfo> users) {
        // 处理用户数据
        users.forEach(user -> {
            // 业务逻辑处理
        });
    }
}

8.3 内存泄漏预防

@Service
public class UserInfoService extends ServiceImpl<UserInfoMapper, UserInfo> {
    
    /**
     * 避免内存泄漏的查询方法
     */
    public List<UserInfo> getLimitedUsers(int limit) {
        QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
        wrapper.select("id", "username", "email")
               .eq("deleted", 0)
               .orderByDesc("create_time")
               .last("LIMIT " + limit); // 使用LIMIT限制结果集大小
        
        return this.list(wrapper);
    }
}

九、项目部署与生产环境优化

9.1 生产环境配置

# 生产环境配置
spring:
  datasource:
    url: jdbc:mysql://prod-db-server:3306/test_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&useServerPrepStmts=false
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 50
      minimum-idle: 10
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000
      validation-timeout: 5000
      connection-test-query: SELECT 1
      leak-detection-threshold: 120000

9.2 性能调优建议

1. 连接池调优：根据应用并发量调整连接池大小
2. SQL优化：定期分析慢查询日志，优化索引
3. 缓存策略：合理使用Redis等缓存技术
4. 分页优化：大数据量时使用游标分页或延迟关联
5. 事务管理：避免长事务，合理设置事务传播行为

结语

通过本文的详细介绍，我们可以看到Spring Boot与MyBatis Plus的集成不仅能够提高开发效率，还能在性能和可维护性方面提供很好的保障。从代码生成到数据库连接池优化，从SQL优化到事务管理，每一个环节都值得我们深入研究和实践。

在实际项目中，我们需要根据具体的业务场景和性能要求，灵活运用这些最佳实践。同时，持续监控和优化也是保证系统稳定运行的关键。希望本文能够为您的Spring Boot + MyBatis Plus项目开发提供有价值的参考和指导。

记住，技术的最终目的是为业务服务，因此在追求技术先进性的同时，也要确保解决方案的实用性和可维护性。通过不断的实践和优化，我们能够构建出既高效又稳定的数据库访问层。