devbox
程序代码艺术家
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Java JPA 多数据源查询 支持动态sql动态查询_jpa 根据不同的数据库使用不同的sql

作者：程序代码艺术家 | 2024-01-31 22:27:13

赞

踩

jpa 根据不同的数据库使用不同的sql

背景介绍
需要对两个mysql的数据库做数据对比，且两个mysql数据库来自不同的数据源；

一、配置信息
（一）依赖配置
文件：pom.xml

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

（二）数据源信息配置
文件：application.properties

单数据源的时候配置的是：spring.datasource.primary.url
多数据源的时候配置的是：spring.datasource.primary.jdbc-url

spring.datasource.primary.jdbc-url=jdbc:mysql://xx.xx.xx.xx:3306/database_name?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&sessionVariables=group_concat_max_len=999999&useSSL=false
spring.datasource.primary.username=username
spring.datasource.primary.password=password
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.secondary.jdbc-url=jdbc:mysql://xx.xx.xx.xx:3306/database_name?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&sessionVariables=group_concat_max_len=999999&useSSL=false
spring.datasource.secondary.username=username
spring.datasource.secondary.password=password
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

（三）数据源配置

1、因为有的类在多个包中都有，所以特地将import的信息都展示出来了；
2、配置文件的位置：单独在应用同目录下建一个文件夹放就可以；
3、如下DataSourceConfig是个标准的配置类，都不需要改的，PrimaryConfig，SecondaryConfig需要改下指定下数据源指定的repository，entity 位置就可以了；

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 = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @ConfigurationProperties(prefix="spring.datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

主数据源配置

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= { "com.example.xxx.xxx.repository" }) //设置查询语句所在的位置
public class PrimaryConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;
    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }
    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .properties(jpaProperties.getProperties())
                .properties(getVendorProperties())
                .packages("com.example.xxx.xxx.entity") //设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }
    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    private Map getVendorProperties() {
        //return jpaProperties.getHibernateProperties(dataSource); 网上很多地方都是这样子写的，其实已经不好使了...
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }
    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61

辅数据源配置（基本和主数据源配置一样，主要是改下名称）

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "com.example.xxx.xxx.repository" }) //设置查询语句所在的位置
public class SecondaryConfig {
    @Autowired @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @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(jpaProperties.getProperties())
                .properties(getVendorProperties())
                .packages("com.example.xxx.xxx.entity") //设置实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }
    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    private Map getVendorProperties() {
        //return jpaProperties.getHibernateProperties(dataSource); 
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }
    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58

以上完成配置信息，以下开始数据库查询操作：

二、查询操作

1、entity repository 就跟普通的一样就行；
2、动态sql 需要对entitymanager指明一下数据源；

reposity

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface ColumnRepo extends JpaRepository<ColumnInfo,Long>, JpaSpecificationExecutor<ColumnInfo> {

    /**
     * create by: admin
     * description: 获取表的所有字段
     * create time: 2021/5/26 下午5:35
     */
    @Query(value = "select COLUMN_NAME from information_schema.COLUMNS where table_name = :tablename and table_schema = :tableschema order by case when INSTR(column_name,'space') > 0 then REPLACE(column_name,'space','project') else column_name end ", nativeQuery = true)
    public List<ColumnInfo> getColumnsByTableName(@Param("tablename") String tablename, @Param("tableschema") String tableschema);
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

动态sql

import static com.example.xxx.xxx.common.GlobalVariableDefine.batch;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.math.BigInteger;
import java.util.List;

@Slf4j
@Component
public class TableDataQuery {
    /*PersistenceContext 指明该处查询使用的数据源，该名称在PrimaryConfig 中存在定义*/
   @PersistenceContext(unitName="entityManagerFactoryPrimary")
    EntityManager entityManager;

    /**
     * create by: admin
     * description: 分批次有序获取数据内容
     * create time: 2021/5/26 下午6:04
     */
    public List<String> getTableData(String tablename, String tableschema, String colstr, String condition, int i){

        String querySQL = String.format("select convert(concat_ws(';', %s) using utf8) from %s.%s %s order by %s limit %d,%d",colstr,tableschema,tablename,condition,colstr,i*batch,batch);
        List<String> list = entityManager.createNativeQuery(querySQL).getResultList();
        return list;
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

当前的解决方案不是很完美，觉得还是麻烦了一点点，相对于配置，我的实际操作内容反而不是很多，不划算，也不高级，后续有空尝试一下动态数据源写法 _

完结，撒花～～～

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号