Java JDBC ResultSet Tutorial

Author:
In this tutorial, we will explore how to use the JDBC ResultSet interface to interact with a MySQL database. The ResultSet interface provides methods to retrieve and manipulate the data obtained from executing SQL queries. It acts as an iterator to move through the data returned by the database.

Introduction

What is JDBC?

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with databases. It provides methods to query and update data in a database, as well as to retrieve metadata about the database itself.

What is ResultSet?

The ResultSet interface represents the result set of a database query. It provides methods to iterate through the results, retrieve individual columns by name or index, and update the data. The ResultSet maintains a cursor pointing to its current row of data, and the cursor can be moved forward, backward, and to specific rows.

Table of Contents

  1. Setting Up the MySQL Database
  2. Adding MySQL JDBC Driver to Your Project
  3. Establishing a Database Connection
  4. Executing SQL Queries and Retrieving ResultSet
  5. Navigating Through ResultSet
  6. Retrieving Data from ResultSet
  7. Updating Data Using ResultSet
  8. Closing the Connection
  9. Conclusion

1. Setting Up the MySQL Database

First, create a database named jdbc_example and a products table within it. Open your MySQL command line or any MySQL client and execute the following commands:

CREATE DATABASE jdbc_example;

USE jdbc_example;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(255),
    price DECIMAL(10, 2)
);

2. Adding MySQL JDBC Driver to Your Project

To interact with a MySQL database, you need to add the MySQL JDBC driver to your project. If you are using Maven, add the following dependency to your pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>

3. Establishing a Database Connection

We will start by establishing a connection to the MySQL database using JDBC.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCExample {
    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_example";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Executing SQL Queries and Retrieving ResultSet

We can use the Connection interface to create a Statement object and execute SQL queries. Here, we will retrieve data from the products table.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RetrieveDataExample {
    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM products";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String description = resultSet.getString("description");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id + ", Name: " + name + ", Description: " + description + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. Navigating Through ResultSet

The ResultSet interface provides methods to navigate through the rows of data. You can move the cursor forward, backward, to a specific row, and more.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class NavigateResultSetExample {
    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM products";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            // Move the cursor to the last row
            resultSet.last();
            System.out.println("Last Row ID: " + resultSet.getInt("id"));

            // Move the cursor to the first row
            resultSet.first();
            System.out.println("First Row ID: " + resultSet.getInt("id"));

            // Move the cursor to the third row
            resultSet.absolute(3);
            System.out.println("Third Row ID: " + resultSet.getInt("id"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Retrieving Data from ResultSet

We can retrieve data from the ResultSet by using the appropriate getter methods for the data type of the column.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RetrieveColumnDataExample {
    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM products";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String description = resultSet.getString("description");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id + ", Name: " + name + ", Description: " + description + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

7. Updating Data Using ResultSet

The ResultSet interface also provides methods to update data in the result set. However, the result set must be updatable.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdateResultSetExample {
    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM products";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement(
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            // Move the cursor to the first row
            resultSet.first();

            // Update the price of the first product
            resultSet.updateDouble("price", 80000.00);
            resultSet.updateRow();

            System.out.println("Updated price of first product.");

            // Verify the update
            resultSet.beforeFirst();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String description = resultSet.getString("description");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id + ", Name: " + name + ", Description: " + description + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

8. Closing the Connection

Using the try-with-resources statement ensures that the connection is closed automatically. This is important to free up database resources.

import java.sql.Connection;
import java.sql.SQLException;

public class CloseConnectionExample {
    public static void main(String[] args) {
        try (Connection connection = JDBCExample.getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

In this tutorial, we have covered the basics of using the JDBC ResultSet interface to interact with a MySQL database. We demonstrated how to establish a connection, execute SQL queries, retrieve data, navigate through the result set, update data, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use the ResultSet interface effectively.

Comments

Post a Comment

Leave Comment