在Spring Boot 3升级后,使用List参数的JPA查询中抛出了异常。

huangapple go评论145阅读模式
英文:

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&lt;Account, Long&gt;, CrudRepository&lt;Account, Long&gt; {
    @Query(&quot;SELECT DISTINCT account.customerName &quot; +
            &quot;FROM Account account &quot; +
            &quot;WHERE UPPER(account.customerName) LIKE CONCAT(&#39;%&#39;,UPPER(:filter),&#39;%&#39;) &quot; +
            &quot;AND ((:accountNames) IS NULL OR account.accountName IN (:accountNames)) &quot; +
            &quot;ORDER BY account.customerName&quot;)
    Page&lt;String&gt; findDistinctCustomerNames(List&lt;String&gt; 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:

	&lt;dependencies&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
			&lt;artifactId&gt;spring-boot-starter&lt;/artifactId&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
			&lt;artifactId&gt;spring-boot-starter-data-rest&lt;/artifactId&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
			&lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;
		&lt;/dependency&gt;

		&lt;dependency&gt;
			&lt;groupId&gt;jakarta.inject&lt;/groupId&gt;
			&lt;artifactId&gt;jakarta.inject-api&lt;/artifactId&gt;
			&lt;version&gt;2.0.1&lt;/version&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.postgresql&lt;/groupId&gt;
			&lt;artifactId&gt;postgresql&lt;/artifactId&gt;
			&lt;scope&gt;runtime&lt;/scope&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.liquibase&lt;/groupId&gt;
			&lt;artifactId&gt;liquibase-core&lt;/artifactId&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.projectlombok&lt;/groupId&gt;
			&lt;artifactId&gt;lombok&lt;/artifactId&gt;
		&lt;/dependency&gt;

		&lt;!-- Test--&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
			&lt;artifactId&gt;spring-boot-starter-test&lt;/artifactId&gt;
			&lt;scope&gt;test&lt;/scope&gt;
		&lt;/dependency&gt;
		&lt;dependency&gt;
			&lt;groupId&gt;com.h2database&lt;/groupId&gt;
			&lt;artifactId&gt;h2&lt;/artifactId&gt;
			&lt;scope&gt;test&lt;/scope&gt;
		&lt;/dependency&gt;
	&lt;/dependencies&gt;

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

huangapple
  • 本文由 发表于 2023年3月7日 22:46:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/75663455.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定