Problem:

I have a Spring Data JPA query that works fine initially but fails after inserting a record three times. The exception message says some named parameters are missing, even though they are passed correctly.

java.lang.IllegalArgumentException: No parameter named ':param3' in query with named parameters [param2, param1]
java.lang.IllegalArgumentException: No parameter named ':param4' in query with named parameters [param2, param1]
java.lang.IllegalArgumentException: No parameter named ':param5' in query with named parameters [param2, param1]

Details:

This happens after inserting an Entity record exactly three times with the same param2 and param3 values. Before that, the query works perfectly no matter how many times it fires. Every subsequent use of this query produces the errors after inserting three Entity records.

Works fine until I insert three (or more) Entity records:

@Query("SELECT ft FROM Entity ft WHERE ft.someField1.param1 = :param1 AND ft.param2 = :param2 " +
        "ORDER BY " +
        "(CASE WHEN ft.param3 = :param3 THEN 0 ELSE 1 END), " +
        "(CASE WHEN ft.param3 = :param3 AND ft.param4 = :param4 AND ft.param5 = :param5 THEN 0 " +
        "     WHEN ft.param3 = :param3 AND (ft.param4 = :param4 OR ft.param5 = :param5) THEN 1 " +
        "     WHEN ft.param3 = :param3 THEN 2 " +
        "     WHEN ft.param4 = :param4 AND ft.param5 = :param5 THEN 3 " +
        "     WHEN (ft.param4 = :param4 OR ft.param5 = :param5) THEN 4 ELSE 5 END), " +
        "ft.updatedTimestamp DESC")
Page<Entity> queryEntitiesWithCustomSorting(
        @Param("param1") String param1,
        @Param("param2") String param2,
        @Param("param3") String param3,
        @Param("param4") EnumA param4,
        @Param("param5") EnumB param5,
        Pageable pageable);

Fix:

I added dummy conditions to ensure that param3, param4, and param5 were always included in the query:

@Query("SELECT ft FROM Entity ft WHERE ft.someField1.param1 = :param1 AND ft.param2 = :param2 " +
        "AND (:param3 IS NULL OR ft.param3 IS NOT NULL) " +
        "AND (:param4 IS NULL OR ft.param4 IS NOT NULL) " +
        "AND (:param5 IS NULL OR ft.param5 IS NOT NULL) " +
        "ORDER BY " +
        "(CASE WHEN ft.param3 = :param3 THEN 0 ELSE 1 END), " +
        "(CASE WHEN ft.param3 = :param3 AND ft.param4 = :param4 AND ft.param5 = :param5 THEN 0 " +
        "     WHEN ft.param3 = :param3 AND (ft.param4 = :param4 OR ft.param5 = :param5) THEN 1 " +
        "     WHEN ft.param3 = :param3 THEN 2 " +
        "     WHEN ft.param4 = :param4 AND ft.param5 = :param5 THEN 3 " +
        "     WHEN (ft.param4 = :param4 OR ft.param5 = :param5) THEN 4 ELSE 5 END), " +
        "ft.updatedTimestamp DESC")
Page<Entity> queryEntityWithCustomSorting(
        @Param("param1") String param1,
        @Param("param2") String param2,
        @Param("param3") String param3,
        @Param("param4") EnumA param4,
        @Param("param5") EnumB param5,
        Pageable pageable);

The fix adds dummy conditions to make sure param3, param4, and param5 were always included in the query:

AND (:param3 IS NULL OR ft.param3 IS NOT NULL)
AND (:param4 IS NULL OR ft.param4 IS NOT NULL)
AND (:param5 IS NULL OR ft.param5 IS NOT NULL)

The issue seems to be related to how Spring Data JPA handles query parameters and its caching behavior.

param3, param4, and param5 were always passed correctly, Spring stopped recognizing them after three records were inserted.

Question:

Has anyone encountered a similar issue, and can you explain what might actually be causing this behavior? Is there a better solution to this problem without using dummy conditions?

Problem:

I have a Spring Data JPA query that works fine initially but fails after inserting a record three times. The exception message says some named parameters are missing, even though they are passed correctly.

java.lang.IllegalArgumentException: No parameter named ':param3' in query with named parameters [param2, param1]
java.lang.IllegalArgumentException: No parameter named ':param4' in query with named parameters [param2, param1]
java.lang.IllegalArgumentException: No parameter named ':param5' in query with named parameters [param2, param1]

Details:

This happens after inserting an Entity record exactly three times with the same param2 and param3 values. Before that, the query works perfectly no matter how many times it fires. Every subsequent use of this query produces the errors after inserting three Entity records.

Works fine until I insert three (or more) Entity records:

@Query("SELECT ft FROM Entity ft WHERE ft.someField1.param1 = :param1 AND ft.param2 = :param2 " +
        "ORDER BY " +
        "(CASE WHEN ft.param3 = :param3 THEN 0 ELSE 1 END), " +
        "(CASE WHEN ft.param3 = :param3 AND ft.param4 = :param4 AND ft.param5 = :param5 THEN 0 " +
        "     WHEN ft.param3 = :param3 AND (ft.param4 = :param4 OR ft.param5 = :param5) THEN 1 " +
        "     WHEN ft.param3 = :param3 THEN 2 " +
        "     WHEN ft.param4 = :param4 AND ft.param5 = :param5 THEN 3 " +
        "     WHEN (ft.param4 = :param4 OR ft.param5 = :param5) THEN 4 ELSE 5 END), " +
        "ft.updatedTimestamp DESC")
Page<Entity> queryEntitiesWithCustomSorting(
        @Param("param1") String param1,
        @Param("param2") String param2,
        @Param("param3") String param3,
        @Param("param4") EnumA param4,
        @Param("param5") EnumB param5,
        Pageable pageable);

Fix:

I added dummy conditions to ensure that param3, param4, and param5 were always included in the query:

@Query("SELECT ft FROM Entity ft WHERE ft.someField1.param1 = :param1 AND ft.param2 = :param2 " +
        "AND (:param3 IS NULL OR ft.param3 IS NOT NULL) " +
        "AND (:param4 IS NULL OR ft.param4 IS NOT NULL) " +
        "AND (:param5 IS NULL OR ft.param5 IS NOT NULL) " +
        "ORDER BY " +
        "(CASE WHEN ft.param3 = :param3 THEN 0 ELSE 1 END), " +
        "(CASE WHEN ft.param3 = :param3 AND ft.param4 = :param4 AND ft.param5 = :param5 THEN 0 " +
        "     WHEN ft.param3 = :param3 AND (ft.param4 = :param4 OR ft.param5 = :param5) THEN 1 " +
        "     WHEN ft.param3 = :param3 THEN 2 " +
        "     WHEN ft.param4 = :param4 AND ft.param5 = :param5 THEN 3 " +
        "     WHEN (ft.param4 = :param4 OR ft.param5 = :param5) THEN 4 ELSE 5 END), " +
        "ft.updatedTimestamp DESC")
Page<Entity> queryEntityWithCustomSorting(
        @Param("param1") String param1,
        @Param("param2") String param2,
        @Param("param3") String param3,
        @Param("param4") EnumA param4,
        @Param("param5") EnumB param5,
        Pageable pageable);

The fix adds dummy conditions to make sure param3, param4, and param5 were always included in the query:

AND (:param3 IS NULL OR ft.param3 IS NOT NULL)
AND (:param4 IS NULL OR ft.param4 IS NOT NULL)
AND (:param5 IS NULL OR ft.param5 IS NOT NULL)

The issue seems to be related to how Spring Data JPA handles query parameters and its caching behavior.

param3, param4, and param5 were always passed correctly, Spring stopped recognizing them after three records were inserted.

Question:

Has anyone encountered a similar issue, and can you explain what might actually be causing this behavior? Is there a better solution to this problem without using dummy conditions?

• java
• spring
• spring-data-jpa

Share Improve this question Follow edited Mar 11 at 16:54 Tim asked Mar 11 at 15:37 TimTim 1122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You could use powers of two (1, 2, 4, 8, 16 etc) and assign a rank to each field match. Summing all the field rankings could then give you a sort ordering. This way rows that match param3 and param5 would appear before rows only matching param3

eg

SELECT 
   ft, 
   CASE WHEN ft.param3 = :param3 THEN 4 ELSE 0 END AS rank3,
   CASE WHEN ft.param4 = :param4 THEN 2 ELSE 0 END AS rank4,
   CASE WHEN ft.param5 = :param5 THEN 1 ELSE 0 END AS rank5,
FROM Entity ft
WHERE ft.param1 = :param1 AND ft.param2 = :param2
ORDER BY rank3 + rank4 + rank5 DESC

or

SELECT ft FROM Entity ft
WHERE ft.param1 = :param1 AND ft.param2 = :param2
ORDER BY 
   (CASE WHEN ft.param3 = :param3 THEN 4 ELSE 0 END) +
   (CASE WHEN ft.param4 = :param4 THEN 2 ELSE 0 END) +
   (CASE WHEN ft.param5 = :param5 THEN 1 ELSE 0 END) DESC