Mapping PostgreSQL JSON column to a Hibernate entity property
JavaJsonHibernatePostgresqlJpaJava Problem Overview
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.
Java Solutions
Solution 1 - Java
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:
Solution 2 - Java
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 3 - Java
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 - Java
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 - Java
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
</connection-url>
Solution 6 - Java
I had a similar problem with Postgres (javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111) when executing native queries (via EntityManager) that retrieved json fields in the projection although the Entity class has been annotated with TypeDefs. The same query translated in HQL was executed without any problem. To solve this I had to modify JsonPostgreSQLDialect this way:
public class JsonPostgreSQLDialect extends PostgreSQL9Dialect {
public JsonPostgreSQLDialect() {
super();
this.registerColumnType(Types.JAVA_OBJECT, "json");
this.registerHibernateType(Types.OTHER, "myCustomType.StringJsonUserType");
}
Where myCustomType.StringJsonUserType is the class name of the class implementing the json type (from above, Tim Fulmer answer) .
Solution 7 - Java
There is an easier to to do this which doesn't involve creating a function by using WITH INOUT
CREATE TABLE jsontext(x json);
INSERT INTO jsontext VALUES ($${"a":1}$$::text);
ERROR: column "x" is of type json but expression is of type text
LINE 1: INSERT INTO jsontext VALUES ($${"a":1}$$::text);
CREATE CAST (text AS json)
WITH INOUT
AS ASSIGNMENT;
INSERT INTO jsontext VALUES ($${"a":1}$$::text);
INSERT 0 1
Solution 8 - Java
I was running into this and didn't want to enable stuff via connection string, and allow implicit conversions. At first I tried to use @Type, but because I'm using a custom converter to serialize/deserialize a Map to/from JSON, I couldn't apply a @Type annotation. Turns out I just needed to specify columnDefinition = "json" in my @Column annotation.
@Convert(converter = HashMapConverter.class)
@Column(name = "extra_fields", columnDefinition = "json")
private Map<String, String> extraFields;
Solution 9 - Java
All the above solution did not work for me. Finally I made use of native queries to insert the data.
Step -1 Create an abstract class AbstractEntity which will implements Persistable
Dont forget - Your entity class should extends your abstract class. (helping your sequence to work as well it may works on date as well(check for date i am not sure))
Step- 3 In repo interface write the native query.
value="INSERT INTO table(?,?)values(:?,:cast(:jsonString as json))",nativeQuery=true
Step - 4 This will convert your java string object to json and insert/store in database and also you will be able to increment the sequence on each insertion as well.
I got casting error when I worked using converter. Also type-52 personally I avoided to use that in my project. Please upvote my ans if it works for you guys.
Solution 10 - Java
I ran into this issue when I migrated my projects from MySQL 8.0.21 to Postgres 13. My project uses Spring boot with the Hibernate types dependency version 2.7.1. In my case the solution was simple.
All I needed to do was change that and it worked.
Referenced from the Hibernate Types Documentation page.