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:

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

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

[php]
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();
}
}
}
[/php]
Download code here(Eclipse)

Executing a stored procedure with OUT Parameters using CallableStatement

[php]
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();
}
}
}
[/php]
Download code here(Eclipse)