Problem using setFetchSize() in JDBC for paging

Discussions

Web tier: servlets, JSP, Web frameworks: Problem using setFetchSize() in JDBC for paging

  1. Problem using setFetchSize() in JDBC for paging (1 messages)

    Hello everyone.

    I am facing problem, using pagin within my JSP application.
    My mySQL database holds records of nearly 2000 users.These records are to be displayed using the paging.I will briefly explain what I am doing...

    I create preparedStatement

    preparedStatement pstmt = myconn.prepareStatement("select * from loginmaster order by userName",TYPE_SCROLL_SENSITIVE,CONCUR_READ_ONLY);
    ResultSet rst = pstmt.executeQuery();

    // I wish to fetch only 20 records at a time say

    rst.setfetchSize(20);
    if(rst.next())
    {
    String name = rst.getString(1);
    }
    I am storing the result in a Hashtable and returning it to my JSP which enumerates and displays result in while loop.
    This is what I do the first time a call to the method.I have noticed that inspite of using setFetchSize(), its returning all the rows.BIG PROBLEM..!
    From here onwards, my work is almost stopped as i don't understand what to do next.
    I am aware of absolute() & relative() methods which are directs u'r cursor to any specific rows in resultset object.But a brief explanation ,of what exactly I shold do to achieve the needed functionality will bea BIG HELP.

    Also, I read its not a good thing to keep u'r resultset object open. A better approach would be to fetch all the rows and store it into some datastructure...
    All u'r expert opinions will be appreciated.
  2. Try this link - the paging of hundreds of resut set has been nicely explained.

    http://www.theserverside.com/discussion/thread.jsp?thread_id=11037