spring JPA Hibernate MariaDB

Question

i am trying to connect to mariaDB v10.2.14 from my spring JPA hibernate application. i am using

driverClassName: com.mysql.jdbc.Driver

and

setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");

i have a query method to find the top first row, by using "findTop1" keywords... when the query is actually formed , it uses 'offset 0 rows fetch next 1 rows only' instead of LIMIT 0,1 ... because of this the maria DB server throws an error...

how can i change my driver / dialect file to form a query using LIMIT clause instead of offset-fetch..

note: i have tried all combinations of mariadbdialect and mariadb driverclass names like , org.hibernate.dialect.MariaDB53Dialect and org.mariadb.jdbc.Driver..

below is the exception i am getting

2018-06-27 11:31:19.936  INFO 10484 --- [nio-8080-exec-2] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select aboutpage0_.id as id1_0_, aboutpage0_.APPLICATION_NAME as APPLICAT2_0_, aboutpage0_.createdDate as createdD3_0_, aboutpage0_.effectiveDate as effectiv4_0_, aboutpage0_.LAST_UPD_TS as LAST_UPD5_0_, aboutpage0_.LAST_UPD_SYSUSR_ID as LAST_UPD6_0_, aboutpage0_.version as version7_0_ from XXXXX aboutpage0_ where aboutpage0_.APPLICATION_NAME=? and aboutpage0_.effectiveDate<=? order by aboutpage0_.LAST_UPD_TS desc, aboutpage0_.effectiveDate desc offset 0 rows fetch next ? rows only
2018-06-27 11:31:20.112  WARN 10484 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2018-06-27 11:31:20.112 ERROR 10484 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset 0 rows fetch next 1 rows only' at line 1
2018-06-27 11:31:20.142 ERROR 10484 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'offset 0 rows fetch next 1 rows only' at line 1

check this https://stackoverflow.com/questions/37066024/what-is-the-mariadb-dialect-class-name-for-hibernate — Peter1982, Jun 27 '18 at 18:23

score 0 · Answer 1 · answered Jun 28 '18 at 04:41

Prettyprinting the query:

select  aboutpage0_.id as id1_0_, aboutpage0_.APPLICATION_NAME as APPLICAT2_0_,
        aboutpage0_.createdDate as createdD3_0_, aboutpage0_.effectiveDate as effectiv4_0_,
        aboutpage0_.LAST_UPD_TS as LAST_UPD5_0_, aboutpage0_.LAST_UPD_SYSUSR_ID as LAST_UPD6_0_,
        aboutpage0_.version as version7_0_
    from  XXXXX aboutpage0_
    where  aboutpage0_.APPLICATION_NAME=?
      and  aboutpage0_.effectiveDate<=?
    order by  aboutpage0_.LAST_UPD_TS desc,
              aboutpage0_.effectiveDate desc
    offset 0 rows fetch next ? rows only 2018-06-27

should make it obvious that something went wrong in creating the SQL from Hibernate. It was probably headed for saying limit 1 offset 0, but failed. (Note: LIMIT comes before OFFSET.)

are u saying that i am using a wrong driver/dialect or , are u saying there is some problem with ASTQueryTranslatorFactory ? — Ram, Jun 29 '18 at 14:27

spring JPA Hibernate MariaDB

1 Answers1