当前位置：首页 > 文章列表 > 数据库 > MySQL > Spring Data JPA 多数据源+异构数据库实践

Spring Data JPA 多数据源+异构数据库实践

来源：SegmentFault 2023-01-22 08:11:13 0浏览收藏

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《Spring Data JPA 多数据源+异构数据库实践》，主要介绍了MySQL、spring、springboot、spring-data-jpa、sql-server，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

有个项目需要作SQL Server到MySQL的数据迁移，而且两边的schema也有不少差异，这时候用工具去迁移就很难了，需要写程序去控制每个字段的变换。于是采用Spring Data JPA来做这件事，自动搞定各种DAO，而且底层的Hibernate也轻松支持SQL Server和MySQL的

做这个事情的第一步就是配置两个数据源，一个连MySQL，一个连SQL Server。很多需要支持读写分离多数据源的和这个类似，只会更简单，因为只用支持MySQL就可以。下面看代码：

配置文件

application.yml的内容是：

spring:
  datasource:
    mssql:
      type: org.apache.tomcat.jdbc.pool.DataSource
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://localhost:1433;database=old;
      username: sa
      password: password
    mysql:
      type: org.apache.tomcat.jdbc.pool.DataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/new?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
      username: root
      password: password
  jpa:
    show-sql: false

可以看到，很清楚的两个数据源配置，下面来看怎样使用这两个配置

DataSource配置类

import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
public class DataSourceConfiguration {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.mssql")
    public DataSource mssqlDataSource() {
        return DataSourceBuilder.create().build();
    }

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

    @Bean(name = "mssqlEntityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean mssqlEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean em = builder
                .dataSource(mssqlDataSource())
                .packages(OldBuy.class)
                .persistenceUnit("mssql")
                .build();
        Properties properties = new Properties();
        properties.setProperty("hibernate.physical_naming_strategy", "org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl");
        em.setJpaProperties(properties);
        return em;
    }

    @Bean(name = "mysqlEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean em = builder
                .dataSource(mysqlDataSource())
                .packages(Buy.class)
                .persistenceUnit("mysql")
                .build();
        Properties properties = new Properties();
        properties.setProperty("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy");
        em.setJpaProperties(properties);
        return em;
    }

    @Bean(name = "mssqlTransactionManager")
    @Primary
    PlatformTransactionManager mssqlTransactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(mssqlEntityManagerFactory(builder).getObject());
    }

    @Bean(name = "mysqlTransactionManager")
    @Primary
    PlatformTransactionManager mysqlTransactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(mysqlEntityManagerFactory(builder).getObject());
    }
}

这里主要分三块：

最上面的两个
```
DataSource
```
类，根据配置文件产生数据源，注意必须一个是Primary的，不然Spring会报错，因为找到了bean的两个实现，默认不知道选哪个
中间的两个
```
LocalContainerEntityManagerFactoryBean
```
则是根据两个数据源产生对应的
```
EntityManager
```
，这里就可以做些hibernate的特定配置了，比如取名策略、sql方言之类的
最后两个
```
PlatformTransactionManager
```
则是根据两个
```
EntityManager
```
产生对应的事务管理器，这样我们才能用事务

Repository配置

现在万事俱备，只欠东风了，也就是指定我们哪些

Repository

用上面配置好的两套

DataSource

、

EntityManager

、

Transaction Manager

：

SQL Server配置

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackageClasses = MsSQLXXXRepository.class,
        entityManagerFactoryRef = "mssqlEntityManagerFactory",
        transactionManagerRef = "mssqlTransactionManager"
)
public class MssqlConfiguration {
}

MySQL配置

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackageClasses = MySQLYYYRepository.class,
        entityManagerFactoryRef = "mysqlEntityManagerFactory",
        transactionManagerRef = "mysqlTransactionManager"
)
public class MysqlConfiguration {
}

上面就是两种