JDBC CallableStatement Stored Procedures Example

In this tutorial, we will learn how to use the JDBC CallableStatement to execute stored procedures in a MySQL database. Stored procedures are a set of SQL statements that can be executed as a single unit, which helps in reusability and maintaining the database logic.

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 is a part of the JDBC API and is used to execute SQL stored procedures. It extends the PreparedStatement interface and provides methods to set input parameters, register output parameters, and execute stored procedures.

Table of Contents

1. Setting Up the MySQL Database
2. Adding MySQL JDBC Driver to Your Project
3. Creating Stored Procedures in MySQL
4. Establishing a Database Connection
5. Executing Stored Procedures with JDBC CallableStatement
6. Closing the Connection
7. Conclusion

1. Setting Up the MySQL Database

First, create a database named jdbc_example and an employee 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 employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employee (name, department, salary) VALUES
('John Doe', 'HR', 50000.00),
('Jane Smith', 'Finance', 60000.00),
('Mike Johnson', 'IT', 75000.00),
('Emily Davis', 'Marketing', 65000.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. Creating Stored Procedures in MySQL

Next, create stored procedures in the jdbc_example database. You can create a stored procedure to insert a new employee record and another to retrieve an employee's details based on their ID.

Stored Procedure to Insert Employee

DELIMITER //

CREATE PROCEDURE insertEmployee(
    IN empName VARCHAR(100),
    IN empDept VARCHAR(100),
    IN empSalary DECIMAL(10, 2)
)
BEGIN
    INSERT INTO employee (name, department, salary) VALUES (empName, empDept, empSalary);
END //

DELIMITER ;

Stored Procedure to Retrieve Employee by ID

DELIMITER //

CREATE PROCEDURE getEmployeeById(
    IN empId INT,
    OUT empName VARCHAR(100),
    OUT empDept VARCHAR(100),
    OUT empSalary DECIMAL(10, 2)
)
BEGIN
    SELECT name, department, salary INTO empName, empDept, empSalary
    FROM employee
    WHERE id = empId;
END //

DELIMITER ;

4. 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();
        }
    }
}

5. Executing Stored Procedures with JDBC CallableStatement

We will use the CallableStatement interface to call the stored procedures we created earlier.

Inserting a New Employee

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

public class InsertEmployeeExample {
    public static void main(String[] args) {
        String insertEmployeeSQL = "{CALL insertEmployee(?, ?, ?)}";

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

            callableStatement.setString(1, "Alice Brown");
            callableStatement.setString(2, "Sales");
            callableStatement.setBigDecimal(3, new BigDecimal("55000.00"));

            callableStatement.execute();
            System.out.println("Employee inserted successfully!");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Retrieving Employee by ID

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

public class GetEmployeeByIdExample {
    public static void main(String[] args) {
        String getEmployeeByIdSQL = "{CALL getEmployeeById(?, ?, ?, ?)}";

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

            callableStatement.setInt(1, 1);
            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(4, java.sql.Types.DECIMAL);

            callableStatement.execute();

            String name = callableStatement.getString(2);
            String department = callableStatement.getString(3);
            BigDecimal salary = callableStatement.getBigDecimal(4);

            System.out.println("Employee Details - ID: 1, Name: " + name + ", Department: " + department + ", Salary: " + salary);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. 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 execute stored procedures in a MySQL database. We demonstrated how to establish a connection, create stored procedures, execute them using CallableStatement, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use stored procedures effectively with MySQL.