JPA参数外一个where子句

Question

我想知道是否可以在where子句之外构建带有参数的JPA查询。这个查询在我的数据库管理器中运行良好。

这是我的查询:

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

当调用查询时，我有以下错误:error:语法错误在":">

Answer 1

默认情况下，Spring Data JPA使用基于位置的参数绑定。我们还可以使用带有@Param注释的命名参数给方法参数一个具体的名称，并在查询中绑定该名称。当您尝试使用命名参数时，请尝试使用下面带有@Param注释的代码片段。

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

Answer 2

ERROR: syntax error at or near意味着您需要转义强制转换冒号。
每个:都需要替换为\:

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

或者使用双冒号

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

或者使用Cast代替冒号

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

第二个问题是您需要创建一个带有查询参数的数组。
尝试ARRAY[:userUUID]代替{:userUUID}

完整查询示例:

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

Answer 3

当您尝试使用命名参数将方法参数传递给查询时，我们使用存储库方法声明中的@Param注释来定义这些参数。

每个带@Param注释的参数必须有一个匹配相应JPQL或SQL查询参数名的值字符串。

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

有关如何使用@Query注释的更多详细信息，请参阅此处。