aspose file tools*
The moose likes JDBC and the fly likes Select Random result from Resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Select Random result from Resultset" Watch "Select Random result from Resultset" New topic
Author

Select Random result from Resultset

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Hi,

I have a requirement to randomly select a string from a resultset. How do I do it ?

I have a resultset which selects 2 strings from Oracle database.

Like this: Select a,b from table a where xxx

Lets say my query gives me 5 record sets.
For each transaction I want to retrieve results randomly, like if first transaction output is 1 recordset then second should be 2 recordset or 3 record set etc[random outputs]
Can I achieve this in Java/JDBC ?

I know I can get Random results using dbms_random package in Oracle, but doing this I have to do two queries.

Please help

thanks
Raj Chila
Ranch Hand

Joined: Mar 18, 2004
Posts: 128

you may not have such a feature in JDBC but you could generate a Random number in the selected column range and use that as the column number to read from the row.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

I think RajaniKanth meant row instead of column. I prefer that approach as it makes the SQL database independent.

If you are ok with something Oracle specific, see this list. Read the comments below it as they list some variations there.


[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
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Jeanne and Raj,

Thanks for the comments. I am using the ORACLE approach right now, using dbms_random package. Which is the preferred approach ?

If Java, random way is the preferred approach, which is the API which I should use. Any samples, or links appreciated

Thanks.
Raj Chila
Ranch Hand

Joined: Mar 18, 2004
Posts: 128

Jeanne, I meant row, which means I misunderstood the question in the first place, but as you said this this approach can be used in both scenarios.

Atul, here are the steps to do it.
1. Use the Math.random() method to get a random value.
2. get the result set.
3. if you dont already know the number of rows then use last(),getRow() on your result set object to get the number of available number of rows. and then calculate a random row number with the help of the random number calculated at 1.
4. use absolute(with number calulated at 3)

your cursor is at a random row.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

"Raj B",
You appear to have changed your display name from a valid one to an invalid one. In particular, a last name is required - not just a last initial.

Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

Thanks,
Jeanne
Forum Bartender
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Select Random result from Resultset