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;
