Mapping postgreSQL JSON column to Hibernate value type

PostgreSQL is excessively,annoyingly strict about data type conversions. It won't implicitly casttexteven to text-like values such asxmlandjson.

The strictly correct way to solve this problem is to write a custom Hibernate mapping type that uses the JDBCsetObjectmethod. This can be a fair bit of hassle,so you might just want to make PostgreSQL less strict by creating a weaker cast.

As noted by @markdsievers in the comments andthis blog post,the original solution in this answer bypasses JSON validation. So it's not really what you want. It's safer to write:

CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$ SELECT json_in($1::cstring); $$ LANGUAGE SQL IMMUTABLE; CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;

AS IMPLICITtells PostgreSQL it can convert without being explicitly told to,allowing things like this to work:

regress=# CREATE TABLE jsontext(x json); CREATE TABLE regress=# PREPARE test(text) AS INSERT INTO jsontext(x) VALUES ($1); PREPARE regress=# EXECUTE test('{}') INSERT 0 1

Thanks to @markdsievers for pointing out the issue.

If you're interested,here are a few code snippets to get the Hibernate custom user type in place. First extend the PostgreSQL dialect to tell it about the json type,thanks to Craig Ringer for the JAVA_OBJECT pointer:

import org.hibernate.dialect.PostgreSQL9Dialect; import java.sql.Types; /** * Wrap default PostgreSQL9Dialect with 'json' type. * * @author timfulmer */ JsonPostgreSQLDialect extends PostgreSQL9Dialect { JsonPostgreSQLDialect() { super(); this.registerColumnType(Types.JAVA_OBJECT, "json"); } }

Next implement org.hibernate.usertype.UserType. The implementation below maps String values to the json database type,and vice-versa. Remember Strings are immutable in Java. A more complex implementation could be used to map custom Java beans to JSON stored in the database as well.

package foo; import org.hibernate.HibernateException; import org.hibernate.engine.spi.SessionImplementor; import org.hibernate.usertype.UserType; import java.io.Serializable; PreparedStatement; ResultSet; SQLException; /** * @author timfulmer */ StringJsonUserType implements UserType { /** * Return the SQL type codes for the columns mapped by this type. The * codes are defined on <tt>java.sql.Types</tt>. * * @return int[] the typecodes * @see java.sql.Types */ @Override int[] sqlTypes() { return new int[] { Types.JAVA_OBJECT}; } /** * The class returned by <tt>nullSafeGet()</tt>. * * @return Class */ Class returnedClass() { return String.class; } /** * Compare two instances of the class mapped by this type for persistence "equality". * Equality of the persistent state. * * @param x * @param y * @return boolean */ boolean equals(Object x, Object y) throws HibernateException { if( x== null){ return y== null; } return x.equals( y); } /** * Get a hashcode for the instance,consistent with persistence "equality" */ int hashCode(Object x) return x.hashCode(); } /** * Retrieve an instance of the mapped class from a JDBC resultset. Implementors * should handle possibility of null values. * * @param rs a JDBC result set * @param names the column names * @param session * @param owner the containing entity @return Object * @throws org.hibernate.HibernateException * * @throws java.sql.SQLException */ Object nullSafeGet(ResultSet rs,175)">String[] names,175)">SessionImplementor session,175)">Object owner) HibernateException,175)">SQLException { if(rs.getString(names[0]) == return rs.getString(names[0]); } /** * Write an instance of the mapped class to a prepared statement. Implementors * should handle possibility of null values. A multi-column type should be written * to parameters starting from <tt>index</tt>. * * @param st a JDBC prepared statement * @param value the object to write * @param index statement parameter index * @param session * @throws org.hibernate.HibernateException * * @throws java.sql.SQLException */ void nullSafeSet(PreparedStatement st,175)">Object value, int index,175)">SessionImplementor session) if (value == null) { st.setNull(index,175)">Types.OTHER); return; } st.setObject(index, value,175)">Types.OTHER); } /** * Return a deep copy of the persistent state,stopping at entities and at * collections. It is not necessary to copy immutable objects,or null * values,in which case it is safe to simply return the argument. * * @param value the object to be cloned,which may be null * @return Object a copy */ Object deepCopy(Object value) return value; } /** * Are objects of this type mutable? * * @return boolean */ boolean isMutable() { true; } /** * Transform the object into its cacheable representation. At the very least this * method should perform a deep copy if the type is mutable. That may not be enough * for some implementations,however; for example,associations must be cached as * identifier values. (optional operation) * * @param value the object to be cached * @return a cachable representation of the object * @throws org.hibernate.HibernateException * */ Serializable disassemble(return (String)this.deepCopy( value); } /** * Reconstruct an object from the cacheable representation. At the very least this * method should perform a deep copy if the type is mutable. (optional operation) * * @param cached the object to be cached * @param owner the owner of the cached object * @return a reconstructed object from the cachable representation * @throws org.hibernate.HibernateException * */ Object assemble(Serializable cached,139)">this.deepCopy( cached); } /** * During merge,replace the existing (target) value in the entity we are merging to * with a new (original) value from the detached entity we are merging. For immutable * objects,or null values,it is safe to simply return the first parameter. For * mutable objects,it is safe to return a copy of the first parameter. For objects * with component values,it might make sense to recursively replace component values. * * @param original the value from the detached entity being merged * @param target the value in the managed entity * @return the value to be merged */ Object replace(Object original,175)">Object target,139)">return original; } }

Now all that's left is annotating the entities. Put something like this at the entity's class declaration:

@TypeDefs( {@TypeDef( name= "StringJsonObject", typeClass = StringJsonUserType.class)})

Then annotate the property:

@Type(type = "StringJsonObject") String getBar() { return bar; }

Hibernate will take care of creating the column with json type for you,and handle the mapping back and forth. Inject additional libraries into the user type implementation for more advanced mapping.

Here's a quick sample GitHub project if anyone wants to play around with it:

https://github.com/timfulmer/hibernate-postgres-jsontype