Storing sets of key/value pairs in a single db column with Hibernate using PostgreSQL hstore type

Posted by Jakub Głuszecki on Sep 16, 2011 in Java EE | 2 comments

Few years ago I was working on a platform for creating classified ads websites. It was an international project and first implementation of our website was in Russia – 140 millions people, so performance was our main concern.

Our platform was based on a J2EE webapp with Struts(ouch), Spring 2, Hibernate 2 and a PostgreSQL (7.x AFAIR) cluster.

Ads were categorized and each ad had a set of attributes(key/value pairs) dependend of category they belonged. Our first approach was to model it using a table with each row representing one key/value pair, but – long story short – it didn’t perform well.

Then our DBA came with a solution:
Hstore – contrib module for storing (key,value) pairs
You can read more about hstore in PostgreSQL reference, as since version 8.2 it is a part of a distribution.

You can also read some articles about hstore on Depesz’s Blog (two oldest posts are in polish).

Our classified ads websites were successfully deployed in many european countries, and our solution performed good.
It was all in 2006.
Recently I was considering hstore in my project based on Ruby On Rails. I googled quickly how to handle hstore in RoR, there was even a plugin ready for download on Github. I was curious if there is currently any solution for Hibernate, but I found nothing. So I decided to sit and write this article.
I don’t have access to the source code of our old platform, and it was in the dark ages of Java 1.4, Hibernate 2, XDoclet and other terrible things, so I wrote some code from the scratch.
Basically there are two approaches:

• implementing own UserType and map a field to a column using @Column
• mark field as @Transient and handle it via JDBC, maybe using some interceptors

I will cover the first approach.
I assume You got the idea of hstore and i will focus only on Hibernate code.
Lets write a helper for converting a Map to a String conforming to hstore syntax and vice versa:

public class HstoreHelper {

	private static final String K_V_SEPARATOR = "=>";

	public static String toString(Map<String, String> m) {
		if (m.isEmpty()) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		int n = m.size();
		for (String key : m.keySet()) {
			sb.append(key + K_V_SEPARATOR + m.get(key));
			if (n > 1) {
				sb.append(", ");
				n--;
			}
		}
		return sb.toString();
	}

	public static Map<String, String> toMap(String s) {
		Map<String, String> m = new HashMap<String, String>();
		if (! StringUtils.hasText(s)) {
			return m;
		}
		String[] tokens = s.split(", ");
		for (String token : tokens) {
			String[] kv = token.split(K_V_SEPARATOR);
			String k = kv[0];
			k = k.trim().substring(1, k.length() - 2);
			String v = kv[1];
			v = v.trim().substring(1, v.length() - 2);
			m.put(k, v);
		}
		return m;
	}
}

Here are some basic tests:

public class HstoreHelperTest {
	
	@Test
	public void testEmptyMapToString() {
		Map m = new HashMap();
		assertEquals("", HstoreHelper.toString(m));
	}

	@Test
	public void testToString() {
		Map m = new HashMap();
		m.put("foo", "bar");
		assertEquals("foo=>bar", HstoreHelper.toString(m));
	}
	
	@Test
	public void testToString2() {
		Map m = new LinkedHashMap();
		m.put("foo", "bar");
		m.put("xxx", "yyy");
		assertEquals("foo=>bar, xxx=>yyy", HstoreHelper.toString(m));
	}
	
	@Test
	public void testEmptyStringToMap() {
		Map m = HstoreHelper.toMap("");
		assert(m.isEmpty());
	}
	
	@Test
	public void testToMap() {
		Map m = HstoreHelper.toMap("\"foo\" => \"bar\"");
		assertEquals(1, m.size());
		assertEquals("bar", m.get("foo"));
	}
	
	@Test
	public void testToMap2() {
		Map m = HstoreHelper.toMap("\"foo\" => \"bar\", \"xxx\" => \"yyy\"");
		assertEquals(2, m.size());
		assertEquals("bar", m.get("foo"));
		assertEquals("yyy", m.get("xxx"));
	}
}

And now a UserType implementation:

public class HstoreUserType implements UserType {

	public Object assemble(Serializable cached, Object owner)
			throws HibernateException {
		return cached;
	}

	public Object deepCopy(Object o) throws HibernateException {
		// It's not a true deep copy, but we store only String instances, and they
		// are immutable, so it should be OK
		Map m = (Map) o;
		return new HashMap(m);
	}

	public Serializable disassemble(Object o) throws HibernateException {
		return (Serializable) o;
	}

	public boolean equals(Object o1, Object o2) throws HibernateException {
		Map m1 = (Map) o1;
		Map m2 = (Map) o2;
		return m1.equals(m2);
	}

	public int hashCode(Object o) throws HibernateException {
		return o.hashCode();
	}

	public boolean isMutable() {
		return true;
	}

	public Object nullSafeGet(ResultSet rs, String[] arg1, Object arg2)
			throws HibernateException, SQLException {
		String col = arg1[0];
		String val = rs.getString(col);
		return HstoreHelper.toMap(val);
	}

	public void nullSafeSet(PreparedStatement ps, Object obj, int i)
			throws HibernateException, SQLException {
		String s = HstoreHelper.toString((Map) obj);
		ps.setObject(i, s, Types.OTHER);
	}

	public Object replace(Object original, Object target, Object owner)
			throws HibernateException {
		return original;
	}

	public Class returnedClass() {
		return Map.class;
	}

	public int[] sqlTypes() {
		/*
		 * i'm not sure what value should be used here, but it works, AFAIK only
		 * length of this array matters, as it is a column span (1 in our case)
		 */
		return new int[] { Types.INTEGER };
	}
}

Finally, let’s annotate our entity bean.

@Entity
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
public class Item {
    [...]

    @Type(type = "hstore")
    @Column(columnDefinition = "hstore not null")
    private Map<String, String> properties = new HashMap<String, String>();

    [...]
}

Some things to take into consideration:

• This is not a complete solution, I just wanted to show You how to do it. I made only basic integration tests.
• Maybe it would be better to use LinkedHashMap or some other sort of Map that preserves order.
• It handles only String values, if You need some other types you need to convert them on your own
• Some HstoreUserType methods should check for null values (e.g. nullSafeGet, nullSafeSet), but I assumed ‘properties’ to be not null, to make code shorter

When writing this article I was using:

• Hibernate 3.6.0
• PostgreSQL 8.4.8
• PostgreSQL JDBC driver 8.4-701.jdbc4

Please leave some feedback if it helped You, maybe some day I will make it complete and package it as a JAR.