当我尝试在 JPQL 中使用 Case 语句时出现问题

Question

您好我正在尝试从数据库部门实现查询（其中包含案例语句），但我无法弄清楚问题所在。

@Query("select new com.withgratitude.api.core.domain.ReportTopTenBiddersTable( CONCAT(u.firstName, ' ', u.lastName), " +

           "b.auctionId, b.date), " +

           " case when (b.isTheWinner = 0) then 'No' when (b.isTheWinner = 1) then 'Yes' else 'No Winner' end as winnerBid" +

           "                        from Bids b, Users u " +

           "                       where b.userId = u.userId " +

           "                       order by CONCAT(u.firstName, ' ', u.lastName)")

   List<ReportTopTenBiddersTable> reportTopTenBiddersTable();

Here is the Query I am trying to implement:

#top 10 bidders - table

select CONCAT(u.first_name, ' ', u.last_name) as name, b.auction_id, b.date, 

                              case b.is_thewinner when 0 then 'No' when 1 then 'Yes' end as WinnerBid, b.offer

                       from bids b, users u 

                       where b.user_id = u.user_id

                       order by name

                       limit 10;

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 130 [select new com.withgratitude.api.core.domain.ReportTopTenBiddersTable( CONCAT(u.firstName, ' ', u.lastName), b.auctionId, b.date),  case when b.isTheWinner = 0 then 'No' when b.isTheWinner = 1 then 'Yes' else 'No Winner' end as winnerBid                        from com.withgratitude.api.core.dao.Bids b, com.withgratitude.api.core.dao.Users u                        where b.userId = u.userId                        order by CONCAT(u.firstName, ' ', u.lastName)]

at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]

at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:296) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]

Answer 1

这里有很多问题 - 其中一个是返回类型（ReportTopTenBiddersTable 对象的列表），它似乎没有为完整的投标信息留下任何位置。零件不合适（sql 与 jpql 混合在一起），类型不对齐 - 但从这个简短的示例中很难判断错误的根源是什么 - 以及最佳解决方案。最简单的解决方案之一是将投标信息添加到 ReportTopTenBiddersTable 对象；这样您就可以删除case（但仍保留数据）并保持签名完整：更改构造函数ReportTopTenBiddersTable以接受“Bid”对象（并将其存储在字段中）。将查询开头的构造函数调用更改为包含b（您可以省略日期，因为它b无论如何都可以访问）：&nbsp;&nbsp;select&nbsp;new&nbsp;com.withgratitude.api.core.domain.ReportTopTenBiddersTable( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CONCAT(u.firstName,&nbsp;'&nbsp;',&nbsp;u.lastName),&nbsp;b.auctionId,&nbsp;b.date)&nbsp;...

Answer 2

要添加到 Andronicus 的答案，我想您可以使用nativeQuery纯 SQL 来使用和编写您的数据库请求。@Query(value = "select CONCAT(u.first_name, ' ', u.last_name) as name, b.auction_id, b.date, " +&nbsp; &nbsp; &nbsp; &nbsp; "case b.is_thewinner when 0 then 'No' when 1 then 'Yes' end as WinnerBid, b.offer " +&nbsp; &nbsp; &nbsp; &nbsp; "from bids b, users u " +&nbsp; &nbsp; &nbsp; &nbsp; "where b.user_id = u.user_id " +&nbsp; &nbsp; &nbsp; &nbsp; "order by name " +&nbsp; &nbsp; &nbsp; &nbsp; "limit 10", nativeQuery = true)List<ReportTopTenBiddersTable> reportTopTenBiddersTable();