CODEWITHSUNDEEP
jpaeclipselinkjpql
Buddhika Ariyaratne
Buddhika Ariyaratne

Reputation: 2433

Group by the Class of Object in JPQL

The JavaEE app with JPA and JSF is having a Bill Entity which is sub classed to BilledBill, CancelledBill and RefundBill. I use EclipseLink as persistance provider. I want to write a query which groups the sum of totals by the type of the class. I tired, but failed. Is that possible?

The relevant constructor is as below.

public BillSummery(PaymentMethod paymentMethod, Class billClass, Double total, Double discount, Double netTotal, Double tax, Long count, BillType billType) {
    this.paymentMethod = paymentMethod;
    this.total = total;
    this.discount = discount;
    this.netTotal = netTotal;
    this.tax = tax;
    this.count = count;
    this.billType = billType;
    this.BillClass = billClass;
}

The JPQL is as below

String j = "select new com.divudi.data.BillSummery(b.paymentMethod, type(b), sum(b.total), sum(b.discount), sum(b.netTotal), sum(b.vat), count(b), b.billType) "
            + " from Bill b "
            + " where b.retired=false "
            + " and b.billTime between :fd and :td "
            + " group by b.paymentMethod, type(b), b.billType";

Upvotes: 0

Views: 300

Answers (1)

crizzis
crizzis

Reputation: 10716

Which inheritance strategy are you using?

If it involves a discriminator column, you can always map it as a regular column, just like any other property. It will then be available for use in queries.

Example: 

@Entity
@Inheritance(strategy = JOINED)
@DiscriminatorColumn(name = "dtype")
public abstract class Bill {

    @Column(name = "dtype", insertable=false, updatable=false)
    private String type;
    ...
}

That being said, I'd seriously consider flattening the inheritance structure. It sounds as though a Bill could turn into a BilledBill or CancelledBill, so perhaps it would be sufficient for Bill to have a status property instead?

Upvotes: 1

Related Questions