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

有个项目需要作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());
    }
}

这里主要分三块：

1. 最上面的两个DataSource类，根据配置文件产生数据源，注意必须一个是Primary的，不然Spring会报错，因为找到了bean的两个实现，默认不知道选哪个

2. 中间的两个LocalContainerEntityManagerFactoryBean则是根据两个数据源产生对应的EntityManager，这里就可以做些hibernate的特定配置了，比如取名策略、sql方言之类的

3. 最后两个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 {
}

上面就是两种Repository的配置了，很简单。basePackageClasses指向的那个类，同一个包里面的所有Repository都会用这个数据源的配置