Hibernate JSONUserType with Jackson

JSONUserType.java

package com.keepithttps.hibernate.type;

import java.io.IOException;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.internal.util.ReflectHelper;
import org.hibernate.usertype.ParameterizedType;
import org.hibernate.usertype.UserType;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.base.Objects;

/**
* Hibernate {@link UserType} implementation to handle JSON objects
*
* @see https
*      ://docs.jboss.org/hibernate/orm/4.1/javadocs/org/hibernate/usertype/
*      UserType.html
*/
public class JSONUserType implements UserType, ParameterizedType, Serializable {
  
  private static final long serialVersionUID = 1L;
  
  private static final ObjectMapper MAPPER = new ObjectMapper();
  
  private static final String CLASS_TYPE = "classType";
  private static final String TYPE = "type";
  
  private static final int[] SQL_TYPES = new int[] { Types.LONGVARCHAR, Types.CLOB, Types.BLOB };
  
  private Class<?> classType;
  private int sqlType = Types.LONGVARCHAR; // before any guessing
  
  @Override
  public void setParameterValues(Properties params) {
    String classTypeName = params.getProperty(CLASS_TYPE);
    try {
      this.classType = ReflectHelper.classForName(classTypeName, this.getClass());
    } catch (ClassNotFoundException cnfe) {
      throw new HibernateException("classType not found", cnfe);
    }
    String type = params.getProperty(TYPE);
    if (type != null) {
      this.sqlType = Integer.decode(type).intValue();
    }
    
  }
  
  @Override
  public Object assemble(Serializable cached, Object owner) throws HibernateException {
    return this.deepCopy(cached);
  }
  
  @Override
  public Object deepCopy(Object value) throws HibernateException {
    Object copy = null;
    if (value != null) {
      
      try {
        return MAPPER.readValue(MAPPER.writeValueAsString(value), this.classType);
      } catch (IOException e) {
        throw new HibernateException("unable to deep copy object", e);
      }
    }
    return copy;
  }
  
  @Override
  public Serializable disassemble(Object value) throws HibernateException {
    try {
      return MAPPER.writeValueAsString(value);
    } catch (JsonProcessingException e) {
      throw new HibernateException("unable to disassemble object", e);
    }
  }
  
  @Override
  public boolean equals(Object x, Object y) throws HibernateException {
    return Objects.equal(x, y);
  }
  
  @Override
  public int hashCode(Object x) throws HibernateException {
    return Objects.hashCode(x);
  }
  
  @Override
  public boolean isMutable() {
    return true;
  }
  
  @Override
  public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException,
  SQLException {
    Object obj = null;
    if (this.sqlType == Types.CLOB || this.sqlType == Types.BLOB) {
      byte[] bytes = rs.getBytes(names[0]);
      if (bytes != null && !rs.wasNull()) {
        try {
          obj = MAPPER.readValue(bytes, this.classType);
        } catch (IOException e) {
          throw new HibernateException("unable to read object from result set", e);
        }
      }
    } else {
      try {
        String content = rs.getString(names[0]);
        if (content != null && !rs.wasNull()) {
          obj = MAPPER.readValue(content, this.classType);
        }
      } catch (IOException e) {
        throw new HibernateException("unable to read object from result set", e);
      }
    }
    return obj;
  }
  
  @Override
  public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException,
  SQLException {
    if (value == null) {
      st.setNull(index, this.sqlType);
    } else {
      
      if (this.sqlType == Types.CLOB || this.sqlType == Types.BLOB) {
        try {
          st.setBytes(index, MAPPER.writeValueAsBytes(value));
        } catch (JsonProcessingException e) {
          throw new HibernateException("unable to set object to result set", e);
        }
      } else {
        try {
          st.setString(index, MAPPER.writeValueAsString(value));
        } catch (JsonProcessingException e) {
          throw new HibernateException("unable to set object to result set", e);
        }
      }
    }
  }
  
  @Override
  public Object replace(Object original, Object target, Object owner) throws HibernateException {
    return this.deepCopy(original);
  }
  
  @Override
  public Class<?> returnedClass() {
    return this.classType;
  }
  
  @Override
  public int[] sqlTypes() {
    return SQL_TYPES;
  }
}

mapping.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-cascade="none" default-access="property" default-lazy="true" auto-import="true">
  <class abstract="false" table="person" name="com.keepithttps.hibernate.type.Person" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" select-before-update="false" optimistic-lock="version">
    
    <id type="long" access="field" name="id" column="ID">
      <generator class="native" />
    </id>
    
    <property name="firstName" column="first_name" type="string" unique="false" optimistic-lock="true" lazy="false" generated="never" />
    <property name="lastName" column="last_name" type="string" unique="false" optimistic-lock="true" lazy="false" generated="never" />
    
    <property name="status" column="status" unique="false" optimistic-lock="true" lazy="false" generated="never">
      <type name="org.hibernate.type.EnumType">
        <param name="enumClass">com.keepithttps.hibernate.type.PersonStatus</param>
        <param name="type">12</param>
        <param name="useNamed">true</param>
      </type>
    </property>
    
    <property name="config" column="config" unique="false" optimistic-lock="true" lazy="false" generated="never">
      <type name="com.keepithttps.hibernate.type.Person.JSONUserType">
        <param name="classType">com.keepithttps.hibernate.type.PersonConfig</param>
        <param name="type">2005</param>
      </type>
    </property>
    
  </class>
</hibernate-mapping>

pom.xml

<dependency>
  <groupId>org.codehaus.jackson</groupId>
  <artifactId>jackson-mapper-asl</artifactId>
  <version>1.9.13</version>
</dependency>

JSONUserType.java

Do Null check in line no 116.

String content = rs.getString(names[0]);
if (content != null) {
     obj = MAPPER.readValue(content, this.classType);
}

That sqlTypes() function should be like this

@Override
public int[] sqlTypes() {
     return new int[] {this.sqlType};
}

Otherwise you will get MappingException("property mapping has wrong number of columns") .

My Environment: Java 1.7, Spring 4.1.4, Hibernate 4.3.8

This code is misusing rs.wasNull() on line 104. The wasNull javadoc says:

    /**
     * Reports whether
     * the last column read had a value of SQL <code>NULL</code>.
     * Note that you must first call one of the getter methods
     * on a column to try to read its value and then call
     * the method <code>wasNull</code> to see if the value read was
     * SQL <code>NULL</code>.
     *
     * @return <code>true</code> if the last column value read was SQL
     *         <code>NULL</code> and <code>false</code> otherwise
     * @exception SQLException if a database access error occurs or this method is
     *            called on a closed result set
     */

So it will return true if the last column read was null, even if this json column is not null.

See my fork for the correct way to use wasNull().

Thanks!

@arulrajnet Approved. This code should be more precise or it will get someone struggling to find out what's wrong. I just got that experience. T_T

@fabriziofortino

private static final int[] SQL_TYPES = new int[] { Types.LONGVARCHAR, Types.CLOB, Types.BLOB };
It seems that only 'varchar2', 'Clob' and 'Blob' type can be supported.
How about the column type is 'Jsonb'? Can it wok?