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 t开发者_开发知识库he list filed up.
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.
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.
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;
}
精彩评论