Need help creating JPA criteria query

Question

I'm building my first Java EE web application using Glassfish and JSF. I'm fairly new to the criteria query and I have a query I need to perform but the javaee6 tutorial seems a little thin on examples. Anyway, I'm having a hard time creating the query.

Goal: I want to pull the company with the most documents stored. Companies have a OneToMany relationship with Documents. Documents has a ManyToOne relationship with several tables, the "usertype" column distinguishes them.

MySQL query:

SELECT USERID, COUNT(USERID) AS CNT 
FROM DOCUMENTS 
WHERE USERTYPE="COMPANY" 
GROUP BY USERID 
ORDER BY CNT DESC

Thanks

--update-- Based on user feedback, here is what I have so far:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Documents> cqry = cb.createQuery(Documents.class);
        //Intersting Stuff
        Root<Documents> root = cqry.from(Documents.class);
        Expression userid = root.get("userID");
        Expression usertype = root.get("userType");
        Expression count = cb.count(userid);
        cqry.multiselect(userid, count);
        Predicate userType = cb.equal(usertype, "COMPANY");
        cqry.where(userType);
        cqry.groupBy(userid);
        cqry.orderBy(cb.desc(count));
        //more boilerplate
        Query qry = em.createQuery(cqry);
        List<Documents> results = qry.getResultList();

The error I get is:

Exception Description: Partial object queries are not allowed to maintain the cache or be edited.  You must use dontMaintainCache().

Typical error, means nothing to me!

Answer 1

Your query doesn't return a complete entity object as you're only selecting two fields of the given table (this is why you're getting an error that says yadayadapartialyadayada).

Your solution is almost right, here's what you need to change to make it work—making it partial.

Instead of a plain CriteriaQuery<...> you have to create a tuple CriteriaQuery<..> by calling CriteriaBuilder.createTupleQuery(). (Basically, you can call CriteriaBuilder.createQuery(...) and pass Tuple.class to it as an argument. Tuple is a sort of wildcard entity class.)

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq= cb.createTupleQuery();

Root<Documents> root = cq.from(Documents.class);
Expression<Integer> userId = root.get("USERID");
Expression<String> userType = root.get("USERTYPE");
Expression<Long> count = cb.count(userId);

cq.multiselect(userId.alias("USERID"), count.alias("CNT"));
cq.where(cb.equal(userType, "COMPANY");
cq.groupBy(userId);
cq.orderBy(cb.desc(count));

TypedQuery<Tuple> tq = em.createQuery(cq);
for (Tuple t : tq.getResultsList()) {
  System.out.println(t.get("USERID"));
  System.out.println(t.get("CNT"));
}

_{(Accessing fields of a Tuple gave me an error if I didn't use aliases for them (in multiselect(...)). This is why I've used aliases, but this can be tackled more cleanly by using JPA 2's Metamodel API, which is described in the specification quite thoroughly.} )

The documentation for CriteriaQuery.multiselect(...) describes the behaviour of queries using Tuple objects more deeply.

Answer 2

If you are using Hibernate, this should work:

ProjectionList pl = Projections.projectionList()
.add(Projections.groupProperty("userid"))
.add(Projections.property("userid"))
.add(Projections.count("userid"));

Criteria criteria = session.createCriteria(Document.class)
.add(Restrictions.eq("usertype",usertype))
.setProjection(pl)
.addOrder(Order.desc("cnt"));

Hope it helps!