1

I have a dropwizard application with Hibernate and want to use Hibernate 5's CriteriaQuery to implement a function in a DAO (DataAccessObject). More precisely, I want to query a table with objects that are all equipped with an 'id' column and a 'user_id' column (it is a PostgreSQL DB and the table has more fields, but the other don't matter).

I wanted to calculate the average amount of objects in that table per user, so if the table was called table_a, I would want to write the following query using Java's/Hibernates CriteriaQuery:

    SELECT avg(counts) FROM
        (SELECT user_id, COUNT(id) as counts FROM table_a GROUP BY user_id) AS foo

I tried looking into this documentation and this one but I couldn't find any reference on doing this kind of subquery.

My DAO method looks like the following:

    public double avgCountPerUser() {
        CriteriaBuilder criteriaBuilder = this.currentSession().getCriteriaBuilder();
        CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class);
        Root<TableA> tableARoot = criteriaQuery.from(TableA.class);

        criteriaQuery
            .multiselect(tableARoot.get("user_id"),criteriaBuilder.count(tableARoot.get("id")))
            .groupBy(tableARoot.get("user_id"))
            .orderBy(criteriaBuilder.desc(criteriaBuilder.count(tableARoot.get("id"))));
        List<Tuple> tuples= currentSession().createQuery(criteriaQuery).getResultList();
        double result = 0;
        for (Tuple tuple : tuples) {
            result += (Integer) tuple.get(1);
        }
        return tuples.size() == 0 ? 0 : result / tuples.size();
    }

Can you help me out on how to do it correctly? And not need to pull all results to Java-Objects and then calculate it this inefficiently?

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
Sispeks
  • 11
  • 6
  • "I would like to write the query using Hibernates CriteriaQuery" Why? What's wrong with writing it in plain SQL? – M. Prokhorov Aug 29 '17 at 11:50
  • I find using plain SQL within my Java Code is quite ugly and can't be subject to the checks and code regulations within my team and furthermore it is not reusable. But foremost: I wanted to learn how to do it right. Are there any Hibernate-Pros out there? – Sispeks Aug 29 '17 at 12:00
  • You are using JPA Criteria API. I have updated the tags. You'd better reword your question using JPA terminology, i.e. by adding Entity definitions and relations. If the relationships mappings are ok, you should be able to use the same approach as described here: https://stackoverflow.com/a/14407538/870122 – perissf Aug 29 '17 at 14:54

1 Answers1

0

Subqueries in HQL only may occur in select or where clauses.

https://docs.jboss.org/hibernate/stable/core.old/reference/en/html/queryhql-subqueries.html

if there isnt a way to create a hql query then you should create a native query, and try to do this not using specific sintax from some dbms.

Guilherme Alencar
  • 1,243
  • 12
  • 21
  • Hi Gui, thanks for your answer. You are correct, that it would be possible to do by employing HQL directly. However, I asked specifically for the CriteriaBuilder and CriteriaQuery techniques, which are provided by Hibernate 5 and whether there is a way to define the result of a query as a new Root, for instance. – Sispeks Aug 29 '17 at 12:15