英文:
Exception thrown in jpa query with List parameter after Spring Boot 3 upgrade
问题
我有一个帐户实体:
@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Account {
@Id
@GeneratedValue
@JsonIgnore
private Long id;
private String accountName;
private String supplierName;
private String customerName;
private Double amount;
还有一个使用JPA查询的帐户存储库,该查询应该根据帐户名称列表进行筛选,或者在列表为null时不进行筛选:
@RepositoryRestResource
public interface AccountRestResource extends PagingAndSortingRepository<Account, Long>, CrudRepository<Account, Long> {
@Query("SELECT DISTINCT account.customerName " +
"FROM Account account " +
"WHERE UPPER(account.customerName) LIKE CONCAT('%', UPPER(:filter), '%') " +
"AND ((:accountNames) IS NULL OR account.accountName IN (:accountNames)) " +
"ORDER BY account.customerName")
Page<String> findDistinctCustomerNames(List<String> accountNames, String filter, Pageable page);
}
在升级到最新的Spring Boot 3后,这个查询偶尔会失败(不规律...我会说五次中有一次),并出现以下错误:
org.springframework.orm.jpa.JpaSystemException: Unknown wrap conversion requested: java.util.ArrayList to java.lang.String : `org.hibernate.type.descriptor.java.StringJavaType` (java.lang.String)
Pom文件包含以下依赖项:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>jakarta.inject</groupId>
<artifactId>jakarta.inject-api</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- Test-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
当列表为NULL时,我没有问题,但当我尝试使用列表中的单个项目进行查询时,回退到Spring Boot 2.7.X会解决问题。
我有一个带有用于重现错误的测试的存储库:https://github.com/Jef-VDD/hibernate-demo
英文:
I have an account entity:
@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Account {
@Id
@GeneratedValue
@JsonIgnore
private Long id;
private String accountName;
private String supplierName;
private String customerName;
private Double amount;
and an account repository with jpa query that should filter accounts by a list of account names or no filter when the list is null:
@RepositoryRestResource
public interface AccountRestResource extends PagingAndSortingRepository<Account, Long>, CrudRepository<Account, Long> {
@Query("SELECT DISTINCT account.customerName " +
"FROM Account account " +
"WHERE UPPER(account.customerName) LIKE CONCAT('%',UPPER(:filter),'%') " +
"AND ((:accountNames) IS NULL OR account.accountName IN (:accountNames)) " +
"ORDER BY account.customerName")
Page<String> findDistinctCustomerNames(List<String> accountNames, String filter, Pageable page);
}
After upgrading to the latest Spring Boot 3 upgrade this Query fails from time to time with (irregularly... I'd say 1 in 5)
> org.springframework.orm.jpa.JpaSystemException: Unknown wrap conversion requested: java.util.ArrayList to java.lang.String : org.hibernate.type.descriptor.java.StringJavaType
(java.lang.String)
Pom contains following dependencies:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>jakarta.inject</groupId>
<artifactId>jakarta.inject-api</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!-- Test-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
I don't have problems when the list is NULL but when I try the query with a single item in the list . Reverting to the Spring Boot 2.7.X resolves the issue.
I have a repo with a test to reproduce the error: https://github.com/Jef-VDD/hibernate-demo
答案1
得分: 3
你可以将 H2 降级到版本 1.4.200
。
我的同事建议我们可以将此条件从:
AND (COALESCE(:accountNames) IS NULL OR account.accountName IN :accountNames)
更改为:
AND (:#{null eq #accountNames} is true or account.accountName in :accountNames)
我已经在 Oracle 和 H2(Oracle 模式)与 Spring Boot 2.7 上进行了测试。
英文:
You can downgrade H2 to the version 1.4.200
My colleague suggest that we can change this condition from:
AND (COALESCE(:accountNames) IS NULL OR account.accountName IN :accountNames)
to:
AND (:#{null eq #accountNames} is true or account.accountName in :accountNames)
I've tested with Oracle and H2 (Mode Oracle) with Spring Boot 2.7
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论