hibernate criteria query get multiple counts in single query.

Question

I'm wondering if anybody knows how to get multiple counts from a single query using hibernates criteria query?

I have the following query.

List<PurchaseRequest> prs = this.session.createCriteria(PurchaseRequest.class).list();

within PurchaseRequest, I have a column called current_state. I'd like to query all the PurchaseRequest rows and group the current_states together so I can get a total count of each current_state.

States look like this, creator, authorizer, assignor. etc.

Do you mean Select current_state, count(*) From PurchaseRequest Group By current_state ? — AlexS
– AlexS, Commented Jun 20, 2012 at 14:47

Honza Zidek · Accepted Answer · 2016-03-17 13:10:46Z

1

Try this:

Criteria criteriaPurchaseRequest=this.session.createCriteria(PurchaseRequest.class);

ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.groupProperty("current_state"));
projectionList.add(Projections.rowCount());

criteriaPurchaseRequest.setProjection(projectionList);

List results = criteriaPurchaseRequest.list();

To obtain the results:

List results = criteriaPurchaseRequest.list();
Map currentStateMap = new HashMap();
Iterator it=results.iterator();
while (it.hasNext()){
Object obj[]=(Object[])it.next();           
     CurrentState currentState = (CurrentState )obj[0];
    currentStateMap .put(currentState.getDescription().toLowerCase(), (Integer)obj[1]);
}

where CurrentState is an object that represents the column current_state (remember, Hibernate is working with objects).

edited Mar 17, 2016 at 13:10

Honza Zidek

21.8k10 gold badges97 silver badges149 bronze badges

answered Jun 20, 2012 at 14:56

esmoreno

6665 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Code Junkie Over a year ago

what would I need to cast results to inorder to get the amount?

esmoreno Over a year ago

Do you can try this query: "Select current_state, sum(amount), count(*) From PurchaseRequest Group By current_state,amount" ?

Code Junkie Over a year ago

I'm sorry, I meant the count. What you have seems correct, I'm just not sure how to get it to display the count from the results. If I pull the groupProperty out, I can cast it to a Long, but breaks if I add group back in, I'm assuming it will return the state with the count and would need to create some sort of object where it could be casted to. Just not sure what group property would be.

Code Junkie Over a year ago

I think were close esmoreno, however your code sample doesn't compile, compiling error message "required: array or java.lang.Iterable found: org.hibernate.Criteria" and when I use results, I get this, "incompatible types required: java.lang.Object[]" found: java.lang.Object

Code Junkie Over a year ago

That works and another quick fix to your original code was to use List<Object[]> with results and then pass results into the for loop. Thanks

Collectives™ on Stack Overflow

hibernate criteria query get multiple counts in single query.

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related