• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • Last Modified:

Scrollable Result Set with commons-dbutils

Hi,

I am using the scrollable result set with commons-dbutils package. I have a paging handler which is implementing the ResultSetHandler
in the following way

    public Object handle(ResultSet rs) throws SQLException
    {
        List result = new ArrayList();

        rs.absolute(startRow);

        int rowNum = startRow;
        while (rs.next() && rowNum <= endRow)
        {
            rowNum++;
            result.add(this.convert.toArray(rs));
        }
        return result;
    }

If i m retrieving say 10 rows,  in case of MSSQLServer it gives me 11. And in case of Oracle it gives an SQLException for rs.absolute(0).
How can i make it working, independent of the driver implementations?

regards
aks
0
aks143
Asked:
aks143
  • 8
  • 6
1 Solution
 
neonlinesCommented:
rs.absolute(0)  ==> should start from 1 not from zero


0
 
neonlinesCommented:
Initialize startRow with 1 not 0
0
 
aks143Author Commented:
i tried that too, in Oracle implementation it skips the first row then.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aks143Author Commented:
Initialize startRow with 1 not 0 --> in both oracle and mssqlserver..it skips the first row.
0
 
neonlinesCommented:
Yes, U have one more mistake in ur code

>> rs.absolute(startRow);

For example startRow is 1, ur currest result set is in 1 (first)


>>        while (rs.next() && rowNum <= endRow)

rs.next() increments ur resultset and so ur rs moves to next record

so change the line as

while (!rs.isLast() && rowNum <= endRow)


0
 
neonlinesCommented:
currest  => current
0
 
aks143Author Commented:
i think, i should enhance it in the following way

        while (!rs.isLast() && rowNum <= endRow){
            rowNum++;
            result.add(this.convert.toArray(rs));
            rs.next();
        }

so that the resultset moves to next.

is it correct?

0
 
aks143Author Commented:
sorry, the above handling will not treat the last row of the result set.

0
 
neonlinesCommented:
I don't know what ur variables  rowNum, endRow having values

U try the following code

int rowcount = 1;
while (!rs.isLast())
{
        rs.absolute(rowcount); // moves the cursor to the rowcount th row of result set
        rowcount++;
          rowNum++;
            result.add(this.convert.toArray(rs));

}
0
 
neonlinesCommented:
Btw, I checked ur code,

Just to add the resultset into ur ArrayList why not u try as follows (I changed ur complete function)

 public Object handle(ResultSet rs) throws SQLException
    {
        List result = new ArrayList();


        while (rs.next() )
        {
            result.add(this.convert.toArray(rs));
        }
        return result;
    }


0
 
aks143Author Commented:
b'coz i m using it for pagination. So, i need to scroll it till the endRow (which is startRow+rowsPerPage).

The following is the method

 public Object handle(ResultSet rs) throws SQLException
    {
        List result = new ArrayList();

        rs.absolute(startRow);

        int rowNum = startRow;

     // the problem here is we are skipping the last row.
    // so last row can never be read
      while (!rs.isLast() && rowNum <= endRow){
            rowNum++;
            result.add(this.convert.toArray(rs));
            rs.next();
        }

        return result;
    }
0
 
aks143Author Commented:
HI,

following will do,

    public Object handle(ResultSet rs) throws SQLException {
        List result = new ArrayList();

        rs.absolute(startRow);

        int rowNum = startRow;

        while (!rs.isLast() && rowNum <= endRow){
            rowNum++;
            result.add(this.convert.toArray(rs));
            rs.next();
        }

        if(rs.isLast()){
            result.add(this.convert.toArray(rs));
        }
       
        return result;
    }
0
 
neonlinesCommented:
Then, I think u intialized rowNum as zero

change it to 1

it's problem with ur  rowNum <= endRow checking only. So, find the variables are properly used or not
0
 
neonlinesCommented:
Good ...
0
 
Jim CakalicSenior Developer/ArchitectCommented:
Hi,

Now that an answer has been selected I'd like to interject a comment -- didn't want to hijack the question from you neonlines :-)

Implementing pagination in this way tends to be quite inefficient with large result sets. The reason is that the JDBC driver has to read (possibly across the network) and discard all the rows that precede the first row you really wanted. When you only have a couple hundred rows it might not be a big deal. But a couple thousand starts to be a potential performance problem.

There are better ways of implementing result set pagination but none (AFAIK) that are vendor-neutral. So you might consider changing the SQL you use in this instance to be vendor-specific. In combination with the use of derived tables, you could use the SQL Server TOP keyword to retrieve just the specific set of results that you need. Likewise, you could use Oracle's ROWNUM.

As an example, assume you had an employee table with columns last_name and salary. If you wanted to display all employees in paginated form 20 to a page and ordered by last_name you could do this with SQL Server (replacing endrow and startrow appropriately):

select top (:endrow - :startrow + 1) last_name, salary from (
    select top :endrow last_name, salary
    from employee
    order by last_name
)

and this with Oracle:

select name, salary from (
    select name, salary
    from employee
    order by last_name
) where rownum >= :startrow and rownum <= :endrow

Regards,
Jim Cakalic
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now