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.

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.

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

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();
    }