Still..Fetching Problem..Please Help
Hello! Below is the code snippets of my program to test the setFetchSize() method. When run, it still retrieves all the contents of the table. I just need to retrieve say 4 items per page. Please help. Thanks.
******************************************
int skucd = 0;
String skudesc = "";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:dsShopCart", "", "");
Statement stmt = con.createStatement();
stmt.setFetchSize(4);
ResultSet rs = stmt.executeQuery("SELECT * FROM tblProduce");
while (rs.next())
{
skucd = rs.getInt("sku_code");
skudesc = rs.getString("sku_desc");
System.out.println("Sku Code-"+skucd +"/Desc-"+skudesc);
}
******************************************
[838 byte] By [
jpearlm] at [2007-9-26 2:03:43]
Using Stqtement.setFetchSize() one can only provide the JDBC driver a hint as to how many records the driver code should actually fetch from the database whenever it has reason to fetch results physically from the database and the driver is free to ignore that hint as well.
However, the point to be noted in your case is that when a JDBC driver actually fetches results from the database depends on the specific driver implementation. For example, this may happen not only when one invokes Statement.executeQuery() etc but later on, for example when one invokes some method that performs some row operation on the result set. So, using setFetchSize() one can provide a hint to the driver as to how many records to physically fetch from the database whenever there is such a need to physically fetch from the databse.
As far as I know, one has to implement some kind of paging model over the results by onseself.
If you are involved with diaplaying results on an HTML page, tis might help http://jsptags.com/tags/navigation/pager
Hi jpearlm,Have you tried using setMaxRows instead? Let me know.
Greenmango,
I have not tried using the setMaxRow() method yet. I understand that this method only limits the record retrieval to the specified user-giver row and that's it. What I need is that my resultset will still retrieve all data but fetching is by batch.
My purpose of targeting the use of setFetchSize() method is to limit the display of items per page.
************
Neville,
Yeah, I need to display the records (but by batch, say 10 per page) on a HTML page. Thanks for the site url.
If u have any idea on how to do this, pls. feel free to discuss it with me. Thanks.
I am sure you would have gathered that the site URL I specified in my earlier message is about a JSP custom tag library.
Now, assuming you are indeed developing HTML presentation using JSP, if you download the specified tag library, you should find even the souce code for it in a nested jar file called pager-src.jar. Did you not find it there ?
Another varierty of JSP custom tags that serves the purpose of paged display of databse results can be found in the Java Pet Store application that is part of Sun's J2EE blueprints.
I guess the big picture idea I was trying to put forth was to...
a. get all records from the database once, cache them somewhere and them use the JSP tags to do the pagination.
OR
b. if you do not want to expend memory for the cache, get the complete set of records each time you display a pageful; but just pass over the requisite number(lets say n) of records from the result set by performing n calls to resultSet.next() to get to the one you want to treat as the start record for the current pageful.
Option b. that is the one implemented in the Java Pet Store application in combination with JSP custom tags.
I hope I haven't confused you.
Neville,
I think I still cannot implement the presentation like an AltaVista or Google type because that will still slows down the performance. Imagine, I have to retrieve 10,000-20,000 image records at one time, that will take a lot of time to download. I think, the way here is to control it in retrieval (SQL). But i do not know how to execute right the methd setFetchSize(). it's not working or, based on the code snippet i posted above, i may have wrongly coded it.
Please help.
Thanks.
Neville,I'm interested on your suggestion b and Option b. When you say cache, what do you mean?
I am not sure that I have not confused you.
Anyways, first of all, forget setFetchSize(). See, the purpose of that method is not what you think it is. And let me add, you are not the first person to have mistaken the purpose of that method.
Secondly, I do not know of any way that you can somehow implement the requirement of displaying a pageful at a time by controling it in the SQL execution. Unless ofcourse the databse table you are accessing has some column which you could use to specify a constraint on in your SQL. For example, if this special column is some kind of numeric value that serves as a sequence number, you could instrument your SQL by adding the where clause ... and specialColumn >= m and specialColumn <= n ... where m and n would be chaged dynamically based on which exact pageful is being retreived.
Neville,
Thank you for emphatically saying "forget setFetchSize()". That method gave me false hope in setting the size of data retrieval. At least now, I can focus on a more constructive solution.
Are you saying that there is no way I can control the retrieval in SQL unless i use WHERE conditions and pass parameters? Then, what could be a good solution when retrieving this huge volume of data, and the performance does not suffer.
Please discuss more on cache. I cannot get the whole idea of it.
Thanks.
Neville,
How to do your suggestion read as:
"b. if you do not want to expend memory for the cache, get the complete set of records each time you display a pageful; but just pass over the requisite number(lets say n) of records from the result set by performing n calls to resultSet.next() to get to the one you want to treat as the start record for the current pageful."
Please help.
Thanks.
Hi,
I am not sure how to implement this exactly in code, but if you set JDBC to return the resultSet as a Keyset cursor. This will fetch a key (usually the primary key) of all the data, i.e. your resultSet only holds a *reference* to each record. Then when you are displaying the records, you can use some loop counter to read them in batches. The full records are read only when the record is accessed, so you should be able to read your 10 (or so) images in the batch. Then you could repeat this process for the next batch, etc.
Hope this helps,
Fintan
