How To Map PostgreSQL `point` Data Type To Java PGpoint Data Type?
Tags: java, postgres, springboot, database
Published: 2024-06-18 19:32:55

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 do you use data types that are 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`:
```java
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:
```java
@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**
```java
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.