CODEWITHSUNDEEP
springhibernatejpaspring-dataspring-data-jpa
psms
psms

Reputation: 883

Fetch random records using Spring data JPA

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!

Upvotes: 23

Views: 28931

Answers (3)

Pedro Leite
Pedro Leite

Reputation: 616

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

Upvotes: 42

gmich
gmich

Reputation: 339

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.

Upvotes: 4

Chris Savory
Chris Savory

Reputation: 2755

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.

Upvotes: 1

Related Questions