Select random entity using JPA ql
Hallo,
I'd like to select random entity from database -- e.g. single fortune cookie that will be displayed on application web page. Unfortunately I've been able to think out only very simple (and inefficient) method so far:// Fortune cookie entity
@Entity
class FortuneCookie{
@Idint id;
String text;
}
/**
* @return random fortune
*/
publicvoid FortuneCookie getRandomFortune(){
// Query all fortunes
Query q = em.createQuery("select f from FortuneCookie f");
List allFortunes = q.getResultList();
// Select one at random
return getRandomListElement(allFortunes);
}
How do I perform such (random) select without actually fetching all object from database, please? If possible avoid fallback to native query, I use javaDb for development but product will be deployed on mysql database.
~~
Adam
PS: I am not sure about forum. Does posts about jpa belong here?
[1419 byte] By [
a3cchana] at [2007-11-27 2:18:12]
# 1
Not a JPA-specific issue, is it? Don't your fortune cookies have a unique id as a number? If not, it shouldn't be too hard to create an extra column for those.
You could randomize that id-number and then go to the db to specifically query one fortune cookie which id was chosen randomly (select/where clause).
BTW, how big is your fortune cookie db? Because if you don't have that many of them, you could preload and cache them in a collection and randomize on the position of an element in that collection...That would avoid going to the db everytime.
# 2
Thank you...
> Don't your fortune cookies have a unique id as a number? ...
Yes they have but, cookies can be removed and thus, interval is not continuous. E.g. {1,2,5,6,7} after 3 and 4 has been removed.
> ... you could preload and cache them in a collection and
> randomize on the position of an element in that collection...
Cacheing seems like a good idea -- I'll probably add some FortuneCookieDAO as layer between my accessor session beans (FortuneCookieAccess) and cookie entity (FortuneCookie). But where should I store instance of this DAO? It has to be accessible from any session bean instance. Into my server's directory (jndi)?
Or did you mean cache on site of web application? That's not possible, my selecting random cookie is responsibility of FortuneCookieAccess ejb.
~~
Adam
PS: I know that using ejbs for fortune cookie selection is not a model usecase (there's saying in my country: "It's like shooting sparrow with a cannon") ... is not a model usecase, but it's just training application.
# 3
> Or did you mean cache on site of web application?
No, I was thinking of EJB caching.
There are different ways to do that (Singleton, EB etc..).
Rather than enumerate & discuss them here myself, here's a [url=http://www.theserverside.com/discussions/thread.tss?thread_id=765]thread[/url] that discusses the subject.
# 4
Thanks! I'll take a look at it.
Meanwhile I've figured how to select random fortune quite easily, but w/o cacheing...// get number of cookies
int count = (Integer)em.createNamedQuery("countCookies").getSingleResult();
// select all cookies
Query q = em.createNamedQuery("allCookies");
// move to random page (each result page have size 1)
q.setFirstResult(new Random().nextInt(count));
q.setMaxResults(1);
return (FortuneCookie) q.getSingleResult();
Not tested but hope this will work...
