How can you use JDBC to call stored procedures in a database?

Stored procedures are precompiled and stored database objects that can be executed on demand. Using JDBC, it’s possible to call stored procedures in a database in the following steps:

1. Establish a database connection using DriverManager.getConnection() method, providing the database URL, username, and password.

2. Create a CallableStatement object by calling the prepareCall() method on the connection object, passing the stored procedure call statement as the argument. The syntax for calling a stored procedure with a single IN parameter is as follows:

    CallableStatement stmt = conn.prepareCall("{call procedure_name(?)}");

3. Bind input parameter values to the CallableStatement object. For example, if the stored procedure has one input parameter of type varchar, the parameter can be set as follows:

    stmt.setString(1, "input_value");

4. Register output parameter(s) using the registerOutParameter() method on the CallableStatement object. The first argument is the position of the output parameter, and the second argument is the SQL data type of the parameter. For example, to register an integer output parameter at position 2, the following line of code can be used:

    stmt.registerOutParameter(2, java.sql.Types.INTEGER);

5. Execute the stored procedure using the execute() method on the CallableStatement object.

6. Access the output parameter(s) using the getXXX() methods on the CallableStatement object. For example, to retrieve the value of an integer output parameter at position 2, the following line of code can be used:

    int outputValue = stmt.getInt(2);

7. Close the CallableStatement and Connection objects using close() method.

Here’s an example of how to call a stored procedure in a MySQL database using JDBC:

    try {
        //Establishing database connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "username", "password");
        
        //Creating a CallableStatement
        CallableStatement stmt = conn.prepareCall("{call count_employees_by_dept(?, ?)}");
        
        //Setting IN parameter values
        stmt.setString(1, "Sales");
        
        //Registering OUT parameter types
        stmt.registerOutParameter(2, java.sql.Types.INTEGER);
        
        //Executing the stored procedure
        stmt.execute();
        
        //Retrieving OUT parameter values
        int count = stmt.getInt(2);
        System.out.println("Number of employees in Sales department: " + count);
        
        //Closing the CallableStatement and Connection objects
        stmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

In this example, the stored procedure "count_employees_by_dept" takes an input parameter of type varchar, which is the department name, and returns the number of employees in that department as an integer output parameter. The JDBC code sets the input parameter to "Sales", registers the output parameter as an integer, executes the stored procedure, and retrieves the output parameter value.