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;
        }
    }

Ordinand

How do you try to map the entity pojos to the postgres datatype structure? can you show a piece of the code and configuration code? – Dilisio

I just described the code and the postgres structure. I hope that helps ? – Ordinand

Did you update your hibernate.properties? Did you set there the right "dialect"? – Leath

Have you read the comments on the answer that you've based on? from oliverguenther: This solution worked nicely with Hibernate 4.2.7 except when retrieving null from json columns with the error 'No Dialect mapping for JDBC type: 1111'. However, adding the following line to the dialect class fixed it: this.registerHibernateType(Types.OTHER, "StringJsonUserType"); – Mugwump

Looks like you're using array of json in db. So it shoud be mapped to String[] or smth like that. – Mischance

You may want to read How to map JSON objects using generic Hibernate Types. – Parrish

You could read this, i think will help you. see: #15974974 – Moonraker

@Ordinand I am having the same issue. (How) did you manage to solve it? – Zion

Answer 1

Postgres JSON type has been added to Hibernate in the PostgreSQL92Dialect. So you should either use that dialect or one of its subclasses, or make a custom dialect that adds the following type definition:

        this.registerColumnType(2000, "json");

The type itself can be defined as follows (example for Hibernate 5.x):

public class JsonType implements UserType {

    public static final ObjectMapper MAPPER = new ObjectMapper();

    private int[] sqlTypes;
    private com.fasterxml.jackson.databind.ObjectWriter writer;
    private JavaType type;
    private boolean isBinary;
    private ObjectReader reader;

    public JsonType() {
        init(SimpleType.constructUnsafe(Object.class), false);
    }

    public JsonType(Class clazz, boolean isBinary) {
        this(SimpleType.construct(clazz), isBinary);
    }

    public JsonType(JavaType type, boolean isBinary) {
        init(type, isBinary);
    }

    protected void init(JavaType type, boolean isBinary) {
        this.type = type;
        this.isBinary = isBinary;
        this.reader = MAPPER.readerFor(type);
        this.writer = MAPPER.writerFor(type);
        this.sqlTypes = new int[]{Types.JAVA_OBJECT};
    }


    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y) {
            return true;
        } else if (x == null || y == null) {
            return false;
        } else {
            return x.equals(y);
        }
    }

    public int hashCode(Object x) throws HibernateException {
        return null == x ? 0 : x.hashCode();
    }

    public boolean isMutable() {
        return true;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
        final Object result = rs.getObject(names[0]);
        if (!rs.wasNull()) {
            String content;

            if (result instanceof String) {
                content = (String) result;
            } else if (result instanceof PGobject) {
                // If we get directly the PGobject for some reason (more exactly, if a DB like H2 does the serialization directly)
                content = ((PGobject) result).getValue();
            } else {
                throw new IllegalArgumentException("Unknown object type (excepted pgobject or json string)");
            }
            if (content != null) {
                return convertJsonToObject(content);
            }
        }
        return null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        if (value == null) {
            st.setObject(index, null);
            return;
        }
        PGobject pg = new PGobject();
        pg.setType(isBinary ? "jsonb" : "json");
        pg.setValue(convertObjectToJson(value));
        st.setObject(index, pg);
    }


    Object convertJsonToObject(String content) {
        try {
            return reader.readValue(content);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    String convertObjectToJson(Object object) {
        try {
            return writer.writeValueAsString(object);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public Object deepCopy(Object value) throws HibernateException {
        String json = convertObjectToJson(value);
        return convertJsonToObject(json);
    }


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


    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) deepCopy(value);
    }


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


    public int[] sqlTypes() {
        return sqlTypes;
    }


    public Class returnedClass() {
        return type.getRawClass();
    }
}

This example uses Jackson as a framework for JSON (de)serialization.

You can then use your type as follows:

@Entity
@TypeDefs({@TypeDef( name= "StringJsonObject", typeClass = JsonType.class)})
public class MyEntity {

    @Type(type = "StringJsonObject")
    @Column(name="visuals", columnDefinition = "json")
    private Map<String, String> visuals;

}

---

But this is all very similar to the type that you implemented (presumably for Hibernate 4.x). So why wasn't your implementation working? This is because your field is actually of type json[] (a Postgres array of JSON objects). This mapper only works with JSON objects (type json). This JSON object can very well be a JSON array of JSON objects, but it has to be of type json. So you should change the type in your database schema, or implement a UserType that can work with arrays, but the first option is most likely.

Corcoran

Answer 2

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

Cons