Home >>Java JDBC Tutorial >JDBC Result Sets

JDBC Result Sets

JDBC - Result Sets

This tutorial is basically about the JDBC Result sets.The main concept of the result sets is that the SQL statements that generally read the data from a database query is known to return the data in a result set. The standard way to select rows from a database and view them in a result set is known as the SELECT statement. The java.sql.ResultSet interface is usually used to represent the result set of a database query.

A ResultSet object in the JDBC is known to maintain a cursor that generally points towards the current row in the result set. In simple terms the "result set" generally refers to the row and column data that is possessed by a ResultSet object.

The methods of the ResultSet interface are numerous but they can be simply broken into three categories that have been depicted below:
  • Navigational methods : This method is basically used in order to move the cursor around.
  • Get methods : This method is basically used in order to view the data in the columns of the current row that is being pointed by the cursor.
  • Update methods : This method is basically used in order to update the data in the columns of the current row. The updates that are made can be then updated in the underlying database as well.

The cursor is generally known to be movable based on the properties of the ResultSet. These properties are basically designated whenever the corresponding Statement that is being generated, the ResultSet is created.

JDBC is known to deliver the following depicted connection methods in order to create statements with the desired ResultSet:
  • createStatement(int RSType, int RSConcurrency);
  • prepareStatement(String SQL, int RSType, int RSConcurrency);
  • prepareCall(String sql, int RSType, int RSConcurrency);

The first argument that has been mentioned is basically used to indicate the type of a ResultSet object then the second argument is basically the one of two ResultSet constants that is used for specifying whether a result set is read-only or updatable.

Type of Result Set

In case the programmer does not specify any ResultSet type then the programmer will automatically get one that is TYPE_FORWARD_ONLY.

Here are the possible RSType are mentioned below:

Type Description
ResultSet.TYPE_FORWARD_ONLY In this type the cursor can basically move forward in the result set only.
ResultSet.TYPE_SCROLL_INSENSITIVE In this type, the cursor can basically scroll forward and backward and the best part is that the result that is set is not sensitive to the changes that are made by the others to the database that happens after the result set was even created.
ResultSet.TYPE_SCROLL_SENSITIVE. In this type, the cursor can basically scroll only forward and backward and the thing that is concerning here is that the result set is sensitive to the changes that have been made by the others to the database that generally occur after the result set was created.

Concurrency of ResultSet

In case the programmer does not specify any Concurrency type then the programmer will automatically get one that is CONCUR_READ_ONLY. Here are the possible RSConcurrency are mentioned below:

Concurrency Description
ResultSet.CONCUR_READ_ONLY This type generally creates a read-only result set. In general cases this is taken as the default.
ResultSet.CONCUR_UPDATABLE This is used in order to create an updateable result set.
Here is an example

try {
   Statement stmt = conn.createStatement
   (
    ResultSet.TYPE_FORWARD_ONLY,
    ResultSet.CONCUR_READ_ONLY
	);
}
catch(Exception ex) {
   ....
}
finally {
   ....
}

Navigating a Result Set

There are numerous methods in the ResultSet interface that is known to involve moving of the cursor that are depicted below along with a brief description:

S.N. Methods & Description
1 public void beforeFirst() throws SQLException
This method is generally used to move the cursor just before the first row.
2 public void afterLast() throws SQLException
This method is generally used to move the cursor just after the last row.
3 public boolean first() throws SQLException
This method is basically used to move the cursor to the first row.
4 public void last() throws SQLException
This method is generally used to move the cursor to the last row.
5 public boolean absolute(int row) throws SQLException
This is the method that is being used in order to move the cursor to the specified row.
6 public boolean relative(int row) throws SQLException
This method is generally used to move the cursor to the given number of rows forward or backward from its reference point that simply means where it is currently pointing.
7 public boolean previous() throws SQLException
This method is generally used to move the cursor to the previous row. This method is known to return false in case the previous row is off the result set.
8 public boolean next() throws SQLException
This method is generally used to move the cursor to the next row. This method is also known to return false in case there are no more rows in the result set.
9 public int getRow() throws SQLException
This method is generally used to return the row number that the cursor is pointing to.
10 public void moveToInsertRow() throws SQLException
This method is generally used to move the cursor to a special row in the result set that is basically used in order to insert a new row into the database. The current location of the cursor is always remembered.
11 public void moveToCurrentRow() throws SQLException
This method is generally used to move the cursor back to the current row in case the cursor is currently at the insert row; or else this method is designed to do nothing.

Viewing a Result Set

The ResultSet interface in the JDBC is generally known to contain dozens of methods for getting the data of the current row.

There is basically a get method for each of the data types that are possible and each of the get method generally has two versions that are depicted below:

  • One version is that takes in a column name.
  • One version is that takes in a column index.

For instance, in case the column in which the user is interested in viewing is containing an int, then the programmer will need to use one of the getInt() methods of ResultSet that are explained below:

S.N. Methods & Description
1 public int getInt(String yourcolumnName) throws SQLException
This method is generally used in order to return the int that is in the current row in the column bearing a name, columnName.
2 public int getInt(int yourcolumnIndex) throws SQLException
This method is generally used in order to return the int that is in the current row that is basically present in the specified column index. The column index usually starts at 1 that simply means that the first column of a row is 1 and the second column of a row is 2, and the circle goes on and on.

Correspondingly, there are get methods that are present in the ResultSet interface for each of the eight Java primitive types and they are also known to be present for the common types like java.lang.Object, java.lang.String, and java.net.URL.

Apart from that there are also some methods that are in order to get the SQL data types like java.sql.Time, java.sql.TimeStamp, java.sql.Date, java.sql.Clob, and the java.sql.Blob. It is recommended to check the documentation in order to get more information about the use of these SQL data types.

Updating a Result Set

The ResultSet interface is generally known to consist of a collection of update methods that are used for updating the data of a result set.

Likewise of the get methods, there are also the two update methods for each of the data types that are mentioned below:

  • One version is that takes in a column name.
  • One version is that takes in a column index.

For instance, in order to update a String column of the current row of a result set the user have to use one of the following updateString() methods that are described below along with a brief description:

S.N. Methods & Description
1 public void updateString(int tablecolumnIndex, String s) throws SQLException
This method is generally used to make the changes in the String that has been specified in the column to the value of s.
2 public void updateString(String tablecolumnName, String s) throws SQLException
This method is basically correspondingly to the previous method just it has a twist that the column is generally specified by its very name rather than of its index.

In JDBC, there are generally update methods for each of the eight primitive data types and these methods also imply on String, Object, URL, and the SQL data types that are present in the java.sql package.

Updating a row in the result set means that it will definitely change the columns of the current row in the ResultSet object but this will not make any changes in the underlying database. In order to update the changes that have been made by the programmer to the row in the database, the programmer will need to invoke one of the below mentioned methods:

S.N. Methods & Description
1 public void updateRow()
This method is generally used in order to update the current row just by updating the corresponding row in the database.
2 public void deleteRow()
This method is generally used in order to delete the current row from the database
3 public void refreshRow()
This method is generally used in order to refresh the data that is present in the result set to reflect any recent changes that have been made in the database.
4 public void cancelRowUpdates()
This method is generally used in order to cancel any updates that have been made on the current row.
5 public void insertRow()
This method is generally used in order to insert a row into the database. This method has the restriction that it can only be invoked when the cursor is pointing towards the insert row.