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 separation 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 contactMethods = new HashMap();  } 

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 getAllContacts() {         
          CriteriaQuery 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



$ 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:[email protected]/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.

James Ward

James Ward

James Ward is a Principal Platform Evangelist at James frequently presents at conferences around the world such as JavaOne, Devoxx, and many other Java get-togethers. Along with Bruce Eckel, James co-authored First Steps in Flex.

This article is by James Ward from

Why Programmers Don’t Like Relational Databases

Complaining about relational databases is a staple theme of programmer blogs. Why are so many programmers irritated and frustrated with relational databases? Why do the perceived intricacies of SQL and the “object-relational impedance mismatch” launch so many rants? Why are DBAs more hated than managers? I have some ideas.

Programmers Conflate RDBMSs with SQL
SQL is the standard language for describing, querying, and manipulating data residing in a Relational Database Management System (RDBMS). The deficiencies of SQL, real or imagined, have little to do with relational theory, data integrity, or database management. The SQL language has been criticized by the most distinguished proponents of relational databases. Once you learn relational theory and how (and why) to normalize, SQL’s problems will matter a lot less.

RDBMSs Are Old, Like COBOL and Fortran
Most programmers don’t find databases interesting or fun, so they dismiss databases as “legacy” technology that doesn’t know when it’s time to die. RDBMSs are approximately as old as the C language, and younger than Lisp and Forth, which are trendy again. RDBMSs are still around for two big reasons: There is no serious competing data management technology, and a huge amount of data is committed to RDBMSs. If there was an alternative technology that solved the problems RDBMSs solve so well and offered additional advantages database-based applications would be slowly migrating. Instead we have MySQL and SQLite, low-end open source implementations of 35-year-old technology.

There Are Too Many Incompatible Versions Of SQL
Although SQL is an ANSI standard every vendor has extended the language and implemented parts of the language differently. Oracle’s dialect has some serious deviations from the standard, but then again Oracle and a lot of their installed base pre-date the ANSI standard. The differences between SQL dialects are frequently exaggerated by programmers. In real life converting from one RDBMS to another is such a major undertaking that resolving differences in SQL dialects is only a small part of the problem. Writing a database library that can support multiple RDBMSs is obviously complicated by multiple implementations, but this is a problem with a lot of languages and tools, not specifically an RDBMS or SQL problem.

Who Needs Theory — Just Give Me The Data
Because most of what programmers do has no theoretical basis or system of proof, programmers back away from anything that looks like hard math. Failure to understand and appreciate relational theory has launched countless bad databases (and thousands of witless articles condemning SQL). The idea that there is a right way to design a normalized database–and that the wrong ways are provably wrong–is alien to most programmers. Database management doesn’t come down to a matter of opinion like OOP class hierarchies or coding style.

Most professional programmers know how it feels to see an amateur, unfamiliar with Knuth or any programming book containing equations, implement their own sort routine. That’s how people who understand relational theory feel when they see a badly-designed database. Relational theory and RDBMSs are old and well-established now, so it’s hard not to think a lot of programmers are willfully ignorant.

Relational Databases Don’t Play Nice With My Objects
When OOP took over as the dominant programming paradigm all of the older techniques and technologies were dismissed as bad and unenlightened. In the case of RDBMSs this scorn has a technical name: Object-Relational Impedance Mismatch. RDBMSs can be used as backing store for objects, but mapping object relationships, hierarchies, and references to and from a database is tedious and ugly. A generic code layer to do this (called an Object-Relational Mapping or ORM) is complicated enough, but the incompatibilities of RDBMS vendors and versions (see above) make ORMs really hard to get right. Relational databases are based on sets, not on objects, and relations in a database are based on matching values, not on pointers. There’s definitely an apples vs. oranges problem, but neither technology is necessarily wrong or bad. Object databases may eventually offer a workable solution to the object-relational mismatch, but without a theoretical basis comparable to relational theory it will be hard to make a case for moving valuable data out of RDBMSs.

Some of the most entertaining complaints about the “impedance mismatch” are written by Ruby on Rails advocates–entertaining because by design Rails can’t play nice with legacy databases. The right opinion to have in that community is that legacy databases should be replaced, DBAs should be bypassed by programmers installing MySQL on their own servers, and data integrity should be enforced in every application that uses the data.

Relational Databases Don’t Play Nice With Anything
SQL is a declarative language. Most programmers work with imperative or functional languages, where the code describes how to do something. The SQL SELECT statement may look like a command telling the database which rows (records) to retrieve, but really it’s a description of the relations in the database that satisfy the conditions in the statement. In addition to the imperative/declarative disconnect the data types commonly supported in RDBMSs don’t always map cleanly to the host or calling language. This problem has been around for a long time–OOP just exposed it to a new generation of programmers. One partial solution is embedded SQL, where SQL statements are part of the host language, the types are mapped behind the scenes, and imperative control structures (like loops) can hide RDBMS features like cursors. When tempted to kick RDBMSs out of the playground remember they were probably there first.

Database Administrators (DBAs) Cramp My Style
Companies commit a lot of important and valuable data to RDBMSs, and DBAs are charged with making sure that data is secure, backed up, and protected from programmers deploying broken and untested code. Whatever programmers think of themselves, losing data is just as disastrous as–and frequently more permanent than–a crashing program. Maintaining data integrity is what RDBMSs do, and DBAs are supposed to make sure the RDBMS is allowed to do that. Every DBA I’ve worked with has a lot of stories about programmers losing or corrupting big chunks of valuable data, but programmers mainly complain that DBAs won’t let them do whatever they want. DBAs are called “high priests” and derided as petty tyrants. Some DBAs are incompetent or difficult, but that’s true of programmers, too. A good DBA can design correct, robust databases and help programmers access databases efficiently and safely.

Before flying off the handle about DBAs, consider that the database probably has to support multiple applications, simultaneously, preventing each application from corrupting data or stomping on the other applications. This is a pretty big problem and RDBMSs have offered a workable, scalable solution for a long time.

Recommended Database and SQL Books

Greg Jorgensen

Greg Jorgensen

Itinerant code and database programmer, fixer of broken software and web sites, digital nomad. Typical Programmer

This article is by Greg Jorgensen from