# MySQL and MongoDB together

**URL:** <https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846>\
**Category:** Hibernate OGM\
**Created:** [December 7, 2018, 2:32pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846 "2018-12-07T14:32:05Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![alfredopacino](https://avatars.discourse-cdn.com/v4/letter/a/90ced4/32.png) [@alfredopacino](https://discourse.hibernate.org/u/alfredopacino)\
**Post date:** [December 7, 2018, 2:32pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/1 "2018-12-07T14:32:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [December 8, 2018, 4:05pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/2 "2018-12-08T16:05:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![alfredopacino](https://avatars.discourse-cdn.com/v4/letter/a/90ced4/32.png) [@alfredopacino](https://discourse.hibernate.org/u/alfredopacino)\
**Post date:** [December 8, 2018, 5:31pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/3 "2018-12-08T17:31:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [December 8, 2018, 6:06pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/4 "2018-12-08T18:06:26Z")

</div>

> [@alfredopacino](#):
>
> Is there a performance improvement if we move the heavy part of the data in MongoDB?

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.

---

<div class="post-metadata">

**Author:** ![alfredopacino](https://avatars.discourse-cdn.com/v4/letter/a/90ced4/32.png) [@alfredopacino](https://discourse.hibernate.org/u/alfredopacino)\
**Post date:** [December 8, 2018, 6:31pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/5 "2018-12-08T18:31:26Z")

</div>

> 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?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [December 8, 2018, 6:49pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/6 "2018-12-08T18:49:04Z")

</div>

> 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.

---

<div class="post-metadata">

**Author:** ![alfredopacino](https://avatars.discourse-cdn.com/v4/letter/a/90ced4/32.png) [@alfredopacino](https://discourse.hibernate.org/u/alfredopacino)\
**Post date:** [December 8, 2018, 6:51pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/7 "2018-12-08T18:51:42Z")

</div>

> [@vlad](#):
>
> Composite Persistence Unit

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

---

<div class="post-metadata">

**Author:** ![DavideD](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@DavideD](https://discourse.hibernate.org/u/DavideD)\
**Post date:** [December 8, 2018, 8:39pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/8 "2018-12-08T20:39:14Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![alfredopacino](https://avatars.discourse-cdn.com/v4/letter/a/90ced4/32.png) [@alfredopacino](https://discourse.hibernate.org/u/alfredopacino)\
**Post date:** [December 9, 2018, 1:37am UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/9 "2018-12-09T01:37:22Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![DavideD](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@DavideD](https://discourse.hibernate.org/u/DavideD)\
**Post date:** [December 10, 2018, 4:13pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/10 "2018-12-10T16:13:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![alfredopacino](https://avatars.discourse-cdn.com/v4/letter/a/90ced4/32.png) [@alfredopacino](https://discourse.hibernate.org/u/alfredopacino)\
**Post date:** [December 11, 2018, 9:13pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/11 "2018-12-11T21:13:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![NOTiFY](https://avatars.discourse-cdn.com/v4/letter/n/eada6e/32.png) [@NOTiFY](https://discourse.hibernate.org/u/NOTiFY)\
**Post date:** [January 3, 2019, 11:07pm UTC](https://discourse.hibernate.org/t/mysql-and-mongodb-together/1846/12 "2019-01-03T23:07:25Z")

</div>

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

```auto
<?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:

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

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

```

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