How to Use Callable Statement in Java to Call Stored Procedure?

In JDBC, a Callable Statement is used to execute stored procedures from a Java application. Stored procedures are precompiled SQL statements stored inside the database, which help improve performance, security, and reusability.

• Used to call database stored procedures using the prepareCall() method.
• Improves performance since stored procedures are precompiled in the database.
• Allows handling multiple parameters (IN, OUT, INOUT) efficiently.

Steps to use Callable Statement

Following are the steps to use Callable Statement in Java to call Stored Procedure:

Step 1: Load Driver & Create Connection

• Load the MySQL driver.
• Establish connection with the database.

import java.sql.*;
public class JavaApplication1 {
   public static void main(String[] args) throws Exception
   {
       Class.forName("com.mysql.jdbc.Driver");
       Connection con=DriverManager.getConnection("jdbc:mysql://localhost/root","geek","geek");  
   }  
}

Step 2: Create SQL String

Store the SQL query (or procedure call) in a string.

String sql_string = "{call insertStudent(?, ?, ?)}";

Step 3: Create CallableStatement Object

• Use prepareCall() method of Connection interface.
• The sql_string will be passed as an argument to the prepareCall() method.

CallableStatement cs = con.prepareCall(sql_string);

Step 4: Set Input Parameters

• Set values using appropriate setter methods like setString(), setInt().

cs.setString(1,"geek1");

cs.setString(2,"python");

cs.setString(3,"beginner");

Step 5: Execute Stored Procedure

Call the stored procedure using execute() method.

Example of using Callable Statement in Java to call Stored Procedure

import java.sql.*;

public class CallableExample {
    public static void main(String[] args) throws Exception {

        Class.forName("com.mysql.cj.jdbc.Driver");

        Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/testdb", "root", "password");

        // Calling stored procedure
        String sql = "{call insertStudent(?, ?, ?)}";

        CallableStatement cs = con.prepareCall(sql);

        cs.setString(1, "geek1");
        cs.setString(2, "python");
        cs.setString(3, "beginner");

        cs.execute();

        System.out.println("Stored procedure executed successfully");

        con.close();
    }
}

Output: