How to Create a Criteria Query with SUM and GROUP BY JPA Java

Question

Here my changes base on Ish Answer:

public List<TopUpRAMSInfo> listadoTOPUP2(Date fechaI, Date fechaF) {
        List<TopUpRAMSInfo> lista;
        CriteriaQuery<TopUpRAMSInfo> data = cb.createQuery(TopUpRAMSInfo.class);
        Root<TopUpRAMS> c = data.from(TopUpRAMS.class);
        data.select(cb.construct(TopUpRAMSInfo.class,
                c.get("deviceId"),
                cb.sum(c.get("rechargeValue"))
        )
        );
        data.where(cb.greaterThanOrEqualTo(c.get("connectionTime"), fechaI),
                cb.lessThanOrEqualTo(c.get("connectionTime"), fechaF)
        );
        data.groupBy(c.get("deviceId"));
        TypedQuery<TopUpRAMSInfo> datos = emDAO.createQuery(data);
        lista = datos.getResultList();
        return lista;
    }

Now im having this error: java.lang.IllegalArgumentException: An exception occured looking on class: class ENTITY.TopUpRAMSInfo for constructor using selection criteria types as arguments. If this CriteriaQuery was not intended to be a constructor query please verify that the selection matches the return type.

----- ORIGINAL POST -----

I'm having some issues doing this. This is my first real program I'm trying to build, and I need to summarize one field based on the other.

Here is my code so far (note that TopUpRAMS is my entity and emDAO is my Entity Manager.

public List<TopUpRAMS> listadoTOPUP2(Date fechaI, Date fechaF) {
    List<TopUpRAMS> lista;

    CriteriaQuery<TopUpRAMS> data = cb.createQuery(TopUpRAMS.class);
    Root<TopUpRAMS> c = data.from(TopUpRAMS.class);

    data.multiselect(c.get("deviceId"), cb.sum(c.get("rechargeValue")));
    data.where(cb.greaterThanOrEqualTo(c.get("connectionTime"), fechaI));
    cb.and(cb.lessThanOrEqualTo(c.get("connectionTime"), fechaF));
    data.groupBy(c.get("deviceId"));

    TypedQuery<TopUpRAMS> datos = emDAO.createQuery(data);
    lista = datos.getResultList();
    return lista;
}

Answer 1

There is problem with how you constructed the WHERE part of your CriteriaQuery. This expression is the only one that gets added to the WHERE clause:

cb.greaterThanOrEqualTo(c.get("connectionTime"), fechaI)

But not this one:

cb.lessThanOrEqualTo(c.get("connectionTime"), fechaF)

Any of the ff. can fix this:

1. Using cb.and():

   data.where(cb.and(
           cb.greaterThanOrEqualTo(c.get("connectionTime"), fechaI),
           cb.lessThanOrEqualTo(c.get("connectionTime"), fechaF)
       )
   );
   

2. CriteriaQuery.where() can accept variable number of arguments of type Expression, and they will be combined using conjunction (AND operator). So, we don't need to use cb.and():

   data.where(cb.greaterThanOrEqualTo(c.get("connectionTime"), fechaI),
       cb.lessThanOrEqualTo(c.get("connectionTime"), fechaF)
   );
   

There is also a problem with the type used in your CriteriaQuery. You doing a projection via data.multiselect() of 2 fields and this won't be convertible to TopUpRams entity.

There are 3 ways to do a multiselect/projection:

1. CriteriaQuery type will be Object[]

   CriteriaQuery<Object[]> data = cb.createQuery(Object[].class);
   Root<TopUpRAMS> c = data.from(TopUpRAMS.class);
   data.multiselect(c.get("deviceId"), cb.sum(c.get("rechargeValue")));
   

2. CriteriaQuery type will be Tuple

   CriteriaQuery<Tuple> data = cb.createTupleQuery();
   Root<TopUpRAMS> c = data.from(TopUpRAMS.class);
   data.multiselect(c.get("deviceId"), cb.sum(c.get("rechargeValue")));
   

3. Use of constructor

   CriteriaQuery<TopUpRAMSInfo> data = cb.createQuery(TopUpRAMSInfo.class);
   Root<TopUpRAMS> c = data.from(TopUpRAMS.class);
   data.select(cb.construct(TopUpRAMSInfo.class,
           c.get("deviceId"), 
           cb.sum(c.get("rechargeValue"))
       )
   );
   

   For this you need to create a separate class to represent the type of your query result (it doesn't have to be an entity):

   public class TopUpRAMSInfo {
       private Long deviceId; 
       private Double sumRechargeValue;
   
       public TopUpRAMSInfo(Long deviceId, Double sumRechargeValue) {
           this.deviceId = deviceId;
           this.sumRechargeValue = sumRechargeValue;
       }
       ...
   }
   

So here's a sample fix to your method:

public List<TopUpRAMSInfo> listadoTOPUP2(Date fechaI, Date fechaF) {

    List<TopUpRAMSInfo> lista;

    CriteriaQuery<TopUpRAMS> data = cb.createQuery(TopUpRAMSInfo.class);
    Root<TopUpRAMS> c = data.from(TopUpRAMS.class);
    data.select(cb.construct(TopUpRAMSInfo.class,
            c.get("deviceId"), 
            cb.sum(c.get("rechargeValue"))
        )
    );
    data.where(cb.greaterThanOrEqualTo(c.get("connectionTime"), fechaI),
        cb.lessThanOrEqualTo(c.get("connectionTime"), fechaF)
    );
    data.groupBy(c.get("deviceId"));

    TypedQuery<TopUpRAMSInfo> datos = emDAO.createQuery(data);
    lista = datos.getResultList();
    return lista;
}