Java ResultSet Tutorial With Examples

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.

Java ResultSet Hierarchy

Java ResultSet Class HierarchyJava ResultSet Class HierarchyJava ResultSet Class Hierarchy

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.

ResultSet Example

We will be using MySQL for our example purpose. Use below DB script to create a database and table along with some records.

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.

Output:

Explanation:

  • 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.

ResultSet Types & Concurrency

We can specify type and concurrency of  ResultSet while creating an instance of Statement, PreparedStatement or CallableStatement.

statement.createStatement(int resultSetType, int resultSetConcurrency)

ResultSet Types

1) Forward Only (ResultSet.TYPE_FORWARD_ONLY)

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.

2) Scroll Insensitive (ResultSet.TYPE_SCROLL_INSENSITIVE)

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.

3) Scroll Sensitive (ResultSet.TYPE_SCROLL_SENSITIVE)

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.

ResultSet Concurrency

1) Read Only (ResultSet.CONCUR_READ_ONLY)

It is the default concurrency model.  We can only perform Read-Only operations on ResultSet Instance. No update Operations are allowed.

2) Updatable (ResultSet.CONCUR_UPDATABLE)

In this case, we can perform update operations on ResultSet instance.

ResultSet Methods

We can divide ResultSet methods into the following categories.

  • Navigational Methods
  • Getter/Reader Methods
  • Setter/Updater Methods
  • Miscellaneous Methods – close() and getMetaData()

1. ResultSet Navigational Methods

  • 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.

Output:

2. ResultSet Getter/Reader Methods

  • 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.

3. ResultSet Setter/Updater Methods

  • 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.

Output:

4. ResultSet Miscellaneous Methods

  • 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.

Reference: Java doc

By admin

Leave a Reply

%d bloggers like this: