Using Hibernate and Criteria to Select the Most Common Value

Question

Given a table, say:

orderId | typeId
----------------
1       | 1
2       | 1
3       | 2
4       | 5
5       | 1
6       | 2

And I have an Order object which has a Type object (Type has a Set of Orders in the other direction.

How would I go about retrieving the most popular Type of Order (in this case, type 1) using Hibernate's Criteria API?

score 2 · Answer 1 · answered May 14 '11 at 12:16

2

I took a look at the documentation of the Hibernate Criteria API, could you do something like this:

List results = session.createCriteria(Order.class)
    .setProjection( Projections.projectionList()
        .add( Projections.rowCount(),"rCount")
        .add( Projections.groupProperty("typeId") )
    )
    .addOrder(Order.desc("rCount") )
    .setMaxResults(1)
    .list();

This may need modified to work with the proper classes

answered May 14 '11 at 12:16

Kevin Bowersox

93,289
19
159
189

if Type is an associated class you will have to create a Criteria on that association first to access it's ids. something like `createCriteria(Order.class).createCriteria("types");` – fasseg May 14 '11 at 12:20
I'm sorry, I mis-worded my question. Revised. – Josh Johnson May 14 '11 at 14:28

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

you might wanna look into Projections. You should be able to use Projections.groupProperty() on the "type" association, and add up the results. check this SO question: Expressions in hibernate criteria

edit: tried it and the following seems to work fine:

Order:

@Entity
@Table(name="orders")
public class Order {
    @Id
    @GeneratedValue
    private long id;
    @ManyToOne
    private Type type;
}

Type:

@Entity
@Table(name="order_types")
public class Type {
    @Id
    @GeneratedValue
    private long id;
}

and the Criteria:

    Criteria c=sess.createCriteria(Order.class)
        .createCriteria("type")
        .setProjection(
                Projections.projectionList()
                .add(Projections.groupProperty("id"))
                .add(Projections.rowCount(),"rowcount")
        )
        .addOrder(org.hibernate.criterion.Order.desc("rowcount"));

    for (Object[] result:(List<Object[]>) c.list()){
        System.out.println("type id: " + result[0] + "\tcount: " + result[1]);
    }

edited May 23 '17 at 12:04

Community

1
1

answered May 14 '11 at 12:10

fasseg

17,504
8
62
73

I'm sorry. I misworded my question. An `Order` has a many-to-one relationship with `Type`. – Josh Johnson May 14 '11 at 14:29
i changed the answer to reflect that, but the criteria is almost the same ;) – fasseg May 14 '11 at 14:39
Beauty. I was able to figure it out on my own with what you had but thought you may want to update your answer :) – Josh Johnson May 14 '11 at 14:51
Actually, this didn't work for me. I had to do it this way to get it to work: `Criteria c = sess.createCriteria(Type.class); c.createCriteria("orders");` Does that make sense to you? – Josh Johnson May 14 '11 at 15:00
it seems you have a one-to-many from type to orders and not a many-to-one from order to types. thats why you have a property "orders" on your Type class. check this: http://stackoverflow.com/questions/2584521/in-a-bidirectional-jpa-onetomany-manytoone-association-what-is-meant-by-the-inv – fasseg May 14 '11 at 15:01
Yes, that is correct (my relationships are identical to what's asked in that question [which spelled things out nicely, btw. Thanks]). Should it not be that way? – Josh Johnson May 14 '11 at 15:12
the difference is a one-to-many association needs a jointable since one record in the type table cant hold n orders. but a many-to-one relationship can be built upon just one foreign key in the order record. id say it depends on the use-case which way to go, although i prefer many-to-one because of the lower complexity – fasseg May 14 '11 at 15:18

Using Hibernate and Criteria to Select the Most Common Value

2 Answers2