27 January 2015

Starting PostgreSQL version 9.0, the PostgreSQL hstore datatype data type was introduced, which allows for the storing of arbitrary sets of key/value pairs within a single column. This data type can be leveraged for a bunch of different situations, such as for storing custom/random attributes, semi-structured data or in any scenario where the number and type of columns associated with an entity stored in a table varies. As with most new/database vendor-specific features, support for the PostgreSQL hstore datatype data type does not exist as a first-level data type in the PostgreSQL dialect provided by Hibernate. However, both JPA and Hibernate provide mechanisms that allow you to customize the way in which data stored in an entity is prepared for insertion into a database table and vice-versa. The JPA Attribute Converter specification allows you to provide a custom AttributeConverter implementation that governs how data stored in a field of an entity gets converted for storage in the database and how to convert the column data back into a Java type. Normally, this would be how you would implement support for an un-supported data type. However, due to an Hibernate issue with Converters and parameterized types, this won’t work for the PostgreSQL hstore datatype, unless you want to map it to a Java String type instead of a Java Map<String,Object> type, which makes more sense for an arbitrary key/value store. The solution? Leverage Hibernate’s Custom Types support!

package com.example.jpa.support;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

/**
 * Custom Hibernate {@link UserType} used to convert between a {@link Map}
 * and PostgreSQL {@code hstore} data type.
 */
public class HstoreUserType implements UserType {

    /**
     * PostgreSQL {@code hstore} field separator token.
     */
    private static final String HSTORE_SEPARATOR_TOKEN = "=>";

    /**
     * {@link Pattern} used to find and split {@code hstore} entries.
     */
    private static final Pattern HSTORE_ENTRY_PATTERN = Pattern.compile(String.format("\"(.*)\"%s\"(.*)\"", HSTORE_SEPARATOR_TOKEN));

    /**
     * The PostgreSQL value for the {@code hstore} data type.
     */
    public static final int HSTORE_TYPE = 1111;

    @Override
    public int[] sqlTypes() {
        return new int[] { HSTORE_TYPE };
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Map.class;
    }

    @Override
    public boolean equals(final Object x, final Object y) throws HibernateException {
        return x.equals(y);
    }

    @Override
    public int hashCode(final Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(final ResultSet rs, final String[] names,
            final SessionImplementor session, final Object owner)
            throws HibernateException, SQLException {
        return convertToEntityAttribute(rs.getString(names[0]));
    }

    @SuppressWarnings("unchecked")
    @Override
    public void nullSafeSet(final PreparedStatement st, final Object value, final int index,
            final SessionImplementor session) throws HibernateException, SQLException {
        st.setObject(index, convertToDatabaseColumn((Map<String,Object>)value), HSTORE_TYPE);

    }

    @SuppressWarnings("unchecked")
    @Override
    public Object deepCopy(final Object value) throws HibernateException {
        return new HashMap<String,Object>(((Map<String,Object>)value));
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(final Object value) throws HibernateException {
        return (Serializable) value;
    }

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

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


    private String convertToDatabaseColumn(final Map<String, Object> attribute) {
        final StringBuilder builder = new StringBuilder();
        for (final Map.Entry<String, Object> entry : attribute.entrySet()) {
            if(builder.length() > 1) {
                builder.append(", ");
            }
            builder.append("\"");
            builder.append(entry.getKey());
            builder.append("\"");
            builder.append(HSTORE_SEPARATOR_TOKEN);
            builder.append("\"");
            builder.append(entry.getValue().toString());
            builder.append("\"");
        }
        return builder.toString();
    }

    private Map<String, Object> convertToEntityAttribute(final String dbData) {
        final Map<String, Object> data = new HashMap<String, Object>();
        final StringTokenizer tokenizer = new StringTokenizer(dbData, ",");

        while(tokenizer.hasMoreTokens()) {
            final Matcher matcher = HSTORE_ENTRY_PATTERN.matcher(tokenizer.nextToken().trim());
            if(matcher.find()) {
                data.put(matcher.group(1), matcher.group(2));
            }
        }

        return data;
    }
}

The example above implements the Hibernate UserType interface and provides methods (nullSafeGet and nullSafeSet) to handle the conversion. It also provides the appropriate data type value for the PostgreSQL hstore datatype (1111). Now that you have a custom UserType implementation for the PostgreSQL hstore datatype, the next step is to annotate your entity class to instruct JPA to use the custom type during mapping:

package com.example.jpa.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

import com.example.jpa.support.HstoreUserType;

@Entity
@Table(name = "books")
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
public class BookEntity {

    @Column(name="metadata")
    @Type(type="hstore")
    private Map<String,Object> metadata;

    ...
}

The TypeDef is given a name so that it can be referenced when applied to the field via the Type annotation. Now, when data is persisted or retrieved, our custom UserType implementation will be invoked to handle the data. It is also worth noting that the PostgreSQL JDBC driver library does come with an HStoreConverter class with static methods that could be leveraged from inside the custom UserType to handle the conversion to/from a Map<String,Object> to a String and vice versa. However, I would not recommend an implementation that depends on an internal class from the JDBC driver, as it may cause compatibility issues if you upgrade.

comments powered by Disqus