JPA parameter outside a where clause-CodePudding

I would like to know if the is a way to build a JPA query with parameter outside of the where clause. This query works fine in my database manager.

There is my query :

@Query(value = "SELECT q.quote, q.author, q.display_at, count(ql.*) AS like, (SELECT '{:userUUID}'::uuid[] && ARRAY_AGG(ql.user_uuid)::uuid[]) AS liked "  
        "FROM quotes q "  
        "LEFT JOIN quotes_likes ql ON ql.quote_uuid = q.uuid "  
        "WHERE display_at = :date "  
        "GROUP BY q.quote, q.author, q.display_at;", nativeQuery = true)
Optional<QuoteOfTheDay> getQuoteOfTheDay(UUID userUUID, LocalDate date);

I have the following error when the query is called : ERROR: syntax error at or near ":"

CodePudding user response：

By default, Spring Data JPA uses position-based parameter binding. We can also use named parameter with @Param annotation to give a method parameter a concrete name and bind the name in the query. As you are trying to use the named parameter try using the below snippet with @Param annotations.

Optional<QuoteOfTheDay> getQuoteOfTheDay(@Param("userUUID") UUID userUUID, @Param("date") LocalDate date);

CodePudding user response：

As you are trying to pass method parameters to the query using named parameters. We define these using the @Param annotation inside our repository method declaration.
Each parameter annotated with @Param must have a value string matching the corresponding JPQL or SQL query parameter name.
 @Query(value = "SELECT q.quote, q.author, q.display_at, count(ql.*) AS like, (SELECT '{:userUUID}'::uuid[] && ARRAY_AGG(ql.user_uuid)::uuid[]) AS liked "  
        "FROM quotes q "  
        "LEFT JOIN quotes_likes ql ON ql.quote_uuid = q.uuid "  
        "WHERE display_at = :date "  
        "GROUP BY q.quote, q.author, q.display_at;", nativeQuery = true)
Optional<QuoteOfTheDay> getQuoteOfTheDay(@Param("userUUID") UUID userUUID,@Param("date") LocalDate date);

Refer this for more details on how to use the @Query annotation

CodePudding user response：

ERROR: syntax error at or near means that you need to escape casting colons.
Every : needs to be replaced by \\:

(SELECT ARRAY[:userUUID]'\\:\\:uuid[] && ARRAY_AGG(ql.user_uuid)\\:\\:uuid[]) AS liked

OR use double colons

(SELECT ARRAY[:userUUID]::::uuid[] && ARRAY_AGG(ql.user_uuid)::::uuid[]) AS liked

OR use Cast instead of colons

(SELECT cast(ARRAY[:userUUID] as uuid[]) && cast(ARRAY_AGG(ql.user_uuid) as uuid[])) AS liked

Full query example:

@Query(value = "SELECT q.quote, q.author, q.display_at, count(ql.*) AS like, (SELECT ARRAY[:userUUID]\\:\\:uuid[] && ARRAY_AGG(ql.user_uuid)\\:\\:uuid[]) AS liked "  
        "FROM quotes q "  
        "LEFT JOIN quotes_likes ql ON ql.quote_uuid = q.uuid "  
        "WHERE display_at = :date "  
        "GROUP BY q.quote, q.author, q.display_at;", nativeQuery = true)
Optional<QuoteOfTheDay> getQuoteOfTheDay(UUID userUUID, LocalDate date);

The second problem is that you need to create an array with a query parameter.
Try ARRAY[:userUUID] instead of {:userUUID}, not sure if it is working.