代码之家 › 专栏 › 技术社区 › SRI HARSHA S V S

具有可为空IN子句的JPA自定义查询

spring-data spring-data-jpa jpa java

SRI HARSHA S V S · 技术社区 · 1 年前

我正试图使用JPA在我的SpringBoot应用程序中编写一个自定义查询,我有一个in子句,其中有一个值列表(列表也可以为null)。我写了一个这样的查询:

@Query( "SELECT new com.mypackage.model.CustomOutput( AVG(t.time) ) " +
        "FROM MyTable t WHERE " +
        "t.time IS NOT NULL AND t.updatedTime > :after AND t.updatedTime < :before " +
        "AND " +
        "( (:filterA) IS NULL OR t.columnA IN (:filterA) ) AND " +
        "( (:filterB) IS NULL OR t.columnB IN (:filterB) ) AND " +
        "( (:filterC) IS NULL OR t.columnC IN (:filterC) ) AND " +
        "( (:filterD) IS NULL OR t.columnD IN (:filterD) )"
)
CustomOutput findCustomOutput(
        @Param("filterA") List<String> filterA,
        @Param("filterC") List<String> filterC,
        @Param("filterB") List<String> filterB,
        @Param("filterD") List<String> filterD,
        @Param("after") Date after,
        @Param("before") Date before
);

所有筛选器-A、B、C、D都可以是值列表或null。当任何筛选器为

无效的
单个值。

当存在多个值时,查询失败 (例如:过滤器-A、B、C为空,D为两个值的列表)。这个例外情况是:

2024-06-28 12:32:32,543 [collectorsCluster_Worker-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 920, SQLState: 42000
2024-06-28 12:32:32,544 [collectorsCluster_Worker-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00920: invalid relational operator

2024-06-28 12:32:32,559 [collectorsCluster_Worker-1] ERROR c.c.d.job.servicenow.ServiceNowJob - Unexpected error occurred - 
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy231.findCustomOutput(Unknown Source)

数据库是Oracle,有人能指出语法上的问题吗?

2 回复 | 直到 1 年前

Lesiak 1 年前

问题在 (:filterA) IS NULL 子句,该子句对于大于一个元素的集合失败。

看见 Check that a List parameter is null in a Spring data JPA query 针对建议的解决方案

使用聚结
在查询中使用SpEL(您似乎在使用Spring,查看堆栈跟踪)

另一种选择是使用CriteriaAPI而不是JPQL,并且仅当传入的列表不为空时才添加列表过滤器。

SRI HARSHA S V S 1 年前

根据@Lesiak的回答修改我的自定义查询:

@Query( "SELECT new com.mypackage.model.CustomOutput( AVG(t.time) ) " +
        "FROM MyTable t WHERE " +
        "t.time IS NOT NULL AND t.updatedTime > :after AND t.updatedTime < :before " +
        "AND " +
        "( COALESCE(:filterA, null) IS NULL OR t.columnA IN (:filterA) ) AND " +
        "( COALESCE(:filterB, null) IS NULL OR t.columnB IN (:filterB) ) AND " +
        "( COALESCE(:filterC, null) IS NULL OR t.columnC IN (:filterC) ) AND " +
        "( COALESCE(:filterD, null) IS NULL OR t.columnD IN (:filterD) )"
)
CustomOutput findCustomOutput(
        @Param("filterA") List<String> filterA,
        @Param("filterC") List<String> filterC,
        @Param("filterB") List<String> filterB,
        @Param("filterD") List<String> filterD,
        @Param("after") Date after,
        @Param("before") Date before
);