CODEWITHSUNDEEP
javajpajpqlnamed-query
user3168286
user3168286

Reputation: 91

JPA: select random row

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.

Upvotes: 8

Views: 21663

Answers (3)

Nishant Trivedi
Nishant Trivedi

Reputation: 33

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.

Upvotes: 2

vels4j
vels4j

Reputation: 11298

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.

Upvotes: 6

Alex K.
Alex K.

Reputation: 3304

As far as I understand you want to select random question from the table. You'd rather use WHERE clause, with providing some parameter from your code, like:

SELECT q FROM Question AS q WHERE id = :id

Then in the code, which creates your query you must generate random id to be selected:

query.setParam("id", getRandomId());

And to get random id, you may want to query number of rows from DB and use java.util.Random.nextInt(rowsCount) (if all ids are there, of course).

Btw, something similar is described here: http://www.shredzone.de/cilla/page/53/how-to-fetch-a-random-entry-with-hibernate.html

Upvotes: -1

Related Questions