我的目标是将地理空间查询功能添加到我的 jhipster 生成的 Spring Boot + MySql 项目中,但我未能正确配置我的 H2 数据库以用于由我的测试和我的开发数据库执行的查询以用于本地部署应用程序。由于我们有严格的 CI/CD 管道,这意味着我还不能在 prod 中进行测试,但我怀疑我也会在那里遇到同样的错误。在测试或开发环境中执行空间查询时出现的错误:org.h2.jdbc.JdbcSQLSyntaxErrorException: Function "WITHIN" not found;
。
有很多帖子和指南解决了这个问题,但它们都没有为我解决这个问题。我遵循了教程 here、有用的文档 here,并尝试了 post 1、post 2、post 3、{{3} 中的解决方案/建议},以及其他几个。我还将我的代码与此 post 4 进行了比较。但我仍然无法克服这个错误。
相关配置... pom.xml:
...
<java.version>1.8</java.version>
<spring-boot.version>2.1.6.RELEASE</spring-boot.version>
<spring.version>5.1.8.RELEASE</spring.version>
<hibernate.version>5.3.10.Final</hibernate.version>
<h2.version>1.4.199</h2.version>
<jts.version>1.13</jts.version>
...
<repositories>
<repository>
<id>OSGEO GeoTools repo</id>
<url>http://download.osgeo.org/webdav/geotools</url>
</repository>
<repository>
<id>Hibernate Spatial repo</id>
<url>http://www.hibernatespatial.org/repository</url>
</repository>
</repositories>
...
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-spatial</artifactId>
</dependency>
<dependency>
<groupId>com.vividsolutions</groupId>
<artifactId>jts</artifactId>
<version>${jts.version}</version>
</dependency>
</dependencies>
我的主要application.yml
:
spring:
jpa:
open-in-view: false
properties:
hibernate.jdbc.time_zone: UTC
hibernate:
dialect: org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect
ddl-auto: none
我的开发环境的application-dev.yml
:
spring:
h2:
console:
enabled: false
jpa:
database-platform: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
database: H2
show-sql: true
hibernate:
dialect: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
我的 application-prod.yml
用于生产:
spring:
jpa:
database-platform: org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect
database: MYSQL
show-sql: false
我的test/application.yml
:
spring:
jpa:
database-platform: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
database: H2
open-in-view: false
show-sql: false
hibernate:
dialect: org.hibernate.spatial.dialect.h2geodb.GeoDBDialect
ddl-auto: none
服务层相关代码:
@Override
@Transactional(readOnly = true)
public Page<myobject> findAllWithinDistanceOfLocation(Float distance,Point location,Pageable pageable) {
log.debug("Request to get all myobject within a distance centered on location");
GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
shapeFactory.setNumPoints(32); // 32 = number of points to define circle. Default is 100. Higher the number,the more accurately drawn the circle
shapeFactory.setCentre(location.getcoordinate());
shapeFactory.setSize(distance * 2);
Geometry areaOfInterest = shapeFactory.createCircle();
return myobjectRepository.findAllWithinCircle(areaOfInterest,pageable);
}
存储库中的相关代码:
@Query("select e from myobjectTable e where within(e.location,:areaOfInterest) = true")
Page<myobject> findAllWithinCircle(@Param("areaOfInterest") Geometry areaOfInterest,Pageable pageable);
数据库配置bean中的相关代码:
/**
* Open the TCP port for the H2 database,so it is available remotely.
*
* @return the H2 database TCP server.
* @throws SQLException if the server failed to start.
*/
@Bean(initMethod = "start",destroyMethod = "stop")
@Profile(JHipsterConstants.SPRING_PROFILE_DEVELOPMENT)
public Object h2TCPServer() throws SQLException {
String port = getValidPortForH2();
log.debug("H2 database is available on port {}",port);
return H2ConfigurationHelper.createServer(port);
}
private String getValidPortForH2() {
int port = Integer.parseInt(env.getProperty("server.port"));
if (port < 10000) {
port = 10000 + port;
} else {
if (port < 63536) {
port = port + 2000;
} else {
port = port - 2000;
}
}
return String.valueOf(port);
}
我为上述属性尝试了不同的值,尝试以基于文档和其他项目的原则性方式进行操作,但似乎无法正常工作。我怀疑我缺少为 WITHIN
创建别名的 h2 初始配置命令,但仍然无法理解它并使其正常工作。
注意:我已经包括和排除了 pom 文件的上述部分,但没有任何效果。