Saturday, 29 October 2011

Example cross-store with MongoDB and MySQL

I was recently asked how to handle the situation where an application would like to use both a traditional SQL store as well as a NoSQL store. The answer surprises many people, as it is actually straightforward.

Here is a quick example of a Spring application that uses both MongoDB and MySQL.

Lets say I have an application that maintains products however the business would like to maintain completely different properties based upon different product categories. New products, properties and categories are also being introduced at every sprint. 

One simple way to solve this would be to store the id of the products in MySQL so that we can reference the product ids within our existing SQL statements however the meta-data about the product will be stored in MongoDB. At first glance this may sound complex but it is actually really simple to implement.

Step 1: Domain Model
In this example, a Product is classified as an entity and ProductInfo as component of Product. Lets first create a simple Product entity class with the id and a ProductInfo class with simple properties. As Product is an entity, simply mark the class with the standard JPA annotation @Entity and the id attribute with the @Id annotation.

As ProductInfo is a component of Product, simply create a ProductInfo attribute within Product and mark the attribute with the Spring Data annotation @RelatedDocument. This annotation informs Spring Data that the ProductInfo should be maintained within a document store and not the JPA store.

That is it for the domain model. Here is my domain model: 

Step 2: Repository
Lets define a simple product repository that allows simple CRUD operations on a Product.  Create an interface called ProductRepository and extend the out of the box Spring Data CRUD repository.  You could add custom finders to the ProductRepository if you wish however for simplicity within this blog I am just sticking to the basic CRUD operations provided by Spring Data.

Here is my ProductRepository

Step 3:  JPA Spring Configuration
If you are familiar with JPA configuration with Spring then there is nothing special here. Simply define a your Data Source, Entity Manager Factory and Transaction Manager as you would do normally.

We also need to point Spring Data to our repository package. In this example the JPA repository is our parent repository so use the JPA namespace to define the base repository package.

Here is my JPA spring configuration:

Step 4:  MongoDB Spring Configuration
In this example the MongoDB Spring configuration can be broken down two parts. The first is the configuration of your MongoDB server and MongoTemplate.

The second part is to configure the MongoDB aspects used by Spring Data cross-store support. These aspects enable Spring Data to intercept repository calls and associate the appropriate data store.

Step 5: Maven Configuration
The Spring Data cross-store support uses compile time AspectJ aspects. In Maven simple add the additional plugin to the <build> section of the pom:

Step 6:  Test Application
Lets throw together a quick test application that creates a product and uses the repository to save our entity.

After running the test application, MySQL now has the following Product table:

MongoDB has the collection “org.charris.examples.crosstore.domain.Product”:

Enjoy J


  1. Chris, great writeup. I tried running your implementation and it works great.

    I've tried extending this scenario to update Product and ProductInfo on a subsequent read/write. I'm finding that the MongoChangeSetPersister isn't picking up changes to ProductInfo, whilst Product is being saved properly.

    Have you tried this? Any thoughts/gotchas to watch out for?

    1. It not working for me . Can you please send yourworking source code?

  2. This comment has been removed by the author.

  3. In order for me to get this to work, I added the following to the pom.xml:


    This allowed me to run 'mvn exec:java' to Test the code.

    Note: The package structure of the crossstore has changed since this example was created.

    1. Hey Java developer, i am not able to run above code. Please send me working code as java project.


  4. Thank you this sample help me to clear my concept

  5. The above documentation is quite old, which makes it is difficult to configure; there are many dependencies between libraries and some apis changed. I managed to make it compile, but still am unable to save the data.

    Please update above code with latest dependencies or post correct github url for the same.

  6. Hey,
    is not opening