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

The CallableStatement of JDBC API is used to call a stored procedure. A Callable statement can have output parameters, input parameters, or both. The prepareCall() method of connection interface will be used to create CallableStatement object.

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

1) Load MySQL driver and Create a database connection.

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”);  

   }  

}

2) Create a SQL String

We need to store the SQL query in a String.

String sql_string=”insert into student values(?,?,?)”;

3) Create CallableStatement Object

The prepareCall() method of connection interface will be used to create CallableStatement object. The sql_string will be passed as an argument to the prepareCall() method.

CallableStatement cs = con.prepareCall(sql_string);

4) Set The Input Parameters

Depending upon the data type of query parameters we can set the input parameter by calling setInt() or setString() methods.

cs.setString(1,”geek1″);

cs.setString(2,”python”);

cs.setString(3,”beginner”);

5) Call Stored Procedure

Execute stored procedure by calling execute() method of CallableStatement class.

Example of using Callable Statement in Java to call Stored Procedure

Java

// Java program  to use Callable Statement
// in Java to call Stored Procedure

package javaapplication1;

import java.sql.*;

public class JavaApplication1 {

    public static void main(String[] args) throws Exception
    {
        Class.forName("com.mysql.jdbc.Driver");

        // Getting the connection
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost/root", "acm", "acm");

        String sql_string = "insert into students values(?,?,?)";

        // Preparing a CallableStateement
        CallableStatement cs = con.prepareCall(sql_string);

        cs.setString(1, "geek1");
        cs.setString(2, "python");
        cs.setString(3, "beginner");
        cs.execute();
        System.out.print("uploaded successfully\n");
    }
}

Output:

students table after running code