Spring Boot如何同时连接两个不同的MySQL数据库

嘿，这事儿我熟！在Spring Boot里配置多数据源其实没那么复杂，咱们一步步来，保证你能同时和两个MySQL数据库顺畅通信。

1. 先搞定配置文件 #

首先在application.properties（或者你习惯的YAML格式）里分别配置两个数据源的信息，给它们起个明确的名字（比如primary和secondary），避免混淆：

# 主数据源（Primary）
spring.datasource.primary.url=jdbc:mysql://localhost:3306/db_primary?useSSL=false&serverTimezone=UTC
spring.datasource.primary.username=root
spring.datasource.primary.password=your_password
spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver

# 次数据源（Secondary）
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/db_secondary?useSSL=false&serverTimezone=UTC
spring.datasource.secondary.username=root
spring.datasource.secondary.password=your_password
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver

# JPA通用配置
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

如果偏爱YAML的清晰结构，也可以这么写：

spring:
  datasource:
    primary:
      url: jdbc:mysql://localhost:3306/db_primary?useSSL=false&serverTimezone=UTC
      username: root
      password: your_password
      driver-class-name: com.mysql.cj.jdbc.Driver
    secondary:
      url: jdbc:mysql://localhost:3306/db_secondary?useSSL=false&serverTimezone=UTC
      username: root
      password: your_password
      driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL8Dialect

2. 配置数据源Bean和JPA核心组件 #

接下来写一个配置类，把两个数据源、EntityManagerFactory和TransactionManager都定义好。关键要给主数据源加上@Primary注解，不然Spring启动时会懵，不知道该选哪个当默认数据源。

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
// 主数据源的Repository扫描配置
@EnableJpaRepositories(
        basePackages = "com.yourpackage.primary.repository",
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager"
)
public class DataSourceConfig {

    // 主数据源Bean
    @Primary
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    // 主数据源的EntityManagerFactory
    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource dataSource) {
        Map<String, String> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        properties.put("hibernate.show_sql", "true");
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.primary.entity") // 主库实体类所在包
                .persistenceUnit("primary")
                .properties(properties)
                .build();
    }

    // 主数据源的事务管理器
    @Primary
    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory) {
        return new JpaTransactionManager(primaryEntityManagerFactory.getObject());
    }

    // ------------------- 分割线：次数据源配置 -------------------

    @Configuration
    @EnableJpaRepositories(
            basePackages = "com.yourpackage.secondary.repository",
            entityManagerFactoryRef = "secondaryEntityManagerFactory",
            transactionManagerRef = "secondaryTransactionManager"
    )
    static class SecondaryDataSourceConfig {

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

        @Bean(name = "secondaryEntityManagerFactory")
        public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
                EntityManagerFactoryBuilder builder,
                @Qualifier("secondaryDataSource") DataSource dataSource) {
            Map<String, String> properties = new HashMap<>();
            properties.put("hibernate.hbm2ddl.auto", "update");
            properties.put("hibernate.show_sql", "true");
            return builder
                    .dataSource(dataSource)
                    .packages("com.yourpackage.secondary.entity") // 次库实体类所在包
                    .persistenceUnit("secondary")
                    .properties(properties)
                    .build();
        }

        @Bean(name = "secondaryTransactionManager")
        public PlatformTransactionManager secondaryTransactionManager(
                @Qualifier("secondaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory) {
            return new JpaTransactionManager(secondaryEntityManagerFactory.getObject());
        }
    }
}

这里要注意几个细节：

• @ConfigurationProperties会自动读取配置文件里对应前缀的属性，不用手动逐个设置。
• @EnableJpaRepositories里的basePackages要指向对应数据源的Repository所在包，entityManagerFactoryRef和transactionManagerRef要和上面定义的Bean名字严格对应。
• 实体类的包也要分别指定，确保每个EntityManagerFactory只扫描自己库的实体，避免映射混乱。

3. 编写实体类和Repository #

把两个数据库的实体类分开存放，比如：

• 主库实体：com.yourpackage.primary.entity.User
• 次库实体：com.yourpackage.secondary.entity.Order

对应的Repository也要放在各自的包下：

// 主库Repository
package com.yourpackage.primary.repository;

import com.yourpackage.primary.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
}

// 次库Repository
package com.yourpackage.secondary.repository;

import com.yourpackage.secondary.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
}

4. 测试一下！ #

最后写个简单的Service或者Controller，注入两个Repository，验证能不能同时操作两个数据库：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class TestService {

    private final UserRepository userRepository;
    private final OrderRepository orderRepository;

    @Autowired
    public TestService(UserRepository userRepository, OrderRepository orderRepository) {
        this.userRepository = userRepository;
        this.orderRepository = orderRepository;
    }

    @Transactional("primaryTransactionManager")
    public void addUser(User user) {
        userRepository.save(user);
    }

    @Transactional("secondaryTransactionManager")
    public void addOrder(Order order) {
        orderRepository.save(order);
    }

    // 也可以同时操作两个库
    public void testBothDatabases() {
        User user = new User();
        user.setName("Test User");
        addUser(user);

        Order order = new Order();
        order.setOrderNumber("ORD-001");
        addOrder(order);
    }
}

这里的@Transactional注解要指定对应的事务管理器名字，不然默认会用主数据源的事务管理器。如果方法只操作一个库，最好明确指定，避免出错。

一些额外提醒 #

• 确保MySQL驱动版本和Spring Boot版本兼容，比如MySQL8对应的驱动类是com.mysql.cj.jdbc.Driver，不是旧版的com.mysql.jdbc.Driver。
• 包结构一定要清晰，别把不同库的实体和Repository混在一起，不然会出现实体映射错误。
• 如果用MyBatis而非JPA，配置思路类似，只是把EntityManagerFactory换成SqlSessionFactory即可。

这样下来，你的Spring Boot应用就能同时和两个MySQL数据库愉快通信啦！

内容的提问来源于stack exchange，提问作者Muhammad Basim