CODEWITHSUNDEEP
hibernatejpadaojpa-criteria
Sispeks
Sispeks

Reputation: 11

java hibernate 5 subselect query with CriteriaBuilder and CriteriaSelect

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?

Upvotes: 1

Views: 938

Answers (1)

Guilherme Alencar
Guilherme Alencar

Reputation: 1403

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.

Upvotes: 0

Related Questions