避坑版：springboot+JPA如何配多种数据源（postgresql+mysql）

注意： JPA和mybatis是不同的持久层，各自特点独特，

与mybatis相比，JPA需要考虑对数据源的映射和操作，

通常我们设置在项目启动时，JPA就会根据设置去创建或者更新表，

因此不能像mybayis那样等到调用时再去告诉方法用哪个数据源，

JPA需要一开始就设定好repository和entity所对应的数据源，

在下列文件repository里你可以看到相关设置；

坑，请注意，网上有很多的JPA配多种数据源，但是他们都缺失一点，连方言都没有设置，有的压根不知道这回事，要知道，JPA对不同的数据库以及数据库不同版本有不同的数据方言，

此处一定要手动去设置各自不同的方言，在我的数据源设置那你可以看到手动设置方言，如果你不设置，那除非你把ddl_auto设置为none,可那样的话，项目才开始搭建，你就把JPA的特性干掉了一部分，多尴尬

application.properties数据源配置文件

#pg\u5E93
spring.pg.datasource.show-sql=true
spring.jpa.properties.hibernate.pg-dialect=org.hibernate.dialect.PostgreSQLDialect
spring.pg.datasource.hibernate.ddl-auto=update
spring.pg.datasource.platform=postgres
spring.pg.datasource.jdbc-url=jdbc:postgresql://localhost:7017/ddb
spring.pg.datasource.username=ddb_user
spring.pg.datasource.password=FSCn2qS8
spring.pg.datasource.driver-class-name=org.postgresql.Driver
#mysql
spring.mysql.datasource.show-sql=true
spring.jpa.properties.hibernate.mysql-dialect=org.hibernate.dialect.MySQL8Dialect
spring.mysql.datasource.hibernate.ddl-auto=update
spring.mysql.datasource.jdbc-url=jdbc:mysql://localhost:3306/testData?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true
spring.mysql.datasource.username=root
spring.mysql.datasource.password=666666
spring.mysql.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

DataSourceConfig.java 数据源实例

package com.hikvision.aries.jc.ihswfld.web.modules.dbconfig;

/**
 * @Author: 
 * @Description:
 * @Date: 2021/8/28 13:58
 * @Modified By：
 * @since v1.0.1
 */
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;


/**
 * 数据源的配置
 */
@Configuration
public class DataSourceConfig {

    @Bean(name = "pgDataSource")
    @Qualifier("pgDataSource")
    @ConfigurationProperties(prefix = "spring.pg.datasource")
    @Primary
    public DataSource pgDataSource() {
        return DataSourceBuilder.create().build();
    }

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

}

PgConfig.java   pg数据源的配置

package com.hikvision.aries.jc.ihswfld.web.modules.dbconfig;

/**
 * @Author: 
 * @Description:
 * @Date: 2021/8/28 13:58
 * @Modified By：
 * @since v1.0.1
 */
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
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.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * 数据源一
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPg",
        transactionManagerRef = "transactionManagerPg",
        basePackages = {
                "xx.xx.xx.repository"
        }) //设置Repository所在位置

public class PgConfig {


    @Autowired
    @Qualifier("pgDataSource")
    private DataSource pgDataSource;

    //方言
    @Value("${spring.jpa.properties.hibernate.pg-dialect}")
    private String pgDialect;
    //表操作
    @Value("${spring.pg.datasource.hibernate.ddl-auto}")
    private String pg_ddl_auto;

    @Value("${spring.pg.datasource.show-sql}")
    private String pg_show_sql;

    @Primary
    @Bean(name = "entityManagerPg")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPg(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPg")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPg(EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(pgDataSource);
        em.setPackagesToScan(new String[] { "com.hikvision.aries.jc.ihswfld.web.modules.repository.entity" });
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto",pg_ddl_auto);
        properties.put("hibernate.dialect",pgDialect);
        properties.put("hibernate.show_sql",pg_show_sql);

        em.setJpaPropertyMap(properties);
        return em;
    }


    @Primary
    @Bean(name = "transactionManagerPg")
    public PlatformTransactionManager transactionManagerPg(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPg(builder).getObject());
    }
}

MysqlConfig.java 数据源的配置

package com.hikvision.aries.jc.ihswfld.web.modules.dbconfig;

/**
 * @Author: 
 * @Description:
 * @Date: 2021/8/28 13:59
 * @Modified By：
 * @since v1.0.1
 */
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * 数据源二
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryMysql",
        transactionManagerRef = "transactionManagerMysql",
        basePackages = {
                "xx.xx.xx.mysqlRepository"
        }) //设置Repository所在位置,两个数据库对应的repository和实体类需要不同的路径
public class MysqlConfig {

    @Autowired
    @Qualifier("mysqlDataSource")
    private DataSource mysqlDataSource;


    //方言
    @Value("${spring.jpa.properties.hibernate.mysql-dialect}")
    private String mysqlDialect;
    //表操作
    @Value("${spring.mysql.datasource.hibernate.ddl-auto}")
    private String mysq_ddl_auto;

    @Value("${spring.mysql.datasource.show-sql}")
    private String mysql_show_sql;

    @Bean(name = "entityManagerMysql")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryMysql(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactoryMysql")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryMysql(EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(mysqlDataSource);
        em.setPackagesToScan(new String[] { "com.hikvision.aries.jc.ihswfld.web.modules.mysqlRepository.entity" });
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto",mysq_ddl_auto);
        properties.put("hibernate.dialect",mysqlDialect);
        properties.put("hibernate.show_sql",mysql_show_sql);

        em.setJpaPropertyMap(properties);
        return em;
    }

    //用来作为数据库事务回滚的限定词
    //@Transactional(rollbackFor = OAPMException.class, value = "transactionManagerMysql")
    //事务管理器
    @Bean(name = "transactionManagerMysql")
    PlatformTransactionManager transactionManagerMysql(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryMysql(builder).getObject());
    }
}