JDBC pagination : How can we Read/Fetch all records from huge database tables in java


JDBC pagination : How can we Read/Fetch all records from huge database tables in java.

We will learn how to fetch record between 1 to 5, 6-10 and so on..



--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(1, 'ankit');
insert into EMPLOYEE values(2, 'rohit');
insert into EMPLOYEE values(3, 'amy');
insert into EMPLOYEE values(4, 'ric');
insert into EMPLOYEE values(5, 'hay');
insert into EMPLOYEE values(6, 'wat');
insert into EMPLOYEE values(7, 'kev');
insert into EMPLOYEE values(8, 'stu');
insert into EMPLOYEE values(9, 'sac');
insert into EMPLOYEE values(10, 'gay');
insert into EMPLOYEE values(11, 'lax');
insert into EMPLOYEE values(12, 'adam');
commit;

--If table already exists then execute the DROP command >
drop table EMPLOYEE;

EMPLOYEE table will look like this >

Sql query used >
select emp.id, emp.name from
( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=? and rn< =? ;

Replace both occurrence of ? with range from which to which you want to fetch data.

Example >
select emp.id, emp.name from
( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=6 and rn< =10 ;
Above query will fetch rows between 6-10 (including) from Oracle database.

Full Programs JDBC pagination : How can we Read/Fetch all records from huge tables in database
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PaginationJDBC {
   public static void main(String... arg) {
          for(int i=1; i<=3 ;i++){
                 paginationMethod(i);
          }
   }
   static void paginationMethod(int n){
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = null;                 
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 con = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("\nReturn "+ ((n*5) -4) +" to "+(n*5)+" records");
                
                 prepStmt = con.prepareStatement("select emp.id, emp.name  "
                              + "from ( select rownum rn, e.* from EMPLOYEE e) emp "
                              + "where rn >=? and rn< =? ");
                 prepStmt.setInt(1, (n*5) -4);
                 prepStmt.setInt(2, n*5);
                
                 rs = prepStmt.executeQuery();
                 while (rs.next()) {
                       System.out.print(rs.getInt(1)+" ");
                       System.out.println(rs.getString(2));
                 }
                
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
  
   }
  
}
/*OUTPUT
Return 1 to 5 records
1 ankit
2 rohit
3 amy
4 ank
5 hay
Return 6 to 10 records
6 wat
7 kev
8 stu
9 sac
10 gay
Return 11 to 15 records
11 lax
12 adam
*/

In the above program first we fetched record between 1 to 5, 6-10 and then 11-15 (but only 11-12 were available in database).


Pagination in ORACLE  -
Alternatively you can use following query to fetch rows between 6-10 (including) in Oracle database
(select * from EMPLOYEE
where ROWNUM < 11 )
MINUS
(select * from EMPLOYEE
where ROWNUM < 6);
OR,
select emp.id, emp.name from ( select rownum rn, e.* from EMPLOYEE e) emp
where rn >=6 and rn< =10 ;


Pagination in MySql -
Fetch rows between 6-10 (including) in MySql database
select * from my_schema.employee limit 5  OFFSET 5;

No comments:

Post a Comment