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

Question

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.)

Answer 1

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.

Answer 2

You can use the Grails Postgresql Extensions plugin to use some Postgresql native types inside your domain classes.

At this moment the plugin supports the Json but not the Jsonb type. You have more information about the json support in the plugin documentation

DISCLAIMER: I'm one of the developers of the plugin.

Answer 3

Although, I'm answering this very late but I managed to achieve this with a very simple way which is working so smooth-

I created a custom Hibernate type which implements UserType:

package com.wizpanda.hibernate

import groovy.transform.CompileStatic
import org.grails.web.json.JSONObject
import org.hibernate.HibernateException
import org.hibernate.engine.spi.SessionImplementor
import org.hibernate.usertype.UserType

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Types

/**
 * An implementation of {@link org.grails.web.json.JSONObject} column using Hibernate custom types.
 * https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#_custom_type
 * https://docs.jboss.org/hibernate/orm/current/javadocs/org/hibernate/usertype/UserType.html
 *
 * @author Shashank Agrawal
 */
@CompileStatic
class JSONObjectFooType implements UserType {

    @Override
    int[] sqlTypes() {
        return [Types.OTHER] as int[]
    }

    //@SuppressWarnings("rawtypes")
    @Override
    Class returnedClass() {
        return JSONObject.class
    }

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

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

    @Override
    Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        String value = rs.getString(names[0])
        if (!value) {
            return null
        }

        return new JSONObject(value)
    }

    @Override
    void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        String valueToPersist

        if (value) {
            if (value instanceof JSONObject) {
                valueToPersist = value.toString()
            } else if (value instanceof String) {
                valueToPersist = new JSONObject(value).toString(0)
            } else {
                throw new HibernateException("Unknown type received for JSONObject based column")
            }
        }

        st.setObject(index, valueToPersist, Types.OTHER)
    }

    @Override
    Object deepCopy(Object value) throws HibernateException {
        if (!value) {
            return null
        }
        if (value instanceof JSONObject) {
            return new JSONObject(value.toString(0))
        }

        return value
    }

    @Override
    boolean isMutable() {
        return false
    }

    @Override
    Serializable disassemble(Object value) throws HibernateException {
        if (value instanceof JSONObject) {
            return value?.toString(0)
        }

        return value?.toString()
    }

    @Override
    Object assemble(Serializable cached, Object owner) throws HibernateException {
        if (!cached) {
            return null
        }

        return new JSONObject(cached.toString())
    }

    @Override
    Object replace(Object original, Object target, Object owner) throws HibernateException {
        return deepCopy(original)
    }
}

I'm using org.grails.web.json.JSONObject because this is internal from Grails, you can use others like org.json.JSONObject or Groovy json and replace the occurrences above.

Now, simple use this in your domain class-

class User {

    String email
    String name
    JSONObject settings

    static mapping = {
        settings type: JSONObjectFooType, sqlType: "text"
    }
}

Namaste!