java - 如何获取 JPA 中两个日期之间只有一个日期的用户数量？

Question

我试图使用查询注释来获取 JPA 中两个日期之间仅获得一次 1GB_DATA 奖励的用户计数，但出现错误。

如何使用 JPA 做到这一点？

我的 SQL 查询:

select count(client_id) from (select client_id, count(client_id) from AV_CUSTOMER_PRIZE where prize_type ='1GB_DATA' and prize_date>'08.11.2017' and prize_date<'01.12.2017' group by client_id having count(client_id)=1);

我的方法:

@Query("select count(client_id) from (select client_id, count(client_id) from AV_CUSTOMER_PRIZE where prize_type ='1GB_DATA' and prize_date> :sDate and prize_date< :eDate group by " +
            "client_id having count(client_id)=1)")
    int getCount(@Param("sDate") Date startDate, @Param("eDate") Date endDate);

错误:

QuerySyntaxException: unexpected token: ( near line 1, column 30 [select count(client_id) from (select client_id, count(client_id) from AV_CUSTOMER_PRIZE where prize_type ='1GB_DATA' and prize_date> :sDate and prize_date< :eDate group by client_id having count(client_id)=1)]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)

Answer 1

您可以

1) 将 nativeQuery=true 添加到 @Query 注释中。不过，您可能会遇到日期参数问题。

或

2) 仅使用内部查询并期望一个列表:

@Query("select clientId, count(clientId) from AV_CUSTOMER_PRIZE 
        where prizeType ='1GB_DATA' and prizeTate> :sDate and prizeDate< :eDate 
        group by clientId having count(clientId)=1")
    List<Object[]> getCount(@Param("sDate") Date startDate, @Param("eDate") Date endDate);

然后您可以简单地在返回的列表上调用 .size() 。

还记得在该查询中使用实体字段名称而不是 native 数据库列名称

底线是您不能在 JPA 的 from 子句中使用 select 语句。