How can I map postgresql json data type using Hibernate?
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
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