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 then 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 the JDBC driver you are using to connect to the database. Every database vendor uses a 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 the 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 the 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.

Sounds easy right, well it is indeed easy once you have done it once. But, if you have just started with Java and JDBC, sometimes it may feel overwhelming because of setting up a database, including JDBD driver into the classpath, and other issues which surface while connecting to a database. In that case, I suggest you first go through a comprehensive Java course like The Complete Java MasterClass - Covers Java 11, instead of learning bits and pieces. Trust me, you will learn a lot in a very short duration of time.

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() methods depending upon the data type of query parameters on the CallableStatement object, similar to PreparedStatment e.g.

cs.setString(1, "982928");

Btw, if you are not familiar with PreparedStatement then you can also see my post, difference between PreparedStatement and CallableStatement to learn more.



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.


These were the steps you need to follow to use the CallableStatement in JDBC to execute a stored procedure on the database side and receive data on your Java program. If you want to learn more about JDBC or want to start from scratch, I recommend Java Platform: Working with Databases Using the JDBC course on Pluralsight. If you don't have a monthly or annual membership, you can just signup for the 10-day free trial to get access to this course.

Java Program to call Stored Procedure in SQL Server using CallableStatement

Here is our complete Java program to call a stored procedure from SQL Server using CallableStatemetn class of JDBC. 

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 that are used to run the stored procedure from Java programs. 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 the Java side, which means you can implement new functionalities without building and deploying a new JAR file on the Java side.

Other SQL Tutorials and  Resources you may like

1. Top 5 Free JDBC Courses for Java Programmers
2. 10 JDBC Best Practices Every Java Programmer should follow (read)
3. Difference between Type 1 and Type 4 JDBC drivers (answers)
4. Top 10 JDBC Interview Question for Java programmers (read)
5. 6 Essential JDBC Performance tips for Java Programmers (tips)
6. How to connect to MySQL database from Java Program? (tutorial)
7. JDBC Batch Insert and Update example using PreparedStatement (tutorial)
8. Difference between java.sql.Date, java.sql.Timestamp, and java.util.Date in JDBC? (answer)

Thanks for reading this tutorial so far. If you like this JDBC tutorial then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.