How to use Callable Statement in Java to call Stored Procedure? JDBC Example

The CallableStatement of JDBC API is used to call a stored procedure from Java Program. Calling a stored procedure follows the same pattern as creating PreparedStatment and than executing it. You first need to create a database connection by supplying all the relevant details e.g. database URL, which comprise JDBC protocol and hostname, username, and password. Make sure your JDBC URL is acceptable by JDBC driver you are using to connect to the database. Every database vendor uses different JDBC URL and they have different driver JAR which must be in your classpath before you can run the stored procedure from Java Program.

Once you are done with initial setup, you can obtain CallableStatement from Connection by calling prepareCall(String SQL) method, where SQL must be in the format required by your database vendor e.g. Microsoft SQL Server requires curly braces e.g.
{call Books.BookDetails_Get(?)}.

This stored proc requires an INPUT parameter which must be set by calling setXXX() method on the CallableStatement object before you can execute the query.

Once this is done, just call the executeQuery() method of CallableStatement and it will return the ResultSet contains all the rows returned by this stored proc.

Just loop through ResultSet and extract all the rows. You have successfully run the stored procedure from Java Program using CallableStatement.

Steps to call a stored procedure from Java

1) Create a database connection.

Connection con = null;
try {
   Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
   String url = "jdbc:sqlserver://localhost:42588;";
   con = DriverManager.getConnection(url, "username", "pw");
} catch (Exception e) {
   e.printStackTrace();
}

2) Create a SQL String

You need to create SQL using a String variable to call the stored procedure, for example, CallableStatement e.g. {call Books.BookDetails_Get(?)}. This is database dependent, for Oracle the format is different it starts with BEGIN and ends with ENDS instead of curly braces e.g.

String SQL = "{call Books.BookDetails_Get(?)}" // for Microsoft SQL Server
String Oracle = "BEGIN BOOKDETAILS_GET(?); END;";

3) Create CallableStatement Object

You can create a CallableStatement by calling Connection.prepareCall(SQL) method, pass the SQL created in the previous step.

CallableStatement cs = con.prepareCall(SQL);

4)  Provide Input Parameters

You can set the input parameter by calling various setXXX() method depending upon the data type of query parameters on the CallableStatement object, similar to PreparedStatment e.g.

cs.setString(1, "982928");

5)  Call Stored Procedure

You can execute a stored procedure on the database by calling executeQuery() method of CallableStatement class, as shown below:

ResultSet rs = cs.executeQuery();

This will return a ResultSet object which contains rows returned by your stored procedure.

6) Extract Rows from ResultSet

You can get the data from the ResultSet by Iterating over ResultSet and print the results or create Java objects, as shown below:

while(rs.next()){
  System.out.println(rs.getString(1));
}

This will print the first column of every row. You should also close the ResultSet object once you are done with it.

Java Program to call Stored Procedure in SQL Server using CallableStatement

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

/**
 *
 * A Simple example to use CallableStatement in Java Program.
 */
public class Hello {

  public static void main(String args[]) {
   
    Connection con = null;
    try {
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       String url = "jdbc:sqlserver://localhost:42588;";
       con = DriverManager.getConnection(url, "username", "pw");
    } catch (Exception e) {
       e.printStackTrace();
    }

    String SQL = "{call Books.dbo.usp_BookDetails_Get(?)}";

    CallableStatement cs = con.prepareCall(SQL);

    cs.setString(1, "978-0132778046");

    cs.setString(2, "978-0132778047");

    ResultSet rs = cs.executeQuery();
   
    while(rs.next()){
      System.out.println(rs.getString(1));
    }
   
    rs.close();
  }
}

That's all about how to run CallableStatement in Java JDBC. If you are thinking to build your Data Access layer around stored procedures, which is a great design, then you should have a good understanding of CallableStatement.

They are the ones which are used to run the stored procedure from Java programs. By encapsulating your data access logic and SQL on a stored procedure, allow you to change them easily on SQL editor without making any change on Java side, which means you can implement new functionalities without building and deploying a new JAR file on Java side.