Hibernate: how can I retrieve last N entries with Criteria Interface?

Question

How can I retrieve last N entries from database with Hibernate Criteria Interface?

Criteria crit = hiberSession.createCriteria(UserTable.class);

Or even would be better to retrieve random N entries...

score 2 · Accepted Answer · answered Sep 10 '14 at 08:17

2

You have to take the total count and then fetch the last N records.

        Criteria count = session.createCriteria(UserTable.class);
        count.setProjection(Projections.rowCount());
        Long total = count.uniqueResult();

        int n;

        Criteria criteria = session.createCriteria(UserTable.class);
        criteria.setFirstResult(total-n);
        criteria.setMaxResults(n);

        List<UserTable> list = criteria.list();

If you maintain the entry timestamp then you can add order and fetch the last n results

    Criteria criteria = session.createCriteria(UserTable.class);
    criteria.addOrder(Order.desc("timestamp"));
    criteria.setMaxResults(n);

    List<UserTable> list = criteria.list();

To get any random entries set your first result to any random value, which must be less than total count.

answered Sep 10 '14 at 08:17

ares

4,283
6
32
63

which approach would be more efficient? – devesh-ahuja Feb 15 '18 at 19:51
@devesh-ahuja, If you have an index on the `timestamp` column then definitely the second approach will be faster. To be clear `last N records` only makes sense when you're sorting the records. Otherwise you'll get records based on the natural ordering of the database. – ares Feb 16 '18 at 09:15
thank you for the explanation. I also tried and observed the same behaviour. – devesh-ahuja Feb 19 '18 at 07:13
It retrieves in messed up order (not ordered by id) – parsecer Dec 17 '19 at 14:06

score 1 · Answer 2 · answered Sep 10 '14 at 08:16

The Last is always dependent on the ORDER BY. So we should firstly sort the list to be sure what is the last. Reversed order then (e.g. desc) would move the first to top

17.3. Ordering the results

crit.addOrder( Order.desc("name") )

Once order is defined we can use paging

11.4.1.5. Pagination

If you need to specify bounds upon your result set, that is, the maximum number of rows you want to retrieve and/or the first row you want to retrieve, you can use methods of the Query interface:

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

in this case, we would have it similar

...
crit.setFirstResult(20);
crit.setMaxResults(10);
...

Hibernate: how can I retrieve last N entries with Criteria Interface?

2 Answers2