JDBC ResultSet: How To Use Java ResultSet To Retrieve Data

This tutorial explains how to use JDBC ResultSet to retrieve data. We will also learn about ResultSetMetaData and DatabaseMetaData interfaces with examples:

In the JDBC DriverManager tutorial of the JDBC tutorial series, we learned how to use JDBC DriverManager and its methods, JDBC PreparedStatement in Java applications.

In this tutorial, we will discuss the remaining interfaces in JDBC. We have covered Statement, PreparedStatement, and CallableStatement interfaces in our previous tutorials.

Here, we will learn about JDBC ResultSet, ResultSetMetaData, and DatabaseMetaData interfaces, their methods and how to use the methods in Java program.

JDBC ResultSet Interface

ResultSet Interface is present in the java.sql package. It is used to store the data which are returned from the database table after the execution of the SQL statements in the Java Program. The object of ResultSet maintains cursor point at the result data. In default, the cursor positions before the first row of the result data.

The next() method is used to move the cursor to the next position in a forward direction. It will return FALSE if there are no more records. It retrieves data by calling the executeQuery() method using any of the statement objects. It may be Statement or PreparedStatement or CallableStatement object. PreparedStatement, and CallableStatement interfaces are the sub-interfaces of the Statement interface.

Statement Interface

Statement statemnt1 = conn.createStatement();
ResultSet rs1 = statemnt1.executeQuery(“Select * from EMPLOYEE_DETAILS”);

PreparedStatement Interface

PreparedStatement pstatemnt1 = conn.prepareStatement(insert_query);
ResultSet rs1 = pstatemnt1.executeQuery(“Select * from EMPLOYEE_DETAILS”);

We can use getX() method to get the data of the columns while iterating through the results where X – is the datatype of the column. We can use either Column Names or Index to get the values using getX() methods.

while(rs1.next())
{
	int empNum = rs1.getInt("empNum");
	String lastName = rs1.getString("lastName");
	String firstName = rs1.getString("firstName");
	String email = rs1.getString("email");
	String deptNum = rs1.getString("deptNum");
	String salary = rs1.getString("salary");
	System.out.println(empNum + "," +lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
}

We can also mention index number of the Column instead of Column Name in the getX() methods.

while(rs1.next())
{
	int empNum = rs1.getInt(1);
	String lastName = rs1.getString(2);
	String firstName = rs1.getString(3);
	String email = rs1.getString(4);
	String deptNum = rs1.getString(5);
	String salary = rs1.getString(6);
	System.out.println(empNum + "," +lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary);
}

ResultSet Types

In default, we can iterate the data/values in ResultSet which have returned as an output of the executed SQL statement in the forward direction. We can iterate the values in other directions using Scrollable ResultSet. We can specify the type and concurrency of ResultSet while creating Statement, PreparedStatement, and CallableStatement objects.

There are 3 types in ResultSet. They are:

1. TYPE_FORWARD_ONLY: It is the default option, where the cursor moves from start to end i.e. in the forward direction.
2. TYPE_SCROLL_INSENSITIVE: In this type, it will make the cursor to move in both forward and backward directions. If we make any changes in the data while iterating the stored data it won’t update in the dataset if anyone changes the data in DB. Because the dataset has the data from the time the SQL query returns the Data.
3. TYPE_SCROLL_SENSITIVE: It is similar to TYPE_SCROLL_INSENSITIVE, the difference is if anyone updates the data after the SQL Query has returned the data, while iterating it will reflect the changes to the dataset.

ResultSet Concurrency

There are 2 modes of Concurrency in ResultSet. They are:

1. ResultSet.CONCUR_READ_ONLY: It is the default concurrency mode. We can only read the data in the ResultSet. Updation is not applicable.
2. ResultSet.CONCUR_UPDATABLE: We can update the data in the ResultSet object.

Some databases don’t support concurrency mode for all ResultSet types. In that case, we need to check whether they support our desired type and concurrency mode using supportsResultSetConcurrency() method.

Methods In ResultSet Interface

There are 4 categories of ResultSet methods. They are:

1. Navigational Methods
2. Getter Methods
3. Setter Methods
4. Miscellaneous Methods

First, we will discuss the Navigational Methods and then will move further.

#1) Navigational Methods

This method is used to move the cursor around the dataset.

• Boolean absolute(int row): It is used to move the cursor to the specified row which is mentioned in the parameter and return true if the operation is successful else return false.
• Void afterLast(): It makes the ResultSet cursor to move after the last row.
• Void beforeFirst(): It makes the ResultSet cursor to move before the first row.
• Boolean first(): It makes the ResultSet cursor to move to the first row. It returns True if the operation is successful else False.
• Boolean last(): It makes the ResultSet cursor to move to the last row. It returns True if the operation is successful else False.
• Boolean next(): It makes the ResultSet cursor to move to the next row. It returns True if there are more records and False if there are no more records.
• Boolean previous(): It makes the ResultSet cursor to move to the previous row. It returns True if the operation is successful else False.
• Boolean relative(): It moves the cursor to the given number of rows either in the forward or backward direction.
• Int getRow(): It returns the current row number the ResultSet object is pointing now.
• Void moveToCurrentRow(): It moves the cursor back to the current row if it is currently in insert row.
• Void moveToInsertRow(): It moves the cursor to the specific row to insert the row into the Database. It remembers the current cursor location. So we can use the moveToCurrentRow() method to move the cursor to the current row after the insertion.

In this tutorial, all programs are written in Java. We have used Java 8 version and Oracle DB.

>>You can download the Oracle software from here

>>You can download the Java version 8 from here

It has the step-by-step Java installation process.

JDBC ResultSet Example Program:(Using Navigational methods)

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
	
public class ResultSet_Example {	

         public static void main(String[] args) throws ClassNotFoundException {
		// TODO Auto-generated method stub
		//Select query 
		String select_query = "select * from employee_details";
		
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Connecting to Oracle DB
		try(Connection conn =
 DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:X
E"))
		{
				//Creating DatabaseMetaData object
				DatabaseMetaData dbmd = conn.getMetaData();
				//Checking whether the driver supports scroll 
sensitive type and concur updatable
				boolean isSupportResultSetType = 
dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_SENSITIVE,
 ResultSet.CONCUR_UPDATABLE);
				if(isSupportResultSetType == true)
				{
				// Creating prepared Statement
				PreparedStatement pstatemnt1 = 
conn.prepareStatement(select_query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.
CONCUR_UPDATABLE);
				ResultSet rs = pstatemnt1.executeQuery();
				//Moving the cursor to point first row
				rs.first();
				System.out.println("FIRST ROW \n EMP NUM = " + 
rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point last row		
		
                                rs.last();
				System.out.println("LAST ROW \n EMP NUM = " +
 rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point before first row
				
                                rs.beforeFirst();
				System.out.println("Cursor is pointing at before the
 first row. Use next() to move in forward direction");
				//Moving the cursor to point first row using next()				
                                
                                rs.next();
				System.out.println("FIRST ROW \n EMP NUM = " +
 rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point after last row	

          			rs.afterLast();
				System.out.println("Cursor is pointing at after the last row. Use previous() to move in backward direction");
				//Moving the cursor to point last row  using previous()	

         			rs.previous();
				System.out.println("LAST ROW \n EMP NUM = " + 
rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point third row				

                               rs.absolute(3);
				System.out.println("Cursor is pointing at 3rd row");
				System.out.println("THIRD ROW \n EMP NUM = " +
 rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point previous row of third row				

                               rs.relative(-1);
				System.out.println("Cursor is pointing to the 1 row previous to the 3rd row");
				System.out.println("Second ROW \n EMP NUM = " + rs.getInt("empNum") + "\n LAST NAME = "+
 rs.getString(2)+"\n FIRST NAME = " + 
rs.getString(3));
				//Moving the cursor to point 4th row after the 2nd row				

                                rs.relative(4);
				System.out.println("Cursor is pointing to the 4th row after the 2nd row");
				System.out.println("SIXTH ROW \n EMP NUM = " +
 rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point current row				

                                System.out.println(" Current Row = " + rs.getRow());
				
				}
				
		}
		
		catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

OUTPUT:

Data in Employee_details table

Explanation:

In the above program we have implemented the first(), last(), beforeFirst(), afterLast(), next(), previous(), absolute(), relative() and getRow() methods in ResultSet. To use these methods we set ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE values in the prepareStatement method.

Next, we will discuss what are the Getter Methods in ResultSet:

#2) Getter Methods

ResultSet has stored the data of the table from the Database. Getter methods are used to get the values of the table in ResultSet. For that, we need to pass either column Index value or Column Name.

The following are the getter methods in ResultSet:

• int getInt(int ColumnIndex): It is used to get the value of the specified column Index as an int data type.
• float getFloat(int ColumnIndex): It is used to get the value of the specified column Index as a float data type.
• java.sql.date getDate(int ColumnIndex): It is used to get the value of the specified column Index as a date value.
• int getInt(String ColumnName): It is used to get the value of the specified column as an int data type.
• float getFloat(String ColumnName): It is used to get the value of the specified column as a float data type.
• Java.sql.date getDate(String ColumnName): It is used to get the value of the specified column as a date value.

There are getter methods for all primitive data types (Boolean, long, double) and String also in ResultSet interface. We can obtain an array and binary type of data also from the Database. It has methods also for that.

#3) Setter/Updater Methods

We can update the value in the Database using ResultSet Updater methods. It is similar to Getter methods, but here we need to pass the values/ data for the particular column to update in the Database.

The following are the updater methods in ResultSet:

• void updateInt(int ColumnIndex, int Value): It is used to update the value of the specified column Index with an int value.
• void updateFloat(int ColumnIndex, float f): It is used to update the value of the specified column Index with the float value.
• void updateDate(int ColumnIndex, Date d): It is used to update the value of the specified column Index with the date value.
• void updateInt(String ColumnName, int Value): It is used to update the value of the specified column with the given int value.
• void updateFloat(String ColumnName, float f): It is used to update the value of the specified column with the given float value.
• Java.sql.date getDate(String ColumnName): It is used to update the value of the specified column with the given date value.

There are Updater methods for all primitive data types (Boolean, long, double) and String also in ResultSet interface.

Updater methods just update the data in the ResultSet object. Values will be updated in DB after calling the insertRow or updateRow method.

Updating a Row:

We can update the data in a row by calling updateX() methods, passing the column name or index, and values to update. We can use any data type in place of X in the updateX method. Till now, we have updated the data in the ResultSet object. To update the data in DB, we have to call the updateRow() method.

Inserting a Row:

We need to use moveToInsertRow() to move the cursor to insert a new row. We have already covered this in the Navigation methods section. Next, we need to call updateX() method to add the data to the row. We should provide data for all the columns else it will use the default value of that particular column.

After updating the data, we need to call the insertRow() method. Then use the moveToCurrentRow() method, to take the cursor position back to the row we were at before we started inserting a new row.

ResultSet Example:

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
	
public class ResultSet_Example1 {	

       public static void main(String[] args) throws ClassNotFoundException {
		// TODO Auto-generated method stub
		String select_query = "select empnum,lastName,firstName from employee_details";
		String insert_query = "insert into employee_details values(?,?,?,?,?,?)";
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		//Connecting to Oracle DB
		try(Connection conn = 
DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				//Creating DatabaseMetaData object
				DatabaseMetaData dbmd = conn.getMetaData();
				//Checking whether the driver supports scroll insensitive type and concur updatable
				boolean isSupportResultSetType = 
dbmd.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE, 
ResultSet.CONCUR_UPDATABLE);
				if(isSupportResultSetType == true)
				{
				// Creating prepared Statement
					PreparedStatement pstatemnt1 = 
conn.prepareStatement(select_query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
				ResultSet rs = pstatemnt1.executeQuery(select_query);
				//Moving the cursor to point last row of the table
				rs.last();
				System.out.println("LAST ROW: Before inserting new Employee");
				System.out.println("LAST ROW: EMPNUM = " + rs.getInt(1));
				System.out.println("\n EMP NUM = " + rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				// Setting the values to insert in the EMPLOYEE_DETAILS Table
                              //Moving the cursor to point insert a row to table
				rs.moveToInsertRow();
				//Update EMPNUM value
				rs.updateInt(1, 1017);
				//Update LAST NAME value
				rs.updateString(2, "Bond");
				//Update FIRST NAME value
				rs.updateString(3, "James");
				//Insert a new row
				rs.insertRow();
					
				//Moving the cursor to point 5th row
				rs.absolute(5);
				System.out.println("Befor Updating EMPNUM of the 5th ROW");
				System.out.println("\n EMP NUM = " + rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + 
rs.getString(3));
				
				System.out.println(" Updating EMP id of the 5th EMPLOYEE");
			      //Updating EMPNUM of 5th row
				rs.updateInt(1,3005);
				rs.updateRow();
				System.out.println("\n EMP NUM = " + rs.getInt("empNum") + "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				//Moving the cursor to point last row
				rs.last();
				System.out.println("LAST ROW: EMPNUM = " + rs.getInt(1));
				System.out.println("\n EMP NUM = " + rs.getInt("empNum") +
 "\n LAST NAME = "+ rs.getString(2)+"\n FIRST NAME = " + rs.getString(3));
				
				
				}
				
		}
		
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

OUTPUT:

Explanation:

In the above program what we have done is first, we stored the data of the Employee_details table in the ResultSet object using the SELECT query. Then, we displayed the data of the last row in the employee_details table using the last() method of ResultSet. moveToInsertRow() method makes the cursor to point the current row, now the current row is the last row.

updateXXX()methods used to update the values to the row and insertRow() method has inserted the data in a new row. Using absolute() method, we made the cursor to point to the 5^th row. UpdateInt() method has been used to update the EMPNUM with a new id of the 5^th employee in the table. After that, displayed the data to check whether the EMPNUM is updated or not.

Made the cursor to point the last row of the table using last() and displayed it. To perform the above logic, we need to set ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE values in the prepareStatement method.

#4) Miscellaneous Methods

• void close(): It is used to close the ResultSet instance and free up the resources associated with ResultSet instance.
• ResultSetMetaData getMetaData(): It returns the ResultSetMetaData Instance. It has the information about the type and property of columns of the query output. We will learn more about ResultSetMetaData in the next section.

ResultSetMetaData

What is Metadata?

Metadata means data about data. Using this interface, we will get more information about ResultSet. It is available in the java.sql package. Every ResultSet object is associated with one ResultSetMetaData object.

This object will have the details of the properties of the columns like datatype of the column, column name, number of columns, table name, schema name, etc., We can get the ResultSetMetaData object using the getMetaData() method of ResultSet.

Syntax of the ResultSetMetaData:

PreparedStatement pstatemnt1 = conn.prepareStatement(insert_query);
ResultSet rs1 = pstatemnt1.executeQuery(“Select * from EMPLOYEE_DETAILS”);
ResultSetMetaData rsmd = rs.getMetaData();

Important methods of ResultSetMetaData interface:

ResultSetMetaData Example

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
	
public class ResultSetMetaData_Example {	

       public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		String QUERY= " select * from employee_details";
		Class.forName("oracle.jdbc.driver.OracleDriver");  
		try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
		{
				Statement statemnt1 = conn.createStatement();
				ResultSet rs1 =null;
				rs1 = statemnt1.executeQuery(QUERY); 
				ResultSetMetaData rsmd = rs1.getMetaData();
				System.out.println(" We are using ResultSetMetaData ");
				System.out.println("No: of Columns: "+ rsmd.getColumnCount());
				System.out.println("ColumnName of  Column 1: "+ rsmd.getColumnName(1));
				System.out.println("Data Type of Column 2: " + rsmd.getColumnTypeName(2));
				System.out.println("Table Name of the Column 1: " + rsmd.getTableName(1));
				System.out.println("Schema Name of the Column 1: " + rsmd.getSchemaName(1));
				
	}
}
}

OUTPUT:

Explanation:

In the above program, we have implemented getColumnCount(),getColumnName(), getColumnTypeName(), getTableName() and getSchemaName() methods in the ResultSetMetaData interface.

DatabaseMetaData

The DatabaseMetaData interface gives information about the Database like DatabaseName, Database version, and so on.

Important methods of DatabaseMetaData interface:

Here, we listed some important methods of the DatabaseMetaData interface. You can refer to the official site of the Oracle where you can see all methods available in the DatabaseMetaData interface.

DatabaseMetaData Example:

package com.STH.JDBC;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
	
public class DatabaseMetaData_Example {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
			Class.forName("oracle.jdbc.driver.OracleDriver");  
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE");
		
		DatabaseMetaData dbmd = conn.getMetaData();
		System.out.println("Using DatabaseMetaData");
	    System.out.println("Driver Name: " + dbmd.getDriverName());
	    System.out.println("Driver Version: "+ dbmd.getDriverVersion());
	    System.out.println("UserName of the Database: " + dbmd.getUserName());
	    System.out.println("Database Product Name:" + dbmd.getDatabaseProductName());
	    System.out.println("Database Product Version: " + dbmd.getDatabaseProductVersion());
	    System.out.println("List of String Functions in the Database: " + dbmd.getStringFunctions());
	    System.out.println("List of Time & Date functions in the Database: " + dbmd.getTimeDateFunctions());
	    System.out.println("URL of the Database: " + dbmd.getURL());
	    System.out.println("Database is read - only? " +dbmd.isReadOnly());
	    System.out.println("Support Batch Updates? " + dbmd.supportsBatchUpdates());
	    System.out.println("Support savepoints? " + dbmd.supportsSavepoints());
	    System.out.println("Support Statement Pooling? "+ dbmd.supportsStatementPooling());
	    System.out.println("Support Stored Procedures? " + dbmd.supportsStoredProcedures());
	    System.out.println("Support Outer Join? "+ dbmd.supportsOuterJoins());
}
}

OUTPUT:

Explanation:

In the above program, we have used/ implemented the getDriverName(), getDriverVersion(), getUserName(), getDatabaseProductName(), getDatabaseProductVersion(), getStringFunctions(), getTimeDateFunctions(), getURL(), isReadOnly(), supportsBatchUpdates(), supportsStatementPooling(), supportsSavepoints(), supportsStoredProcedures() and supportsOuterJoins() methods in DatabaseMetaData Interface.

Points to be noted:

• JDBC ResultSet interface is used to store the data from the database and use it in our Java Program.
• We can also use ResultSet to update the data using updateXXX() methods.
• ResultSet object points the cursor at before the first row of the result data. Using the next() method, we can iterate through the ResultSet.
• We have navigational methods of ResultSet to move further in the ResultSet object
• ResultMetaData is used to get more information about the ResultSet like column name, number of columns, the datatype of the column, etc.
• DatabaseMetData is used to get the information about the database which we have connected

Frequently Asked Questions

Q #1) What is the use of ResultSet?

Answer: ResultSet is used to store and retrieve the data from DB. When executeQuery() method has executed, it will return ResultSet object. We can use that ResultSet object in our program to perform the logic.

Q #2) How to check whether the ResultSet is empty or not?

Answer: There are no predefined methods like length(), size() available to check the IsResultSet Empty. We can use the next() method to iterate and if it returns True, then it is not empty, if it returns False means the ResultSet is empty.

Q #3) Is it possible that ResultSet may be null?

Answer: No, executeQuery() method returns the ResultSet object that may never be null.

Q #4) What is updatable ResultSet?

Answer: An updatable ResultSet object is used to update the data in the column, insert data in columns and delete rows. To make a ResultSet as an updatable one, we need to make scroll type as sensitive or insensitive and CONCUR type as updatable.

ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE.

Q #5) How to get the Database name that has been connected?

Answer: We can use getDatabaseProductName() method of DatabaseMetaData object.

Conclusion

In this tutorial, we have discussed what are the ResultSet, ResultSetMetaData, and DatabaseMetaData interfaces and their important methods commonly used in the JDBC programs. We have also seen how to update data in DB using ResultSet. ResultSetMetadata contains information about ResultSet such as Column Name, Column count, and so on.

DatabaseMetaData contains Database information.

<<PREV Tutorial | NEXT Tutorial>>