How to make Hibernate read a table in a Mysql database and write to another table in Postgresql?

Question

I need to consult a database in Mysql and take a table from there to write to a database in Postgresql by Hibernate/JPA.

I set up the persistence.xml as follows:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
  http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">

<persistence-unit name="postgreSQL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <properties>
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />

        <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost:5432/tfProjeto" />

        <property name="javax.persistence.jdbc.user" value="postgres" />
        <property name="javax.persistence.jdbc.password" value="admin" />

        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
        <property name="hibernate.connection.CharSet" value="UTF-8" />
        <property name="hibernate.connection.characterEncoding" value="UTF-8" />
        <property name="hibernate.connection.useUnicode" value="true" />

        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="true" />
        <property name="hibernate.hbm2ddl.auto" value="update" />


        <property name="hibernate.c3p0.min_size" value="5" />
        <property name="hibernate.c3p0.max_size" value="10" />
        <property name="hibernate.c3p0.timeout" value="1800" />
        <property name="hibernate.c3p0.max_statements" value="50" />

    </properties>
</persistence-unit>

<persistence-unit name="mySQL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <properties>
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql:/localhost:3306/meuProjeto?zeroDateTimeBehavior=convertToNull" />

        <property name="javax.persistence.jdbc.user" value="root" />
        <property name="javax.persistence.jdbc.password" value="admin" />

        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
        <property name="hibernate.connection.CharSet" value="UTF-8" />
        <property name="hibernate.connection.characterEncoding" value="UTF-8" />
        <property name="hibernate.connection.useUnicode" value="true" />

        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="true" />
        <property name="hibernate.hbm2ddl.auto" value="update" />

        <property name="hibernate.c3p0.min_size" value="5" />
        <property name="hibernate.c3p0.max_size" value="10" />
        <property name="hibernate.c3p0.timeout" value="1800" />
        <property name="hibernate.c3p0.max_statements" value="50" />

    </properties>
</persistence-unit>

And my Entityfactory is like this:

@RequestScoped
public class EntityFactory implements Serializable {

    private static final long serialVersionUID = 1L;
    public static EntityManagerFactory entityManagerFactory;
    public static EntityManagerFactory entityManagerFactoryMySQL;

    public static void initializeEntityManager() {
        if ((entityManagerFactory == null) || (!entityManagerFactory.isOpen())) {
            entityManagerFactory = Persistence.createEntityManagerFactory("postgreSQL");
        }
    }

    public static void initializeEntityManagerMySql() {
        if ((entityManagerFactoryMySQL == null) || (!entityManagerFactoryMySQL.isOpen())) {
            entityManagerFactoryMySQL = Persistence.createEntityManagerFactory("mySQL");
        }
    }

}

However, when I use the classes, Hibernate creates in both banks the two tables. That is, I need to take the users(Mysql) and save to usuarios(Postgresql), but Hibernate creates the tables users and usuarios in Mysql and the user tables and usuarios in Postgresql as well, and what I want is just to create the usuarios only on the basis of Postgresql.

Some solution?

Answer 1

By default Hibernate will look for entities that are in the same classpath of persitence.xml, that is, entities that are in the same jar. How he does this is the expected behavior what is happening to you, that is, he will actually create on both bases. See here: caveats. It is in the topic of Osgi, but the behavior is the same.

To change this behavior you have to find a way to tell Hibernate not to create the tables of everything that he sees ahead, or to make him unaware of the entities that you do not want. There are several ways to do this, some depending on the frameworks you are using (in Spring it is quite simple to configure which packages to scan or have filters), two follow:

• list the entities in each persistence unit that you want Hibernate to manage and tell it to delete those that are not listed, and list those that will be managed by each persistence unit. Will look something like this:

<persistence-unit name="postgreSQL">
    <exclude-unlisted-classes>true</exclude-unlisted-classes>

    <class>com.model.Usuario</class>
</persistence-unit>

<persistence-unit name="mySQL">
    <exclude-unlisted-classes>true</exclude-unlisted-classes>

    <class>com.model.User</class>
</persistence-unit>

• separate the entities of each persistence unit into a project/jar. How by default the project entities will be read, separating into other artifacts you prevent it from knowing, something like that:

  • user-project: contains the entity com.model.User, with their respective persistence.xml with only one persistence unit.
  • user project: contains the entity com.model.Usuario, with their respective persistence.xml with only one persistence unit.

I particularly prefer this second approach in most cases I needed to work with several persistence units, makes it simpler to evolve, you don’t always need to have to configure the entities in the persistence units, but generates a small overhead to manage these packages further, especially if there are common things in the two persistence units (in this case you can configure <jar-file>, at last).

This will make sure that there are no unnecessary tables in the database, but it does not guarantee that the migration will work, since you must ensure that you are working with the EntityManager correct. That is, as an example, let’s assume that we will take the Mysql data and insert it into Postgresql, it would look something like this:

EntityFactory.initializeEntityManager();
EntityFactory.initializeEntityManagerMySql();

final EntityManager emMySQL = EntityFactory.entityManagerFactory.createEntityManager();
final EntityManager emPostgreSQL = EntityFactory.entityManagerFactoryMySQL.createEntityManager();

final List<User> users = emMySQL.createQuery("SELECT u FROM com.model.User u");

final List<Usuario> usuarios = /* converte users para usuarios */;

// faça o tratamento conforme sua necessidade
usuarios.forEach(u -> emPostgreSQL./* atualiza/cria o usuário */);

// continua com o que tem que fazer :)