Search code examples
javaspringhibernatejpa

Spring JPA issue with IN operator

I have a very simple query. It is not the original one, but I can reproduce the issue with it and is simpler to understand.

@Repository
public interface MyRepository extends JpaRepository<MyEntity, Long> {

 @Query("""
        SELECT COUNT(a) FROM MyEntity e WHERE
        (:types IS NULL OR e.type IN :types)
        """)
    long countByTypesIn(@Param("types") List<Type> types);
}

The idea here is, that types list is null, is not used on the where clause, and if the list is populated, only filter by the elements present on the list.

The entity has apart from other columns:

@Entity
public class MyEntity {
    [...]
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "my_type")
    private Type type;
    [...]
}

Then I create a test, where I call the repository for this count:

Assert.assertEquals(myRepository.countByTypesIn(Collections.singletonList(type)), 1);

The problem I have here, is that sometimes the test is launching the next exception:

java.lang.AssertionError: Unexpected value type (expected : java.lang.Long) : [myType (456)] (java.util.Collections$SingletonList)
    at org.hibernate.sql.exec.internal.JdbcParameterBindingImpl.<init>(JdbcParameterBindingImpl.java:23)

Class Type overrides toString()to show exactly the name of the type and an ID.

That as far I can understand, means that is expecting a long in param :types but receives a collection. But it is a collection, and must be a collection for a IN operator. I do not understand why is looking for a Long

And the worst, is only failing the 50% of the times. Sometimes yes, sometimes not. I have debugged, and the hibernate class JdbcParameterBindingImpl is receiving different parameters in some cases. But the test is exactly the same.

Solution

  • Seems that the issue is related to the :types IS NULL part. Removing it make it works perfectly:

     @Query("""
        SELECT COUNT(a) FROM MyEntity e WHERE
        e.type IN :types
        """)
    long countByTypesIn(@Param("types") List<Type> types);
}