CODEWITHSUNDEEP
javahibernatejpaeclipselink
Igor Mukhin
Igor Mukhin

Reputation: 15368

How to query for number of records in select with "group by" clause in JPA/EclipseLink?

Suppose you have a following JPA query:

select car.year, car.month, count(car) from Car car group by car.year, car.month

Before we query for results, we need to know how many records this query will return (for pagination, UI and so on). In other words we need something like this:

select count(*) from 
    (select car.year, car.month, count(car) 
     from Car car group by car.year)

But JPA/EclipseLink does not support subqueries in "from" clause. It there a way around it?

(Of course you can use plain SQL and native queries, but this is not an option for us)

Upvotes: 3

Views: 1715

Answers (5)

Mohammadreza Khatami
Mohammadreza Khatami

Reputation: 1332

Simply you can use setFirstResult and setMaxResult to set record bound for query ,also use size of list to return count of records that query runs. like this :

Query query = em.createQuery("SELECT d.name, COUNT(t) FROM Department d JOIN     
d.teachers t GROUP BY d.name");


//query.setFirstResult(5);
//query.setMaxResult(15); this will return 10 (from 5 to 15) record after query executed.
List<Object[]> results = query.getResultList();
    for (int i = 0; i < results.size(); i++) {
        Object[] arr = results.get(i);
        for (int j = 0; j < arr.length; j++) {
            System.out.print(arr[j] + " ");
        }
        System.out.println();
     }

-----Updated Section------

JPA does not support sub-selects in the FROM clause but EclipseLink 2.4 current milestones builds does have this support.

See, http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Sub-selects_in_FROM_clause

You can probably rewrite the query with just normal joins though.

Maybe,

Select a, size(a.bs) from A a

or

Select a, count(b) from A a join a.bs b group by a

I hope this helps you.

Upvotes: 0

Dragan Bozanovic
Dragan Bozanovic

Reputation: 23552

A portable JPA solution:

select count(*) from Car c where c.id in
    (select MIN(car.id) from Car car group by car.year, car.month)

You could also go with something like:

select COUNT(DISTINCT CONCAT(car.year, "#", car.month)) from car

but I expect this to be less performant due to operations with textual values.

Upvotes: 1

Igor Mukhin
Igor Mukhin

Reputation: 15368

As @chris pointed out EclipseLink supports subqueries. But the subquery can't be the first one in the from-clause.

So I came up with the following workaround which is working:

select count(1) from Dual dual, 
    (select car.year, car.month, count(car) 
     from Car car group by car.year) data
  1. count(1) is important as count(data) would not work
  2. You have to add an entity Dual (If your database does not have a DUAL table, create one with just one record.)

This works but I still consider it a workaround that would only work if you allowed to create the DUAL table.

Upvotes: 0

Navnath
Navnath

Reputation: 133

I have another approach to solve this issue . by using my approach you don't need to know the no of rows this query is going to return.

here it is your solution :-

you going to need two variables

1) pageNo (your page no should be 1 for first request to data base and proceeding request it should be incremental like 2 ,3 , 4 5 ).

2) pageSize.

int start = 0;
        if(pageNo!=null && pageSize!=null){
            start = (pageNo-1) * pageSize;
        }else{

            pageSize = StaticVariable.MAX_PAGE_SIZE; // default page size if page no and page size are missing 
        }

your query 

em.createquery("your query ")
.setfirstResult(start)
.setMaxResult(pageSize)
.getResultList();

Upvotes: 0

inigo skimmer
inigo skimmer

Reputation: 918

What about: 

select count(distinct car.year) from car

Upvotes: 0

Related Questions