MySQL and MongoDB together

Hibernate OGM
1

I have a JPA project which uses mysql. I want to store part of the entities in MongoDB.
Not sure what’s the best way to implement this.
I see there is a thing called Composite Persistence Unit, but I can’t find any example of that.
Maybe just 2 different persistence units are the way to go?
Obviously there are relations between the entities in mysql and the ones I want to move in Mongodb.

2

That does not sound like a good idea.

Why would you want to save entities both in MySQL and MongoDB? If JSON is the only Requiem, then you can use a JSON column in MySQL too.

1 Like
3

This project is my academic thesis. Basically I should answer, with some benchmark, to the question “Is there a performance improvement if we move the heavy part of the data in MongoDB?”
Some data are currently serialized in xml in Mysql column, I should move that to mongodb.

4

If you can manage to store the vats majority of the data in memory, then it won’t make any difference. If the data exceeds the memory, then you can use partitioning. I’m not sure how moving to MongoDB could solve the performance issue especially since the query capabilities are limited on MongoDB, now that MySQL has window functions and CTE.

Anyway, you could use two persistence units: one for Hibernate ORM and the other for Hibernate OGM.

5

If you can manage to store the vats majority of the data in memory, then it won’t make any difference

Indeed I’m in doubt too about the whole thing…

Anyway, how 2 persistence units with 2 different db vendors can communicate (in terms of relations)? Do you know any sample project with 2 PUs?
What’s the differences between a Composite Persistence Unit and 2 different persistence units?

6

Anyway, how 2 persistence units with 2 different db vendors can communicate (in terms of relations)?

They can’t. The only think you could do is issues the joins in the application, but that it’s going to be very inefficient,

What’s the differences between a Composite Persistence Unit and 2 different persistence units?

I’m not sure what exactly you mean by a Composite Persistence Unit. I don’t recall seeing that in the OGM documentation.

7

I guess it’s an EclipseLink feature and not part of JPA specs then

8

Like @Vlad said, you can create 2 separate persistence units but Hibernate OGM does not support associations with entities stored in different dbs. You will need to handle those entities separately.

9

@DavideD let me do this little off-topic: beside Hibernate OGM, do you know any way in Java EE to accomplish that? I mean handle relations in different db vendors?

10

On top of my head, a project I know that can handle pretty much anything is Teiid: http://teiid.jboss.org/

11

Interesting project but it looks it needs a proper Application Server. My project is on Tomcat and currently I cannot change that for Wildfly.

12

I have exactly the same scenario one of my projects. The MySQL database was legacy, but I still want to keep it and the other database is MongoDB.

I have two persistence units defined in the persistance.xml and a MySQL Module in my WildFly Application server.

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns="http://java.sun.com/xml/ns/persistence"
             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="goStopHandleMySQLPersistenceUnit" transaction-type="JTA">

        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <jta-data-source>java:jboss/datasources/NOTiFYwellDS</jta-data-source>

        <properties>

            <!-- <property name="jboss.as.jpa.providerModule" value="org.hibernate:5.2"/> -->
            <property name="jboss.as.jpa.providerModule" value="org.hibernate:5.3"/>

            <property name="hibernate.connection.url" value="jdbc:mysql://127.0.0.1:3306/notifywell"/>
            <!--  <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/> -->
            <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver"/>

            <property name="hibernate.connection.release_mode" value="auto"/>

            <property name="hibernate.connection.username" value="User"/>
            <property name="hibernate.connection.password" value="Password"/>

            <property name="wildfly.jpa.twophasebootstrap" value="false"/>
            <property name="hibernate.cache.use_query_cache" value="false"/>
            <property name="hibernate.cache.use_second_level_cache" value="false"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>

            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>

        </properties>

    </persistence-unit>

    <persistence-unit name="goStopHandleMongoDBPersistenceUnit" transaction-type="JTA">

        <provider>org.hibernate.ogm.jpa.HibernateOgmPersistence</provider>

        <properties>

            <!-- <property name="jboss.as.jpa.providerModule" value="org.hibernate:5.2"/> -->
            <property name="jboss.as.jpa.providerModule" value="org.hibernate:5.3"/>

            <!-- <property name="wildfly.jpa.hibernate.search.module" value="org.hibernate.search.orm:5.8"/> -->
            <property name="wildfly.jpa.hibernate.search.module" value="org.hibernate.search.orm:5.10"/>

            <!-- <property name="hibernate.transaction.jta.platform" value="JBossTS"/> -->
            <!-- <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossAS"/> -->
            <property name="hibernate.transaction.jta.platform" value="org.hibernate.service.jta.platform.internal.JBossStandAloneJtaPlatform"/>
            <property name="hibernate.ogm.datastore.provider" value="org.hibernate.ogm.datastore.mongodb.impl.MongoDBDatastoreProvider"/>
            <property name="hibernate.ogm.datastore.grid_dialect" value="org.hibernate.ogm.datastore.mongodb.MongoDBDialect"/>
            <property name="hibernate.ogm.datastore.database" value="goStopHandleDB"/>
            <property name="hibernate.ogm.mongodb.host" value="127.0.0.1"/>
        </properties>
    </persistence-unit>

</persistence>

In the Java EJBs II have the two Persistence Units:

    /**
     *
     */
    @PersistenceContext(unitName = "goStopHandleMongoDBPersistenceUnit")
    private EntityManager mongoDBEntityManager;

    /**
     *
     */
    @PersistenceContext(unitName = "goStopHandleMySQLPersistenceUnit")
    private EntityManager mySQLEntityManager;

The Entities for MySQL & MongoDB are just under different package names.