java - org.hibernate.engine.jdbc.spi.SqlExceptionHelper - 找不到函数 "WITHIN"； SQL语句

Question

在这里，我试图找到所有在我提供的范围内的实体。我的意思是，如果我给出一个特定半径的圆，它将必须显示位置坐标位于给定圆内的所有实体。

我正在使用 hibernate-spatial 来实现这一点。但是在 JPA Repository 接口(interface)中出现上述错误。

这里是pom.xml,

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<dependency>
    <groupId>org.opengeo</groupId>
    <artifactId>geodb</artifactId>
    <version>${project.version}</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

Jpa 存储库，

public interface ResourceRepository extends ExtendedJpaRepository<Resource, String> {   
    @Query(value = "select resource from Resource resource where within(resource.address.location, :circle) = true")
    List<Resource> test(@Param("circle") Geometry circle);
}

资源.java,

@Entity
@NoArgsConstructor
public class Resource extends UUIDEntity2 implements IsResource {

    @Type(type = "org.hibernate.spatial.GeometryType")
    @OneToOne
    private Address address;

    /*getters setters*/
}

地址.java,

@Entity
public class Address extends UUIDEntity2 implements HasEmailAddress, HasLocation {

    @Embedded
    @Column(columnDefinition = "point")
    private Location location;
    /*getters setters*/
}

location.java,

@Embeddable
@Value(staticConstructor = "of")
@RequiredArgsConstructor(staticName = "of")
public class Location implements Serializable {

    @Column(nullable = true)
    private Double lat;

    @Column(nullable = true)
    private Double lon;
}

测试，

    @Inject
    private ResourceRepository resourceRepository;

    public Geometry createCircle(double x, double y, double radius) {
        GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
        shapeFactory.setNumPoints(32);
        shapeFactory.setCentre(new Coordinate(x, y));
        shapeFactory.setSize(radius * 2);
        return shapeFactory.createCircle();
    }

    @Test
    public void geometry(){
        Geometry m = createCircle(0.0, 0.0, 5);
        List<Resource> resources = resourceRepository.test(m);
    }

application.properties,

hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect

注意:此处未显示实体的所有属性。我正在关注的引用资料:Hibernate-Spatial

Answer 1

听起来您没有为 MySQL 配置 SpatialDialect。你能验证你是否有这条线

hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect

在 hibernate.properties 文件中。

您还可以检查日志以查看 Hibernate 实际使用的方言。它的名称中应该包含“Spatial”，以便 hibernate 时可以使用 Spatial 函数。