背景介绍
需要对两个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>
(二)数据源信息配置
文件: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、因为有的类在多个包中都有,所以特地将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();
}
}
主数据源配置
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());
}
}
辅数据源配置(基本和主数据源配置一样,主要是改下名称)
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、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);
}
动态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;
}
}
当前的解决方案不是很完美,觉得还是麻烦了一点点,相对于配置,我的实际操作内容反而不是很多,不划算,也不高级,后续有空尝试一下动态数据源写法 _
完结,撒花~~~