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;