aspose file tools*
The moose likes JDBC and the fly likes getting a range of returned rows? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting a range of returned rows?" Watch "getting a range of returned rows?" New topic
Author

getting a range of returned rows?

Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
I'm sure you've all seen this somewhere:
do a search on some data with some criteria, return results 10-20.
Is there a way to do this with SQL, or do you have to do this programmatically?
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Here you get into DB-specific (and thus non-standard) SQL.
In MySQL, for instance, you would say
SELECT ... LIMIT 9, 10;
the first number is the number of the first row you want (MySQL counts from 0, not 1); the second is the number of rows to return.


Ron Newman - SCJP 1.2 (100%, 7 August 2002)
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
thanks, exactly what I needed
Ron Newman
Ranch Hand

Joined: Jun 06, 2002
Posts: 1056
Just be aware that this SQL statement probably will not work if you move from MySQL to some other database server.
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
Yah, I've got my app modular enough that I can hop databases by switching classes, so no biggy.
I'll probably stick with mysql for awhile anyway, free is good!
(although I miss views)
maneesh subherwal
Ranch Hand

Joined: Aug 26, 2002
Posts: 42
how would this work in SQL Plus?
Thanks,
Maneesh


Sun Certified Java Programmer 2 (1.4)<br />Sun Certified Web Component Developer
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
Correllatingly (is that a word?),
is it possible to use this in prepared statements?
ie
..."LIMIT ?,?" and specify 9,10 as parameters?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

You can do anything in a prepared statement that you can do in command line SQL for the database under consideration.


[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
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
just to clarify, can I even include keywords as parameters in prepared statements?
such as
"SELECT * from blah where blah = "blah" ? ?,?"
with the parameters being LIMIT,10,10 ?
Or what if I wanted LIMIT to be an optional thing, is there a way to do a true/false sort of thing where if there is no page_number, num_results specified to do away with the whole LIMIT clause altogether?
example in pseudo-SQL:
(no limit or order by specified)
"WHERE ...;"
(limit specified)
"WHERE ... LIMIT ?, ?;"
(order by specified)
"WHERE ... ORDER BY ?;"
(limit and order specified)
"WHERE ... LIMIT ?, ? ORDER BY ?;"
Can these all be done in one prepared statement or should I make multiples?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

I think I misinterpreted the question. The parameters can't be keywords. I meant that you could do "SELECT * from blah where blah = ? limit 10, 10"
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
Then is there any way to do a LIMIT that will return all rows?
I could just say 0,1000000000 but I'd rather not be arbitrary
[ September 15, 2003: Message edited by: Phil Chuang ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting a range of returned rows?