This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Soft Skills and have John Sonmez on-line!
See this thread for details.
The moose likes JDBC and the fly likes Getting random records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting random records" Watch "Getting random records" New topic
Author

Getting random records

Hussein Baghdadi
clojure forum advocate
Bartender

Joined: Nov 08, 2003
Posts: 3479

Hi.
Suppose my database has 1000 recored.
How can I get ten random records from a database -to be displayed , say in the index page- ?
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
Which database are you using?. If you happen to use Oracle then it's simple


sometimes you may not get random rows if SQL Plan happens to use Index. You can try this
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

You could always use the random api in java to select an id from x to y and then use that id to fetch the record from the database.


My Blog: Down Home Country Coding with Scott Selikoff
Mahendra Shelke
Greenhorn

Joined: May 08, 2006
Posts: 20
But i suppose using the SQL function which exists in almost every DBMS/RDBMS would be a far more appropriate option rather than using any java api for that purpose.
Hendy Setyo Mulyo
Ranch Hand

Joined: Dec 01, 2004
Posts: 219

use the Java API random class to generate the random ids then query the record from database based on them.


Hendy Setyo Mulyo
SCJP 1.4 (95%), SCWCD 1.4 (94%)
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

Originally posted by Mahendra Shelke:
But i suppose using the SQL function which exists in almost every DBMS/RDBMS would be a far more appropriate option rather than using any java api for that purpose.

Which function does this refer to?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
I don't understand how selecting random records through java is going to be effective. With java you need to

- Generate the Random numbers (What numbers?, How is it going to be used? You need to find out the domain boundary (max row or max value of numeric column)
- Bind these values to the SQL Query using IN operator. (Define SQL with 10 parameters and bind the values)
- execute query.

Isn't this approach bit expensive compared to doing it through DB?
[ October 21, 2006: Message edited by: Purushothaman Thambu ]
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Yes/No, if you have well structured data we some predefined information, the database call that actually retrieves the results will be a lot faster. Database calls are almost always more expensive than java calls so if you can reduce the database calls easily, do it. If the range is difficult to define and the data is scattered that's another story (although I imagine there's probably a clever way to do this in java still).
Hussein Baghdadi
clojure forum advocate
Bartender

Joined: Nov 08, 2003
Posts: 3479

I'm using MySQL 4.1
Actually, I was thinking about getting a random generated number, but is it the best way ?
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I'd prefer a sql solution, too, but would have to rely on db experts to hand me one.

Java approaches ... as mentioned above generating random keys in Java will only work if you know the database key distribution or generation algorithm. If you know the db has keys 1 through 1000 it's not too hard. If the db key is SSN, it will take a while for a RNG to get even one hit.

Check the performance of select the key from all rows, build an array, shuffle the array, query on the first n keys. This won't scale forever.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Purushoth Thambu
Ranch Hand

Joined: May 24, 2003
Posts: 425
I am not mySQL guy, however when I looked at 4.1 doc's I see note on bug fix to make

work. Even if this doesn't work then you can use LIMIT functionality of MySQL to randomly pick the record.



You need to bind ? with random number generated from Java (if you cannot find some other way to get this done on DB side).

PS: I am not sure what will be the cost of ORDER BY RAND() SQL will be. Since you need just 10 records if the SQL does a full scan then you need to compare between fetching one record at a time and RAND() method. It works differently in Oracle when you use ROWNUM < ? sort of queries.
[ October 23, 2006: Message edited by: Purushothaman Thambu ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting random records