Friday, January 13, 2012

JPA 2 + Criteria API - Defining a subquery


I try to convert a sql query to Criteria API without success so far. I can create two separate queries which return the values I need, but I don't know how to combine them in a single query.



Here is the sql statement which works:




select company.*, ticketcount.counter from company
join
(select company, COUNT(*) as counter from ticket where state<16 group by company) ticketcount
on company.compid = ticketcount.company;



This Criteria query returns the inner query results:




CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<intCompany> qTicket = cb.createQuery(intCompany.class);
Root<Ticket> from = qTicket.from(Ticket.class);
Path groupBy = from.get("company");
Predicate state = cb.notEqual(from.<State>get("state"), getStateById(16));
qTicket.select(cb.construct(
intCompany.class, cb.count(from),from.<Company>get("company")))
.where(state).groupBy(groupBy);
em.createQuery(qTicket).getResultList();



In the application I defined a small wrapper/helper class:




public class intCompany{
public Company comp;
public Long opentickets;
public intCompany(Long opentickets,Company comp){
this.comp = comp;
this.opentickets = opentickets;
}
public intCompany(){

}
}



So does anyone has an idea how to get this working?

1 comment:

  1. You have almost everything done.

    //---//
    CriteriaBuilder cb = em.getCriteriaBuilder();
    //Your Wrapper class constructor must match with multiselect arguments
    CriteriaQuery<IntCompany> cq = cb.createQuery(IntCompany.class);
    //Main table
    final Root<Ticket> fromTicket= cq.from(Ticket.class);
    //Join defined in Ticket Entity
    final Path company = fromTicket.get("company");
    //Data to select
    cq.multiselect(cb.count(from), company);
    //Grouping
    cq.groupBy(company);
    //Restrictions (I don't really understand what you're querying)
    Predicate p = cb.lessThan(fromTicket.get("state"), 16);
    //You can add more restrictions
    // p = cb.and/or(p, ...);
    cq.where(p);
    List<IntCompany> results = entityManager.createQuery(cq).getResultList();


    This should work as expected.

    ReplyDelete