You are here:   HomeJava ProgrammingJDBCHow to Working With Resultset Interface
by Dinesh Thakur Category: JDBC

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.

                         Resultset Interface

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.

                     How to Working With Resultset Interface

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.

                            How to Working With Resultset Interface

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

                      How to Working With Resultset Interface

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



What's New and Popular