CODEWITHSUNDEEP
javaspring-bootjpaspring-data-jpajpql
Madhu
Madhu

Reputation: 2803

How to Select Random 10 records in JPQL?

I have to select random 10 records from the user table,

Below is the SQL Query which gives random 10 records.

 SELECT * FROM user_table ORDER BY RANDOM() LIMIT 10

What is the JPQL alternative for this, Do we have Random() support for JPQL? is it a good practice to use RANDOM() ?

Upvotes: 3

Views: 3170

Answers (1)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59986

I don't know whether there is RANDOM in JPA. As an alternative solution you can use this trick :

Query queryCount = em.createQuery("SELECT COUNT(u) FROM UserTable u");
Long size = (Long) queryCount.getSingleResult();

//I use this way of Java8, you can use the way you see it better to generate random indexes
int[] index = new Random().ints(0, size, 10).toArray();

Query query = em.createQuery("SELECT u FROM UserTable u WHERE INDEX(u) IN :indexs");
                                                              ^^^^^^^^^^^^^^^^^^^
query.setParameter("indexs", index);
List<UserTable> listUsersRandom = query.getResultList();

Global idea

This solution based on INDEX. The idea is:

  • First query - find the size of your objects
  • Generate a list of index between 0 and the size of your list
  • Second query - select the objects which is IN this generated list

Upvotes: 3

Related Questions