SpringBoot Jpa多数据源(MySQL+Oracle不同数据库) 双数据源

注意：@Transactional非默认数据库时需放在Repo层否则会报错：默认数据库无此问题TransactionRequiredException: Executing an update/delete query解决方法：@Transactional从Impl移到Repoimport javax.transaction.Transactional;//删除ById(JPQL)@Transa

tanzongbiao

4384人浏览 · 2022-03-22 12:02:01

tanzongbiao · 2022-03-22 12:02:01 发布

注意：@Transactional非默认数据库时需放在Repo层否则会报错：

默认数据库无此问题

TransactionRequiredException: Executing an update/delete query

解决方法：@Transactional从Impl移到Repo

import javax.transaction.Transactional;

//删除ById(JPQL)
@Transactional//jpa多数据源时非主数据源@Transactional需放repo层才行 否则会报错
@Modifying
@Query(value = "update MemberInfo set enabledFlag=0,lastUpdatedDate=sysdate(),lastUpdatedBy=?2 where memberId=?1")
void deleteById(String id, String currentName);

SpringBoot Jpa多数据源(MySQL+Oracle不同数据库)

application-dev.yml

spring:
  jpa:
    show-sql: false
    hibernate:
      ddl-auto: none #update validate none
      #like12 add,多数据源不同数据库类型时需配置方言
      primary-dialect: org.hibernate.dialect.Oracle10gDialect
      secondary-dialect: org.hibernate.dialect.MySQL5Dialect
  #数据源
  datasource:
    primary:
      #Oracle模式
      driver-class-name: oracle.jdbc.OracleDriver
      jdbc-url: jdbc:oracle:thin:@###:1521:orcl
      username: ###
      password: ###
    secondary:
      #MySQL模式
      driver-class-name: com.mysql.cj.jdbc.Driver
      #like12 find,bug,UTC是全球标准时间 GMT%2B8是东八区,不能用UTC 否则Java时间与数据库时间会有8h时差
      #url: jdbc:mysql://###:3306/###?serverTimezone=UTC&useSSL=false
      jdbc-url: jdbc:mysql://###:3306/###?serverTimezone=GMT%2B8&useSSL=false
      username: ###
      password: ###
    #Druid数据库连接池配置
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5 #初始化时建立物理连接的个数
      min-idle: 5 #最小连接池数量
      max-active: 20 #最大连接池数量
      max-wait: 60000 #获取连接时最大等待时间，单位毫秒
      time-between-eviction-runs-millis: 60000 #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      min-evictable-idle-time-millis: 300000 #配置一个连接在池中最小生存的时间，单位是毫秒
      #Oracle模式
      validation-query: SELECT 1 FROM DUAL #用来检测连接是否有效的sql
      #MySQL模式
      validation-queryM: SELECT 1 #用来检测连接是否有效的sql
      test-while-idle: true #申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
      test-on-borrow: false #申请连接时执行validationQuery检测连接是否有效，如果为true会降低性能
      test-on-return: false #归还连接时执行validationQuery检测连接是否有效，如果为true会降低性能
      pool-prepared-statements: true #打开PSCache，并且指定每个连接上PSCache的大小
      max-pool-prepared-statement-per-connection-size: 20  #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
      #like12 modified,jpa拦截dbms_random wall会拦截dbms_random,使得无法随机排序
      #filters: stat,wall,slf4j #配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,slf4j #配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      #通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      #配置DruidStatFilter，用于采集web-jdbc关联监控的数据
      web-stat-filter:
        enabled: true
        url-pattern: "/*"
        #排除一些不必要的url过滤
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
      #配置DruidStatViewServlet
      stat-view-servlet:
        url-pattern: "/druid/*"
        #IP白名单(没有配置或者为空，则允许所有访问)
        #allow: 127.0.0.1,192.168.1.106
        #IP黑名单 (存在共同时，deny优先于allow)
        #deny: 192.168.1.73
        #禁用HTML页面上的“Reset All”功能
        reset-enable: fasle
        #可视化界面，登录名
        login-username: ###
        #可视化界面，登录密码
        login-password: ###

DataSourceConfig.java

package com.qyj.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;
import java.sql.SQLException;

@Configuration
public class DataSourceConfig {
    //多数据源配置
    @Value("${spring.datasource.primary.jdbc-url}")
    private String primaryUrl;
    @Value("${spring.datasource.primary.username}")
    private String primaryUsername;
    @Value("${spring.datasource.primary.password}")
    private String primaryPassword;
    @Value("${spring.datasource.primary.driver-class-name}")
    private String primaryDriverClassName;
    @Value("${spring.datasource.druid.validation-query}")
    private String primaryValidationQuery;
    //数据源2
    @Value("${spring.datasource.secondary.jdbc-url}")
    private String secondaryUrl;
    @Value("${spring.datasource.secondary.username}")
    private String secondaryUsername;
    @Value("${spring.datasource.secondary.password}")
    private String secondaryPassword;
    @Value("${spring.datasource.secondary.driver-class-name}")
    private String secondaryDriverClassName;
    @Value("${spring.datasource.druid.validation-queryM}")
    private String secondaryValidationQuery;
    //通用配置
    @Value("${spring.datasource.druid.initial-size}")
    private String initialSize;
    @Value("${spring.datasource.druid.min-idle}")
    private String minIdle;
    @Value("${spring.datasource.druid.max-active}")
    private String maxActive;
    @Value("${spring.datasource.druid.max-wait}")
    private String maxWait;
    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private String timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private String minEvictableIdleTimeMillis;
    @Value("${spring.datasource.druid.filters}")
    private String filters;

    @Bean(name = "primaryDataSource")
    @Primary
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDatasource() {
        //return DataSourceBuilder.create().build();

        //like12 modified,改为druid连接池模式
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(primaryUrl);
        datasource.setUsername(primaryUsername);
        datasource.setPassword(primaryPassword);
        datasource.setDriverClassName(primaryDriverClassName);
        datasource.setValidationQuery(primaryValidationQuery);
        //configuration
        if (StringUtils.isNotBlank(initialSize)) {
            datasource.setInitialSize(Integer.parseInt(initialSize));
        }
        if (StringUtils.isNotBlank(minIdle)) {
            datasource.setMinIdle(Integer.parseInt(minIdle));
        }
        if (StringUtils.isNotBlank(maxActive)) {
            datasource.setMaxActive(Integer.parseInt(maxActive));
        }
        if (StringUtils.isNotBlank(maxWait)) {
            datasource.setMaxWait(Integer.parseInt(maxWait));
        }
        if (StringUtils.isNotBlank(timeBetweenEvictionRunsMillis)) {
            datasource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));
        }
        if (StringUtils.isNotBlank(minEvictableIdleTimeMillis)) {
            datasource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));
        }
        datasource.setTestWhileIdle(true);
        datasource.setTestOnBorrow(false);
        datasource.setTestOnReturn(false);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return datasource;
    }

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

        //like12 modified,改为druid连接池模式
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(secondaryUrl);
        datasource.setUsername(secondaryUsername);
        datasource.setPassword(secondaryPassword);
        datasource.setDriverClassName(secondaryDriverClassName);
        datasource.setValidationQuery(secondaryValidationQuery);
        //configuration
        if (StringUtils.isNotBlank(initialSize)) {
            datasource.setInitialSize(Integer.parseInt(initialSize));
        }
        if (StringUtils.isNotBlank(minIdle)) {
            datasource.setMinIdle(Integer.parseInt(minIdle));
        }
        if (StringUtils.isNotBlank(maxActive)) {
            datasource.setMaxActive(Integer.parseInt(maxActive));
        }
        if (StringUtils.isNotBlank(maxWait)) {
            datasource.setMaxWait(Integer.parseInt(maxWait));
        }
        if (StringUtils.isNotBlank(timeBetweenEvictionRunsMillis)) {
            datasource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));
        }
        if (StringUtils.isNotBlank(minEvictableIdleTimeMillis)) {
            datasource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));
        }
        datasource.setTestWhileIdle(true);
        datasource.setTestOnBorrow(false);
        datasource.setTestOnReturn(false);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return datasource;
    }
}

PrimaryConfig.java

package com.qyj.config;

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

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",//配置连接工厂 entityManagerFactory
        transactionManagerRef = "transactionManagerPrimary", //配置 事物管理器  transactionManager
        basePackages = {"com.qyj.repository.common","com.qyj.repository.mini"}//设置持久层所在位置
)
public class PrimaryConfig {
    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;// 自动注入配置好的数据源
    @Value("${spring.jpa.hibernate.primary-dialect}")
    private String primaryDialect;// 获取对应的数据库方言
    @Autowired
    private HibernateProperties hibernateProperties;

    /**
     *
     * @param builder
     * @return
     */
    @Bean(name = "entityManagerFactoryPrimary")
    @Primary
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                //设置数据源
                .dataSource(primaryDataSource)
                //设置数据源属性
                .properties(getVendorProperties(primaryDataSource))
                //设置实体类所在位置.扫描所有带有 @Entity 注解的类
                .packages("com.qyj.domain.common","com.qyj.domain.mini")
                // Spring会将EntityManagerFactory注入到Repository之中.有了 EntityManagerFactory之后,
                // Repository就能用它来创建 EntityManager 了,然后 EntityManager 就可以针对数据库执行操作
                .persistenceUnit("primaryPersistenceUnit")
                .build();

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

    /**
     * 配置事物管理器
     *
     * @param builder
     * @return
     */
    @Bean(name = "transactionManagerPrimary")
    @Primary
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

SecondaryConfig.java

package com.qyj.config;

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.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="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "com.qyj.repository.store"})
public class SecondaryConfig {
    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;
    @Value("${spring.jpa.hibernate.secondary-dialect}")
    private String secondaryDialect;
    @Autowired
    private HibernateProperties hibernateProperties;

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

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties(secondaryDataSource))
                .packages("com.qyj.domain.store")
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }
    private Map<String, Object> getVendorProperties(DataSource dataSource) {
        Map<String,String> map = new HashMap<>();
        map.put("hibernate.dialect",secondaryDialect);// 设置对应的数据库方言
        jpaProperties.setProperties(map);
        return hibernateProperties.determineHibernateProperties(
                jpaProperties.getProperties(), new HibernateSettings());
    }

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

目录结构