Java ResultSet interface is a part of the java.sql package. It is one of the core components of the JDBC Framework. ResultSet Object is used to access query results retrieved from the relational databases.
ResultSet maintains cursor/pointer which points to a single row of the query results. Using navigational and getter methods provided by ResultSet, we can iterate and access database records one by one. ResultSet can also be used to update data.
The above diagram shows the place of ResultSet in the JDBC Framework. ResultSet can be obtained by executing SQL Query using Statement, PreparedStatement or CallableStatement.
AutoCloseable, Wrapper are super interfaces of ResultSet. Now we will see how to work with ResultSet in our Java programs.
Let’s have look at the below example program to fetch the records from the table and print them on the console. Please make sure you have the MySQL JDBC driver in the project classpath.
packagecom.journaldev.examples;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.Statement;importjava.util.Date;/**
* Java Resultset Example of Retrieving records.
*
* @author pankaj
*
*/publicclassResultSetDemo{publicstaticvoidmain(String[] args){String query ="select empid, firstname, lastname, dob from tblemployee";Connection conn =null;Statement stmt =null;try{Class.forName("com.mysql.jdbc.Driver");
conn =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb","root","root");
stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(query);while(rs.next()){Integer empId = rs.getInt(1);String firstName = rs.getString(2);String lastName = rs.getString(3);Date dob = rs.getDate(4);System.out.println("empId:"+ empId);System.out.println("firstName:"+ firstName);System.out.println("lastName:"+ lastName);System.out.println("dob:"+ dob);System.out.println("");}
rs.close();}catch(Exception e){
e.printStackTrace();}finally{try{
stmt.close();
conn.close();}catch(Exception e){}}}}
ResultSet is obtained by calling the executeQuery method on Statement instance. Initially, the cursor of ResultSet points to the position before the first row.
The method next of ResultSet moves the cursor to the next row. It returns true if there is further row otherwise it returns false.
We can obtain data from ResultSet using getter methods provided by it. e.g. getInt(), getString(), getDate()
All the getter methods have two variants. 1st variant takes column index as Parameter and 2nd variant accepts column name as Parameter.
Finally, we need to call close method on ResultSet instance so that all resources are cleaned up properly.
This type of ResultSet instance can move only in the forward direction from the first row to the last row. ResultSet can be moved forward one row by calling the next() method. We can obtain this type of ResultSet while creating Instance of Statement, PreparedStatement or CallableStatement.
Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);ResultSet rs = stmt.executeQuery("select * from tbluser");
Scroll Insensitive ResultSet can scroll in both forward and backward directions. It can also be scrolled to an absolute position by calling the absolute() method. But it is not sensitive to data changes. It will only have data when the query was executed and ResultSet was obtained. It will not reflect the changes made to data after it was obtained.
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);ResultSet rs = stmt.executeQuery("select * from tbluser");
Scroll Sensitive ResultSet can scroll in both forward and backward directions. It can also be scrolled to an absolute position by calling the absolute() method. But it is sensitive to data changes. It will reflect the changes made to data while it is open.
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);ResultSet rs = stmt.executeQuery("select * from tbluser");
boolean absolute(int row) throws SQLException**:** This method moves ResultSet cursor to the specified row and returns true if the operation is successful.
void afterLast() throws SQLException**:** This method moves ResultSet cursor to the position after the last row.
void beforeFirst() throws SQLException**:** This method moves ResultSet cursor to the position before the first row.
boolean first() throws SQLException: This method moves ResultSet cursor to the first row.
boolean last() throws SQLException: This method moves ResultSet cursor to the last row.
boolean next() throws SQLException: This method moves ResultSet cursor to the next row.
boolean previous() throws SQLException: This method moves ResultSet cursor to the previous row.
packagecom.journaldev.examples;importjava.sql.*;/**
* Java Resultset Example using navigational methods.
*
* @author pankaj
*
*/publicclassResultSetDemo{publicstaticvoidmain(String[] args){String query ="select empid, firstname, lastname, dob from tblemployee";Connection conn =null;Statement stmt =null;try{Class.forName("com.mysql.jdbc.Driver");
conn =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb","root","root");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);ResultSet rs = stmt.executeQuery(query);System.out.println("All the rows of table=>");while(rs.next()){// Go to next row by calling next() methoddisplayData(rs);}System.out.println("Now go directly to 2nd row=>");
rs.absolute(2);// Go directly to 2nd rowdisplayData(rs);System.out.println("Now go to Previous row=>");
rs.previous();// Go to 1st row which is previous of 2nd rowdisplayData(rs);
rs.close();}catch(Exception e){
e.printStackTrace();}finally{try{
stmt.close();
conn.close();}catch(Exception e){}}}publicstaticvoiddisplayData(ResultSet rs)throwsSQLException{System.out.println("empId:"+ rs.getInt(1));System.out.println("firstName:"+ rs.getString(2));System.out.println("lastName:"+ rs.getString(3));System.out.println("dob:"+ rs.getDate(4));System.out.println("");}}
Output:
All the rows of table=>
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11
empId:2
firstName:Josh
lastName:Martin
dob:1988-10-22
empId:3
firstName:Ricky
lastName:Smith
dob:1999-05-11Now go directly to2nd row=>
empId:2
firstName:Josh
lastName:Martin
dob:1988-10-22Now go toPrevious row=>
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11
int getInt(int columnIndex) throws SQLException: This method returns value of specified columnIndex as int.
long getLong(int columnIndex) throws SQLException: This method returns value of specified columnIndex as long
String getString(int columnIndex) throws SQLException: This method returns value of specified columnIndex as String
java.sql.Date getDate(int columnIndex) throws SQLException: This method returns value of specified columnIndex as java.sql.Date
int getInt(String columnLabel) throws SQLException: This method returns value of specified column name as int.
long getLong(String columnLabel) throws SQLException: This method returns value of specified column name as long.
String getString(String columnLabel) throws SQLException: This method returns the value of the specified column name as String.
java.sql.Date getDate(String columnLabel) throws SQLException: This method returns the value of the specified column name as java.sql.Date.
ResultSet contains getter methods that return other primitive datatypes like boolean, float and double. It also has methods to obtain array and binary data from the database.
void updateInt(int columnIndex, int x) throws SQLException: This method updates the value of specified column of current row with int value.
void updateLong(int columnIndex, long x) throws SQLException: This method updates the value of the specified column of the current row with long value.
void updateString(int columnIndex, String x) throws SQLException: This method updates the value of the specified column of the current row with a String value.
void updateDate(int columnIndex, java.sql.Date x) throws SQLException: This method updates the value of specified column of current row with java.sql.Date value.
void updateInt(String columnLabel, int x) throws SQLException: This method updates the value of the specified column label of the current row with int value.
void updateLong(String columnLabel, long x) throws SQLException: This method updates the value of the specified column label of the current row with long value.
void updateString(String columnLabel, String x) throws SQLException: This method updates the value of the specified column label of the current row with a String value.
void updateDate(String columnLabel, java.sql.Date x) throws SQLException: This method updates the value of specified columnLabel of current row with java.sql.Date value.
Note: Setter/Updater Methods doesn’t directly update database values. Database values will be inserted/updated after calling the insertRow or updateRow method.
packagecom.journaldev.examples;importjava.sql.*;/**
* Java Resultset Example using updater methods.
*
* @author pankaj
*
*/publicclassResultSetUpdateDemo{publicstaticvoidmain(String[] args){String query ="select empid, firstname, lastname, dob from tblemployee";Connection conn =null;Statement stmt =null;try{Class.forName("com.mysql.jdbc.Driver");
conn =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/empdb","root","root");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);ResultSet rs = stmt.executeQuery(query);System.out.println("Now go directly to 2nd row for Update");if(rs.absolute(2)){// Go directly to 2nd rowSystem.out.println("Existing Name:"+ rs.getString("firstName"));
rs.updateString("firstname","Tyson");
rs.updateRow();}
rs.beforeFirst();// go to startSystem.out.println("All the rows of table=>");while(rs.next()){// Go to next row by calling next() methoddisplayData(rs);}
rs.close();}catch(Exception e){
e.printStackTrace();}finally{try{
stmt.close();
conn.close();}catch(Exception e){}}}publicstaticvoiddisplayData(ResultSet rs)throwsSQLException{System.out.println("empId:"+ rs.getInt(1));System.out.println("firstName:"+ rs.getString(2));System.out.println("lastName:"+ rs.getString(3));System.out.println("dob:"+ rs.getDate(4));System.out.println("");}}
Output:
Now go directly to2nd row forUpdateExistingName:JoshAll the rows of table=>
empId:1
firstName:Mike
lastName:Davis
dob:1998-11-11
empId:2
firstName:Tyson
lastName:Martin
dob:1988-10-22
empId:3
firstName:Ricky
lastName:Smith
dob:1999-05-11
void close() throws SQLException**:** This method frees up resources associated with ResultSet Instance. It must be called otherwise it will result in resource leakage.
ResultSetMetaData getMetaData() throws SQLException: This method returns ResultSetMetaData instance. It gives information about the type and property of columns of the query output.
While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.
I am interested Java but my problem money please help sir
- Prem Kumar