Victor Chen

I recently learned how to call a stored procedure using Java.

CallableStatement is used when a Java application needs to call a stored procedure. The stored procedure contains the SQL query to be executed on the database and is stored on the database.

To use CallableStatement, the Java code will need to first import the CallableStatement class.

import java.sql.CallableStatement;

In your method, create a CallableStatement object:

CallableStatement cs = null;

Create a connection with the database:

cs = connection.prepareCall("{ call procedure_name}");

connection is the connection to the database, and prepareCall is the method used to call the stored procedure. The syntax used in the prepareCall parameters is shown above, but replace procedure_name with the actual stored procedure name.

Next, use cs.execute(); to execute. So to put it together, you will need to add it to try/catch block as shown below:

CallableStatement cs = null;
try{
  // The syntax for a stored procedure with no
  // parameters would look like this:
  cs = connection.prepareCall("{ call procedure_name}");
  cs.execute();
} catch (SQLException e) {
}

This is the simplest use of CallableStatement. The stored procedure above does not have any parameters. If the stored procedure has parameters, you will need to modify your code to add the parameters in the Java code. CallableStatements can have IN parameters, OUT parameters, IN/OUT parameters, or no parameters. The Java API for CallableStatement is a good reference as well. Here are some examples I found that are helpful and have the proper syntax:

try {
  // Call a function that takes NO parameters
  cs = connection.prepareCall("{call procedure_name}");
  cs.execute(); //execute the stored procedure

  // Call a function that takes a String IN parameter
  // An IN parameters is when you input a value
  // for the stored procedure

  cs = connection.prepareCall("{call procedure_name_in(?)}");
  cs.setString(1, "ABC"); // Set the value for the IN parameter
  cs.execute();

  // Call a function that returns a String OUT parameter
  // An OUT parameter is when the stored procedure
  // has an output value

  cs = connection.prepareCall("{call procedure_name_out(?)}");
  cs.registerOutParameter(1, Types.VARCHAR);
  // Register the types of the return value and OUT parameter
  cs.execute();
  String outParam = cs.getString(1); // OUT parameter

  // Call a function with one IN/OUT parameter
  // An IN/OUT parameter has been an input and an output

  cs = connection.prepareCall("{call procedure_name_inout(?)}");
  cs.registerOutParameter(1, Types.VARCHAR);
  cs.setString(1, "ABC");
  cs.execute();
  String outParam = cs.getString(1);
} catch (SQLException e) {
}