SpringBootJpa整合MySQL和postgresql

最新推荐文章于 2024-06-03 17:22:01 发布

闪现送五杀

最新推荐文章于 2024-06-03 17:22:01 发布

阅读量715

点赞数

分类专栏： java jpa 多数据源文章标签： spring boot 数据库 jpa

本文链接：https://blog.csdn.net/fanyizhuang/article/details/116304296

版权

java 同时被 3 个专栏收录

3 篇文章 0 订阅

订阅专栏

jpa

2 篇文章 0 订阅

订阅专栏

多数据源

1 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在SpringBoot中整合JPA与多个数据源，包括MySQL和PostgreSQL，以及遇到的JPA自动注入方言错误和多数据源事务管理问题。通过自定义配置和AOP实现事务管理，解决了不同数据源的事务生效问题。

摘要由CSDN通过智能技术生成

SpringBootJpa整合多数据源

maven配置
配置文件配置
整合
Jpa整合不同数据源遇到的问题
- Jpa默认自动注入了方言，在相同数据源类型时没问题，但数据源不同时会启动报错。
- 多数据源同时访问时只有一个事物生效。

maven配置

	<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.1.RELEASE</version>
    </parent>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

配置文件配置

spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss.SSS
    time-zone: GMT+8
  datasource:
    primary:
#      type: com.alibaba.druid.pool.DruidDataSource
      jdbc-url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
      username : root
      password : XXXXXX
      driver-class-name: com.mysql.jdbc.Driver
    secondary:
      jdbc-url:  jdbc:postgresql://localhost:5432/test
      username: postgres
      password: XXXXXX
      driver-class-name: org.postgresql.Driver
  jpa:
    hibernate:
      primary-dialect: org.hibernate.dialect.MySQL5InnoDBDialect
      secondary-dialect: org.hibernate.spatial.dialect.postgis.PostgisDialect
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        temp:
          use_jdbc_metadata_defaults: false

整合

数据源配置

@Configuration
public class DataSourceConfig {

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

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

}

MySQL整合到Jpa中

/**
 * 数据源一
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {
                "com.fyz.repository.mysql"

        }) //设置Repository所在位置

public class PrimaryConfig {


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

	//方言
    @Value("${spring.jpa.hibernate.primary-dialect}")
    private String primaryDialect;

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

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary() {
    //定义数据库类型和连接方言等主要配置（不写两个数据库方言一样会报错）
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);
        jpaVendorAdapter.setDatabase(Database.MYSQL);
        jpaVendorAdapter.setDatabasePlatform(primaryDialect);
        LocalContainerEntityManagerFactoryBean builder = new LocalContainerEntityManagerFactoryBean();
        builder.setDataSource(primaryDataSource);
        builder.setPackagesToScan("com.fyz.entity.mysql");
        builder.setJpaVendorAdapter(jpaVendorAdapter);
        builder.setPersistenceUnitName("primaryPersistenceUnit");
        builder.setJpaPropertyMap(getVendorProperties());
        return builder;
    }


    @Autowired
    private JpaProperties jpaProperties;

    private Map getVendorProperties() {

        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

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

psotgresql整合到Jpa中

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.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.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.Map;

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

    //方言
    @Value("${spring.jpa.hibernate.secondary-dialect}")
    private String secondaryDialect;

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

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

    /**
     * 将配置文件中对应的配置信息注册到jpa中进行管理
     * @return
     */
    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary() {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);
        jpaVendorAdapter.setDatabase(Database.POSTGRESQL);
        jpaVendorAdapter.setDatabasePlatform(secondaryDialect);
        LocalContainerEntityManagerFactoryBean builder = new LocalContainerEntityManagerFactoryBean();
        builder.setDataSource(secondaryDataSource);
        builder.setPackagesToScan("com.fyz.entity.postgresql");
        builder.setJpaVendorAdapter(jpaVendorAdapter);
        builder.setPersistenceUnitName("secondaryPersistenceUnit");
        builder.setJpaPropertyMap(getVendorProperties());
        return builder;
    }

    @Autowired
    private JpaProperties jpaProperties;
    private Map getVendorProperties() {

        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

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

Jpa整合不同数据源遇到的问题

Jpa默认自动注入了方言，在相同数据源类型时没问题，但数据源不同时会启动报错。

我这里改成都自己注入配置。核心代码如下：

		HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);
        jpaVendorAdapter.setDatabase(Database.POSTGRESQL);
        jpaVendorAdapter.setDatabasePlatform(secondaryDialect);
        LocalContainerEntityManagerFactoryBean builder = new LocalContainerEntityManagerFactoryBean();
        builder.setDataSource(secondaryDataSource);
        builder.setPackagesToScan("com.fyz.entity.postgresql");
        builder.setJpaVendorAdapter(jpaVendorAdapter);
        builder.setPersistenceUnitName("secondaryPersistenceUnit");
        builder.setJpaPropertyMap(getVendorProperties());

多数据源同时访问时只有一个事物生效。

我通过AOP拦截然后手动开启事物，关闭事物，回滚事物解决的。思路如下：

自定义事物注解
拦截自定义的事物注解
在方法调用前开启事物
方法调用成提交事物\方法调用失败回滚事物
自定义注解方法：

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface DataSourceTransactions {
 
    /**
     * 事务管理器数组（默认管理所有的事物）
     */
    String[] transactionManagers() default {"transactionManagerPrimary","transactionManagerSecondary"};
}

AOP拦截方法：

@Component
@Aspect
@Slf4j
public class DataSourceTransactionAspect {
 
    /**
     * 线程本地变量：为什么使用栈？※为了达到后进先出的效果※
     */
    private static final ThreadLocal<Stack<Pair<PlatformTransactionManager, TransactionStatus>>> THREAD_LOCAL = new ThreadLocal<>();
 
    /**
     * 用于获取事务管理器
     */
    @Autowired
    private ApplicationContext applicationContext;
 
    /**
     * 事务声明
     */
    private DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    {
        // 非只读模式
        def.setReadOnly(false);
        // 事务隔离级别：采用数据库的
        def.setIsolationLevel(TransactionDefinition.ISOLATION_DEFAULT);
        // 事务传播行为
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    }
 
    /**
     * 切面
     */
    @Pointcut("@annotation(com.fyz.config.annotation.DataSourceTransactions)")
    public void pointcut() {
    }
 
    /**
     * 声明事务
     *
     * @param transactional 注解
     */
    @Before("pointcut() && @annotation(transactional)")
    public void before(DataSourceTransactions transactional) {
        // 根据设置的事务名称按顺序声明，并放到ThreadLocal里
        String[] transactionManagerNames = transactional.transactionManagers();
        //第一次拦截时创建，后面拦截时添加，防止嵌套调用多个事物时后面的事物删除前面的事物，导致方法添加失败
        Stack<Pair<PlatformTransactionManager, TransactionStatus>> pairStack = THREAD_LOCAL.get() ==null?new Stack<>():THREAD_LOCAL.get();
        for (String transactionManagerName : transactionManagerNames) {
            PlatformTransactionManager transactionManager = applicationContext.getBean(transactionManagerName, PlatformTransactionManager.class);
            TransactionStatus transactionStatus = transactionManager.getTransaction(def);
            pairStack.push(new Pair(transactionManager, transactionStatus));
        }
        THREAD_LOCAL.set(pairStack);
    }
 
    /**
     * 提交事务
     */
    @AfterReturning("pointcut()  && @annotation(transactional)")
    public void afterReturning(DataSourceTransactions transactional) {
        rollbackOrCommit(transactional,Boolean.TRUE);
    }
 
    /**
     * 回滚事务
     */
    @AfterThrowing(value = "pointcut()  && @annotation(transactional)")
    public void afterThrowing(DataSourceTransactions transactional) {
        rollbackOrCommit(transactional,Boolean.FALSE);
    }

    /**
     * 回滚或提交事物
     * @param transactional
     * @param isCommit
     */
    private void rollbackOrCommit(DataSourceTransactions transactional,Boolean isCommit){
        // ※栈顶弹出（后进先出）
        Stack<Pair<PlatformTransactionManager, TransactionStatus>> pairStack = THREAD_LOCAL.get();
        String[] transactionManagerNames = transactional.transactionManagers();
        for (int i = 0; i < transactionManagerNames.length; i++) {
            Pair<PlatformTransactionManager, TransactionStatus> pair = pairStack.pop();
            if(isCommit){
                pair.getKey().commit(pair.getValue());
            }else {
                pair.getKey().rollback(pair.getValue());}
        }
        //事物都消费完后删除线程，减少内存支出
        if(Objects.isNull(pairStack)|| pairStack.empty()){
            THREAD_LOCAL.remove();
        }
    }
}