NoSQL Inside SQL with Java, Spring, Hibernate, and PostgreSQL

There are many benefits to schema-less NoSQL datastores, but there are always trade-offs. The primary gift the NoSQL movement has given us is the variety of options we now have for data persistence. With NoSQL we no longer must try to shoehorn everything into a relational model. Now the challenge is in deciding which persistence model fits best with each domain in a system and then combining those models in a cohesive way. The general term to describe this is Polyglot Persistence and there are many ways to accomplish it. Lets walk through how you can combine a regular SQL model with a key-value NoSQL model using Java, Spring, Hibernate, and PostgreSQL.

This article covers the pieces of a simple web application which uses regular SQL and PostgreSQL’s hstore for key value pairs. This method is a mix of NoSQL inside SQL. One benefit of this approach is that the same datastore can be used for both the SQL and the NoSQL data.

In this example the server technologies will be Java, Spring, and Hibernate. (The same thing can also be done with Rails, Django, and many other technologies.) To add Hibernate support for hstore I found a fantastic blog about “Storing sets of key/value pairs in a single db column with Hibernate using PostgreSQL hstore type“. I won’t go through that code here but you can find everything in the GitHub repo for my demo project.

This demo app uses Maven to define the dependencies. Embedded Jetty is started via a plain ‘ole Java application that sets up Spring MVC. Spring is configured via Java Config for the main stuff, the web stuff, and the database stuff.

The client technologies will be jQuery and Bootstrap and there is a strict seperation between the client and server via RESTful JSON services. The whole client-side is in a plain ‘ole HTML file. Via jQuery / Ajax the client communicates to JSON services exposed via a Spring MVC Controller.

Ok. Now onto the NoSQL inside SQL stuff. This application stores “Contacts” that have a name but also can have many “Contact Methods” (e.g. phone numbers and email addresses). The “Contact Methods” are a good use of a schema-less, key-value pair column because it avoids the cumbersome alternatives: putting that information into a separate table or trying to create a model object that has all of the possible “Contact Methods”. So lets take a look at the simple Contact Entity:

package com.jamesward.model;
import net.backtothefront.HstoreUserType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import java.util.HashMap;
import java.util.Map;
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
public class Contact {
    public Integer id;
    @Column(nullable = false)
    public String name;
    @Type(type = "hstore")
    @Column(columnDefinition = "hstore")
    public Map<String, String> contactMethods = new HashMap<String, String>();

If you are familiar with Hibernate / JPA then most of this should look pretty familiar to you. The new / interesting stuff is the contactMethods property. It is a Map<String, String> and it uses PostgreSQL’s hstore datatype. In order for that to work, the type has to be defined and the columnDefinition set. Thanks again to Jakub Głuszecki for putting together the HstoreHelper and HstoreUserType that make this possible.

Now the rest is simple because it’s just plain Hibernate / JPA. Here is the ContactService that does the basic query and updates:

package com.jamesward.service;
import com.jamesward.model.Contact;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaQuery;
import java.util.List;
public class ContactServiceImpl implements ContactService {
    EntityManager em;
    public void addContact(Contact contact) {
    public List<Contact> getAllContacts() {
        CriteriaQuery<Contact> c = em.getCriteriaBuilder().createQuery(Contact.class);
        return em.createQuery(c).getResultList();
    public Contact getContact(Integer id) {
        return em.find(Contact.class, id);
    public void addContactMethod(Integer contactId, String name, String value) {
        Contact contact = getContact(contactId);
        contact.contactMethods.put(name, value);

Now that you understand how it all works, check out a live demo on Heroku.

If you want to run this app locally or on Heroku, then first you need to grab the source code and continue working inside the newly created spring_hibernate_hstore_demo directory:

$ git clone
$ cd spring_hibernate_hstore_demo

To run locally:

  1. Setup your PostgreSQL database to support hstore by opening a psql connection to it:
    $ psql -U username -W -h localhost database
  2. Then enable hstore:
    => create extension hstore;
    => \q
  3. Build the app (depends on having Maven installed):
    $ mvn package
  4. Set the DATABASE_URL environment variable to point to your PostgreSQL server:
    $ export DATABASE_URL=postgres://username:password@localhost/databasename
  5. Start the app:
    $ java -cp target/classes:target/dependency/* com.jamesward.Webapp
  6. Try it out

Cool! Now you can run it on the cloud with Heroku. Here is what you need to do:

  1. Install the Heroku Toolbelt
  2. Login to Heroku:
    $ heroku login
  3. Create a new app:
    $ heroku create
  4. Add Heroku Postgres:
    $ heroku addons:add heroku-postgresql:dev
  5. Tell Heroku to set the DATABASE_URL environment variable based on the database that was just added (replace YOUR_HEROKU_POSTGRESQL_COLOR_URL with your own):
    $ heroku pg:promote YOUR_HEROKU_POSTGRESQL_COLOR_URL
  6. Open a psql connection to the database:
    $ heroku pg:psql
  7. Enable hstore support in your database:
    => create extension hstore;
    => \q
  8. Deploy the app:
    $ git push heroku master
  9. View the app on the cloud:
    $ heroku open

Fantastic! Let me know if you have any questions.

  • kusoksna

    What about getting users that have “email” that end with “” ?

    • What do you mean?

      • kusoksna

        I mean that sometimes you want to get data from database based on specific values from your hstore. It would be good to include in article how it could be achieved with hibernate/jpa

    • I do this by making a @Query in the @Repository interface (plus note, this example uses paging – from spring data jpa).

      @Query(“SELECT e FROM Contact e WHERE upper( like upper(:name))
      public Page findLikeName(@Param(“name”) String name, Pageable page);

  • Brian Quinn

    I’m new to heroku. Ran this example locally and worked great. Deployed to heroku and it seems like the js didn’t run. I see the Contacts header, but no inputs. Any ideas?

    • Hmmm… Can you see if you are getting any errors in your browser console or in the heroku logs?

      • Brian Quinn

        I cloned your repo to start with and that’s working fine locally. I was able to track down the error – Postgres is refusing the connection in heroku. I followed your steps above and if I run ‘heroku config’ I see the DATABASE_URL matches the HEROKU_POSTGRES_SILVER_URL I got from the addon step

        • You can login to and do more testing from there. But it seems like this should be working.

          • Brian Quinn

            Not sure what happened, but I made a new db and put in a useless commit and that got me up and running. Thanks for helping

  • Navjot

    i am able to do all the steps except the Step no 5 To start the app.I have git bash and postgresql database installed. when i do Step no 5 it says
    Error: Could not find or load main class com.jamesward.Webapp
    please help

    • The `mvn package` command worked? If you are on Windows you might need to quote the classpath:
      java -cp "target/classes:target/dependency/*" com.jamesward.Webapp

      • Navjot

        it worked thanks :) As i was doing those steps in git bash but i have windows 7 ,there i have to set the variable not export it. I think you have done it for unix .For running the app while setting the classpath in windows it takes semicolon like java -cp target/classes;target/dependency/* com.jamesward.Webapp
        Any how it worked thanx :)

  • Syed

    Hi, i am new to NoSql as in the above Example you have used NoSql with PostgreSQL is it possible to use MySQL or Oracle instead of that?


    • I’m not sure if MySQL and Oracle have similar capabilities. Sorry.

  • Hello James, I try your article with Play! 1. It’s works but I don’t know how to find object with find method ? Have you got an idea

  • Hello James. I try your article with Play! 1. But I don’t know how to use find method with hstore (Ex. Contact.find(“byEmail”, email).first()) ?

    • I don’t think this will work with Play 1. Sorry.

      • Alan

        Thanks. I try with this Contact.em().createNativeQuery(“select * from contact where contactMethods->’email’ = ?”).setParameter(1, email).getSingleResult(); but i have this error PersistenceException occured : org.hibernate.MappingException: No Dialect mapping for JDBC type: 2003… Any idea ?

  • Andreas Schöneck
  • Yvan Dupre

    any way to query contactMethods?