Get Class in JPQL

Question

In a JPA application, Bill class has been extended to BilledBill, CancelledBill and RefundBill.

There is a JPQL query which list category name, item name, etc, as followed.

        String jpql = "select c.name, i.name, count(bi.bill), sum(bf.feeValue), bf.fee.feeType "
            + " from BillFee bf join bf.billItem bi join bi.item i join i.category c  "
            + " where bi.bill.institution=:ins "
            + " and bi.item.department.institution=:ins "
            + " and  bi.bill.billType= :bTp  "
            + " and  bi.bill.createdAt between :fromDate and :toDate "
            + " and (bi.bill.paymentMethod = :pm1 "
            + " or  bi.bill.paymentMethod = :pm2 "
            + " or  bi.bill.paymentMethod = :pm3 "
            + " or  bi.bill.paymentMethod = :pm4)"
            + " group by i.name, c.name, bf.fee.feeType "
            + " order by c.name, i.name, bf.fee.feeType";

    temMap.put("toDate", toDate);
    temMap.put("fromDate", fromDate);
    temMap.put("ins", institution);
    temMap.put("bTp", BillType.OpdBill);
    temMap.put("pm1", PaymentMethod.Cash);
    temMap.put("pm2", PaymentMethod.Card);
    temMap.put("pm3", PaymentMethod.Cheque);
    temMap.put("pm4", PaymentMethod.Slip);

    List<Object[]> lobjs = getBillFacade().findAggregates(jpql, temMap, TemporalType.TIMESTAMP);

I want to group by the class and get the class in the select statement. Is that possible?

Maciej Dobrowolski Maciej Dobrowolski · Accepted Answer · 2014-08-05T08:09:41

If you have discriminator column then it is possible.

Let's assume your discriminator column is String field named DTYPE. You should add to your entities:

@Column(name = "DTYPE")
private String clazz;
// ..
// getter

Now you can use it in your JPQL Query:

String jpql = "select c.name, i.name, bill.clazz // .. rest of the query

Get Class in JPQL

1 Answers