CODEWITHSUNDEEP
javamysqlhibernatejpa
Ke.
Ke.

Reputation: 457

Random select rows via JPA

In Mysql, 

SELECT id FROM table ORDER BY RANDOM() LIMIT 5

this sql can select 5 random rows. How to do this via JPA Query (Hibernate as provider, Mysql database)?

Thanks.

Upvotes: 12

Views: 16011

Answers (3)

Mohammed Housseyn Taleb
Mohammed Housseyn Taleb

Reputation: 1828

I just Achieved this by a simple way, This may not seem lot of cute but it will do the purpose. this solution is for Java 8 or above only, I m using spring boot.

First I have the following service call that will provide an EntityManager to my default Repository interface method, The autowiring is a singleton so I believe that this stays performance freindly.

 @PersistenceContext
 private EntityManager entityManager;
    

 @Override
 public UserResp getRandomUser() {
        long orderByRandom = (long) ((java.lang.Math.random()*100 % 10 )+1);
        return userMapper.entityToModel(userRepository.pickRandomUser(orderByRandom,entityManager));
    }

Then my repository looks like

 @Transactional
    default User pickRandomUser(Long rand, EntityManager manager)  {
        return manager.createQuery("SELECT u FROM User u ORDER BY "+rand,User.class).setMaxResults(1).getSingleResult();
    }

Upvotes: 0

Pascal Thivent
Pascal Thivent

Reputation: 570385

Only the functions defined in the specification are guaranteed to be supported by all JPA providers and RAND or RANDOM aren't. So I don't think that you can do it in JPQL.

However, it would be possible in HQL (the order by clause in HQL is passed through to the database, so you can use any function):

String query = "SELECT o.id FROM Order o ORDER BY random()";
Query q = em.createQuery(query);
q.setMaxResults(5);

But, I repeat:

  1. This may not work with another database.
  2. This may not work with another JPA provider.

Upvotes: 12

Bozho
Bozho

Reputation: 597114

Try calculating the random beforehand and construct your JPQL/HQL/native query with the pre-calculated random value.

Upvotes: 0

Related Questions