How to get list of 10 random unique objects with Hibernate?

Question

Does anyone have HQL query on how to get list of 10 random unique objects from the database?

It should be done in database not in application. I'd like to get something that has better performance than my current solution which pretty much makes 10 requests to get the list filed up.

score 11 · Answer 1 · answered Dec 11 '10 at 23:49

11

HQL would be something like:

session.createQuery("select o from Object o order by rand()")
   .setMaxResults(10)
   .list()

The rand() is passed through to the database so replace this with whatever function your database uses.

answered Dec 11 '10 at 23:49

Damo

11,410
5
57
74

score 5 · Accepted Answer · edited May 23 '17 at 12:13

5

I'm no HQL expert by any means, but in SQL you would do this with

select ... order by RANDOM() limit 10

So with a bit of googling, I figured out how to do the limit bit and the random bit.

edited May 23 '17 at 12:13

Community

1
1

answered Dec 11 '10 at 04:42

Nick ODell

15,465
3
32
66

Please [don't use signatures or taglines](http://stackoverflow.com/faq#signatures) in your posts. – user229044 Dec 11 '10 at 05:44

score 0 · Answer 3 · answered Dec 11 '10 at 23:42

Please feel free to comment and post improvements. This is what I got:

public List<Item> getRandomTenItems() {

    DetachedCriteria criteria = DetachedCriteria.forClass(Item.class).addOrder(Order.desc("id"));
    List<Item> idlist = new LinkedList<Item>(getHibernateTemplate().findByCriteria(criteria, 0, 1));
    long max =  idlist.get(0).getId();

    criteria = DetachedCriteria.forClass(Item.class).addOrder(Order.asc("id"));
    idlist = new LinkedList<Item>(getHibernateTemplate().findByCriteria(criteria, 0, 1));
    long min =  idlist.get(0).getId();

    List<Item> rtn = new LinkedList<Item>();
    HashSet<Long> ids = new HashSet<Long>();
    int i=0;
    while(i<10) {
        long itemId = RandomUtils.rand(min, max);
        if(ids.contains(itemId)) continue;
        List<Item> list = new LinkedList<Item>(getHibernateTemplate().findByNamedParam(
                "from Item where archived = false and available = true and id = :itemId", "itemId", itemId));
         if(!list.isEmpty()){
            rtn.add(list.get(0));
            ids.add(list.get(0).getId());
            i++;
         }
    }
    return rtn;
}

How to get list of 10 random unique objects with Hibernate?

3 Answers3