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();
}
}
}
}
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.