ResultSet is an interface present in java.sql package and provided under JDBC core API. The java.sql. ResultSet interface is defined to describe an object called ResultSet object, implemented by third-party vendor as a part of JDBC driver.
The ResultSet object represent the data in a tabular form that is retrieved using SQL query. It implies that a ResultSet object represents a data in a table whose cursor is returned by executing an SQL query. We can obtain a ResultSet by using the executeQuery() or getResultSet() method of JDBC Statement object.
Only a single ResultSet object can be opened at a time, however we can obtain multiple ResultSet objects by using one statement. When we try to open a ResultSet using a statement that is already associated with an open ResultSet, the existing ResultSet is implicitly closed. A ResultSet is also closed when the JDBC statement used to generate it is closed.
We’ll be covering the following topics in this tutorial:
Methods of ResultSet
The java.sql.ResultSet interface provides certain methods to work with the ResultSet object.
• public boolean absolute(int row)
This method is used to move the cursor to the given number of rows in a ResultSet object.
• public void afterLast()
This method is used to move the cursor just after the last row in ResultSet object.
• public void beforeFirst()
This method is used to move the cursor before the first row in ResultSet object.
• getXXX()
XXX=int, long, byte, character, String, double or any large object type. This method retrieves the column value of the specified types from the current row. The type can be any of the Java predefined data type such as int, long, byte etc.
• public ResultSetMetadata getMetaData()
This method retrieves the number, type and properties of the ResultSet object.
• public boolean first()
Move the cursor to the first row in a ResultSet object.
• public boolean last()
This methods move the cursor to the last
• public int getRow()
This method gives the current row number associated with the ResultSet object.
• public void beforeFirst()
This method moves the cursor before first row.
• public void previous()
This method moves cursor backward one row. It returns true if the cursor is newly positioned on a row and false if the cursor is positioned before the first row.
Using ResultSet
After obtaining a ResultSet object, you can use it to read the data encapsulated in it, that is the data stored in the ResultSet.
You can retrieve data from a ResultSet in two simple steps:
• Move the cursor position to the required row.
• Read the column data using the getxxx() methods.
Moving the Cursor Position
When you obtain a ResultSet, initially the cursor is positioned before the first row, that is beforeFirst, you use the next() method of ResultSet to move the cursor positioned to the next record in ResultSet.
Reading the Column Value
After moving the cursor to the respective row, you can use the getter method of ResultSet to retrieve the data from the row where the cursor is positioned. The methods of ResultSet are overloaded to support the navigation of column by using its index or name.
The ResultSet object supports two versions for each of the JDBC type. One of these two indexes takes column index of int type, where column index start with 1 and other takes column name of java .lang. String type. Column name passed into getter method are not case sensitive. If a select list contains same column more than the first instance of column returns.
By default Result Set is non scrollable. it means the cursor can be moved in forward direction only. if we want to move the cursor randomly ,that is ,in forward and in backward direction, then we need a scrollable result set
Example: Retrieving all the row from table (Using Non scrollable ResultSet)We have to retrieve all records from emp table.
Empno | Ename | Sal |
7000 | smith | 7000 |
7005 | raj | 8000 |
7009 | ravi | 9000 |
import java.sql.*;
public class AllRow
{
public static void main (String arg[ ])throws Exception
{
//load the type-4 driver
Class.forName (“oracle.jdbc.driver.OracleDriver”) ;
//get connection
Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”,”system”,”manager”);
//create CallableStatement object
Statement st=cn.createStatement();
//execute sgl query
ResultSet rs=st.executeQuery(“Select * from emp”);
//print the result
while(rs.next())
{
int empno =rs.getInt(1);
String name=rs.getString(2);
int sal = rs.getInt(3);
System.out.println(“empno” +empno+”ename”+name+”sal”+sal);
}
rs.close();
st.close();
cn.close();
}
}
Output
empno 7000 ename Smith sal 7000
empno 7005 ename Raj sal 8000
empno 7009 ename Ravi sal 9000
Example:2 Scrollable ResultSet
Empno | Ename | Sal |
7000 | smith | 7000 |
7005 | raj | 8000 |
7009 | ravi | 9000 |
import java.sql.*;
public class ScrollableTest
{
public static void main (String args[]) throws Exception
{
//load the type-4 driver
Class.forName(“oracle.jdbc.driver.oracleDriver”);
//getconnection
Connection cn=DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:XE”,”system”,”manager”);
//create Statement object
Statement st=cn.createStatement(1004,1007);
//execute sql query
ResultSet rs=st.executeQuery(“Select * from emp”);
rs.afterLast(); //Move the cursor after the last row
//print the result
while(rs.previous()) //Move in backward direction
{
System.out.println(rs.getInt(1)+”\t”+rs.getString(2)+”\t”+rs.getInt(3));
}
rs.close();
st.close();
cn.close();
}
}
Output:
7009 Ravi 9000
7005 Raj 8000
7000 Smith 7000
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular