Java中plSql数据库同步到mysql，和xx-job使用

作者: 雪飘千里 | 来源:发表于2019-07-11 14:28 被阅读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

image.png

entity需要注意实体上的注解；
mysql

image.png

postgreSql

image.png

4、dao
dao是直接继承的JpaRepository

image.png

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>

Java中plSql数据库同步到mysql，和xx-job使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

学习

随笔-生活工作点滴

javaWeb学习