A Stored Procedure is set of SQL statements which performs one or more specific task. For calling Stored Procedure in JDBC, we uses CallableStatement.

The following is the syntax of creating a stored procedure:

  Create or [Replace] Procedure procedure_name
    [(parameter[,parameter])]
    IS
    [Declarations]
    BEGIN
        executables
            [EXCEPTION exceptions]
    END [procedure_name]

A stored procedure encapsulates the values of the following types of parameters:

IN- Refers to the parameter that can be referenced by the stored procedure.The value of this parameter cannot be overwritten by the procedure.

OUT- Refers to the parameter that cannot be referenced by the stored procedure.The value of this parameter can be overwritten by the procedure.

IN OUT- Refers to the parameter that can be referenced by the stored procedure.The value of this parameter can be overwritten by the procedure.

Executing a stored procedure with IN Parameters using CallableStatement

package com.tech2our.jdbc;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class CallableStatementExample {
 public static void main(String[] args) {
  System.out.println("Insert records example using statement!");
  Connection con = null;
  try {
   Class.forName("com.mysql.jdbc.Driver");
   con = DriverManager.getConnection(
			"jdbc:mysql://localhost:3306/jdbctutorial", "root",
			"tech2our");
   try {
	CallableStatement callableStatement = con.prepareCall("
                                    {Call CreateEmployee(?,?,?,?)}");
	callableStatement.setInt(1, 1001);
	callableStatement.setString(2, "Tech2Our");
	callableStatement.setString(3, "New Delhi");
	callableStatement.setString(4, "9999999999");
	callableStatement.execute();
	System.out.println("Employee Created Successfully.");
	con.close();
  } catch (SQLException s) {
    System.out.println("SQL statement is not executed!"+s);
  }
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
}

Download code here(Eclipse)

Executing a stored procedure with OUT Parameters using CallableStatement

package com.tech2our.jdbc;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class CallableStatementExample {
 public static void main(String[] args) {
  System.out.println("Insert records example using statement!");
  Connection con = null;
  try {
   Class.forName("com.mysql.jdbc.Driver");
   con = DriverManager.getConnection(
	  	    "jdbc:mysql://localhost:3306/jdbctutorial", "root",
			"tech2our");
   try {
	CallableStatement callableStatement = con.prepareCall("{Call getEmployee(?,?)}");
	callableStatement.setInt(1, 1001);
	callableStatement.registerOutParameter(2, Types.DOUBLE);
	callableStatement.execute();
	System.out.println("Employee Salary: "+callableStatement.getDouble(2));
	con.close();
   } catch (SQLException s) {
	System.out.println("SQL statement is not executed!"+s);
   }
  } catch (Exception e) {
	e.printStackTrace();
  }
 }
}

Download code here(Eclipse)