7

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.

vels4j
  • 11,208
  • 5
  • 38
  • 63
user3168286
  • 81
  • 1
  • 1
  • 3
  • 1
    `SELECT q FROM Question AS q ORDER BY RANDOM()` but it would return all the rows with random order. – vels4j Jan 29 '14 at 13:15
  • possible duplicate of [Random select rows via JPA](http://stackoverflow.com/questions/2459119/random-select-rows-via-jpa) – Jasper de Vries Sep 23 '15 at 09:11

3 Answers3

6

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.

vels4j
  • 11,208
  • 5
  • 38
  • 63
  • if I understand right, you gave solution for randomizing 1 question. but if I want some few ? I tried to do setMaxResult(4) for getting 4 questions from DB, but it gives me 4 questions at the same order it is in the DB (let's say the first one being randomized is question number 5, the other 3 questions from DB would be number 6,7 and 8) but I want the other 3 questions to be randomized too. what is the best way to do this ?Thanks – user3168286 Jan 30 '14 at 16:57
  • 1
    @user3168286 you need to call this method n time to get n random questions. Also you may need to implement a logic to skip duplicate since random number is used – vels4j Jul 24 '15 at 07:10
  • @Mr.Pichler did you try that before down vote ? I answered assuming ids starts from (1,2,3....n). – vels4j Jul 24 '15 at 07:12
  • @Mr.Pichler where is the persist happen here in select query. – vels4j Jul 27 '15 at 06:39
  • @vels4j I removed my comments. Sorry for the down voting. If you edit your question I can remove it. What happened is that I down voted your answer instead the previous answer that I wanted it. Also my comments was target to the other answer. – John John Pichler Jul 27 '15 at 23:33
  • @Mr.Pichler ok no problem – vels4j Jul 28 '15 at 04:57
  • @vels4j Removed the down vote, and about your answer, it's work perfectly. – John John Pichler Jul 28 '15 at 11:55
  • Caused by: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long – zdarsky.peter Jun 10 '16 at 19:36
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.

Community
  • 1
  • 1
Nishant Trivedi
  • 33
  • 5
  • The constructor `PageRequest pageRequest = new PageRequest(pageIndex, someLimit)` is deprecated, so we can use `PageRequest pageRequest = PageRequest.of(pageIndex, someLimit)` instead. – tunapq Jun 13 '21 at 10:32
-1

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

Alex K.
  • 3,294
  • 4
  • 29
  • 41