JDBC Pagination Oracle get 1000 at a time execute then get next 1000

Question

I'm using this code to get 1000 records at a time. It works fine but how do I stop at the 1st 1000 run my job, then pick up where I left off and get the next set 1001 - 2000, run the next job and so on? Please help I'm kind of stuck.

public class PaginationJDBC {

   public static void main(String arg) {
       for(int i=1; i<=5 ;i++){
              paginationMethod(i);
   }
}

static void paginationMethod(int n){
       Connection con = null;
       PreparedStatement ps = null;
       ResultSet rs = null;                  
       try {
              con = gettingMyConnection;
              ps = con.prepareStatement("select emp.id, emp.name  "
                + "from ( select rownum rn, e.* from EMPLOYEE e) emp "
                + "where rn >=? and rn< =? ");
              ps .setInt(1, (n*1000) -999);
              ps .setInt(2, n*1000);

              rs = prepStmt.executeQuery();
              int rowCount = 0;
              while (rs.next()) {
              ++rowCount;
                    System.out.print(rs.getInt(1)+" ");
                    System.out.println(rs.getString(2));
              if(rowCount >= 1000-4) {
               system.out.printlin("total records" + rowCount);
              //I would like to run a job here, returns the 1st 1000
               return;
              } 
              if(rowCount >= 1001) {
               **// this is where I would do the next job between 1001 and 2000 and so on, but I'm stuck**
              }

       } catch (ClassNotFoundException e) {
              e.printStackTrace();
       } catch (SQLException e) {
              e.printStackTrace();
       }
       finally{
              try {
                    if(rs!=null) rs.close(); //close resultSet
                    if(ps !=null) ps .close(); //close PreparedStatement
                    if(con!=null) con.close(); // close connection
              } catch (SQLException e) {
                    e.printStackTrace();
              }
       }

}

}

Please describe your actual problem outside of the code, don't make us guess as to what exactly you're stuck on. And please, properly indent your code, as it stands your logic is hard to follow given your bad indentation. Also, why not just set the fetch size instead of trying to manually paginate, when you are processing the result in one go anyway. — Mark Rotteveel, Feb 11 '18 at 08:51
Actually I wanted to loop through the 1st 1000 and when that's done get the next 1000. I will try setFetchSize method. Thanks — Gee, Feb 11 '18 at 20:04

score 0 · Accepted Answer · answered Feb 12 '18 at 01:00

Use your application to take note of where you got up to, eg, lets say I'm showing records for today, 20 rows at a time. I might write:

Page 1

select * 
from T 
where date_col = trunc(sysdate) 
order by id desc
fetch first 20 rows only

I fetch ID=100 down to 80...I take note of the 80. My next query will then be

select * 
from T 
where date_col = trunc(sysdate) 
AND ID<80  <<==== additional predicate
order by id desc
fetch first 20 rows only

and my next query would be

select * 
from T 
where date_col = trunc(sysdate) 
AND ID<60
order by id desc
fetch first 20 rows only

and so forth.

JDBC Pagination Oracle get 1000 at a time execute then get next 1000

1 Answers1