Limiting the number of rows returned by SQL or ResultSet
Hello,
I'm trying to limit the number of rows that my application receives to a user-specified value. The user is asked how many accounts they want returned and I want to limit my ResultSet to this amount. I'd prefer to set up the SQL to do this if possible rather than waste time returning more records than necessary to the ResultSet.
If it's not possible to limit the SQL, how can I limit the number when processing the ResultSet?
Or, is there any way to setup a cursor in JDBC without being able to use Stored Procedures?
I'm going up against DB2 using DB2Connect.
Thanks!
Your database may or may not support this SQL syntax:Select * from FoosballTable where feet=4 limit 20
I've not tried it but the statement class has this method:
public void setMaxRows(int max)
throws SQLException
Sets the limit for the maximum number of rows that any ResultSet object can contain to the given number. If the limit is exceeded, the excess rows are silently dropped.
Parameters:
max - the new max rows limit; zero means unlimited
Throws:
SQLException - if a database access error occurs
try setFetchSize(int rows) of the statement or resultset. i am not sure if it works as i have never used it.
Some databases support syntax like :select top <number> from...orselect first <number> from ...
Hello,
Another solution, could be to use the features introduced in the JDBC2.0 (I hope there is one for your database).
You can define a scrollable result set and extract the relevant rows.
Take a look here for more details:
http://java.sun.com/j2se/1.3/docs/api/java/sql/ResultSet.html
I hope it helps.
I agree with davejenk. The purpose of setMaxRows(int) is to do exactly what you are asking without resorting to a database specific function. setFetchSize does not limit the number of rows in a ResultSet and the other solutions seem like expensive work arounds for a method that already exists (setMaxRows())
my 2 cents
Jamie
Caveat : setMaxRows would work for Oracle, MS-SQL Server but it would not work for Sybase.
Hello,
Actually as the ResutSet is an interface, the "real" implementation of the setMaxRows function is in charge of the JDBC vendor. Thus, if the function setMaxRows does not work in the case of a Sybase datase, it is only because the JDBC vendor has not implemented the function.
Furthermore, as a response to the initial question, I think that the problem was to create a pagination mechanism. In this case, one could suggest to use the "absolute" function to move the cursor on the first row to page. There, one processes with the usual "next" function until the last row to page is reached.
I hope it helps.
P.S.: the first think to do is to check in the release notes of the JDBC what are the supported functions.
>it is only because the JDBC vendor has not implemented the function.
To a certain degree. However, there might not be any reason to implement it. For example if the database itself does not provide an easy way to count the number of rows, then should the interface do the same thing the programmer would have to do to count it anyways?
Further it might not be possible. Using the same example of counting rows. The actual interface might not allow the interface to 'go back to the beginning' so what happens if the count method is called before the user accesses the data - the interface would have to do the query twice. Or worse if the user codes the count method in the middle of the result set sequence, what then?
Hello,
You are entirelly right jschell ! Some methods does not make sense for some underlying databases.
Furthermore, one must pay attention on how the result set is processed. As underlined, to scroll up a result set or to count the number of returned rows right in the middle of a treatment have unexpected results.
Thanks for your precisions.
Hi Jamie,
I read the java docs and confused about the description on set/getFetchSize() of Resultset.
We are developing a JCA Adapter for an EIS where we need to implement this resultset interface onlly , where there is no statement and preparestatement and all here is my query about the it:
When will be the get/setFetchSize methods are called on Resultset and who would call these methods and how is it possible to fetch more rows from database into the same resultset the one which I got it already.
Thanks in advance.
E-mail : kcnu@ggn.hcltech.com.
It's not surprising that you are confused because the documentation doesn't bother to explain what the "fetch size" actually is, it just says that setFetchSize sets it and getFetchSize gets it. As I understand it from some other documents I read about JDBC, the fetch size is a number that may be used internally by the JDBC driver. Here's an example of how I understand it (others, I know you will feel free to correct me if you disagree):
When the driver produces a result set with a very large number of records, it has to generate those records and deliver them to the system that requested them. If the database is not on the same system, then those records must all travel over the network. It could be a performance problem if you had to wait for (say) 80,000 records to travel across the network. Enter the fetch size. If you set the fetch size to 100, then the driver will bring the records across in batches of 100, as the program calls for them. Now, this buffering is transparent to your program; the driver doesn't tell you that it's getting another batch and you can't tell it to get another batch. So it is not a solution to the problem that everybody has here, namely how to display your records 10 per page and allow the user to go back and forth among those pages, like search engines do.
Hi Dr,
Thanks for your valuable information.In our application we are directly getting ResultSet instance,from underlying legacy system.(We don't have a connection,Statement etc)and we have to provide the implementation of resultset the way a driver vendor does.
How do we implement set/get FetchSize() in our case.Are you aware of some sample implementation /source code that could shed some light.
Thanks,
k. Srinivas
In this situation the fetch size makes no sense, I think.
It would mean you must either cache the retrieved rows in your middleware's environment, or you must retrieve them step by step from the database using cursors etc.
Maybe anyone else has a better idea there.
But if you really have to emulate a JDBC driver against the client side, you should have got enough work, so simply let this feature unimplemented, like many JDBC driver vendors do, at least for the first version of their products.
set/get FetchSize() could simply be left out or bring the information "Not yet implemented" (exception?) or are available as dummies: set does nothing, get returns 0.
Well...as it turns out my drivers don't support JDBC 2.0 features so I can't make any headway with fetchSize(), etc.
And, DB2 doesn't support the limit feature of SQL.
So, I'm back to square one and looking at some non-JDBC solutions for our problem. Basically, our SQL is just a monster and we'll have to redesign it.
Thanks for all of your responses!!
setFetchSize makes no sense, sight.But it has not yet got clear for me:Did you try setMaxRows() already, or not?
I hope there is still someone here ;-)My question is the following: if I use setMaxRows (which I do all the time), will the query actually stop on the database, or will the driver just stop reading from the database, while the query still runs there (on and on and on ...)
> I hope there is still someone here ;-)
>
> My question is the following: if I use setMaxRows
> (which I do all the time), will the query actually
> stop on the database, or will the driver just stop
> reading from the database, while the query still runs
> there (on and on and on ...)
Depends on the database/driver. Sorry.
I am using MS SQL 2000 server.
I want a parameterised query for pagination support. I wann to fetch a records in following order 1-100 then 100-200 , 200- 300 & so on
i.e just want to fetch 100 records only per request
Select * from table limit 100,200
above syntax is not working
How can i do it with MS SQL 2000 server? any suggesions?
Thanks in advance...
I am using MS SQL 2000 server.
I want a parameterised query for pagination support. I wann to fetch a records in following order 1-100 then 100-200 , 200- 300 & so on
i.e just want to fetch 100 records only per request
Select * from table limit 100,200
above syntax is not working
How can i do it with MS SQL 2000 server? any suggesions?
Thanks in advance...
