Spring Boot 3操作PostgreSQL Geometry：插入/查询Point失败求助

环境 #

• Spring Boot 3.0.5
• PostgreSQL 15（已安装PostGIS扩展）
• 实体字段使用org.locationtech.jts.geom.Point类型，数据库对应列类型为geometry(Point,4326)

原有代码 #

实体类 #

import org.locationtech.jts.geom.Point;
import jakarta.persistence.*;

public class MyEntity {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "address", columnDefinition = "geometry(Point,4326)")
    @Convert(converter = PointConverter.class)
    private Point address;

    // 其余类定义、getter/setter
}

自定义转换器 #

import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.ParseException;
import org.locationtech.jts.io.WKBReader;
import org.locationtech.jts.io.WKBWriter;
import jakarta.persistence.AttributeConverter;
import java.util.concurrent.locks.ReentrantLock;

public class PointConverter implements AttributeConverter<Geometry, byte[]> {

    private static final WKBWriter WKB_WRITER = new WKBWriter();
    private static final WKBReader WKB_READER = new WKBReader();
    private static final ReentrantLock WRITE_LOCK = new ReentrantLock();
    private static final ReentrantLock READ_LOCK = new ReentrantLock();

    @Override
    public byte[] convertToDatabaseColumn(Geometry attribute) {
        WRITE_LOCK.lock();
        try {
            return WKB_WRITER.write(attribute);
        } finally {
            WRITE_LOCK.unlock();
        }
    }

    @Override
    public Geometry convertToEntityAttribute(byte[] dbData) {
        READ_LOCK.lock();
        try {
            return WKB_READER.read(dbData);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        } finally {
            READ_LOCK.unlock();
        }
    }
}

Gradle依赖（原有） #

implementation 'org.locationtech.jts:jts-core:1.19.0'
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
runtimeOnly 'org.postgresql:postgresql'

遇到的问题 #

1. 初始问题：使用上述转换器时，保存实体到数据库正常，但查询时抛出异常：

Caused by: org.locationtech.jts.io.ParseException: Unknown WKB type 592
    at org.locationtech.jts.io.WKBReader.readGeometry(WKBReader.java:282)
    at org.locationtech.jts.io.WKBReader.read(WKBReader.java:191)
    at org.locationtech.jts.io.WKBReader.read(WKBReader.java:159)
    at com.mypath.PointConverter.convertToEntityAttribute(PointConverter.java:87)

2. 更新后问题：更换转换器后，查询能正常获取Point实体，但保存新实体时报错：

Caused by: org.postgresql.util.PSQLException: Unsupported Types value: 596,497,711
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:737)
    at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:974)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java)
    at org.hibernate.type.descriptor.jdbc.ObjectJdbcType$1.doBind(ObjectJdbcType.java:58)
    at org.hibernate.type.descriptor.jdbc.BasicBinder.bind(BasicBinder.java:63)
    at org.hibernate.type.internal.ConvertedBasicTypeImpl.nullSafeSet(ConvertedBasicTypeImpl.java:271)
    at org.hibernate.type.internal.ConvertedBasicTypeImpl.nullSafeSet(ConvertedBasicTypeImpl.java:249)
    at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:3252)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3435)

解决方案 #

问题根源在于自定义转换器未适配PostGIS的**EWKB（扩展WKB）**存储格式（带SRID信息），且原生JDBC驱动不直接支持JTS几何类型。正确的做法是使用Hibernate Spatial来自动处理几何类型的转换：

1. 更新Gradle依赖 #

添加Hibernate Spatial和PostGIS JDBC驱动：

implementation 'org.locationtech.jts:jts-core:1.19.0'
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
// 添加Hibernate Spatial支持（适配Spring Boot 3的Hibernate 6版本）
implementation 'org.hibernate.orm:hibernate-spatial:6.1.7.Final'
// PostGIS JDBC驱动
runtimeOnly 'net.postgis:postgis-jdbc:2.5.0'
runtimeOnly 'org.postgresql:postgresql'

2. 配置Hibernate方言 #

在application.properties或application.yml中指定PostGIS方言：

spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.postgis.PostgisDialect

3. 修改实体类 #

移除自定义转换器，Hibernate Spatial会自动处理类型转换：

import org.locationtech.jts.geom.Point;
import jakarta.persistence.*;

@Entity
public class MyEntity {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(name = "address", columnDefinition = "geometry(Point,4326)")
    private Point address;

    // getter/setter
}

4. 确保数据库启用PostGIS扩展 #

执行SQL语句启用PostGIS：

CREATE EXTENSION IF NOT EXISTS postgis;

原理说明 #

• PostGIS默认使用带SRID的EWKB格式存储几何数据，JTS原生的WKBReader无法解析这种扩展格式，导致查询报错。
• Hibernate Spatial通过PostGIS方言和专用类型处理器，自动完成JTS几何对象与PostGIS EWKB格式的双向转换，无需自定义转换器。
• PostGIS JDBC驱动增强了原生PostgreSQL驱动，支持直接处理几何类型的参数绑定与结果读取。

内容的提问来源于stack exchange，提问作者anonymous beak