How to make hibernate read a table in a MySQL database and write to another table in PostgreSQL?

Question

How to make hibernate read a table in a MySQL database and write to another table in PostgreSQL?

Navigation

#1 by (4 votes)

2

I need to query a database in MySQL and get a table from there to write to a PostgreSQL database through Hibernate / JPA.

I configured 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 looks 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 you generate the classes, Hibernate creates both tables in both banks. That is, I need to get from users (MySQL) and write to usuarios (PostgreSQL), but Hibernate creates the users and usuarios tables in MySQL and the users tables and usuarios in PostgreSQL as well, what I want is to just create usuarios on the basis of PostgreSQL only.

Any solution?

java java-ee hibernate jpa

asked by anonymous 12.02.2016 / 22:14

1 answer

How do I use the if and else functions? Get URL ID instead of INDEX

score 4 · Accepted Answer

By default, Hibernate will look for entities that are on the same classpath as the persitence.xml , that is, entities that are in the same jar. As he does this is expected behavior what is happening to you, ie he will even create on both bases. See here: caveats . It's in the OSGi topic, but the behavior is the same.

To change this behavior you have to make sure Hibernate does not create the tables of everything it sees ahead, or cause it to have no knowledge of the entities 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), follow two:

list the entities in each persistence unit that you want Hibernate to manage and tell it to exclude those that are not listed, and list the ones that will be managed by each persistence unit. It 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 entities from each persistence unit in a / jar project. As by default the project entities will be read, separating in other artifacts you prevent it from knowing, something like this:
- project-user : Contains the com.model.User entity, with its persistence.xml with only one persistence unit.
- user-project : Contains the com.model.Usuario entity, with its persistence.xml with only one persistence unit.

I particularly prefer this second approach in most cases I needed to work with multiple persistence units, it makes it simpler to evolve, you do not always have to configure entities in persistence units, but it generates a small overhead to manage these packages more, especially if there are common things in the two persistence units (in this case you can configure <jar-file> , anyway).

This will ensure that there are no unnecessary tables in the databases, but it does not guarantee that the migration will work, since you must ensure that you are working with the correct EntityManager . That is, as an example, let's assume that we will get the data from MySQL 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 :)