JPA: select random row

Question

This is my JPA ENTITY

@Entity
@NamedQueries({  
        @NamedQuery(name = "Question.randQuestion", query = "SELECT q FROM Question AS q ORDER BY     RANDOM")
})
@Table(name = "questions")
public class Question implements Serializable {
.....
}

The problem is:

eclipse gives me an error for this namedQuery. It says: "The identification variable 'RANDOM' is not defined in the FROM clause"

I've tried also with RAND() instead of RANDOM and also NEWID().

Thanks.

Answer 1

To get a Random Row, first get list of total question and get any one.

public Question  getRandomQuestion(EntityManager em) {
  Query countQuery = em.createNativeQuery("select count(*) from Question");
  long count = (Long)countQuery.getSingleResult();

  Random random = new Random();
  int number = random.nextInt((int)count);

  Query selectQuery = em.createQuery("select q from Question q");
  selectQuery.setFirstResult(number);
  selectQuery.setMaxResults(1);
  return (Question)selectQuery.getSingleResult();
}

Note: You may need to implement a logic to avoid duplicates while calling method more than once.

Answer 2

I had to solve a specific case of this problem where I had to select random records from a set of records matching a certain input criteria. The solution also had to support limit. I describe my solution below starting with assumptions.

Assumptions:

• Given the set of criteria as input, it is possible to count number of records that match a selection criteria, as supported by the org.springframework.data.querydsl.QueryDslPredicateExecutor<T>.count(Predicate predicate) method.

• Pages are zero indexed.

• It is possible to request specific page as supported by the org.springframework.data.domain.PageRequest(int page, int size) method.

Algorithm

1. Count all records matching the input criteria.

2. Calculate total number of pages based on the count and specified limit.

3. Generate a random page index in range [0, total pages).

4. Request the page with index generated in previous step.

5. Shuffle elements in the returned page.

Code

Long totalRecords = someRepository.count(somePredicate);
Long totalPages =
    (totalRecords % someLimit == 0)
        ? (totalRecords / someLimit)
        : ((totalRecords / someLimit) + 1);
int pageIndex = (int) (Math.random() * totalPages);

PageRequest pageRequest = new PageRequest(pageIndex, someLimit);
Page<T> somePage = someRepository.findAll(somePredicate, pageRequest);
List<T> someList;
if (somePage.getTotalElements() > 0) {
  someList = new ArrayList<>(somePage.getContent());
} else {
  someList = new ArrayList<>();
}

Collections.shuffle(someList);

The second shuffle is to ensure records within the page are also randomized. The general case of this solution is that there is no criteria and so the count() has to be invoked with no predicate thus getting a count of all rows in the table.