java – 在不同数据库之间复制数据(两者都支持jdbc)

我想将测试数据库(mysql)中的所有数据复制到生产数据库(hsqldb)我使用了hibernate,让它在我的数据库中创建表.所以结构/架构是相同的.使用mysql dump我可以在两个mysql数据库之间复制数据.但在我的情况下,数据库是不...

我想将测试数据库(mysql)中的所有数据复制到生产数据库(hsqldb)
我使用了hibernate,让它在我的数据库中创建表.所以结构/架构是相同的.

使用mysql dump我可以在两个mysql数据库之间复制数据.但在我的情况下,数据库是不同的,我听说mysqldump生成的sql不能与hsqldb一起使用.由于这两种数据库类型都是由jdbc / hibernate支持的,有没有方法/方法/ java库来复制它们之间的数据？

解决方法:

当然,如果模式相同,则可以以非常简单的方式实现.而且由于您使用相同的Hibernate映射创建了两个数据库,因此它们在实体意义上应该相同.

您只需要两个Hibernate持久性单元(数据源).如果两者都配置正确并且你有特定的EntityManager实例,只需转到Hibernate会话级别 – 据我所知JPA不支持这种方式(如果我错了就纠正我) – 并复制你的源代码实体到您的目标数据库.

因为我喜欢使用Spring,所以我将使用Spring Boot作为以下示例.除了配置之外,复制步骤将与任何Hibernate应用程序一样实现.

我也使用两个PostgreSQL数据库而不是HSQLB来保持简单.如果您的配置分开,只需扩展配置部分,我的持久性单元之间的唯一区别是数据源URL.

首先,我们需要一个实体来测试复制：

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class StorageEntry {

    @Id
    @GeneratedValue
    private Long id;

    private String someValue;

    // imagine getters and setter here

}

这是(YAML版本)两个数据源的配置(请参阅第二个数据源url,名为targetDatabaseUrl),配置的所有其他部分将用于两个持久性单元：

spring:
  datasource:
    url: jdbc:postgresql://localhost/postgres
    targetDatabaseUrl: jdbc:postgresql://localhost/postgres2
    username: <username>
    password: <password>
    driver-class-name: org.postgresql.Driver
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: create-drop

下一部分是数据源的配置类：

import java.util.Properties;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
public class PersistenceConfig {

    @Autowired
    private JpaVendorAdapter jpaVendorAdapter;

    @Value("${spring.datasource.url}")
    private String databaseUrl;

    @Value("${spring.datasource.targetDatabaseUrl}")
    private String targetDatabaseUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.jpa.database-platform}")
    private String dialect;

    @Value("${spring.jpa.hibernate.ddl-auto}")
    private String ddlAuto;

    @Bean
    public EntityManager sourceEntityManager() {
        return sourceEntityManagerFactory().createEntityManager();
    }

    @Bean
    public EntityManager targetEntityManager() {
        return targetEntityManagerFactory().createEntityManager();
    }

    @Bean
    public EntityManagerFactory sourceEntityManagerFactory() {
        return createEntityManagerFactory("source", databaseUrl);
    }

    @Bean
    public EntityManagerFactory targetEntityManagerFactory() {
        return createEntityManagerFactory("target", targetDatabaseUrl);
    }

    @Bean
    public PlatformTransactionManager sourceTransactionManager() {
        return new JpaTransactionManager(sourceEntityManagerFactory());
    }

    @Bean
    public PlatformTransactionManager targetTransactionManager() {
        return new JpaTransactionManager(targetEntityManagerFactory());
    }

    private EntityManagerFactory createEntityManagerFactory(final String persistenceUnitName,
            final String databaseUrl) {
        final LocalContainerEntityManagerFactoryBean entityManagerFactory = new LocalContainerEntityManagerFactoryBean();

        final DriverManagerDataSource dataSource = new DriverManagerDataSource(databaseUrl, username, password);
        dataSource.setDriverClassName(driverClassName);
        entityManagerFactory.setDataSource(dataSource);

        entityManagerFactory.setJpaVendorAdapter(jpaVendorAdapter);
        entityManagerFactory.setPackagesToScan("com.example.model");
        entityManagerFactory.setPersistenceUnitName(persistenceUnitName);

        final Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", dialect);
        properties.setProperty("hibernate.hbm2ddl.auto", ddlAuto);
        entityManagerFactory.setJpaProperties(properties);

        entityManagerFactory.afterPropertiesSet();
        return entityManagerFactory.getObject();
    }

}

现在,您可以使用不同的实体管理器简单地将数据从一个数据源读取和写入另一个数据源.为了表明这是一个小测试用例：

import static org.hamcrest.CoreMatchers.notNullValue;
import static org.hamcrest.CoreMatchers.nullValue;
import static org.hamcrest.MatcherAssert.assertThat;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.ReplicationMode;
import org.hibernate.Session;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import com.example.model.StorageEntry;

@SpringBootTest
@RunWith(SpringRunner.class)
@Transactional(transactionManager = "targetTransactionManager")
public class ReplicationTests {

    @PersistenceContext(unitName = "source")
    private EntityManager sourceEntityManager;

    @PersistenceContext(unitName = "target")
    private EntityManager targetEntityManager;

    @Test
    public void copyEntityBetweenPersistenceUnits() {
        final StorageEntry entityToCopy = new StorageEntry();
        entityToCopy.setSomeValue("copyMe!");
        sourceEntityManager.persist(entityToCopy);

        final Long id = entityToCopy.getId();

        final StorageEntry sourceEntity = sourceEntityManager.find(StorageEntry.class, id);
        assertThat("Entity should exist in default schema!", sourceEntity, notNullValue());

        StorageEntry targetEntity = targetEntityManager.find(StorageEntry.class, id);
        assertThat("Target schema should not contain the entity, yet!", targetEntity, nullValue());

        final Session hibernateSession = targetEntityManager.unwrap(Session.class);
        hibernateSession.replicate(sourceEntity, ReplicationMode.OVERWRITE);

        targetEntityManager.flush();
        targetEntityManager.clear();

        targetEntity = targetEntityManager.find(StorageEntry.class, id);
        assertThat("Entity should be copied now!", targetEntity, notNullValue());
    }

}

最后,选择符合您需求的possible replication modes之一.

就这样.你甚至可以使用一个事务,只决定其中一个持久性单元并利用它的事务管理器,就像测试对@Transactional(transactionManager =“targetTransactionManager”)一样.