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.

Answer 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.

Answer 2

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);
}