CODEWITHSUNDEEP
mysqlhibernatejpaspring-booth2
user1653027
user1653027

Reputation: 809

Spring boot jpa H2 compatibility mode MYSQL not working

I am using Date function in my query so I am trying to run H2 in MYSQL mode for my SpringBoot JPA application. I have added url: jdbc:h2:mem:testdb;Mode=MYSQL in my application.yml file because I got the error org.h2.jdbc.JdbcSQLException: Function "DATE" not found; SQL statement.

But even after adding that mode=mysql property in aplicaiton.ymll file I am still getting same error. Is there anything else I need to add in any where?

Here is my Application.yml

spring: 
  datasource:
    url: jdbc:h2:mem:test;Mode=MYSQL

My Query

@Query(value = "select * from user where  Date(created_date) <= ?1 and Date(modified_date) <= ?1", nativeQuery = true)
List<Users> usersByDate(String date );

I am creating the table schema by spring/hibernate with the javax.persistence annotations in my entity. I am using spring-boot 1.4.2 and spring-data-jpa 1.10.5 . Unable to understand why it is not working for me.

Upvotes: 2

Views: 3308

Answers (1)

Sudipto Sarkar
Sudipto Sarkar

Reputation: 74

I was able to make this work (although it feels more like a hack). They do have an implementation, but they've provided it as a pluggable module for some reason. The class in question is this: https://github.com/h2database/h2database/blob/master/h2/src/main/org/h2/mode/FunctionsMySQL.java

To top off the list of problems, this class isn't packaged in the maven jar currently available.

So I copied it in my source. For non-spring uses, this needs to be called: FunctionsMYSQL.register(connection);

For spring-jdbc based uses, since we won't have direct access to java.sql.Connection, (in my case spring boot jdbc starter), this sql needs to be executed before using the function: CREATE ALIAS IF NOT EXISTS DATE FOR "org.h2.mode.FunctionsMySQL.date";

This class also has implementations for UNIX_TIMESTAMP and FROM_UNIXTIME. I used UNIX_TIMESTAMP by plugging it into schema.sql (which spring-jdbc automatically executes in the beginning).

Upvotes: 2

Related Questions