站点图标 IDC铺

关于SpringBoot Jpa 双数据源mysql + oracle + liquibase+参考源码

一、yml文件配置

spring:
  # 数据库配置
  datasource:
    primary:
      jdbc-url: jdbc:mysql://localhost:3306/mes-dev?useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: root
      driver-class-name: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
      liquibase:
        change-log: classpath:/db/changelog/master.xml
        enabled: true

    secondary:
      username: system
      password: mesabc123
      jdbc-url: jdbc:oracle:thin:@localhost:1521:orcl
      driver-class-name: oracle.jdbc.OracleDriver
      type: com.alibaba.druid.pool.DruidDataSource
      liquibase:
        enabled: false

    # 下面为连接池的补充设置,应用到上面所有数据源中
    # 参考网站:https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
    druid:
      # 初始化连接池的连接数量 大小,最小,最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间,单位是毫秒
      min-evictable-idle-time-millis: 30000
      validation-query: SELECT 1 FROM DUAL
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 是否缓存preparedStatement,也就是PSCache  官方建议MySQL下建议关闭   个人建议如果想用SQL防火墙 建议打开
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
      connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 合并多个DruidDataSource的监控数据
      #use-global-data-source-stat: true
      # !!!请勿配置timeBetweenLogStatsMillis 会定时输出日志 并导致统计的sql清零
      #timeBetweenLogStatsMillis=20000

      # WebStatFilter配置,说明请参考Druid Wiki,配置_配置WebStatFilter
      web-stat-filter:
        #是否启用StatFilter默认值true
        enabled: true
        #url-pattern:
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico

      # StatViewServlet配置,说明请参考Druid Wiki,配置_StatViewServlet配置
      stat-view-servlet:
        #是否启用StatViewServlet默认值true
        enabled: true
        url-pattern: /druid/*
        reset-enable: false
        login-username: admin
        login-password: 123456

  # jpa 配置
  jpa:
    # 是否显示 sql语句
    show-sql: true
    # hibernate 配置
    hibernate:
      #数据库方言
      primary-dialect: org.hibernate.dialect.MySQL5Dialect
      secondary-dialect: org.hibernate.dialect.Oracle10gDialect
      # 表更新策略
      ddl-auto: none
      naming:
        # 数据库表字段和实体字段名称对应实现类
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    open-in-view: false

二、DataSourceConfig 配置类

import liquibase.integration.spring.SpringLiquibase;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.liquibase.LiquibaseProperties;
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 = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.primary.liquibase")
    public LiquibaseProperties primaryLiquibaseProperties() {
        return new LiquibaseProperties();
    }

    @Bean(name = "liquibase")
    public SpringLiquibase primaryLiquibase() {
        return createSpringLiquibase(primaryDataSource(), primaryLiquibaseProperties());
    }

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

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.secondary.liquibase")
    public LiquibaseProperties secondaryLiquibaseProperties() {
        return new LiquibaseProperties();
    }

    @Bean
    public SpringLiquibase secondaryLiquibase() {
        return createSpringLiquibase(secondaryDataSource(), secondaryLiquibaseProperties());
    }

    private SpringLiquibase createSpringLiquibase(DataSource ds, LiquibaseProperties properties) {
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setDataSource(ds);
        liquibase.setChangeLog(properties.getChangeLog());
        liquibase.setContexts(properties.getContexts());
        liquibase.setDefaultSchema(properties.getDefaultSchema());
        liquibase.setDropFirst(properties.isDropFirst());
        liquibase.setShouldRun(properties.isEnabled());
        liquibase.setLabels(properties.getLabels());
        liquibase.setChangeLogParameters(properties.getParameters());
        liquibase.setRollbackFile(properties.getRollbackFile());
        return liquibase;
    }

}

三、PrimaryConfig 配置类

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.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.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.lh.mes.link.api.erp.primaryRepository"}) //设置Repository所在位置
public class PrimaryConfig {

    @Resource
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    /** 获取对应的数据库方言 */
    @Value("${spring.jpa.hibernate.primary-dialect}")
    private String primaryDialect;

    @Resource
    private Properties properties;

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

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean entityManagerFactory = builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties())
                .packages("com.lh.mes.link.api.identity.primaryDomain") //设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
        entityManagerFactory.setJpaProperties(properties);
        return entityManagerFactory;
    }

    private Map<String, Object> getVendorProperties() {
        Map<String, String> map = new HashMap<>(1);
        // 设置对应的数据库方言
        map.put("hibernate.dialect", primaryDialect);
        jpaProperties.setProperties(map);
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

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

}

四、SecondaryConfig 配置类

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.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.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.lh.mes.link.api.erp.secondaryRepository"}) //设置Repository所在位置
public class SecondaryConfig {

    @Resource
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Resource
    private Properties properties;

    @Autowired
    private JpaProperties jpaProperties;

    /** 获取对应的数据库方言 */
    @Value("${spring.jpa.hibernate.secondary-dialect}")
    private String primaryDialect;

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

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        LocalContainerEntityManagerFactoryBean entityManagerFactory = builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties())
                .packages("com.lh.mes.link.api.identity.secondaryDomain") //设置实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")//持久化单元创建一个默认即可,多个便要分别命名
                .build();
        entityManagerFactory.setJpaProperties(properties);
        return entityManagerFactory;
    }

    private Map<String, Object> getVendorProperties() {
        Map<String, String> map = new HashMap<>(1);
        // 设置对应的数据库方言
        map.put("hibernate.dialect", primaryDialect);
        jpaProperties.setProperties(map);
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

    @Bean(name = "transactionManagerSecondary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }

}
退出移动版