Scrollable Result Set with commons-dbutils

rs.absolute(0)  ==> should start from 1 not from zero

Initialize startRow with 1 not 0

i tried that too, in Oracle implementation it skips the first row then.

Initialize startRow with 1 not 0 --> in both oracle and mssqlserver..it skips the first row.

currest  => current

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?

sorry, the above handling will not treat the last row of the result set.

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));

}

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;
    }

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;
    }

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;
    }

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

Good ...

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