Fetch/retrieve top n records from table in java


setMaxRows(2) method sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the specified number. If in case limit is exceeded then excess number of rows are dropped without intimating user.


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

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

EMPLOYEE table will look like this >

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 FetchTopNrows {
   public static void main(String... arg) {
          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("Connection established successfully!");
                
                 prepStmt = con.prepareStatement("select * from EMPLOYEE");             
                
                 //Fetch top 2 rows only
                 prepStmt.setMaxRows(2);
                
                 rs = prepStmt.executeQuery();
                 while (rs.next()) {
                       System.out.print(rs.getInt(1)+" ");
                       System.out.println(rs.getString(2));
                 }
                 rs.close();
                
          } 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
Connection established successfully!
1 ankit
2 rohit
*/
                

Internally, prepStmt.setMaxRows(2); tells database that only 2 rows has to be fetched from database.



This can also be done by using rownum in oracle database query

select * from EMPLOYEE where ROWNUM<3;
This query will fetch top 2 rows from oracle database.


Fetch top 2 rows in MySql database
select * from my_schema.employee limit 2;
select * from my_schema.employee limit 2  OFFSET 0;

No comments:

Post a Comment