在不同数据库之间复制数据（两者都支持jdbc） [英] Copy data between different databases (both are jdbc supported)

问题描述

我想将测试数据库（mysql）中的所有数据复制到生产数据库（hsqldb）
我使用了hibernate并让它为我创建了这些数据库中的表。所以结构/模式是相同的。

I'd like to copy all data from my testing database (mysql) to a production database (hsqldb) I used hibernate and let it created tables in those database for me. So the structure/schema are the same.

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

Using mysql dump i can copy data between two mysql databases. but in my case the database are different, and i heard the generated sql from mysqldump wont work with hsqldb. Since both database type are support by jdbc/hibernate, are there any way/method/java library to copy data between them?

推荐答案

当然，如果架构是相同的，可以通过非常简单的方式实现。而且由于您使用相同的Hibernate映射创建了两个数据库，因此它们在实体意义上应相等。

Sure, it is possible in a very easy way if the schemata are the same. And since you created both databases with the same Hibernate mapping they should be equal in the Entity sense.

仅限您需要两个Hibernate持久性单元（datasources）。如果两者都配置正确并且您具有特定的 EntityManager 实例，只需转到Hibernate Session 级别 - 据我所知，JPA不支持这种方式（如果我错了，请纠正我） - 并将源实体复制到目标数据库。

You only need two Hibernate persistence units (datasources). If both are configured properly and you have the particular EntityManager instances handy, just go down to the Hibernate Session level - as far as I know JPA does not support that in this way (correct me if I'm wrong) - and replicate your source entity to your target database.

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

Because I like to work with Spring, I will use Spring Boot for the following example. Except of the configuration the replication step would be implemented the same with any Hibernate application.

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

I'm also using two PostgreSQL databases instead of an HSQLB only to keep it simple. Just extend the configuration part if your configurations drift apart, the only difference between my persistence units is the datasource url.

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

So first we need an entity to test the replication:

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 ），配置的所有其他部分将用于两个持久性单位：

This is (the YAML version of) the configuration of the two datasources (see the second datasource url called targetDatabaseUrl), All other parts of the config will be used for both persistence units:

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

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

The next part is the configuration class for the datasources:

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();
    }

}

现在你可以使用不同的实体管理员只需将数据从一个数据源读取和写入另一个数据源。为了表明这是一个小测试用例：

Now you can use the different entity managers to simply read and write your data from one datasource to another. To show that here is a small test case:

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());
    }

}

最后，选择其中一个< a href =https://docs.jboss.org/hibernate/orm/5.2/javadocs/org/hibernate/ReplicationMode.html\"rel =nofollow noreferrertitle =ReplicationMode>可能的复制模式适合您的需求。

Finally, choose one of the possible replication modes which fits your needs.

这就是全部。你甚至可以使用一个事务，只决定其中一个持久性单元并利用它的事务管理器，就像测试一样用 @Transactional（transactionManager =targetTransactionManager）。

That's all. You can even use a transaction, just decide for one of both persistence units and leverage it's transaction manager like the test does with @Transactional(transactionManager = "targetTransactionManager").

这篇关于在不同数据库之间复制数据（两者都支持jdbc）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！