How to limit a collection in an Object's property in hibernate?

Question

I have two entities: item, and bid, each item have many bids for it, so bid is a collection property of item.

in the page that show an item, I just want to show the first 10 records of bids about this "item".

so, I do this query:

from item
left join fetch item.bids
where item.id=3
...

but this will fetch all bids about an item, so, how to limit the bids about an item ?

if you have control over sql, you can use LIMIT and OFFSET commands — working, Aug 01 '14 at 05:07
You can use Limit. or else if you using Prepared Statement you can set max result limit — Kishan Bheemajiyani, Aug 01 '14 at 05:16

Vlad Mihalcea · Answer 1 · 2017-03-14T19:08:27.973

This works so much better if you also have an association from Bid to Item.

Then you can select the items and apply the limit restriction:

session
    .createQuery(
    "select b
    from bid b
    join fetch b.item i
    where 
        i.id=3")
    .setMaxResult(10)
    .list();

Your previous query, the one selecting Item and fetching Bids will always select all items with all their bids and apply the max result limit in-memory. This is because Hibernate must always fetch all children, it can't give you partial collection results.

Selecting the child and joining the parent is a better alternative. The restriction applies to the selected children with no restriction whatsoever.

score 2 · Answer 2 · edited May 23 '17 at 12:09

2

you can use criteria's setFirstResult() and setMaxResult(). or this post would be helpful for your question

example:

sessionFactory.getCurrentSession().createQuery("your query here").setFirstResult(0).setMaxResult(10);

this will generate 10 rows of data from database

you have use annotation driven. simply add the annotation Size to your bid object

    @OneToMany( mappedBy = "object", cascade = CascadeType.ALL)
    @Size(min=1, max=10)
    private Set<Bid>    bid;

edited May 23 '17 at 12:09

Community

1
1

answered Aug 01 '14 at 05:12

Ker p pag

1,568
12
25

this can limit the "item" not it's bids's record! – lovespring Aug 01 '14 at 05:13
1

you can use annotation driven in your entity you could simply put @Size(min=1, max=10) to your one to many relationship or many to many – Ker p pag Aug 01 '14 at 05:19
if we can override the mapping in a HQL way, it will be more flexible! – lovespring Aug 01 '14 at 05:42
you can refer to this [link](http://www.journaldev.com/2954/hibernate-query-language-hql-example-tutorial) i guess – Ker p pag Aug 01 '14 at 05:49

score 0 · Answer 3 · answered Aug 01 '14 at 05:11

For plain SQL, do the following:

Select B.*
From item I, bid B,
Where T.itemId = B.itemId
Limit 10;

Same can be done in case you are using hibernate, using criteria API or HQL HQL example:

Query query = session.createQuery("from MyTable");
query.setMaxResults(10);

Criteria example: 
session.createCriteria(Abc.class)
                    .addOrder(Order.desc("id"))
                    .setMaxResults(10)
                    .list();

This only limit the "item" not the "bids" ! – lovespring Aug 01 '14 at 05:21 — lovespring, Aug 01 '14 at 05:21

How to limit a collection in an Object's property in hibernate?

3 Answers3

Linked