Hibernate 6 migration, issue with empty collection parameter

Question

The Springboot migration from 2.7.x to 3.0.1 is not going smoothly on my project.

I'm currently facing multiple issues regarding the interpretation of the custom queries used in our repositories. Those queries were working fine before the migration.

I can't get my head around the migration of this kind of query :

@Query("""
        SELECT DISTINCT new com.dto.NameIdDto(ws.id, ws.name)
        FROM UserRole ur
        JOIN ur.workspace ws
        WHERE (COALESCE(:workspaceIds) IS NULL OR ws.id IN (:workspaceIds))
        """)
List<NameIdDto> findWorkspacesWithUsers(Collection<Long> workspaceIds);

@Entity
public class UserRole {

    @Id
    @GeneratedValue(generator = MY_SEQUENCE)
    private Long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "workspace_id")
    private Workspace workspace;

}

@Entity
public class Workspace  {

    @Id
    @GeneratedValue(generator = MY_SEQUENCE)
    private Long id;

    @OneToMany(fetch = LAZY, mappedBy = "workspace", cascade = ALL, orphanRemoval = true)
    private Set<UserRole> userRoles = new HashSet<>();

}

When running the following tests


@Test
public void shouldReturnEmptyForNullParameter() {
    assertThat(workspaceRepository.findWorkspacesWithUsers(null)).isEmpty();
}

@Test
public void shouldReturnEmptyForEmptyParameter() {
assertThat(workspaceRepository.findWorkspacesWithUsers(Collections.emptyList())).isEmpty();
}

shouldReturnEmptyForEmptyParameter fails with the exceptions :

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [[]] did not match expected type [BasicSqmPathSource(id : Long) ]

    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:371)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
    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:184)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:134)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218)
    at jdk.proxy2/jdk.proxy2.$Proxy121.findWorkspacesWithUsers(Unknown Source)
    at com.example.TestApplicationTests.shouldReturnEmptyForNullOrEmptyParameter(TestApplicationTests.java:26)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
Caused by: java.lang.IllegalArgumentException: Parameter value [[]] did not match expected type [BasicSqmPathSource(id : Long) ]
    at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:119)
    at org.hibernate.query.spi.AbstractCommonQueryContract.setParameter(AbstractCommonQueryContract.java:860)
    at org.hibernate.query.spi.AbstractSelectionQuery.setParameter(AbstractSelectionQuery.java:708)
    at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:1222)
    at org.hibernate.query.sqm.internal.QuerySqmImpl.setParameter(QuerySqmImpl.java:129)
    at org.springframework.data.jpa.repository.query.QueryParameterSetter$BindableQuery.setParameter(QueryParameterSetter.java:322)
    at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$4(QueryParameterSetter.java:113)
    at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$1.execute(QueryParameterSetter.java:136)
    at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:113)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:83)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:75)
    at org.springframework.data.jpa.repository.query.ParameterBinder.bindAndPrepare(ParameterBinder.java:97)
    at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateQuery(AbstractStringBasedJpaQuery.java:107)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:234)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:127)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:148)
    at  org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 78 more
Caused by: org.hibernate.type.descriptor.java.CoercionException: Cannot coerce value `[]` [java.util.Collections$EmptyList] as Long
    at org.hibernate.type.descriptor.java.LongJavaType.coerce(LongJavaType.java:155)
    at org.hibernate.type.descriptor.java.LongJavaType.coerce(LongJavaType.java:24)
    at org.hibernate.query.internal.QueryParameterBindingImpl.coerce(QueryParameterBindingImpl.java:144)
    at org.hibernate.query.internal.QueryParameterBindingImpl.setBindValue(QueryParameterBindingImpl.java:111)
    ... 107 more

OR (yes OR, I've not identified the reason why when executing the same test multiple times, I get one or the other exception)

java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1

    at org.hibernate.persister.entity.AbstractEntityPersister.isSelectable(AbstractEntityPersister.java:2039)
    at org.hibernate.persister.entity.AbstractEntityPersister.fetchProcessor(AbstractEntityPersister.java:2012)
    at org.hibernate.loader.ast.internal.LoaderSqlAstCreationState.visitFetches(LoaderSqlAstCreationState.java:118)
    at org.hibernate.sql.results.graph.AbstractFetchParent.afterInitialize(AbstractFetchParent.java:32)
    at org.hibernate.sql.results.graph.entity.AbstractEntityResultGraphNode.afterInitialize(AbstractEntityResultGraphNode.java:100)
    at org.hibernate.persister.entity.AbstractEntityPersister.createDomainResult(AbstractEntityPersister.java:1300)
    at org.hibernate.persister.entity.AbstractEntityPersister.selectFragment(AbstractEntityPersister.java:1892)
    at org.hibernate.query.sql.internal.SQLQueryParser.resolveProperties(SQLQueryParser.java:236)
    at org.hibernate.query.sql.internal.SQLQueryParser.substituteBrackets(SQLQueryParser.java:159)
    at org.hibernate.query.sql.internal.SQLQueryParser.process(SQLQueryParser.java:63)
    at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.<init>(NativeSelectQueryPlanImpl.java:54)
    at org.hibernate.engine.query.internal.NativeQueryInterpreterStandardImpl.createQueryPlan(NativeQueryInterpreterStandardImpl.java:39)
    at org.hibernate.query.sql.internal.NativeQueryImpl.createQueryPlan(NativeQueryImpl.java:649)
    at org.hibernate.query.sql.internal.NativeQueryImpl.lambda$resolveSelectQueryPlan$7(NativeQueryImpl.java:610)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveSelectQueryPlan(QueryInterpretationCacheStandardImpl.java:83)
    at org.hibernate.query.sql.internal.NativeQueryImpl.resolveSelectQueryPlan(NativeQueryImpl.java:608)
    at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:602)
    at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363)
    at org.hibernate.query.Query.getResultList(Query.java:94)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:401)
    at jdk.proxy2/jdk.proxy2.$Proxy136.getResultList(Unknown Source)
    at com.example.repository.CatalogItemRepository.findModelingAndHarmDefinitions(CatalogItemRepository.java:26)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:752)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:703)
    at com.example.repository.CatalogItemRepository$$SpringCGLIB$$0.findModelingAndHarmDefinitions(<generated>)
    at com.example.TestApplicationTests.shouldBuildNamedQuery(TestApplicationTests.java:31)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)

There's nothing fancy in my pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>MyExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.1</version>
        <relativePath/>
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>17</java.version>
        <testcontainers.version>1.17.6</testcontainers.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>testcontainers</artifactId>
            <version>${testcontainers.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>${maven-compiler-plugin.version}</version>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Nor in my application.properties

spring.datasource.password=postgres
spring.datasource.username=postgres
spring.datasource.driver-class-name=org.testcontainers.jdbc.ContainerDatabaseDriver
spring.datasource.url=jdbc:tc:postgresql:12:///

spring.jpa.hibernate.ddl-auto = update

spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

The issue clearly comes from the fact that the collection is empty. I've therefore tried to update the query to : WHERE (COALESCE(:workspaceIds,NULL) IS NULL OR :#{#workspaceIds.size()} = 0 OR ws.id IN (:workspaceIds) )

The test shouldReturnEmptyForNullParameter then fails with the following exception :

org.springframework.expression.spel.SpelEvaluationException: EL1011E: Method call: Attempted to call method size() on null context object

    at org.springframework.expression.spel.ast.MethodReference.throwIfNotNullSafe(MethodReference.java:154)
    at org.springframework.expression.spel.ast.MethodReference.getValueRef(MethodReference.java:83)
    at org.springframework.expression.spel.ast.CompoundExpression.getValueRef(CompoundExpression.java:70)
    at org.springframework.expression.spel.ast.CompoundExpression.getValueInternal(CompoundExpression.java:91)
    at org.springframework.expression.spel.ast.SpelNodeImpl.getTypedValue(SpelNodeImpl.java:117)

Am I missing something?

Thank you for you help.

En empty collection isn't null, and thus will lead to an illegal query with an empty `in`, I would argue that it succeeded falsely in previous versions of Hibernate. The expression won't work as it will be called for `null` so you would need to cather for that case as well. Why not simply write 2 methods and dispatch to one or the other, or just use the `Criteria` API to dynamically generate the query. — M. Deinum, Jan 10 '23 at 12:19

score 1 · Answer 1 · answered Jan 11 '23 at 06:53

Thank you for your answers. Using the Criteria API would imply a lot of refactoring some involving complex recursive queries.

After posting my message here, I found the following explanation https://hibernate.atlassian.net/browse/HHH-15743

The proposed solution involves using Spring Data Specification which would in my case also implies some refactoring. Too bad empty lists are not supported anymore.

I ended renaming the existing method as follow :

@Query("""
        SELECT DISTINCT new com.dto.NameIdDto(ws.id, ws.name)
        FROM UserRole ur
        JOIN ur.workspace ws
        WHERE (COALESCE(:workspaceIds) IS NULL OR ws.id IN (:workspaceIds))
        """)
List<NameIdDto> findWorkspacesWithUsersInternal(Collection<Long> workspaceIds);

And adding the following method :

default List<NameIdDto> findWorkspacesWithUsersInternal(Collection<Long> workspaceIds) {
    return findWorkspacesWithUsersInternal(CollectionUtils.isEmpty(workspaceIds) ? null : workspaceIds);
}

You should rather be using `findAll()` instead if the `workspaceIds` are empty. Having all of these nonsensical predicates in the query is the root of the problem that I tried to explain in the JIRA issue. I guess your examples here are just that, examples, but I think you could benefit from using Blaze-Persistence Entity-Views (https://github.com/Blazebit/blaze-persistence#entity-view-usage) here, as you can get rid of the custom query and model all your filters nicely through Spring Data `Specification` objects. — Christian Beikov, Jan 11 '23 at 12:44

score -1 · Answer 2 · answered Jan 10 '23 at 18:04

-1

An empty collection can't be null, and thus will lead to an illegal query with an empty in in your query.

We would argue that it succeeds falsely in previous versions of Hibernate.

The expression cannot work as it will be called for null so you would need to check for that case as well.

Try writing two methods and dispatch to one or the other, depending on whether it returns null or not; or better use the Criteria API to dynamically generate query.

answered Jan 10 '23 at 18:04

Rayyan Karimi

63
3

As the op mentioned, switching to criteria API involves a lot of refactoring for existing codebases.There should be a proven, recommended path for @Query usages at least during the migration phase – edigu Jul 01 '23 at 15:23

Hibernate 6 migration, issue with empty collection parameter

2 Answers2