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!
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:
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;
}
PageRequest.of(idx, 1) ;)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.
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.