SpringBoot多数据源配置和使用

程序员文章站 2022-07-14 09:18:23

...

SpringBoot中使用JPA如果是单数据源，很好处理，只需要在配置文件中配置连接参数和性能参数即可。如下：

username、password、url、driver-class-name等配置省略。。。
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialSize=10
spring.datasource.minIdle=20
spring.datasource.maxActive=300
spring.datasource.maxWait=30000
spring.datasource.timeBetweenEvictionRunsMillis=120000
spring.datasource.minEvictableIdleTimeMillis=120000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

但是多数据源就没这么简单了，需要我们自己配置数据源、EntityManager、TransactionManager等，其中一般地应该有个主数据源。

首先是配置数据源，配置文件中配置以spring.datasource.primary和spring.datasource.AAAAdb。。。开头的连接配置，如果每一个数据源的性能参数不一样，也以这些开头设置即可，createDruidDataSource就不需要设置了。

@Configuration
public class DataSourceConfig {

    private static final Logger logger = LoggerFactory.getLogger(DataSourceConfig.class);

    @Bean(name = Constant.PRIMARY_DATASOURCE)
    @Qualifier(Constant.PRIMARY_DATASOURCE)
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        DataSource dataSource = createMySqlDataSource();
        logger.info("primary datasource = " + dataSource);
        return dataSource;
    }

    
    @Bean(name = Constant.AAAA_DB_DATASOURCE)
    @Qualifier(Constant.AAAA_DB_DATASOURCE)
    @ConfigurationProperties(prefix = "spring.datasource.AAAAdb")
    public DataSource AAAAdbDataSource() {
        DataSource dataSource = createOracleDataSource();
        logger.info("AAAA datasource = " + dataSource);
        return dataSource;
    }

    @Bean(name = Constant.BBBB_DB_DATASOURCE)
    @Qualifier(Constant.BBBB_DB_DATASOURCE)
    @ConfigurationProperties(prefix = "spring.datasource.BBBBdb")
    public DataSource BBBBdbDataSource() {
        DataSource dataSource = createOracleDataSource();
        logger.info("BBBB datasource = " + dataSource);
        return dataSource;
    }

    @Bean(name = Constant.CCCC_DB_DATASOURCE)
    @Qualifier(Constant.CCCC_DB_DATASOURCE)
    @ConfigurationProperties(prefix = "spring.datasource.CCCCdb")
    public DataSource CCCCdbDataSource() {
        DataSource dataSource = createOracleDataSource();
        logger.info("CCCC datasource = " + dataSource);
        return dataSource;
    }


    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    private DataSource createMySqlDataSource() {
        DruidDataSource druidDataSource = createDruidDataSource();
        logger.info("driverClassName : " + driverClassName);
        //注意oracle不能这做
        druidDataSource.setDriverClassName(driverClassName);
        return druidDataSource;
    }

    private DataSource createOracleDataSource() {
        DruidDataSource druidDataSource = createDruidDataSource();
        return druidDataSource;
    }

    /**因为4个数据源大部分属性都一致，如果要完全通过配置文件的方式，要写很多行
    为了简化，这里采用配置文件+代码的方式设置属性*/
    private DruidDataSource createDruidDataSource() {
        logger.info("maxActive : {},initialSize : {},maxWait : {}," +
                "timeBetweenEvictionRunsMillis : {},minEvictableIdleTimeMillis : {},validationQuery : {}," +
                "testWhileIdle : {},poolPreparedStatements : {},maxPoolPreparedStatementPerConnectionSize : {}," +
                "testOnBorrow : {},testOnReturn : {}" , maxActive , initialSize , maxWait , timeBetweenEvictionRunsMillis,
                minEvictableIdleTimeMillis , validationQuery , testWhileIdle , poolPreparedStatements , maxPoolPreparedStatementPerConnectionSize ,
                testOnBorrow , testOnReturn);

        DataSourceBuilder builder = DataSourceBuilder.create().type(DruidDataSource.class);
        DruidDataSource druidDataSource = (DruidDataSource) builder.build();
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setValidationQuery(validationQuery);
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setTestOnReturn(testOnReturn);
        return druidDataSource;
    }


}

主数据源配置

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.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.Database;
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 = PrimaryConfig.ENTITY_MANAGER_FACTORY_PRIMARY,
        transactionManagerRef = PrimaryConfig.TRANSACTION_MANAGER_PRIMARY,
        basePackages = {Constant.PRIMARY_DATA_PACKAGE}) //设置Repository所在位置
public class PrimaryConfig {

    public static final String TRANSACTION_MANAGER_PRIMARY    = "transactionManagerPrimary";
    public static final String ENTITY_MANAGER_FACTORY_PRIMARY = "entityManagerFactoryPrimary";

    @Value("${primary.hibernate.dialect}")
    private String primaryDialect;

    @Autowired
    @Qualifier(Constant.PRIMARY_DATASOURCE)
    private DataSource primaryDataSource;

    @Primary
    @Bean(name = Constant.ENTITY_MANAGER_PRIMARY_DB)
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = ENTITY_MANAGER_FACTORY_PRIMARY)
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties(primaryDataSource))
                //设置实体类所在位置
                .packages(Constant.PRIMARY_DATA_PACKAGE)
                .persistenceUnit(Constant.PRIMARY_PERSISTENCE_UNIT)
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        jpaProperties.setDatabase(Database.MYSQL);
        Map<String,String> map = new HashMap<>();
        map.put("hibernate.dialect", primaryDialect);
        System.out.println("primary " + primaryDialect);
        jpaProperties.setProperties(map);
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Primary
    @Bean(name = TRANSACTION_MANAGER_PRIMARY)
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }

}

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.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.Database;
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 = AAAAdbConfig.ENTITY_MANAGER_FACTORY_AAAA_DB,
        transactionManagerRef = AAAAdbConfig.TRANSACTION_MANAGER_AAAA_DB,
        basePackages = {Constant.AAAADB_DATA_PACKAGE}) //设置Repository所在位置
public class AAAAdbConfig {

    @Value("${boss.hibernate.dialect}")
    private String bossDialect;

    public static final String TRANSACTION_MANAGER_AAAA_DB    = "transactionManagerAAAAdb";
    public static final String ENTITY_MANAGER_FACTORY_AAAA_DB = "entityManagerFactoryAAAAdb";

    @Autowired
    @Qualifier(Constant.AAAA_DB_DATASOURCE)
    private DataSource AAAAdbDataSource;

    @Bean(name = Constant.ENTITY_MANAGER_AAAA_DB)
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactory(builder).getObject().createEntityManager();
    }

    @Bean(name = ENTITY_MANAGER_FACTORY_AAAA_DB)
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(AAAAdbDataSource)
                .properties(getVendorProperties(AAAAdbDataSource))
                //设置实体类所在位置
                .packages(Constant.AAAADB_DATA_PACKAGE)
                .persistenceUnit("AAAAdbPersistenceUnit")
                .build();
    }

    @Autowired
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties(DataSource dataSource) {
        jpaProperties.setDatabase(Database.ORACLE);
        Map<String,String> map = new HashMap<>();
        map.put("hibernate.dialect", bossDialect);
        System.out.println("AAAA " +  bossDialect);
        jpaProperties.setProperties(map);
        return jpaProperties.getHibernateProperties(dataSource);
    }

    @Bean(name = TRANSACTION_MANAGER_AAAA_DB)
    PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactory(builder).getObject());
    }
}

其他几个类似配置即可。

参数名字很多地方一样，所以设置为常量

public class Constant {

    public static final String PRIMARY_DATASOURCE        = "primaryDataSource";
    public static final String ENTITY_MANAGER_PRIMARY_DB = "entityManagerPrimary";
    public static final String PRIMARY_PAGINATION        = "pagination";

    public static final String AAAA_DB_DATASOURCE = "AAAAdbDataSource";
    public static final String BBBB_DB_DATASOURCE = "BBBBdbDataSource";
    public static final String CCCC_DB_DATASOURCE = "CCCCdbDataSource";

    public static final String ENTITY_MANAGER_AAAA_DB = "entityManagerAAAAdb";
    public static final String ENTITY_MANAGER_BBBB_DB = "entityManagerBBBBdb";
    public static final String ENTITY_MANAGER_CCCC_DB = "entityManagerCCCCdb";


    public static final String AAAA_PAGINATION  = "AAAAPagination";
    public static final String BBBB_PAGINATION  = "BBBBPagination";
    public static final String CCCC_PAGINATION  = "CCCCPagination";


    ///在使用Pagination的时候需要注入EntityManager，就要使用@PersistenceContext(unitName=PRIMARY_PERSISTENCE_UNIT)
    ///其他数据源没得问题，只是对主数据源有问题，具体原因未知
    /**
         @PersistenceContext(unitName = Constant.PRIMARY_PERSISTENCE_UNIT)
         @Autowired
         @Qualifier(Constant.ENTITY_MANAGER_PRIMARY_DB)
         private EntityManager entityManager;
     */
    public static final String PRIMARY_PERSISTENCE_UNIT = "primaryPersistenceUnit";
}

每个数据源的包必须不同（包括Entity和Repository）。如此，就可以正常使用各种Repository了。

但是由于业务的复杂性，我们可能需要动态生成SQL语句，并查询，参照上篇博文的Pagination，我们需要给Pagination注入一个EntityManager，对于一般的数据源，直接注入相应名字的即可，但是对于主数据源则需要特殊处理，（其实我感觉都应该像后者一样处理，没仔细测试）。

import cn.palmte.gpas.utils.Pagination;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

/**
 * @author xiongshiyan at 2018/7/23 , contact me with email [email protected] or phone 15208384257
 */
@Configuration
public class PaginationConfig {

    @PersistenceContext(unitName = Constant.PRIMARY_PERSISTENCE_UNIT)
    @Autowired
    @Qualifier(Constant.ENTITY_MANAGER_PRIMARY_DB)
    private EntityManager entityManagerPrimary;

    ///错误的注入方式，可能发生数据库8小时问题
    /*@Primary
    @Bean(Constant.PRIMARY_PAGINATION)
    public Pagination primary(@Qualifier(Constant.ENTITY_MANAGER_PRIMARY_DB) EntityManager entityManager){
        return new Pagination(entityManager);
    }*/
    @Primary
    @Bean(Constant.PRIMARY_PAGINATION)
    public Pagination primary(){
        return new Pagination(entityManagerPrimary);
    }
    @Bean(Constant.AAAA_PAGINATION)
    public Pagination AAAA(@Qualifier(Constant.ENTITY_MANAGER_AAAA_DB) EntityManager entityManager){
        return new Pagination(entityManager);
    }
    @Bean(Constant.BBBB_PAGINATION)
    public Pagination BBBB(@Qualifier(Constant.ENTITY_MANAGER_BBBB_DB) EntityManager entityManager){
        return new Pagination(entityManager);
    }
    @Bean(Constant.CCCC_PAGINATION)
    public Pagination CCCC(@Qualifier(Constant.ENTITY_MANAGER_CCCC_DB) EntityManager entityManager){
        return new Pagination(entityManager);
    }
}

最重要的是使用@PersistenceContext注解EntityManager，并制定相应的unitName。

这样，我们就可以注入相应的Pagination使用了。

参考：

一个JPA环境下原生SQL查询的工具类

SpringBoot多数据源配置和使用

springboot多模块多环境配置文件问题(动态配置生产和开发环境)

详解log4j.properties的简单配置和使用

走进SpringBoot之配置文件与多环境详解

Mybatis分页插件PageHelper的配置和简单使用方法(推荐)

详解Vue CLI3配置之filenameHashing使用和源码设计使用和源码设计

SpringBoot多配置切换的配置方法

springboot 多环境配置教程

SpringBoot使用Druid数据源的配置方法

CorePlex的安装、配置和使用图文教程

Linux ifconfig命令详解和使用示例（Linux网络参数配置和查看）