为Oracle休眠空间API查询

Question

我尝试在Spring引导中使用空间API。

• 我能够将SDO_GEOMETRY数据类型保存到oracle数据库中
• 我可以使用SQL查询来检索它
• 唯一的问题是，如果我使用Hibernate API，它会抛出一个错误(不想使用SQL(

我测试了以下SQL查询，它运行良好，所以问题不在数据库中

SELECT
s.ID,
s.LOCATION
FROM PORTS s
where SDO_WITHIN_DISTANCE(
s.LOCATION,
SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE( 24.817768,46.599417, NULL),NULL, NULL
),
'distance=10 unit=KM'
) = 'TRUE';

我的实体：

@Entity
@Table(name = "ports")
@Getter @Setter
@NoArgsConstructor
public class JpaPort {
@Id
@Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@Column(name = "location",columnDefinition="SDO_GEOMETRY")
private Geometry location;
private String name;
}

我的存储库查询：

portRepository.findAll(filterWithinRadius(portDTO.getLat(), portDTO.getLon(), portDTO.getRangeInMeters()));

规格：

public static Specification<JpaPort> filterWithinRadius(double latitude, double longitude, double radius) {
return new Specification<JpaPort>() {
@Override
public Predicate toPredicate(Root<JpaPort> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
GeometryFactory factory = new GeometryFactory();
Geometry comparisonPoint = factory.createPoint(new Coordinate(latitude,longitude));
comparisonPoint.setSRID(8307);
Expression<Geometry> dbPoint = root.get("location").as(Geometry.class);
Expression<Boolean> expression = builder.function("SDO_WITHIN_DISTANCE", boolean.class,
dbPoint, builder.literal(comparisonPoint),builder.literal("DISTANCE=1 UNIT=MILE"));
return builder.equal(expression, true);
}
};
}

如果我使用Hibernate，我得到以下错误

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
oracle.jdbc.OracleDatabaseException: ORA-01722: invalid number

我不知道我的这种方法是正确的/错误的，因为我是春季开发的新手，如果你有任何其他解决方案，请随时回答或评论。

是否有其他方法可以在Hibernate中进行Spatial API查询？

我的源代码-https://drive.google.com/file/d/1loSLFg3Cok9iwtv3apXP3f59QiJdI2eS/view?usp=sharing

Answer 1

您必须将结果与varchar'TRUE'进行比较，就像您的SQL示例中一样。使用此：

Expression<String> expression = builder.function("SDO_WITHIN_DISTANCE", String.class,
dbPoint, builder.literal(comparisonPoint),builder.literal("DISTANCE=1 UNIT=MILE"));
return builder.equal(expression, "TRUE");