[FIXED] How do I configure a grails domain class attribute to be stored as (postgres 9.4) jsonb?

Issue

I've tried to configure a domain class like this:

class Test {

    String data

    static constraints = {
    }

    static mapping = {
        data type: 'jsonb'
    }
}

This throws an exception (the reason, in the end, being Invocation of init method failed; nested exception is org.hibernate.MappingException: Could not determine type for: jsonb, at table: test, for columns: [org.hibernate.mapping.Column(data)]).

I also tried column: 'data', sqlType: 'jsonb', which creates a text column named data.

How do I correctly tell grails to use jsonb as the sql column type? Is it at all possible?

(The postgresql jdbc driver is used in version 9.4-1200.jdbc4 with hibernate 4.)

Solution

To configure domain to map jsonb type to String you can:

1. Declare your own org.hibernate.usertype.UserType. Add to src/java:

   public class JSONBType implements UserType {
   
       @Override
       public int[] sqlTypes() {
           return new int[] { Types.OTHER };
       }
   
       @SuppressWarnings("rawtypes")
       @Override
       public Class returnedClass() {
           return String.class;
       }
   
       @Override
       public boolean equals(Object x, Object y) throws HibernateException {
           return (x != null) && x.equals(y);
       }
   
       @Override
       public int hashCode(Object x) throws HibernateException {
           return x.hashCode();
       }
   
       @Override
       public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor sessionImplementor, Object owner)
           throws HibernateException, SQLException {
           return rs.getString(names[0]);
       }
   
       @Override
       public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor sessionImplementor)
           throws HibernateException, SQLException {
           st.setObject(index, value, (value == null) ? Types.NULL : Types.OTHER);
       }
   
       @Override
       public Object deepCopy(Object value) throws HibernateException {
           if (value == null) return null;
           return new String((String)value);
       }
   
       @Override
       public boolean isMutable() {
           return false;
       }
   
       @Override
       public Serializable disassemble(Object value) throws HibernateException {
           return (Serializable)value;
       }
   
       @Override
       public Object assemble(Serializable cached, Object owner)
           throws HibernateException {
           return cached;
       }
   
       @Override
       public Object replace(Object original, Object target, Object owner)
           throws HibernateException {
           return deepCopy(original);
       }
   }
   

2. After that you can simply declare mapping in the domain:

   static mapping = {
       data type: "your.package.JSONBType", sqlType: "jsonb"
   }
   

Also you can map jsonb not to String, but directly to JSONObject or to your existing class or interface. In that case GORM will take responsibility for serializing/deserializing json and you no longer need do it explicitly in an application. Here is an example of such UserType implementation.

Answered By - jasp
Answer Checked By - Marie Seifert (JavaFixing Admin)