How can I make JPQL query compatible with h2 on an integration test

Question

I'm doing and integration test which calls a service which in turn calls a repository, there I have a JPQL query that is working in prod (MySQL) but not in on the integration test (h2).

If possible I would like a solution different to setting the query to native since that would be suboptimal.

    @Query("SELECT vrf  " +
            "FROM GoogleRequiredField vrf INNER JOIN vrf.googleRequiredFieldProducts p  " +
            "INNER JOIN vrf.googleRequiredFieldOperations o " +
            "INNER JOIN vrf.googleRequiredFieldLanguages l " +
            "WHERE p.product = :product and o.operation = :operation and l.locale = :locale ")

The exception is the following one

WARN  o.h.e.j.s.SqlExceptionHelper - SQL Error: 42001, SQLState: 42001
ERROR o.h.e.j.s.SqlExceptionHelper - Syntax error in SQL statement "SELECT GOOGLEREQU0_.FIELD_ID AS FIELD_ID6_16_0_, GOOGLEREQU0_.ID AS ID1_16_0_, GOOGLEREQU0_.ID AS ID1_16_1_, GOOGLEREQU0_.CREATED_ON AS CREATED_2_16_1_, GOOGLEREQU0_.FIELD_ID AS FIELD_ID6_16_1_, GOOGLEREQU0_.LAST_MODIFIED AS LAST_MOD3_16_1_, GOOGLEREQU0_.ORDER[*] AS ORDER4_16_1_, GOOGLEREQU0_.PRODUCT AS PRODUCT5_16_1_ FROM GOOGLE_VRF_FIELD_PRODUCT GOOGLEREQU0_ WHERE GOOGLEREQU0_.FIELD_ID=? "; expected "identifier"; SQL statement:
select googlerequ0_.field_id as field_id6_16_0_, googlerequ0_.id as id1_16_0_, googlerequ0_.id as id1_16_1_, googlerequ0_.created_on as created_2_16_1_, googlerequ0_.field_id as field_id6_16_1_, googlerequ0_.last_modified as last_mod3_16_1_, googlerequ0_.order as order4_16_1_, googlerequ0_.product as product5_16_1_ from google_vrf_field_product googlerequ0_ where googlerequ0_.field_id=? [42001-197]

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

Thanks!

What you mean by "not working"? Any exception, or unexpected result? — Alex Salauyou, May 23 '19 at 15:06
@Lesiak yes I've set up the h2 dialect ``` jpa: hibernate: ddl-auto: validate properties: hibernate: dialect: org.hibernate.dialect.H2Dialect ``` — Tytalus, May 23 '19 at 18:50

score 0 · Answer 1 · answered May 23 '19 at 19:12

Found an answer it seems the entity we were persisting had a field name order which is a reserved keyword in H2, and it seems it's not escaped hence the error. Renamed order field everything works now.

Link to a question about the same issue:

hibernate h2 embeddable list expected "identifier"

How can I make JPQL query compatible with h2 on an integration test

1 Answers1