How to Retrieve Data from a ResultSet in JDBC?

In Java database connectivity, the ResultSet is an interface, and it has a lot of built-in methods for handling the results of SQL queries. In this article, we will learn to retrieve data from a ResultSet in JDBC.

ResultSet in Interface

ResultSet interface represents the result set of a database query by using SQL.

• When we use PreparedStatement for performing SQL query statements, it will return the PreparedStatement object.
• Then we call the executeQuery() by using the PreparedStatement object and this result is assigned to the ResultSet object.
• This ResultSet object contains the result of the SQL query.

Now we will explore this concept to understand this concept in a better way. In this article, we learn about ResultSet in JDBC and Its functionality and how to retrieve data from a ResultSet in JDBC.

Syntax:

The below statements is representing the syntax of ResultSet with PreparedStatement object.

PreparedStatement preparedStatement = connection.prepareStatement(sql_query);
ResultSet resultSet = preparedStatement.executeQuery();

We can observe syntax of ResultSet, which gets the ResultSet by using preparedStatement. This preparedStatement is used for executing the SQL queries.

ResultSet in JDBC

The ResultSet is an interface available in JDBC which is used for data handling by using result object of preparedStatement class.

Implementation of ResultSet

• First, we need create One class in our Java project.
• After that need to establish the connection with MySQL Database by using JDBC and We need one jar file that is mysql-connector.jar file.
• Once complete connection then create table in database with some data in it.
• Now, write the SQL query for retrieving all data from Table by using preparedStatement.
• After that preparedStatement object is call the executeQuery method for executing the SQL query once the query is successfully executed then we get result of data based this SQL query.
• Now this result is assigned to ResultSet object.

Implementation to retrieve data from a ResultSet in JDBC

Below we have provided one example for ResultSet interface and Its functionality.

For this example,

• We already created one database named books in this database.
• Then, created one table named book with some data.

In this example, by using ResultSet we will see the data which is already exist in books table.

Note: We have already the Book table in Books database, and we have inserted some rows in book table. Now we will use this table for retrieve data from ResultSet in JDBC.

Data in Book Table:

Below we can see the existing Table and Its data.

Below is the example to retrieve data from a ResultSet in JDBC.

Java

// Java Program to retrieve data from a ResultSet in JDBC
package geeksforgeeks;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

// Driver Class
public class RetrieveDataExample  {
    // Main Function
    public static void main(String[] args) {
        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish a connection to the database
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");

            if (con != null) {
                // Prepare the SQL query to select all records from the book table
                String selectQuery = "SELECT * FROM book";
                PreparedStatement statement = con.prepareStatement(selectQuery);

                // Execute the query and retrieve the result set
                ResultSet resultSet = statement.executeQuery();
                System.out.println("The Available Data\n");

                // Iterate over the result set and print the retrieved data
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String author_name = resultSet.getString("author");
                    String book_name = resultSet.getString("name");
                    String book_price = resultSet.getString("price");

                    System.out.println("ID: " + id + ", Author_Name: " + author_name + ", Book_Name: "
                                       + book_name + ", Book_Price "+book_price);
                }
            } else {
                System.out.println("Not Connected...");
            }
        } catch (Exception e) {
            // Print any exceptions that occur during database access
            System.out.println("Exception is " + e.getMessage());
        }
    }
}

Output:

Here we have the output of the available data.

Explanation of the code:

In this above code, first we have created connection for MySQL database by using Connection class which is available in java.sql package. While connecting we need to provide some properties like Database URL, Username, Password.

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");

After this connection we write SQL query for selecting all records from the table by using PreparedStatement class with connection object. Refer below query statement for better understanding.

String selectQuery = "SELECT * FROM book";
PreparedStatement statement = con.prepareStatement(selectQuery);

Now we got result of SQL query. So, now we call the executeQuery method by using statement object.

Now we assign this result to ResultSet after that by using resultSet.next() in while we display the result in the console. (Already provided the output image above)

ResultSet resultSet = statement.executeQuery();
                System.out.println("The Available Data\n");
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String author_name = resultSet.getString("author");
                    String book_name = resultSet.getString("name");
                    String book_price = resultSet.getString("price");

                    System.out.println("ID: " + id + ", Author_Name: " + author_name + ", Book_Name: " 
                                                   + book_name + ", Book_Price "+book_price);
                }