Spring Boot使用Oracle SDO_GEOMETRY时遇ORA-00932类型不匹配错误

问题场景 #

在Spring Boot实体中尝试用MDSYS.SDO_GEOMETRY类型存储Polygon空间数据，实体字段定义如下：

@Column(name = "shape",columnDefinition = "MDSYS.SDO_GEOMETRY")
private Polygon shape;

执行数据保存时抛出类型不匹配错误：

java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes:
expected MDSYS.SDO_GEOMETRY got BINARY

环境配置 #

pom.xml依赖 #

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>6.3.0.Final</version>
</dependency>

application.properties配置 #

# Hibernate properties
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

# Hibernate Spatial properties
spring.jpa.properties.hibernate.spatial.dialect=org.hibernate.spatial.dialect.oracle.OracleSpatial10gDialect

服务层代码 #

// set shape of range
List<Coordinate> coordinates = new ArrayList<>();
for (RangeSpotsModel spot : rangeModel.getRangeSpotsModel()) {
    coordinates.add(new Coordinate(spot.getLongitude(), spot.getLatitude()));
}
GeometryFactory geometry = new GeometryFactory();
range.setShape(geometry.createPolygon(coordinates.toArray(new Coordinate[0])));

range = rangeRepository.save(range);

完整错误栈 #

message: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
stackTrace: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    ...
Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got BINARY

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
    ...

解决方案 #

1. 修正Hibernate方言配置 #

问题根源是同时配置了普通Hibernate方言和空间方言，导致Hibernate无法正确识别空间类型映射。需将普通方言替换为对应Oracle版本的空间方言，删除重复的空间方言配置：

# Hibernate properties
spring.jpa.properties.hibernate.dialect=org.hibernate.spatial.dialect.oracle.OracleSpatial19cDialect
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

说明：Oracle 19c推荐使用OracleSpatial19cDialect，无需额外配置hibernate.spatial.dialect

2. 优化实体字段注解 #

移除columnDefinition配置，Hibernate Spatial会自动映射JTS的Polygon到Oracle的SDO_GEOMETRY：

@Column(name = "shape")
private Polygon shape;

确保Polygon的导入包是org.locationtech.jts.geom.Polygon，避免使用其他包的空间类型类

3. 确保Polygon坐标闭合 #

JTS要求Polygon的第一个和最后一个坐标必须相同，否则会生成无效的几何对象，导致Hibernate处理异常。修改服务层代码补充坐标闭合：

// set shape of range
List<Coordinate> coordinates = new ArrayList<>();
for (RangeSpotsModel spot : rangeModel.getRangeSpotsModel()) {
    coordinates.add(new Coordinate(spot.getLongitude(), spot.getLatitude()));
}
// 闭合坐标：添加第一个坐标作为最后一个坐标
if (!coordinates.isEmpty()) {
    coordinates.add(coordinates.get(0));
}
GeometryFactory geometry = new GeometryFactory();
range.setShape(geometry.createPolygon(coordinates.toArray(new Coordinate[0])));

range = rangeRepository.save(range);

4. 验证Oracle数据库空间配置 #

确保Oracle数据库已启用空间扩展（MDSYS用户存在，SDO_GEOMETRY类型可用），如果是新数据库，需执行Oracle空间组件的初始化脚本。

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