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

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，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());
    }
}

这里主要分三块：

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

到这里，我们也就讲完了《Spring Data JPA 多数据源+异构数据库实践》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！