Skip to main content

Posts

Showing posts with the label criteria-api

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