How To Map PostgreSQL `point` Data Type To Java PGpoint Data Type?

Location

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.