hibernate - spring boot创建的查询无效

Question

我有一个使用spring boot 2.7.4和spring data JPA连接到AS400的小应用程序。它一直工作得很好。现在我试图将其升级到spring boot 3并得到这个错误

ERROR 10876 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select * from (select s1_0.id c0,s1_0.message c1,row_number() over() rn from table1 s1_0) r_0_ where  order by r_0_.rn]] with root cause
com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword BY not expected. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN NOT.
at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:948) ~[jt400-11.1.jar:JTOpen 11.1]

我不知道为什么要创建第二个选择语句(语法错误)来包含看似正确的选择语句。

build.gradle

plugins {
id 'java'
id 'org.springframework.boot' version '3.0.0'
id 'io.spring.dependency-management' version '1.1.0'
}
group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '17'
repositories {
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
implementation group: 'net.sf.jt400', name: 'jt400', version: '11.1'
implementation 'org.springframework.boot:spring-boot-starter-web'
//implementation 'com.ibm.db2:jcc'
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

属性

spring.datasource.driver-class-name=com.ibm.as400.access.AS400JDBCDriver
spring.datasource.username=**
spring.datasource.password=**
spring.datasource.url=jdbc:as400://**
spring.datasource.hikari.connection-test-query:values 1
spring.jpa.generate-ddl=false

使用JPA存储库

public interface Table1Repository extends JpaRepository<Table1, Long> {

}

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
@Entity
@Table(name = "TABLE1")
public class Table1 {
@Id
private Long id;

private String message;

public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}

public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
}

这是导致这个错误的调用

tablel1Repository.findAll();

在spring boot 2.7.4下可以正常工作，而在SB 3.0.0下则不能。

Answer 1

select s1_0.id c0,s1_0.message c1,row_number() over() rn from table1 s1_0) r_0_ where  order by r_0_.rn

where order by

是无效的SQL语法。我不知道为什么sb3会这样做，但你可以看看这是否是一个开放的问题。

Answer 2

所以问题是100%的hibernate包含在sb3的版本。我能够通过覆盖我的build.gradle中的hibernate版本来解决这个问题:

ext['hibernate.version'] = '6.1.7.Final'