logo标签列表

Hibernate 随机抛出错误：无法将值 `[]` [java.util.ArrayList] 强制转换为 Long。

javaspring-boothibernatekotlinspring-data-jpa
7

当我在我的存储库方法中使用List类型的参数时，我遇到了不稳定的错误。这个问题似乎与Kotlin列表和Java之间的互操作性有关。当我运行单元测试时，它会随机失败，我怀疑这与Kotlin中由listOf()返回的MutableList有关。更多信息可以在这个StackOverflow帖子中找到：listOf() returns MutableList。

这是我的单元测试和存储库方法的示例：

            @Test
            fun `should return recipes with the specified author, locale and categories`() {
                println("AuthorID: " + author.id)

                recipeRepo.findRecipesBy(
                    locale = LanguageSelection.ENGLISH,
                    authorIds = arrayListOf(44),
                )
                // it throws before reaching asserts
            }

代码库：

interface RecipeRepository : JpaRepository<Recipe, Long> {

    @Query("SELECT r FROM Recipe r WHERE "
            + "(:authorIds is null or r.author.id in (:authorIds)) "
            + "and (:recipeIds is null or r.id in (:recipeIds)) "
            + "and (:minPrice is null or r.estimatedPrice >= :minPrice) "
            + "and (:maxPrice is null or r.estimatedPrice <= :maxPrice) "
            + "and ( cast(:beforeDate as timestamp) is null or r.createdAt < cast(:beforeDate as timestamp)) "
            + "and ( cast(:afterDate as timestamp) is null or r.createdAt > cast(:afterDate as timestamp)) "
            + "and (:minLikeCount is null or r.likeCount >= :minLikeCount) "
            + "and (:categoryIds is null or exists(select rc from r.categories rc where rc.id in (:categoryIds))) "
    )

    fun findRecipesBy(
        @Param("authorIds") authorIds: ArrayList<Long>? = null,
        @Param("recipeIds") recipeIds: ArrayList<Long>? = null,
        @Param("minPrice") minPrice: Double? = null,
        @Param("maxPrice") maxPrice: Double? = null,
        @Param("beforeDate") beforeDate: Date? = null,
        @Param("afterDate") afterDate: Date? = null,
        @Param("minLikeCount") minLikeCount: Int? = null,
        @Param("categoryIds") categoryIds: ArrayList<Long>? = null,
        sort: Sort = Sort.by(Sort.Direction.ASC, "createdAt"),
    ): List<Recipe>

}

错误：


Parameter value [[44]] did not match expected type [BasicSqmPathSource(id : Long) ]
org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [[44]] did not match expected type [BasicSqmPathSource(id : Long) ]
    at app//org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371)
    at app//org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)
    at app//org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
    at app//org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at app//org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at app//org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at app//org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at app//org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:134)
    at app//org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at app//org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at app//org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at app//org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:94)
    at app//org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at app//org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218)
    at app/jdk.proxy3/jdk.proxy3.$Proxy189.findRecipesBy(Unknown Source)
    at app//com.fittastetic.fittastetic_backend.shared.recipe.repository.RecipeRepository$DefaultImpls.findRecipesBy$default(RecipeRepository.kt:38)
    ...
Caused by: org.hibernate.type.descriptor.java.CoercionException: Cannot coerce value `[44]` [java.util.ArrayList] as Long
    at app//org.hibernate.type.descriptor.java.LongJavaType.coerce(LongJavaType.java:155)
    at app//org.hibernate.type.descriptor.java.LongJavaType.coerce(LongJavaType.java:24)
    at app//org.hibernate.query.internal.QueryParameterBindingImpl.coerce(QueryParameterBindingImpl.java:144)
    at app//org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:111)
    ... 141 more
由于它内部使用了Hibernate，因此会间歇性地在列表中出现问题，建议在这种情况下优先使用原生查询。 - Gaurav Jeswani
确切的错误信息是什么（问题标题可能有点混乱），它在哪里抛出？看一下你的“Recipe”类也可能会有所帮助。 - gidds
我添加了错误日志。食谱类非常基础，但如果有帮助的话，我也可以添加它。 - Just A Question
你可以记录生成的SQL，然后在SQL开发人员中尝试执行它，也许你会发现与映射相关的内容。https://www.baeldung.com/sql-logging-spring-boot - Emanuel Trandafir
1个回答
9

自从Hibernate团队在HBN6中完全重新设计了查询模型，预计某些功能已停止工作，特别是像您这样的情况，实际上看起来像是一个黑客/技巧。在这里，我建议在HBN论坛上提问。

然而，一些解决方法确实存在：

I. 实现此类查询的惯用方式是使用jpa规范，例如：

public interface RecipeRepository extends JpaRepository<Recipe, Long>, JpaSpecificationExecutor<Recipe> {

    default List<Recipe> findRecipesBy(List<Long> authorIds, List<Long> recipeIds) {
        Specification<Recipe> specification = (root, cq, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (authorIds != null && !authorIds.isEmpty()) {
                predicates.add(root.get("author").get("id").in(authorIds));
            }
            if (recipeIds != null && !recipeIds.isEmpty()) {
                predicates.add(root.get("id").in(recipeIds));
            }
            return cb.and(predicates.toArray(new Predicate[0]));
        };
        return findAll(specification);
    }
    
}

II. 使用 coalesce 函数的技巧 - 似乎仅适用于 PostgreSQL：

@Query("SELECT r FROM Recipe r WHERE "
            + "(coalesce(:authorIds) is null or r.author.id in (:authorIds)) "
            + "and (coalesce(:recipeIds) is null or r.id in (:recipeIds)) "
            + "and (:minPrice is null or r.estimatedPrice >= :minPrice) "
            + "and (:maxPrice is null or r.estimatedPrice <= :maxPrice) "
            + "and ( cast(:beforeDate as timestamp) is null or r.createdAt < cast(:beforeDate as timestamp)) "
            + "and ( cast(:afterDate as timestamp) is null or r.createdAt > cast(:afterDate as timestamp)) "
            + "and (:minLikeCount is null or r.likeCount >= :minLikeCount) "
            + "and (coalesce(:categoryIds) is null or exists(select rc from r.categories rc where rc.id in (:categoryIds))) "
    )

三、SpEL技巧 - 将空值检查委托给SpEL：

@Query("SELECT r FROM Recipe r WHERE "
            + "(:#{#authorIds == null} = true or r.author.id in (:authorIds)) "
            + "and (:#{#recipeIds == null} = true or r.id in (:recipeIds)) "
            + "and (:minPrice is null or r.estimatedPrice >= :minPrice) "
            + "and (:maxPrice is null or r.estimatedPrice <= :maxPrice) "
            + "and ( cast(:beforeDate as timestamp) is null or r.createdAt < cast(:beforeDate as timestamp)) "
            + "and ( cast(:afterDate as timestamp) is null or r.createdAt > cast(:afterDate as timestamp)) "
            + "and (:minLikeCount is null or r.likeCount >= :minLikeCount) "
            + "and (:#{#categoryIds == null} = true or exists(select rc from r.categories rc where rc.id in (:categoryIds))) "
    )
看起来不错，稍后我会测试一下。基本上我想实现的是有可选参数，这就是解决这个问题的方法。 - Just A Question
您知道哪种解决方案在性能方面最好吗？ - Just A Question
1
通常，尝试通过单个查询来覆盖所有可能的用户输入并不是一个好主意 - 通常，数据库只维护单个SQL的几个执行计划，认为这些计划将对所有可能的用户输入组合都最优是太天真了。因此，从数据库的角度来看，最好的选择是为特定的输入构建特定的查询 - 这就是选项I。 - Andrey B. Panfilov
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接