Java中plSql数据库同步到mysql,和xx-job使用
2019-07-11 本文已影响31人
雪飘千里
公司最近在把其中一个项目微服务化,数据库也从postgreSql要迁移到mysql上,因为有些字段类型在mysql上不支持,同时在项目迁移过程中,还要正常运行,直接写sql脚本迁移数据不灵活又麻烦,所以想到单独搞一个数据同步的微服务,使用多数据源同步连接多个库,然后在swagger中手动控制数据同步。
项目使用springboot开发,ORM采用Spring data jpa ,因为这个主要是单表操作,使用spring data jpa很方便,同时集成了xx-job分布式定时任务调度平台,通过定时任务可以提前在上线之前把一些数据量大的表(订单表)在不忙的时间段(夜里2点?)分批次的迁移,这样既可以为发版时同步数据节省时间,又可以在测试阶段就直接用生产过期数据测试,提前发现bug。
- 1、配置文件
这里要注意,这里mysql数据库是可以配置多个的,格式和这个一样就行了
server:
session-timeout: 30
servlet:
context-path: /${spring.application.name}
tomcat:
max-threads: 800
uri-encoding: UTF-8
basedir: null
logging:
path: null
file: null
#分布式任务调度
xxl:
job:
admin:
addresses:
http://testjob.xxx.cn/xxl-job-admin
accessToken: ''
executor:
appname: ${spring.application.name}
ip:
port: 28103
logpath: /logs/xxl-job-datasynv
logretentiondays: -1
spring:
datasource:
postgresql:
name: postgresql
url: jdbc:postgresql://127.0.0.1:32215/bmtsystem-space-sharing
username: postgres
password: 12345678
driver-class-name: org.postgresql.Driver
type: com.alibaba.druid.pool.DruidDataSource
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
mysql-shared:
name: mysql
url: jdbc:mysql://127.0.0.1:3306/bmt-shared?charset=utf8&serverTimezone=UTC
username: root
password: 12345678
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
jackson:
data-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
aop:
auto: true
proxy-target-class: true
http:
encoding:
charset: UTF-8
enable: true
force: true
jpa:
#database: mysql
show-sql: false
open-in-view: true
properties:
hibernate:
#format_sql: true
#enable_lazy_load_no_trans: true
hibernate:
format_sql: true
ddl-auto: update
naming:
#implicit_naming_strategy:
physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
#database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
- 2、多数据源配置
package com.xxx.database.convert.config;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
public class DataSourceConfig {
@Bean("postgresqlDataSource")
@ConfigurationProperties(prefix = "spring.datasource.postgresql")
public DataSource postgresqlSDataSource() {
return new DruidDataSource();
}
@Bean(name = "postgresqlJdbcTemplate")
public JdbcTemplate postgresqlJdbcTemplate(@Qualifier("postgresqlDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean("mysqlSharedDataSource")
@Primary
@Qualifier("mysqlSharedDataSource")
@ConfigurationProperties(prefix = "spring.datasource.mysql-shared")
public DataSource mysqlSharedDataSource() {
return new DruidDataSource();
}
@Bean(name = "mysqlSharedJdbcTemplate")
public JdbcTemplate mysqlSharedJdbcTemplate(@Qualifier("mysqlSharedDataSource") DataSource dataSource) { return new JdbcTemplate(dataSource); }
}
postmresql配置
package com.xxx.database.convert.config;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
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.context.properties.EnableConfigurationProperties;
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;
@Configuration
@EnableTransactionManagement
@EnableConfigurationProperties(JpaProperties.class)
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryPostgresql", transactionManagerRef = "transactionManagerPostgresql", basePackages = {
PostgresqlConfig.DEFAULT_DAO_PACKAGE }) // 设置Repository所在位置
public class PostgresqlConfig {
//实体所在路径
protected final static String DEFAULT_ENTITY_PACKAGE = "com.xxx.database.convert.entity.postgresql";
//dao所在路径
protected final static String DEFAULT_DAO_PACKAGE = "com.xxx.database.convert.repository.postgresql";
@Autowired
@Qualifier("postgresqlDataSource")
private DataSource postgresqlDataSource;
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
@Bean(name = "entityManagerPostgresql")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactoryPostgresql(builder).getObject().createEntityManager();
}
@Bean(name = "entityManagerFactoryPostgresql")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryPostgresql(EntityManagerFactoryBuilder builder) {
return builder.dataSource(postgresqlDataSource).properties(getVendorProperties())
.packages(DEFAULT_ENTITY_PACKAGE) // 设置实体类所在位置
.persistenceUnit("postgresqlPersistenceUnit").build();
}
private Map<String, Object> getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
@Bean(name = "transactionManagerPostgresql")
public PlatformTransactionManager transactionManagerPostgresql(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactoryPostgresql(builder).getObject());
}
}
mysql配置
package com.xxx.database.convert.config;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.sql.DataSource;
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;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryMysqlShared", transactionManagerRef = "transactionManagerMysqlShared", basePackages = {
MysqlSharedConfig.DEFAULT_DAO_PACKAGE }) // 设置Repository所在位置
public class MysqlSharedConfig {
protected final static String DEFAULT_ENTITY_PACKAGE = "com.xxx.database.convert.entity.mysql.shared";
protected final static String DEFAULT_DAO_PACKAGE = "com.xxx.database.convert.repository.mysql.shared";
@Autowired
@Qualifier("mysqlSharedDataSource")
private DataSource mysqlSharedDataSource;
@Autowired
private JpaProperties jpaProperties;
@Autowired
private HibernateProperties hibernateProperties;
@Primary
@Bean(name = "entityManagerMysqlShared")
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactoryMysqlShared(builder).getObject().createEntityManager();
}
@Primary
@Bean(name = "entityManagerFactoryMysqlShared")
public LocalContainerEntityManagerFactoryBean entityManagerFactoryMysqlShared(EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean entityManagerFactoryMysql = builder.dataSource(mysqlSharedDataSource)
.properties(getVendorProperties()).packages(DEFAULT_ENTITY_PACKAGE) // 设置实体类所在位置
.persistenceUnit("mysqlSharedPersistenceUnit").build();
return entityManagerFactoryMysql;
}
private Map<String, Object> getVendorProperties() {
return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}
@Primary
@Bean(name = "transactionManagerMysqlShared")
public PlatformTransactionManager transactionManagerMysqlShared(EntityManagerFactoryBuilder builder) {
JpaTransactionManager txManager = new JpaTransactionManager();
txManager.setEntityManagerFactory(entityManagerFactoryMysqlShared(builder).getObject());
return txManager;
}
}
XxlJob配置
xxjob详细使用方法看官网,介绍很详细
http://www.xuxueli.com/xxl-job/#/?id=_32-glue%E6%A8%A1%E5%BC%8Fjava
package com.xxx.database.convert.config;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import com.xxl.job.core.executor.impl.XxlJobSpringExecutor;
@Configuration
@ComponentScan(basePackages = "com.xxx.database.convert.jobhandler")
public class XxlJobConfig {
private Logger logger = LoggerFactory.getLogger(XxlJobConfig.class);
@Value("${xxl.job.admin.addresses}")
private String adminAddresses;
@Value("${xxl.job.executor.appname}")
private String appName;
@Value("${xxl.job.executor.ip}")
private String ip;
@Value("${xxl.job.executor.port}")
private int port;
@Value("${xxl.job.accessToken}")
private String accessToken;
@Value("${xxl.job.executor.logpath}")
private String logPath;
@Value("${xxl.job.executor.logretentiondays}")
private int logRetentionDays;
@Bean(initMethod = "start", destroyMethod = "destroy")
public XxlJobSpringExecutor xxlJobExecutor() {
logger.info(">>>>>>>>>>> xxl-job config init.");
XxlJobSpringExecutor xxlJobSpringExecutor = new XxlJobSpringExecutor();
xxlJobSpringExecutor.setAdminAddresses(adminAddresses);
xxlJobSpringExecutor.setAppName(appName);
xxlJobSpringExecutor.setIp(ip);
xxlJobSpringExecutor.setPort(port);
xxlJobSpringExecutor.setAccessToken(accessToken);
xxlJobSpringExecutor.setLogPath(logPath);
xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays);
return xxlJobSpringExecutor;
}
}
- 3、entity
entity需要注意实体上的注解;
mysql
image.png
postgreSql
image.png
-
4、dao
dao是直接继承的JpaRepository
image.png
- 5、定时任务
package com.xxx.database.convert.jobhandler.shared;
import com.xxx.database.convert.service.IService;
import com.xxx.database.convert.util.Constant;
import com.xxx.database.convert.util.DateUtil;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.IJobHandler;
import com.xxl.job.core.handler.annotation.JobHandler;
import com.xxl.job.core.log.XxlJobLogger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;
import java.util.Date;
@JobHandler(value = "deviceAisleJobHandler")
@Component
public class DeviceAisleJobHandler extends IJobHandler {
@Autowired
@Qualifier("deviceAisleServiceImpl")
private IService deviceAisleServiceImpl;
@Override
public ReturnT<String> execute(String param) throws Exception {
XxlJobLogger.log("实例{},参数{}", DeviceAisleJobHandler.class, param);
param = param.trim();
if (param != null && !param.equals("")) {
Date nowDate = new Date();
Constant.putTimeToMap(Constant.DEVICE_AISLE, DateUtil.getBeforeMinDate(nowDate, Integer.valueOf(param)), nowDate);
Constant.IS_JOB_HANDLE = true;
deviceAisleServiceImpl.convert();
}
return SUCCESS;
}
}
- 6、pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
</parent>
<groupId>com.xxx</groupId>
<artifactId>xxx-datasynv</artifactId>
<version>1.0.0-SNAPSHOT</version>
<packaging>jar</packaging>
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<lombok.version>1.18.2</lombok.version>
<druid.version>1.1.9</druid.version>
</properties>
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-eureka</artifactId>
<version>1.4.0.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<!--分布式任务调度系统-->
<dependency>
<groupId>com.xuxueli</groupId>
<artifactId>xxl-job-core</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>io.swagger</groupId>
<artifactId>swagger-annotations</artifactId>
<version>1.5.16</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.8.0</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.8.0</version>
<scope>compile</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<!--fork : 如果没有该项配置,可能devtools不会起作用,即应用不会restart -->
<fork>true</fork>
<executable>true</executable>
</configuration>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<configuration>
<skipTests>true</skipTests>
<testFailureIgnore>true</testFailureIgnore>
</configuration>
</plugin>
</plugins>
<finalName>xxx-datasynv</finalName>
</build>
</project>