How To Map PostgreSQL `point` Data Type To Java PGpoint Data Type?
This article describes using PostgreSQL point data in Spring Boot & Spring JPA/Hibernate projects.
- Which Java Data Type should be used for the point Data Type mapped?
- Why can’t you use PGpoint directly?
- How to Use Data Types Not Supported in JPA?
Technology
- Java 11
- Spring Boot 2.x
- Spring JPA 2.x
- PostgreSQL
- Maven
`point` Mapped TO `PGpoint`
The official PostgreSQL library provides definitions of some special data that we can use directly in our projects.
For example`PGpoint.class`:
public class PGpoint extends PGobject implements PGBinaryObject, Serializable, Cloneable {
public double x;
public double y;
public boolean isNull;
}
Then for columns of type point in Table, you can use `PGpoint` in Java Model Class:
@Data
@Entity
@Table(name = "cities")
public class City implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private PGpoint location;
}
However, when querying the city data, an exception occurs:
Caused by: org.hibernate.type.SerializationException: could not deserialize
at org.hibernate.internal.util.SerializationHelper.doDeserialize(SerializationHelper.java:243) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.internal.util.SerializationHelper.deserialize(SerializationHelper.java:287) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.fromBytes(SerializableTypeDescriptor.java:138) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.wrap(SerializableTypeDescriptor.java:113) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.descriptor.java.SerializableTypeDescriptor.wrap(SerializableTypeDescriptor.java:29) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.descriptor.sql.VarbinaryTypeDescriptor$2.doExtract(VarbinaryTypeDescriptor.java:60) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
at ...
Reason for Exception
According to the StackCause of the above exception, it fails to deserialize the query result to a Java object. It's using the default AbstractStandardBasicType in Hibernate because there is no type implementation of PGpoint in BasicType, so it can’t do serialization and deserialization.
How to fix this Exception
Hibernate provides an interface for UserType user-defined types.
This interface should be implemented by user-defined “types”. A “type” class is _not_ the actual property type — it is a class that knows how to serialize instances of another class to and from JDBC.
PGpointType
package com.example.demo;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import org.postgresql.geometric.PGpoint;
import org.springframework.util.ObjectUtils;
import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
public class PGpointType implements UserType {
/**
* Return the SQL type codes for the columns mapped by this type.
* @return int[]
*/
@Override
public int[] sqlTypes() {
return new int[] {
Types.VARCHAR
};
}
/**
* The class returned by nullSafeGet().
* @return Class
*/
@Override
public Class returnedClass() {
return PGpoint.class;
}
@Override
public boolean equals(Object o, Object o1) throws HibernateException {
return ObjectUtils.nullSafeEquals(o, o1);
}
@Override
public int hashCode(Object o) throws HibernateException {
return ObjectUtils.nullSafeHashCode(o);
}
/**
* Retrieve an instance of the mapped class from a JDBC resultset.
*/
@Override
public Object nullSafeGet(ResultSet resultSet, String[] names, SharedSessionContractImplementor sharedSessionContractImplementor, Object o) throws HibernateException, SQLException {
if (names.length == 1) {
if (resultSet.wasNull() || resultSet.getObject(names[0]) == null) {
return null;
} else {
return new PGpoint(resultSet.getObject(names[0]).toString());
}
}
return null;
}
/**
* Write an instance of the mapped class to a prepared statement.
*/
@Override
public void nullSafeSet(PreparedStatement preparedStatement, Object o, int i, SharedSessionContractImplementor sharedSessionContractImplementor) throws HibernateException, SQLException {
if (o == null) {
preparedStatement.setNull(i, Types.OTHER);
} else {
preparedStatement.setObject(i, o.toString(), Types.OTHER);
}
}
@Override
public Object deepCopy(Object o) throws HibernateException {
return o;
}
@Override
public boolean isMutable() {
return false;
}
@Override
public Serializable disassemble(Object o) throws HibernateException {
return (Serializable) o;
}
@Override
public Object assemble(Serializable serializable, Object o) throws HibernateException {
return serializable;
}
@Override
public Object replace(Object o, Object o1, Object o2) throws HibernateException {
return o;
}
}
Conclusion
The above is how to use the PostgreSQL point Data Type in a Spring Boot project, if there are other custom data types, you can also follow this way to implement.