Spring Boot中如何通过JDBC同时访问多MySQL数据库？

1. 配置多数据源连接信息 #

在application.yml（或application.properties）中分别配置两个MySQL数据库的连接参数，区分主/次数据源：

spring:
  datasource:
    primary:
      url: jdbc:mysql://localhost:3306/db1?useSSL=false&serverTimezone=UTC
      username: root
      password: your_password
      driver-class-name: com.mysql.cj.jdbc.Driver
    secondary:
      url: jdbc:mysql://localhost:3306/db2?useSSL=false&serverTimezone=UTC
      username: root
      password: your_password
      driver-class-name: com.mysql.cj.jdbc.Driver

2. 创建数据源配置类 #

分别为两个数据源创建配置Bean，绑定对应配置并生成专属JdbcTemplate：

@Configuration
public class PrimaryDataSourceConfig {
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "primaryJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

@Configuration
public class SecondaryDataSourceConfig {
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryJdbcTemplate")
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

3. DAO层绑定对应JdbcTemplate #

为每个数据库的表创建DAO类，通过@Qualifier指定要使用的JdbcTemplate：

@Repository
public class Db1UserDao {
    private final JdbcTemplate jdbcTemplate;

    // 注入主数据源的JdbcTemplate
    public Db1UserDao(@Qualifier("primaryJdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public User getUserById(Long userId) {
        String sql = "SELECT id, name, is_vip FROM user WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{userId}, (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setName(rs.getString("name"));
            user.setVip(rs.getBoolean("is_vip"));
            return user;
        });
    }
}

@Repository
public class Db2OrderDao {
    private final JdbcTemplate jdbcTemplate;

    // 注入次数据源的JdbcTemplate
    public Db2OrderDao(@Qualifier("secondaryJdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Order getLatestUserOrder(Long userId) {
        String sql = "SELECT id, user_id, status FROM order WHERE user_id = ? ORDER BY create_time DESC LIMIT 1";
        return jdbcTemplate.queryForObject(sql, new Object[]{userId}, (rs, rowNum) -> {
            Order order = new Order();
            order.setId(rs.getLong("id"));
            order.setUserId(rs.getLong("user_id"));
            order.setStatus(rs.getString("status"));
            return order;
        });
    }
}

4. Service层整合多数据源数据做逻辑判断 #

在Service类中注入两个DAO，调用各自方法获取数据后执行条件判断：

@Service
public class DataCheckService {
    private final Db1UserDao userDao;
    private final Db2OrderDao orderDao;

    public DataCheckService(Db1UserDao userDao, Db2OrderDao orderDao) {
        this.userDao = userDao;
        this.orderDao = orderDao;
    }

    public boolean judgeUserCondition(Long userId) {
        // 从db1取用户信息
        User user = userDao.getUserById(userId);
        // 从db2取订单信息
        Order order = orderDao.getLatestUserOrder(userId);

        // 自定义条件判断逻辑
        return user != null && user.isVip() && order != null && "UNPAID".equals(order.getStatus());
    }
}

5. Controller层暴露API接口 #

编写Controller调用Service方法，完成同一API内的多数据库数据访问：

@RestController
@RequestMapping("/api/data")
public class DataCheckController {
    private final DataCheckService dataCheckService;

    public DataCheckController(DataCheckService dataCheckService) {
        this.dataCheckService = dataCheckService;
    }

    @GetMapping("/check/{userId}")
    public ResponseEntity<Boolean> checkUserCondition(@PathVariable Long userId) {
        boolean result = dataCheckService.judgeUserCondition(userId);
        return ResponseEntity.ok(result);
    }
}

注意事项 #

• 若需事务管理，需为每个数据源配置对应的PlatformTransactionManager Bean；
• 确保配置文件的属性前缀与@ConfigurationProperties的prefix完全匹配；
• 注入JdbcTemplate时必须用@Qualifier指定Bean名称，避免依赖注入冲突。

内容的提问来源于stack exchange，提问作者Hari Krishnan