Get random object from SQL database via Hibernate

Question

I have following MySql dependent code ( ORDER BY RAND() ) . I would like to know if there is hibernate HQL alternative for it (admin is boolean tag indicating that the user as an admin). This is working code:

public long getRandomAdmin() {
    Session session = getSession();
    Query selectQuery = session.createSQLQuery("SELECT user_id FROM users WHERE admin = '1' ORDER BY RAND()");
    selectQuery.setMaxResults(1);

    List<BigInteger> list = null;
    try {
        list = selectQuery.list();
    } catch (HibernateException e) {
        log.error(e);
        throw SessionFactoryUtils.convertHibernateAccessException(e);
    }

    if (list.size() != 1) {
        log.debug("getRandomAdmin didn't find any user");
        return 0;
    }
    log.debug("found: " + list.get(0));

    return list.get(0).longValue();
}

possible duplicate of Hibernate Criteria API: get n random rows — Simon Tower
– Simon Tower, Commented Aug 31, 2015 at 23:23

rogerdpack · Accepted Answer · 2014-12-02 16:12:11Z

2

See this link: http://www.shredzone.de/cilla/page/53/how-to-fetch-a-random-entry-with-hibernate.html

Criterion restriction = yourRestrictions;
Object result = null;  // will later contain a random entity
Criteria crit = session.createCriteria(Picture.class);
crit.add(restriction);
crit.setProjection(Projections.rowCount());
int count = ((Number) crit.uniqueResult()).intValue();
if (0 != count) {
  int index = new Random().nextInt(count);
  crit = session.createCriteria(Picture.class);
  crit.add(restriction);
  result = crit.setFirstResult(index).setMaxResults(1).uniqueResult();
}

This is what you want. Keep Hibernate as an abstraction layer while still being able to query a random object. Performance suffers a bit, though.

Although I've been using Hibernate a lot, I don't know a more elegant way that is easy to use. Imho you should wrap that method behind a facade.

edited Dec 2, 2014 at 16:12

rogerdpack

67.7k40 gold badges291 silver badges410 bronze badges

answered Nov 20, 2010 at 13:46

Falcon

3,1802 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rogerdpack Over a year ago

hard to believe hibernate doesn't hide these implementations cross database..anyway this way works [and might even be faster than doing a straight order by rand() webtrenches.com/post.cfm/avoid-rand-in-mysql ] though it only returns you one random row, so you'd have to do X queries if you want X random entries, FWIW. Another option would be to create your own custom hibernate dialect that registers the "rand" function for Oracle as "dbms_random.value" <sigh>

Simon Tower Over a year ago

stackoverflow.com/questions/2810693/… has a simpler implementation

vadim Over a year ago

This might not return any record if you have missing ids between 0 and maxId.

tietze111 · Accepted Answer · 2020-04-29 09:54:18Z

Since the Criterion used in the accepted answer is deprecated, I figured out how to do it with the CriteriaBuilder & CriteriaQuery and just wanted to share it here. I used the pattern described here to extend my repository by the custom method:

@Repository
public class UserRepositoryCustomImpl implements UserRepositoryCustom {
@Autowired
EntityManager em;

public User findRandomUserInCountry(String countryCode) throws NotFoundException {
    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery<Long> cqCount = qb.createQuery(Long.class);

    Root<User> userCountRoot = cqCount.from(User.class);

    cqCount.select(qb.count(userCountRoot)).where(qb.equal(userCountRoot.get("countryCode"), countryCode));

    int count = em.createQuery(cqCount).getSingleResult().intValue();

    System.out.println("Count of users: " + count);

    if (0 != count) {
        int index = new Random().nextInt(count);
        CriteriaQuery<User> cqUser = qb.createQuery(User.class);
        Root<User> userRoot = cqUser.from(User.class);
        cqUser.select(userRoot).where(qb.equal(userRoot.get("countryCode"), countryCode));

        User randomUser = em.createQuery(cqUser).setFirstResult(index).setMaxResults(1)
                .getSingleResult();

        System.out.println("Random user: " + randomUser.getName());

        return randomUser;
    } else {
        throw new NotFoundException("No users available in repository for country: " + countryCode);
    }
}

}

Collectives™ on Stack Overflow

Get random object from SQL database via Hibernate

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related