JPA SQL从实体管理器中选择和求和

Question

select 
    LOGGING_ID,
    SUM(NORMAL_HOURS +OVERTIME_HOURS +DOUBLE_TIME_HOURS) AS TOTAL_HOURS
FROM
    LOGGING_DETAIL
GROUP BY 1

@PersistenceContext
    private EntityManager database;

    List<loggingDetail> loggingDetail = new ArrayList<loggingDetail>();
    timeLoggingDetail = database.createQuery("").getResultList();

最后，我需要Logging_id和该id的总小时数。谢谢

Answer 1

实体管理器可以调用您在jpa实体对象上设置的命名查询。我假设您有一个实体对象。我想，如果您没有这个对象，您可以在查询中做它，就像您在问题中布置它一样。我喜欢将它放在entity对象中，这样其他调用就可以重用它了。

实体对象-

 @Entity
 @NamedQuery( name = "loggingDetail.getLoggingId", query = "select 
LOGGING_ID,SUM(NORMAL_HOURS +OVERTIME_HOURS +DOUBLE_TIME_HOURS) AS TOTAL_HOURS
 FROM LOGGING_DETAIL GROUP BY 1" )
public class LoggingDetail
{ ...}

实体管理器调用将无法返回完全填充的loggingDetail对象，因为查询没有返回完整的对象，所以您必须循环遍历对象数组-

@PersistenceContext
private EntityManager database;
Query query = database.createNamedQuery( "loggingDetail.getLoggingId" );
List<Object[]> obj = query.getResultList();
    for( Object[] objects : obj )
    {
      String logId = (String) objects[0] ;
      String logTime = (String) objects[1] ;
     }