What is JDBC Result Set?

In this article, we will learn JDBC Result Set,This free Java tutorial for complete beginners will help you learn Java from scratch.

JDBC Result Set

JDBC - Type 1 Driver

CharsetDecoder Class in Java

Java Database Connectivity is Java-based technology and that provides a standard API for accessing databases in Java applications. The Key Component of Java Database Connectivity is the ResultSet. JDBC driver allows developers to read and manipulate data from the database.

The JDBC ResultSet is Object which represents the result of a SQL Query executed on a database. It acts as a cursor to navigate through the retrieved data, manipulate data, Fetching Specific columns and others. In this article, we will learn more about JDBC Result Set.

JDBC Result Set in Java

The ResultSet is essentially a table of data where each row represents a record and each column represents a field in the database. The ResultSet has a cursor that points to the current row in the ResultSet and we can able to navigate in ResultSet by using the next(), previous(), first(), and last() methods. We can retrieve data by using different methods like getString(), getInt(), getDouble() and other methods.

In Java, the ResultSet is the Object which is used for holding the result of a database query typically the SQL select statement. And It is the part of JDBC API which is used for interacting with relational databases. The ResultSet allows us over the rows of tables returned by the SQL query and extract a specific column from the SQL query result.

Syntax:

try {
      Connection conn = DriverManager.getConnection(url, username, password);
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM your_table");

      while (rs.next()) {
           // Process the result set
       }

      rs.close();
      stmt.close();
      conn.close();
} 
catch (SQLException e) {
      e.printStackTrace(); // Handle the exception
}

Common Operations with ResultSet:

Fetching data from database: We can fetch data from the database based on the requirements by using conditional statements.
Navigating the ResultSet: We can able navigating the ResultSet by using methods like next(), previous(), first(), and last().
Getting column values: We can fetch column values with specific conditions or without conditions.
Closing the result: Once database operations are completed we need close the connections related to database here we close the ResultSet connection. By using close method.

Types of ResultSet

There are three different characteristics by which ResultSet types are differentiated

Scrollability: Determines whether you can move back and forth in the ResultSet
- TYPE_FORWARD_ONLY: Can only move forward through the rows
- TYPE_SCROLL_INSENSITIVE: Can move forward and backward but changes are not reflect ResultSet
- TYPE_SCROLL_SENSITIVE: Can move forward and backward but changes are affect the ResultSet
Concurrency: Determines whether you can update the ResultSet
- CONCUR_READ_ONLY: Can only read data
- CONCUR_UPDATABLE: Allows updates to the ResultSet
Holdability: Determines what happens to the ResultSet when a Transaction is committed.
- HOLD_CURSORS_OVER_COMMIT: The ResultSet remains open after a commit
- CLOSE_CURSORS_AT_COMMIT: The ResultSet closes after a commit

Category of Methods in Result Set

We have different types of Methods are available based on their functionality below we listed them for you reference.

i). Navigating a ResultSet:

Basically these methods are allow is to navigating through the ResultSet and we can navigate in different ways, Below We provide those methods to navigate in the ResultSet.

Method	Description
next()	used for move next row in the ResultSet.
previous()	used for move to previous row in the ResultSet
first()	used for move to first row in the ResultSet
last()	used for move to last row in the ResultSet
absolute(int row)	used to move to specific row
relative(int rows)	used for Moves forward or backward by the specified number of rows
beforeFirst()	used for Positions the cursor before the first row
afterLast()	used for Positions the cursor after the last row

ii). Retrieving Data from a ResultSet:

These methods retrieve data from the current row in the ResultSet. And also You can retrieve data by column index or column name.

Method	Description
getInt(int columnIndex)	used for Retrieves an integer from the specified column
getString(int columnIndex)	used for Retrieves a string from the specified column
getDouble(int columnIndex)	used for Retrieves a double from the specified column
getBoolean(int columnIndex)	used for Retrieves true or false from the specified column
getDate(int columnIndex)	used for Retrieves a java.sql.Date
getObject(int columnIndex)	used for Retrieves any type of object
getArray(int columnIndex)	used for Retrieves a SQL array

iii). Updating Data in a ResultSet:

These methods allow you to update data in the Result.

Method	Description
updateInt(int columnIndex, int x)	used for Updates an integer value in the specified column
updateString(int columnIndex, String x)	used for Updates a string value
updateBoolean(int columnIndex, boolean x)	used for Updates a boolean value
updateRow()	used for Updates a row
deleteRow()	used for delete a row

Employees Table Structure:

We create a employees table in work database. Below we provide table structure

Program Implementing the JDBC Result Set

In this example we perform CRUD operations by using ResultSet. After running this program as java application It show four options to you. Need to select 1 to 4 based on your requirement below I provide the example with related images.

Java

package w3wiki;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class JDBCOperations {

  // Establish database connection
  private static Connection getConnection()
  throws Exception {
    
    String jdbcUrl = "jdbc:mysql://localhost:3306/data";
    String jdbcUser = "root";
    String jdbcPassword = "password";
    
    return DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
  }

  // Insert record into the database
  private static void insertRecord(Connection connection,
    String name, double salary)
  throws Exception {
    String query = "INSERT INTO employees (name, salary) VALUES (?, ?)";
    
    PreparedStatement preparedStatement
      = connection.prepareStatement(query);
    preparedStatement.setString(1, name);
    preparedStatement.setDouble(2, salary);
    preparedStatement.executeUpdate();
    
    System.out.println("Record inserted successfully.");
  }

  // Update record in the database
  private static void updateRecord(Connection connection,
    int id, String name,double salary)
  throws Exception {
    
    String query
      = "UPDATE employees SET name = ?, salary = ? WHERE id = ?";
    
    PreparedStatement preparedStatement
      = connection.prepareStatement(query);
    preparedStatement.setString(1, name);
    preparedStatement.setDouble(2, salary);
    preparedStatement.setInt(3, id);
    preparedStatement.executeUpdate();
    
    System.out.println("Record updated successfully.");
  }

  // Retrieve records from the database
  private static void
  retrieveRecords(Connection connection) throws Exception {
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");
    
    System.out.println("Records in the database:");
    while (resultSet.next()) {
      int id = resultSet.getInt("id");
      String name = resultSet.getString("name");
      double salary = resultSet.getDouble("salary");
      System.out.println("ID: " + id + ", Name: " + name +
        ", Salary: " + salary);
    
    }
  }

  // Delete record from the database
  private static void deleteRecord(Connection connection,
    int id)
  throws Exception {
    String query = "DELETE FROM employees WHERE id = ?";
    PreparedStatement preparedStatement
      = connection.prepareStatement(query);
    preparedStatement.setInt(1, id);
    preparedStatement.executeUpdate();
    System.out.println("Record deleted successfully.");
  }

  public static void main(String[] args) {
    Scanner scanner = new Scanner(System.in);
    try {
      Connection connection = getConnection();

      System.out.println("Select an operation:");
      System.out.println("1. Insert");
      System.out.println("2. Update");
      System.out.println("3. Retrieve");
      System.out.println("4. Delete");

      int choice = scanner.nextInt();

      switch (choice) {
      case 1:
        System.out.println("Enter name:");
        
        String nameToInsert = scanner.next();
        System.out.println("Enter salary:");
        double salaryToInsert = scanner.nextDouble();
        insertRecord(connection, nameToInsert,
          salaryToInsert);
        break;

      case 2:
        System.out.println("Enter ID to update:");
        int idToUpdate = scanner.nextInt();
        System.out.println("Enter new name:");
        String nameToUpdate = scanner.next();
        System.out.println("Enter new salary:");
        double salaryToUpdate
          = scanner.nextDouble();
        updateRecord(connection, idToUpdate,
          nameToUpdate, salaryToUpdate);
        break;

      case 3:
        retrieveRecords(connection);
        break;

      case 4:
        System.out.println("Enter ID to delete:");
        int idToDelete = scanner.nextInt();
        deleteRecord(connection, idToDelete);
        break;

      default:
        System.out.println("Invalid choice.");
        break;
      }

      // Close the connection at the end
      connection.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      scanner.close();
    }
  }
}

Explanation of the above Program:

Database Connection: Create Database connection by using configuration details like username, password, database name and other information. Below we provide the database connection java code.
Insert Record: Now we implement logic for inserting new records by using this connection object. And we use PreparedStatement for preventing SQL Injection and It will takes input data in the form placeholders. After that we execute the SQL query by using executeUpdate method from PreparedStatement.
Update Record: Now we implement logic for update function. We employees details based on their existing employee id only. It is same like inserting new record but before that we fetch existing data then only we can able to update the data.
Fetch All Records: Now we develop logic for fetching all records from the table by using ResultSet object. First we execute the SQL query then hold that result in ResultSet Object. Then we iterate that data and print line by line the entire data in the table.
Delete Record by Using ID: In this function we can delete an existing employee record by using employee ID from the Table.
Main Function: In main function of the class, We develop logic for selecting operation from the console by using switch statement in java. Based on the selection you can perform related database operation. Below we provide that entire code for your reference.