How can I map postgresql json data type using Hibernate?

Question

I am following the example mentioned in the below URL ? Mapping PostgreSQL JSON column to a Hibernate entity property

But always get the following exception:

Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: 2000
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:76)
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:99)
    at org.hibernate.dialect.Dialect.getTypeName(Dialect.java:310)
    at org.hibernate.mapping.Column.getSqlType(Column.java:226)
    at org.hibernate.mapping.Table.validateColumns(Table.java:369)
    at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1305)
    at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:155)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:512)

I am using TomEE as the Server. and trying to store Json body to a postgresql column. I am trying to map the entity pojos to the postgres datatype structure.

Any idea what could be the issue ? or does any has a better technique to handle such as scenario ? Please point me to that source.

The script used to create the entity table is:

    CREATE TABLE historyentity
    (
      id character varying(255) NOT NULL,
      userid character varying(255),
      lastchanged timestamp without time zone,
      type character varying(255),
      history json [],
      CONSTRAINT historyentity_pkey PRIMARY KEY (id),
      CONSTRAINT historyentity_userid_fkey FOREIGN KEY (userid)
          REFERENCES userentity (id) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE historyentity
      OWNER TO postgres;
    GRANT ALL ON TABLE historyentity TO postgres;

Entity Pojos look like as follows:

    @Entity
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @TypeDefs({ @TypeDef(name = "StringJsonObject", typeClass = StringJsonUserType.class) })
    public class HistoryEntity {
    
        @Id
        private String id;
        private String userid;
        private String type;
        @Type(type = "StringJsonObject")
        private String history;
        private Date lastchanged;
    
    }

I am using lombok to define the entity pojos.

Following is the Dialect extended class: I have tried with both the registered types, Column and Hibenate. But both are not working out.

    import org.hibernate.dialect.PostgreSQL82Dialect;
    
    public class JsonPostgreSQLDialect extends PostgreSQL82Dialect
    
        {
            @Inject
            public JsonPostgreSQLDialect()
            {
                super();
                   this.registerColumnType(Types.JAVA_OBJECT, "json");
                // this.registerHibernateType(Types.JAVA_OBJECT, "json");
            }
        }

The following class is being used to define the User Type:

    import java.io.Serializable;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Types;
    
    import org.hibernate.HibernateException;
    import org.hibernate.engine.spi.SessionImplementor;
    import org.hibernate.usertype.UserType;
    
    
    public class StringJsonUserType implements UserType
    {
        private final int[] sqlTypesSupported = new int[]{ Types.JAVA_OBJECT };
    
        /**
         * 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
        public int[] sqlTypes()
        {
            return sqlTypesSupported;
        }
    
        /**
         * The class returned by <tt>nullSafeGet()</tt>.
         *
         * @return Class
         */
        @Override
        public Class returnedClass()
        {
            return String.class;
        }
    
        /**
         * Compare two instances of the class mapped by this type for persistence "equality". Equality of the persistent
         * state.
         *
         * @return boolean
         */
        @Override
        public 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"
         */
        @Override
        public int hashCode(Object x) throws HibernateException
        {
    
            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 owner the containing entity  @return Object
         */
        @Override
        public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
            throws HibernateException, SQLException
        {
            if (rs.getString(names[0]) == null)
            {
                return null;
            }
            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
         */
        @Override
        public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
            throws HibernateException, SQLException
        {
            if (value == null)
            {
                st.setNull(index, Types.OTHER);
                return;
            }
    
            st.setObject(index, value, 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
         */
        @Override
        public Object deepCopy(Object value) throws HibernateException
        {
    
            return value;
        }
    
        /**
         * Are objects of this type mutable?
         *
         * @return boolean
         */
        @Override
        public boolean isMutable()
        {
            return 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
         */
        @Override
        public Serializable disassemble(Object value) throws HibernateException
        {
            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
         */
        @Override
        public Object assemble(Serializable cached, Object owner) throws HibernateException
        {
            return 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
         */
        @Override
        public Object replace(Object original, Object target, Object owner) throws HibernateException
        {
            return original;
        }
    }

Answer 1

This work for me :

Your Entity :

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@TypeDef(name = "json", typeClass = JSONUserType.class, parameters = {
        @Parameter(name = JSONUserType.CLASS, value = "java.lang.String")})
    public class HistoryEntity {

        @Id
        private String id;
        private String userid;
        private String type;
        @Type(type = "json")
        private String history;
        private Date lastchanged;

    }

Implement Hibernate ParameterizedType and UserType to ensure the conversion between the 2 types (json <->string)

public class JSONUserType implements ParameterizedType, UserType {

    private static final ObjectMapper objectMapper = new ObjectMapper();
    private static final ClassLoaderService classLoaderService = new ClassLoaderServiceImpl();

    public static final String JSON_TYPE = "json";
    public static final String CLASS = "CLASS";

    private Class jsonClassType;

    @Override
    public Class<Object> returnedClass() {
        return Object.class;
    }

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.JAVA_OBJECT};
    }

    @Override
    public Object nullSafeGet(ResultSet resultSet, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        try {
            final String json = resultSet.getString(names[0]);
            return json == null ? null : objectMapper.readValue(json, jsonClassType);
        } catch (IOException e) {
            throw new HibernateException(e);
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        try {
            final String json = value == null ? null : objectMapper.writeValueAsString(value);
            PGobject pgo = new PGobject();
            pgo.setType(JSON_TYPE);
            pgo.setValue(json);
            st.setObject(index, pgo);
        } catch (JsonProcessingException e) {
            throw new HibernateException(e);
        }
    }

    @Override
    public void setParameterValues(Properties parameters) {
        final String clazz = (String) parameters.get(CLASS);
        jsonClassType = classLoaderService.classForName(clazz);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Object deepCopy(Object value) throws HibernateException {

        if (!(value instanceof Collection)) {
            return value;
        }

        Collection<?> collection = (Collection) value;
        Collection collectionClone = CollectionFactory.newInstance(collection.getClass());

        collectionClone.addAll(collection.stream().map(this::deepCopy).collect(Collectors.toList()));

        return collectionClone;
    }

    static final class CollectionFactory {
        @SuppressWarnings("unchecked")
        static <E, T extends Collection<E>> T newInstance(Class<T> collectionClass) {
            if (List.class.isAssignableFrom(collectionClass)) {
                return (T) new ArrayList<E>();
            } else if (Set.class.isAssignableFrom(collectionClass)) {
                return (T) new HashSet<E>();
            } else {
                throw new IllegalArgumentException("Unsupported collection type : " + collectionClass);
            }
        }
    }

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

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y) {
            return true;
        }

        if ((x == null) || (y == null)) {
            return false;
        }

        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        assert (x != null);
        return x.hashCode();
    }

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

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        Object deepCopy = deepCopy(value);

        if (!(deepCopy instanceof Serializable)) {
            throw new SerializationException(String.format("%s is not serializable class", value), null);
        }

        return (Serializable) deepCopy;
    }

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

And extends PostgreSQL94Dialect class to tell the serializer the matching type:

public class JSONPostgreSQLDialect extends PostgreSQL94Dialect {

    public JSONPostgreSQLDialect() {
        super();
        registerColumnType(Types.JAVA_OBJECT, JSONUserType.JSON_TYPE);
    }
}

If you use Spring you must declare this last class in application.properties like this : spring.jpa.database-platform=com.yourpackage.JSONPostgreSQLDialect