Mapping PostgreSQL JSON column to a Hibernate entity property

Question

I have a table with a column of type JSON in my PostgreSQL DB (9.2). I have a hard time to map this column to a JPA2 Entity field type.

I tried to use String but when I save the entity I get an exception that it can't convert character varying to JSON.

What is the correct value type to use when dealing with a JSON column?

@Entity
public class MyEntity {

    private String jsonPayload; // this maps to a json column

    public MyEntity() {
    }
}

A simple workaround would be to define a text column.

Solution 1

See PgJDBC bug #265.

PostgreSQL is excessively, annoyingly strict about data type conversions. It won't implicitly cast text even to text-like values such as xml and json.

The strictly correct way to solve this problem is to write a custom Hibernate mapping type that uses the JDBC setObject method. This can be a fair bit of hassle, so you might just want to make PostgreSQL less strict by creating a weaker cast.

As noted by @markdsievers in the comments and this blog post, the original solution in this answer bypasses JSON validation. So it's not really what you want. It's safer to write:

CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;

AS IMPLICIT tells PostgreSQL it can convert without being explicitly told to, allowing things like this to work:

regress=# CREATE TABLE jsontext(x json);
CREATE TABLE
regress=# PREPARE test(text) AS INSERT INTO jsontext(x) VALUES ($1);
PREPARE
regress=# EXECUTE test('{}')
INSERT 0 1

Thanks to @markdsievers for pointing out the issue.

Solution 2

If you're interested, here are a few code snippets to get the Hibernate custom user type in place. First extend the PostgreSQL dialect to tell it about the json type, thanks to Craig Ringer for the JAVA_OBJECT pointer:

import org.hibernate.dialect.PostgreSQL9Dialect;

import java.sql.Types;

/**
 * Wrap default PostgreSQL9Dialect with 'json' type.
 *
 * @author timfulmer
 */
public class JsonPostgreSQLDialect extends PostgreSQL9Dialect {

    public JsonPostgreSQLDialect() {

        super();

        this.registerColumnType(Types.JAVA_OBJECT, "json");
    }
}

Next implement org.hibernate.usertype.UserType. The implementation below maps String values to the json database type, and vice-versa. Remember Strings are immutable in Java. A more complex implementation could be used to map custom Java beans to JSON stored in the database as well.

package foo;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

/**
 * @author timfulmer
 */
public class StringJsonUserType implements UserType {

    /**
     * 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 new int[] { Types.JAVA_OBJECT};
    }

    /**
     * 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.
     *
     * @param x
     * @param y
     * @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 session
     * @param owner   the containing entity  @return Object
     * @throws org.hibernate.HibernateException
     *
     * @throws java.sql.SQLException
     */
    @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
     * @param session
     * @throws org.hibernate.HibernateException
     *
     * @throws java.sql.SQLException
     */
    @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
     * @throws org.hibernate.HibernateException
     *
     */
    @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
     * @throws org.hibernate.HibernateException
     *
     */
    @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;
    }
}

Now all that's left is annotating the entities. Put something like this at the entity's class declaration:

@TypeDefs( {@TypeDef( name= "StringJsonObject", typeClass = StringJsonUserType.class)})

Then annotate the property:

@Type(type = "StringJsonObject")
public String getBar() {
    return bar;
}

Hibernate will take care of creating the column with json type for you, and handle the mapping back and forth. Inject additional libraries into the user type implementation for more advanced mapping.

Here's a quick sample GitHub project if anyone wants to play around with it:

https://github.com/timfulmer/hibernate-postgres-jsontype

Solution 3

Maven dependency

The first thing you need to do is to set up the following Hibernate Types Maven dependency in your project pom.xml configuration file:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

Domain model

Now, you need to declare the JsonType on either class level or in a package-info.java package-level descriptor, like this:

@TypeDef(name = "json", typeClass = JsonType.class)

And, the entity mapping will look like this:

@Type(type = "json")
@Column(columnDefinition = "jsonb")
private Location location;

If you're using Hibernate 5 or later, then the JSON type is registered automatically by the Postgre92Dialect.

Otherwise, you need to register it yourself:

public class PostgreSQLDialect extends PostgreSQL91Dialect {

    public PostgreSQL92Dialect() {
        super();
        this.registerColumnType( Types.JAVA_OBJECT, "jsonb" );
    }
}

Solution 4

In case someone is interested, you can use JPA 2.1 @Convert / @Converter functionality with Hibernate. You would have to use the pgjdbc-ng JDBC driver though. This way you don't have to use any proprietary extensions, dialects and custom types per field.

@javax.persistence.Converter
public static class MyCustomConverter implements AttributeConverter<MuCustomClass, String> {

    @Override
    @NotNull
    public String convertToDatabaseColumn(@NotNull MuCustomClass myCustomObject) {
        ...
    }

    @Override
    @NotNull
    public MuCustomClass convertToEntityAttribute(@NotNull String databaseDataAsJSONString) {
        ...
    }
}

...

@Convert(converter = MyCustomConverter.class)
private MyCustomClass attribute;

Solution 5

I tried many methods I found on the Internet, most of them are not working, some of them are too complex. The below one works for me and is much more simple if you don't have that strict requirements for PostgreSQL type validation.

Make PostgreSQL jdbc string type as unspecified, like <connection-url> jdbc:postgresql://localhost:test?stringtype=‌​unspecified </connect‌​ion-url>

View more solutions

Comments

[Ümit]

thanks for the thorough code examples! I am using a normal text field but I might take your approach in future

[Craig Ringer]

Thanks for taking the time to write this. It intensely frustrates me that JPA doesn't define SPI hooks for user defined types to be written in a JPA-provider-independent way.

[Tim Fulmer]

No worries guys, I ended up with the code and this page in front of me and figured why not :) That might be the downside of the Java process. We get some pretty well thought through solutions to tough problems, but it's not easy to go in and add a good idea like generic SPI for new types. We're left with whatever the implementers, Hibernate in this case, put in place.

[rtcarlson]

there's a problem in your implementation code for nullSafeGet. Instead of of if(rs.wasNull()) you should do if(rs.getString(names[0]) == null). I'm not sure what rs.wasNull() does, but in my case it burned me by returning true, when the value I was looking for was in fact not null.

[Tim Fulmer]

@rtcarlson Nice catch! Sorry you had to go through that. I've updated the code above.

[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");

[markdsievers]

Worth reading the resulting blog post of this answer. Inparticular the comment section highlights the dangers of this (allows invalid json) and the alternative / superior solution.

[Craig Ringer]

@markdsievers Thankyou. I've updated the post with a corrected solution.

[markdsievers]

@CraigRinger No problem. Thank you for your prolific PG / JPA / JDBC contributions, many have been of great assistance to me.

[Tim Fulmer]

@oliverguenther I've updated the example above to work with latest Hibernate. Nice catch.

[Καrτhικ]

JPA 2.1 has a converter annotation - but it only maps standard types (such as string) to your custom types (e.g. a java bean).

[Nick Barnes]

@CraigRinger Since you're going through the cstring conversion anyway, couldn't you simply use CREATE CAST (text AS json) WITH INOUT?

[zeroDivisible]

@NickBarnes that solution also worked perfectly for me (and from what I had seen, it fails on invalid JSON, as it should). Thanks!

[giaffa86]

don't forget to change your hibernate.properties replacing property "dialect" content with 'JsonPostgreSQLDialect'

[yunspace]

I have tried this out and it works for reading/persisting the Entity. However if I need to run a query against Restrictions.sqlRestriction(sql, Object, Type) on the Jsonb column, the method has to take in a Type instead of UserType. It seems the only work around is to implement Type instead?

[myrosia]

This sounds useful - what types should it convert between to be able to write JSON? Is it <MyCustomClass, String> or some other type?

[myrosia]

Thanks - just verified that it works for me (JPA 2.1, Hibernate 4.3.10, pgjdbc-ng 0.5, Postgres 9.3)

[tfranckiewicz]

Is it possible to make it work without specifing @Column(columnDefinition = "json") on the field? Hibernate is making a varchar(255) without this definition.

[vasily]

Hibernate cannot possibly know what column type you want there, but you insist that it's Hibernate's responsibility to update the database schema. So I guess it picks the default one.

[Arun M R Nair]

I am also looking for running a query. but show ' Unrecognized field' error for inner fields of json.Can you help me.

[rü-]

I don't see any code on the linked github-project ;-) BTW: Wouldn't it be useful to have this code as a library for reuse?

[Kevin Vasko]

How does this solution differ for Hibernate 5?

[Evan Carroll]

@NickBarnes you should have answered. That would have been a valuable contribution -- here it is though stackoverflow.com/a/42032206/124486

[kensai]

Nice example, but can this be used with some generic DAO, like Spring Data JPA repositories to query data without native queries like we can do with MongoDB? I didn't find any valid answer or solution to this case. Yes we can store the data, and we can retreive them by filtering columns in RDBMS, but I cannot filter by JSONB coluns so far. I wish I am wrong and there is such solution.

[Vlad Mihalcea]

Yes, you can. But you need to use nativ queries which are supported by Spring Data JPA too.

[kensai]

I see, that was actually my questin, if we can go without native queries, but just via objects methods. Something like @Document annotation for MongoDB style. So I assume this is not so far in case of PostgreSQL and the only solution is native queries -> nasty :-), but thanks for confirmation.

[kensai]

It would be good to see in future something like entity which really represents table and document annotation on fields type of json and I can use Spring repositories to do CRUD stuff on the fly. Think is that I am generating quite advanced REST API for databases with Spring. But with JSON in place I am facing quite unexpected overhead so I will need to process every single document with generate queries as well.

[Vlad Mihalcea]

You can use Hibernate OGM with MongoDB if JSON is your single store.

[kensai]

I mention MongoDB just as example. My question is more about: Do you know about some JPA standard which could bring same functionalities regarding QUERYING data within PostgreSQL JSONB structures in same way as you can do with JPA (hibernate) with MongoDB?

[NotABot]

Nice answer tyvm Tim !

[Righto]

Is there JUnit support for JSONB in springboot projects?

[Righto]

Can you do this with returnedClass as a JSONObject or an Object?

[Vladimir M.]

Thanks, used this to cast varchar to ltree, works perfectly.

[jaco0646]

This solution worked for me only after changing the Java class of the json field to Jackson's JsonNode. It did not work for a pojo or a raw json string.

[Vlad Mihalcea]

The GitHub repository associated with the hibernate-types contains unit tests which show you how to map to a Pojo as well.

[James]

Thank you! I was using hibernate-types but this is way easier! FYI here are the docs about this param jdbc.postgresql.org/documentation/83/connect.html

[sandeep]

Where have you defined this HashMapConverter class. How it look like.

[Philippe Gioseffi]

It's worth mentioning that starting from Postgre 9.2 the json column type register is already done.

[Dmytro]

Thanks for your work. Just one thing: you forgot to mention that the json class must implement Serializable. Struggled a few hours until I saw your GitHub...

[JJ Ward]

This works with Gson as the "custom" class, and you can return the resultant JsonElement as a JsonObject, JsonArray, JSonPrimitive, or JsonNull. No need for TypeDefs or anything else, just use columnDefinition = "jsonb" and it reads just fine. Very sexy, great answer!