我有下面的SQL查询运行有不同的数据从表按日期排序,这是工作正常。
select * from content_details c
inner join(select spendpool_id, max(uploaded_date) as latest
from content_details group by spendpool_id) r on c.uploaded_date = latest
and r.spendpool_id = c.spendpool_id
where c.spendpool_id IN (2,3,4,5)
group by c.spendpool_id order by uploaded_date desc;
下面是我的JPARepository查询。
@Query("SELECT m1.spendpoolId FROM ContentDetails m1 join ( "
+ "select spendpoolId, MAX(uploadedDate) latest from ContentDetails "
+ "group by spendpoolId) m2 "
+ "on m1.uploadedDate = latest and m2.spendpoolId= m1.spendpoolId "
+ "where m1.spendpoolId IN (:spendpoolIds) "
+ " group by m1.spendpoolId "
+ "order by m1.uploadedDate desc ) ")
List<Spendpool> findSortedSpendpoolIds(@Param("spendpoolIds") List<Long> spendpoolIds);
但是每当我使用查询注解使用子查询时,运行时的spring数据无法识别语法并抛出下面的错误。
原因:java.lang.IllegalArgumentException:org.hibernate.hql.internal.ast.QuerySyntaxException:意外令牌:(近,第85列[SELECT m1.spendpoolId FROM com.beroe.insync2.domain.entities.ContentDetails m1 join(select spendpoolId,MAX(up Date)latest from com.beroe.insync2.domain.entities.ContentDetails group by spendpoolId)m2 on m1.uploadedDate = latest and m dpoolId= m1.spendpoolId where m1.spendpoolId IN(:spendpoolIds)group by m1.spendpoolId order by m1.uploadedDate desc)]
我怎样才能使这在jpa工作?
1条答案
按热度按时间myzjeezk1#
您可以使用原生SQL查询而不是基于jpa的查询,因为在将jpa对象转换为SQL查询时,它无法理解'(',此处预期为Object