[SOLVED] Selecting random rows from database with ejb ql

Selecting random rows from database with ejb ql

I'm developing a web application for advertising real estates. On the search page i'd like to show some random advertisements, but so far I've had no success with selecting random records from the database. This worked in the console, but i couldn't really put it into EJB QL:

SELECT * FROM RealEstate ORDER BY RANDOM()

What i tried:

Random random = new Random();
Query q = em.createQuery("SELECT r FROM RealEstate r ORDER BY :random");
q.setParameter("random", random.nextInt());
return q.getResultList();

It seemed to work when i wrote a number instead of the :random parameter but with this i got an exception. I tried to solve the problem with a NativeQuery

Query q = em.createNativeQuery("SELECT * FROM RealEstate ORDER BY RANDOM()");
List<RealEstate> resL = q.getResultList();

But there was a problem with the fields when i tried to display the advertisements on the webpage. I guess because there were Objects in the resultlist not RealEstates. I use joined type inheritence I don't know if it interferes with the use of nativequery but I would prefer to use ejb ql queries. I'm open to any ideas.

Note: I know that I select all the records only the order differs, limiting the result set will be the next step.

Solution

I think you almost solved your own problem.

I would retrieve the number of record

Integer singleResult = em.createQuery("SELECT count(r) FROM RealEstate r", Integer.class).getSingleResult();

Then, I would create a random number out of that and finally

    Random random = new Random();

        RealEstate RealEstate = em.createQuery("SELECT r FROM RealEstate r", RealEstate.class)
            .setFirstResult(random.nextInt(size))
            .setMaxResults(1) 
            .getSingleResult();