英文:
MySQL syntax error in generated JPA Repository Pageable query: near 'offset x rows fetch first y rows only'
问题
I am trying to get some rows from my table with pagination by Spring Boot JPA Repository, but MySQL keeps showing a syntax error in the query that was generated by JPA.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'offset 0 rows fetch first 1 rows only' at line 1
This is my repository
@Repository
public interface MyRepository extends JpaRepository<MyEntity, Integer> {
ArrayList<MyEntity> findById(Integer id, Pageable pageable);
}
and the generated query by JPA
select ... from myTable c1_0 order by c1_0.id desc offset 0 rows fetch first 1 rows only
Perhaps, I am still using JDBC driver for SQL
Here is a part in pom.xml file
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>
And here is application.properties
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.database=mysql
How can I get rid of this?
英文:
I am trying to get some rows from my table with pagination by Spring Boot JPA Repository, but MySQL keeps showing a syntax error in the query that was generated by JPA.
It seems MySQL only supports limit y offset x, instead of offset x rows fetch first y rows only (I am testing with the first page, page: 0 and page_size: 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'offset 0 rows fetch first 1 rows only' at line 1
This is my repository
@Repository
public interface MyRepository extends JpaRepository<MyEntity, Integer> {
ArrayList<MyEntity> findById(Integer id, Pageable pageable);
}
and the generated query by JPA
select ... from myTable c1_0 order by c1_0.id desc offset 0 rows fetch first 1 rows only
Perhaps, I am still using JDBC driver for SQL
Here is a part in pom.xml file
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>
And here is application.properties
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.database=mysql
How can I get rid of this?
答案1
得分: 0
问题是我的项目正在使用来自Spring Cloud配置服务器的配置。
spring.jpa.properties.hibernate.dialect 配置值为 org.hibernate.dialect.H2Dialect。这就是为什么JPA会生成与MySQL不完全兼容的SQL查询（在我的情况下是 offset 和 limit）。
通过使用VM选项将其改回 org.hibernate.dialect.MySQLDialect 解决了我的问题。
英文:
The problem is my project is using the configuration from a Spring cloud config server.
The spring.jpa.properties.hibernate.dialect config value was org.hibernate.dialect.H2Dialect. That's why JPA keep generating SQL query that didn't fully work with MySQL (in my case: offset and limit).
Changing it back to org.hibernate.dialect.MySQLDialect using VM options solved my problem.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论