Fetch random records using Spring data JPA

Question

I want to fetch random records using Spring data JPA. I was using @Query for the same.But it is taking a long time.

@Query("select que from Question que order by RAND()")
public List<Question> findRandamQuestions();

Which is the efficient way for doing the same?Please help!

Solution 1

The problem with select que from Question que order by RAND() is that your DB will order all records before return one item. So it's expensive in large data sets.

A cheaper way to achieve this goal consist in two steps:

1. Find total of records from where you will select one.
2. Get one random item in this set.

To do that in MySql for example, you can do:

select count(*) from question;

// using any programming language, choose a random number between 0 and count-1 (let's save this number in rdn), and finally

select * from question LIMIT $rdn, 1;

Ok, but to do that in spring data you need to create some native queries...

Fortunately, we can use pagination in order to resolve that. In your Repository Interface, create the methods (some repositories has this without need to define it):

Long count(); 
Page<Question> findAll(Pageable pageable);

And in your Service you can user your repository in the following way:

public Question randomQuestion() {
    Long qty = questionRepository.countAll();
    int idx = (int)(Math.random() * qty);
    Page<Question> questionPage = questionRepository.findAll(new PageRequest(idx, 1));
    Question q = null;
    if (questionPage.hasContent()) {
        q = questionPage.getContent().get(0);
    }
    return q;
}

Solution 2

You could do this post fetch.

Get a list of all the questions and just get random ones from those.

public List<Question> getRandomQuestions(List<Questions> questions, int numberOfQuestions) {
    List<Question> randomQuestions = new ArrayList<>();
    List<Question> copy = new ArrayList<>(questions);

    SecureRandom rand = new SecureRandom();
    for (int i = 0; i < Math.min(numberOfQuestions, questions.size()); i++) {
        randomQuestions.add( copy.remove( rand.nextInt( copy.size() ) );
    }

    return randomQuestions;
}

Or if your list was really large and you knew the IDs beforehand, you could do the same thing and just fetch the Questions Ids that you needed.

Solution 3

AFAIK there is no support for this in Spring Data. IMHO, your best course of action is to create a native query e.g. @Query(nativeQuery=true, value="SELECT * FROM question ORDER BY random() LIMIT 10") using PostgreSQL's native random() sorting method, or some equivalent in your DB.

Comments

[fivedogit]

This is one of those perfect answers where (a) it's exactly what I needed and (b) Google and I were unable to find anything else remotely close. But I think you have a typo: shouldn't "countAll()" be "count()"?

[Doogle]

High Five... By far the best explaination on the topic. Thanks for taking time and sharing the result. Its super and easy to understand the concept every for a newbie like me.

[Yazan Khalaileh]

new PageRequest() is now deprecated. User PageRequest.of(idx, 1) as an argument instead