Skip to content
Advertisement

Validation failed for query in tests but not in production

Problem description

I have the following test class with a few tests. The setUp() method executes insert statements from a file. The first test simply makes a post request and expects an array of JSON objects in return with a 200(OK) status:

JavaScript

Upon running the first test I get the following error:

JavaScript

What I Tried

Spring obviously does not like the query – Validation failed for query for method public...

Many other users have stumpled upon the same error and often the accepted solution would be to mark the query in the repository as a native query: @Query(value="", native=true). Below is how the query that fails looks like. It is a mix of JPQL(because of the new api.coloradodashboard...) and SQL(because of the DATE_FORMAT function).

JavaScript

The application runs perfectly when I start it and make a request to the same endpoint with Postman. The above stated problem occurs only when running the test.

I tried disabling hibernate validation by adding spring.jpa.properties.javax.persistence.validation.mode=none in the application.properties, but that does not help.

Question

Is there any annotation or setting to disable the query validation that I am missing or is it simply not possible and the only solution is converting all queries to native ones?

I think it is important to mention that the application runs with MySQL and the tests are run with H2:

JavaScript

Advertisement

Answer

There is no DATE_FORMAT function in H2. H2 has FORMATDATETIME function, however I can’t say if it works exactly the same. As you’ve noticed, DATE_FORMAT isn’t a standard JPA function either, so the JPA provider is not required to translate this name into database-specific dialects of SQL.

I’d recommend to restructure your code so that InkUsageDto stores the date itself. Move date formatting to Java in places where the InkUsageDto objects are used. This will save you from dealing with peculiarities of different database vendors.

Advertisement