如何通过JPA Java创建具有SUM和GROUP的条件查询

Question

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;
    }

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

您如何构建标准中的WHERE部分存在问题。这个表达式是唯一被添加到WHERE子句中的表达式：

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

但不是这个：

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

任何FF。可以解决这个问题：

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

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

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

CriteriaQuery类型将为元组

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

构造函数的使用

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"))
    )
);

为此，您需要创建一个单独的类来表示查询结果的类型（它不必是实体）：

public class TopUpRAMSInfo {
    private Long deviceId; 
    private Double sumRechargeValue;

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

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;
}