Java JDBC CallableStatement Tutorial

In this tutorial, we will explore how to use the JDBC CallableStatement interface to interact with a MySQL database. The CallableStatement interface is used to execute SQL stored procedures. Stored procedures are beneficial for encapsulating business logic and improving performance.

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 CallableStatement?

The CallableStatement interface extends the PreparedStatement interface and provides methods for executing stored procedures in a database. Stored procedures are precompiled SQL statements stored in the database, which can be executed with the help of CallableStatement.

Table of Contents

1. Setting Up the MySQL Database
2. Adding MySQL JDBC Driver to Your Project
3. Establishing a Database Connection
4. Creating Stored Procedures in MySQL
5. Executing Stored Procedures with CallableStatement
6. Retrieving Output Parameters from CallableStatement
7. Closing the Connection
8. 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)
);

INSERT INTO products (name, description, price) VALUES
('Laptop', 'Dell Inspiron', 75000.00),
('Smartphone', 'Samsung Galaxy', 30000.00),
('Tablet', 'Apple iPad', 50000.00);

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. Creating Stored Procedures in MySQL

Next, let's create a stored procedure in the MySQL database. This stored procedure will insert a new product into the products table.

DELIMITER //

CREATE PROCEDURE addProduct (
    IN p_name VARCHAR(100),
    IN p_description VARCHAR(255),
    IN p_price DECIMAL(10, 2)
)
BEGIN
    INSERT INTO products (name, description, price) VALUES (p_name, p_description, p_price);
END //

DELIMITER ;

5. Executing Stored Procedures with CallableStatement

We can use the CallableStatement interface to execute the stored procedure.

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

public class CallableStatementExample {
    public static void main(String[] args) {
        String callSQL = "{CALL addProduct(?, ?, ?)}";

        try (Connection connection = JDBCExample.getConnection();
             CallableStatement callableStatement = connection.prepareCall(callSQL)) {

            callableStatement.setString(1, "Smartwatch");
            callableStatement.setString(2, "Apple Watch Series 6");
            callableStatement.setBigDecimal(3, new BigDecimal("40000.00"));

            callableStatement.execute();
            System.out.println("Stored procedure executed successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Retrieving Output Parameters from CallableStatement

Let's create another stored procedure that retrieves the price of a product and returns it as an output parameter.

DELIMITER //

CREATE PROCEDURE getProductPrice (
    IN p_name VARCHAR(100),
    OUT p_price DECIMAL(10, 2)
)
BEGIN
    SELECT price INTO p_price FROM products WHERE name = p_name;
END //

DELIMITER ;

We can use the CallableStatement interface to call this stored procedure and retrieve the output parameter.

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

public class CallableStatementOutputExample {
    public static void main(String[] args) {
        String callSQL = "{CALL getProductPrice(?, ?)}";

        try (Connection connection = JDBCExample.getConnection();
             CallableStatement callableStatement = connection.prepareCall(callSQL)) {

            callableStatement.setString(1, "Laptop");
            callableStatement.registerOutParameter(2, java.sql.Types.DECIMAL);

            callableStatement.execute();

            BigDecimal price = callableStatement.getBigDecimal(2);
            System.out.println("Price of Laptop: " + price);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

7. 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 CallableStatement interface to interact with a MySQL database. We demonstrated how to establish a connection, create stored procedures, execute stored procedures, retrieve output parameters, 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 CallableStatement interface effectively with MySQL.