Get Query is not working when using HQL in Hibernate Spatial

Question

I am using Spring boot along with Hibernate for a Spatial query. Libraries used: 1. Spring boot - 2.1.3.RELEASE 2. Hibernate Spatial - 5.3.7.Final 3. MariaDB - mysql Ver 15.1 Distrib 10.1.36-MariaDB

Whenever I use HQL as depicted below in Query, I am getting the following error during application startup however when I try using native query it works.

I have tried with different dialects. Also, tried using columnDefinition with value as geometry, geolatte-geometry.

pom.xml

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-spatial</artifactId>
      <exclusions>
        <exclusion>
          <groupId>org.postgresql</groupId>
          <artifactId>postgresql</artifactId>
        </exclusion>
      </exclusions>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        jdbc:
          lob:
            non_contextual_creation: true
        physical_naming_strategy: com.orange.alc.polygon.dao.config.DefaultNamingStrategy 
        format_sql: false
        dialect: org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect 

@Entity
public class PolygonMasterEntity {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  // Here we have used Geolatte library
  private Polygon geometry;

  @Column(name = "is_active")
  private Boolean active;

  @Column(name = "is_deleted")
  private Boolean deleted;
}

@Repository
public interface PolygonMasterRepository extends JpaRepository<PolygonMasterEntity, Long>,
    JpaSpecificationExecutor<PolygonMasterEntity> {

  @Query("select master from #{#entityName} master WHERE" 
      + " and within(master.geometry, :point)")
  List<PolygonMasterEntity> findCostUsingPointForLLME(
      @Param("point") Point point);

}

Currently, I am getting the following error during startup:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 164 [select master from com.orange.alc.polygon.dao.entity.PolygonMasterEntity master WHERE within(master.geometry, :point)]

~~~~~~~~~   at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93)
~~~~~~~~~   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:277)
~~~~~~~~~   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191)
~~~~~~~~~   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143)
~~~~~~~~~   at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119)
~~~~~~~~~   at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
~~~~~~~~~   at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
~~~~~~~~~   at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:595)
~~~~~~~~~   at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704)
~~~~~~~~~   ... 100 common frames omitted

You cannot use : to delimit properties for spring.jpa.properties they have to use .. The properties are used as is and thus don't support the :. So use hibernate.dialect instead etc. This applies to all the properties under the spring.jpa.properties namespace. See github.com/spring-projects/spring-boot/issues/17975 — M. Deinum
– M. Deinum, Commented Nov 4, 2019 at 8:56
Also, I have debugged it further and the exception is happening form HqlSqlBaseWalker class under package org.hibernate.hql.internal.antlr. It does not have any token for spatial methods. at line org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2146) — Vishal nigam
– Vishal nigam, Commented Nov 4, 2019 at 11:51
Because the wrong dialect is mapped, which is due to use of wrong properties. — M. Deinum
– M. Deinum, Commented Nov 4, 2019 at 12:15

Jens Schauder · Accepted Answer · 2019-11-04 06:39:26Z

0

You have and right behind WHERE in your query. I don't think that is legit HQL and you should drop the and.

answered Nov 4, 2019 at 6:39

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vishal nigam Over a year ago

Tried just now. Even after that, it is not working code corrected

M. Deinum · Accepted Answer · 2019-11-04 09:01:51Z

0

The properties under spring.jpa.properties are used as is and directly passed to JPA as a Map. As Spring (Boot) doesn't parse these you cannot use the normal YAML syntax but have to include the property names as is with a .. This is also explained in this Github issue and the Spring Boot Documentation.

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: none
    properties:
      hibernate.jdbc.lob.non_contextual_creation: true
      hibernate.physical_naming_strategy: com.orange.alc.polygon.dao.config.DefaultNamingStrategy 
      hibernate.format_sql: false
      hibernate.dialect: org.hibernate.spatial.dialect.mysql.MySQLSpatialDialect

Should work and set the proper dialect.

answered Nov 4, 2019 at 9:01

M. Deinum

127k22 gold badges235 silver badges253 bronze badges

4 Comments

Vishal nigam Over a year ago

thank you for the quick response. I have made the changes as suggested. Still no luck. The same error persist.

Vishal nigam Over a year ago

Also, I would like to add those properties with ":" are also working. I can see it in logs. Perhaps, some update fixed that issue.

M. Deinum Over a year ago

No it hasn't. You can see the properties, they simply won't get mapped to the proper place. This is also what is documented.

Vishal nigam Over a year ago

Nevertheless, it is not working even after the changes.

Collectives™ on Stack Overflow

Get Query is not working when using HQL in Hibernate Spatial

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related