JDBC CallableStatement

In many enterprise applications, the business logic for the application will be encapsulated in sprocs (which is short for stored procedures) inside the database. Stored procedures are just like methods in our Java code. They have names, they can have parameter lists, they have a body (containing SQL and procedural commands) that performs some work, and they can return values.

In this section, the term stored procedure is used generically to refer to both procedures and functions. The main difference between the two is that a function returns a value and a procedure does not return a value. If our database supports storing procedural and SQL statements in the database for execution, but uses a different term, we should consider “stored procedure” to be a synonym for the term used by our database.

There are many reasons why we would use stored procedures. Some of the services provided by stored procedures are encapsulation and reuse of functionality, control of transactions, and standardization of business rules:

• A sproc can encapsulate a common set of SQL commands. A client can access this functionality without needing to make many different JDBC calls.
• We can reuse sprocs that are already developed, rather than recreating their functionality from scratch in JDBC.
• The sproc makes transaction control easier. We look at transactions and transaction control in greater detail later in this chapter.
• Providing a given functionality in a sproc ensures that every part of the application that uses the functionality does so in the same way. If requirements change, only the procedure may need to be changed, and everyone who uses the procedure automatically gets the change.
• By having a procedure in a database, the code exists in one place only, yet is accessible to any client, Java or not, that can connect to the database.

In this tutorial, you will learn how to call MySQL stored procedures from JDBC using CallableStatement object. JDBC code can call stored procedures using a CallableStatement object.

The methods to create a CallableStatement object are as follows:

Using CallableStatement Objects

The first argument in each prepareCall() method is a SQL string. The SQL string for calling a stored procedure can take one of several forms. Common between all the forms is the SQL keyword call that appears before the procedure name, and the curly braces that surround the SQL. This signals the driver that the SQL is not an ordinary SQL statement and that the SQL must be converted into the correct form for calling a procedure in the target database. The most basic form is the SQL for calling a stored procedure that takes no parameters. The SQL string looks like this:

{ call procedure_name }

For example, suppose the database had a stored procedure named adjust_prices, which took no parameters and returned no value. The code to create a CallableStatement object for this stored procedure would look like:

String sql = "{ call adjust_prices }";
CallableStatement cs = connection.prepareCall(sql);

When a procedure or function takes parameters, the SQL will look something like this:

String sql = "{ call set_price(?, ?) }";
CallableStatement cs = connection.prepareCall(sql);

The set_price procedure takes two parameters and returns no value. Placeholders mark each parameter in the procedure call. We have already looked at placeholders in detail in the PreparedStatement objects section of this chapter. Finally, the SQL for calling a stored function would look like this:

String sql = "{ ? = call get_price(?) }";
CallableStatement cs = connection.prepareCall(sql);

The return value of the function is marked by a placeholder, as is the parameter sent to the function.

Using Placeholders

Like the PreparedStatement object, the placeholders are numbered consecutively, starting with number 1 for the placeholder that appears in the left-most position in the string. Moving from left to right, each placeholder is given the next number in sequence. If a placeholder is used to pass an argument to a stored procedure, this parameter is known as an IN parameter. Its value must be set before the statement can be executed. If we fail to set one of the placeholders, the driver will throw a SQLException when we attempt to execute the SQL. The CallableStatement interface inherits the setXXX() methods of the PreparedStatement interface for doing this.

A stored procedure can also set an input parameter to a new value, and that value is passed back to the caller through the parameter list. For example, this SQL command:

call set_price(?, ?)

The sproc has two parameters in the parameter list. If this were a Java method call, the method could set the value of either parameter inside the method, and that value is not visible to the caller. With a SQL stored procedure, the parameters can be set, and the new values can be visible to the caller. If the placeholder is used to pass data to the sproc, and the sproc passes data back through the parameter, this is an INOUT parameter. A placeholder that is only used to pass data back, or that is a return value, is an OUT parameter.

If any of the parameters in the SQL command are INOUT or OUT parameters, the JDBC type of the placeholder must be registered before the call can be executed. If we do not register a placeholder that returns a value, we will get a SQLException. This is done with the following methods:

void registerOutParameter(int parameterIndex, int jdbcType)
void registerOutParameter(int parameterIndex, int jdbcType, int scale)

Unlike the setXXX() methods, the registerOutParameter() method only has two forms. The first parameter in the method is the position of the placeholder in the SQL string. The second parameter is one of the constants defined in the java.sql.Types class. The Types class defines a constant for each generic JDBC type.

So, for example, if we were calling a stored procedure that passed a value through the second parameter in a parameter list, and the SQL type returned was a varchar (essentially a string), we would register the parameter like this:

cs.registerOutParameter(2, java.sql.Types.STRING);

If the return value of a function was a double, we could use this:

cs.registerOutParameter(1, java.sql.Types.DOUBLE);

For the complete list of the available java.sql.Types constants, consult the API Java documentation. When registering a parameter that is one of the numeric types such as float, double, numeric, or decimal, we could also use the second form of the registerOutParameter() method. This method takes a third parameter that defines the scale of the returned value. For example, to register a return type that returned a number with two digits to the right of the decimal point, we could use:

cs.registerOutParameter(1, java.sql.Types.DOUBLE, 2);

Note that if any of the placeholders is an INOUT parameter, the JDBC code must call both a setXXX() method and a registerOutParameter() method prior to executing the callable statement. If we fail to set the value or register the parameter, the driver will throw a SQLException.

As with the PreparedStatement object, once a placeholder has been set with data, that placeholder remains set until the code explicitly changes the placeholder. All the placeholders can be cleared by calling the method clearParameters(). The value of a placeholder is changed by calling one of the setXXX() or registerOutParameter() methods, again with the appropriate index. After the data values are set, the code calls one of the execute methods, executeUpdate(), executeQuery(), or execute(), to tell the database to execute the stored procedure.

If we call any of the executeQuery(String), executeUpdate(String), or execute(String) methods, the driver will throw a SQLException. We must call the no parameter versions of those methods with a CallableStatement.

After executing the sproc, the return values of any placeholders are retrieved with getXXX() methods, similar to those used to retrieve the column values from a row in a resultset. The getXXX() methods only have one form, one that takes an int parameter. The parameter int is the index of the placeholder in the CallableStatement.

JDBC MySQL Stored Procedure Example

We follow these steps one-by-one.

1. Sample database creation: We execute the following commands in the MySQL 8.0 command line client.

mysql> create database test;
mysql> use test;
mysql> create table emp(id int, name varchar(30), salary decimal(10,2));
mysql> insert into emp values(101, 'Rajan Sharma', 25347.25);
mysql> insert into emp values(102, 'Sneha Khurana', 36523.75);
mysql> insert into emp values(103, 'Anthony Joshua', 29326.65);

Now, we have the emp table with the following records.

mysql> select * from emp;
+------+----------------+----------+
| id   | name           | salary   |
+------+----------------+----------+
|  101 | Rajan Sharma   | 25347.25 |
|  102 | Sneha Khurana  | 36523.75 |
|  103 | Anthony Joshua | 29326.65 |
+------+----------------+----------+

2. Stored procedure creation: For demonstration purpose, we will create a new stored procedure named emp_info with IN, OUT and INOUT parameters. The following SQL script name sproc.sql shows it.

sproc.sql

DELIMITER $
CREATE PROCEDURE emp_info (IN eid int, OUT ename varchar(30), INOUT esalary decimal(10,2))

BEGIN
declare tempSalary decimal(10,2);

-- Select data
select name, salary into ename, tempSalary from emp where id=eid;

-- Update new salary
update emp set salary = esalary where id=eid;

-- Return old esalary
set esalary = tempSalary;

END$
DELIMITER ;

After writing, the procedure can be created by running the SQL script from the MySQL 8.0 Workbench CE.

3. Calling stored procedure from JSP program: 

The following JSP program named “call-stmt.jsp” is the complete example of calling the MySQL stored procedure from JDBC.

call-stmt.jsp

<%@ page import="java.sql.*" %>
<%!
    Connection con = null;
    CallableStatement stmt = null;
    ResultSet rs = null;
    String url = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true";
    String user = "root";
    String pass = "system";
    String sql = "{call EMP_INFO(?,?,?)}";
    int count;
%>
<%
    try {
        //Load the MySQL driver
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

        //Create the Connection object for MySQL
        con = DriverManager.getConnection(url, user, pass);
        if (con != null) {
            out.println("Successfully connected to " + "MySQL server using TCP/IP..." + "<br>");
        }

        //Create the CallableStatement object
        CallableStatement stmt = con.prepareCall(sql);

        //Set IN parameter
        stmt.setInt(1, 101);  

        //Set OUT parameter
        stmt.registerOutParameter(2, Types.VARCHAR);

        //Set INOUT parameter
        stmt.setDouble(3, 32456.45);
        stmt.registerOutParameter(3, Types.DOUBLE);

        //Execute stored procedure
        stmt.execute();

        //Get Out and InOut parameters
        out.println("Employee Name = " + stmt.getString(2) + "<br>");
        out.println("Old Salary = "  + stmt.getDouble(3) + "<br>");

    } 
    catch (Exception e) {
        out.println("Exception: " + e.getMessage());
    } 
    finally {
        try {
            if (con != null) {
                con.close();
            }
        } 
        catch (SQLException e) {  }
    }
%>

4. Running the JSP program: When the JSP program runs, it shows the following output.

The program works as expected.

In this tutorial, we have shown you how to call a stored procedure in MySQL database from a JSP program using JDBC CallableStatement object.