Back to JDBC Hub

Nearby lessons

19 of 37
JDBC - ACID PropertiesJDBC - Batch ProcessingJDBC - Stored ProceduresJDBC - CursorsJDBC - Streaming Data

JDBC - Stored Procedures and CallableStatement

📌 What You Will Learn
  • What a stored procedure is and why we use it
  • Each variation in its own program: IN params, IN+OUT params, ResultSet-returning procedure
  • Calling procedures from Java with CallableStatement
  • Batch updates — Statement version and PreparedStatement version

Stored Procedures and CallableStatement is a fundamental part of database programming with JDBC. This lesson explains What is a Stored Procedure?, Creating a Stored Procedure (MySQL) and Program 1: Calling a Procedure with IN and OUT Parameters with complete, runnable code examples, clear step-by-step explanations, and common mistakes to avoid with exam-style MCQs at the end.

What is a Stored Procedure?

A stored procedure is a block of SQL statements stored inside the database itself with a name. Instead of sending many SQL statements from Java, you send one call to the procedure, and the database runs all the statements inside it.

In simple words: A stored procedure is a named block of SQL saved inside the database. From Java you send just one {call ...} and the database runs all the statements in that block.

Why use stored procedures?

  • Performance — the procedure is already compiled in the database.
  • Less network traffic — one call instead of many queries.
  • Reusability — many applications can call the same procedure.
  • Security — the database can hide the table details.

Creating a Stored Procedure (MySQL)

The three parameter types you will meet:

Parameter typeMeaning
INValue passed INTO the procedure (like a method parameter)
OUTValue returned FROM the procedure (like a return value)
INOUTBoth — a value goes in and a new value comes out
In simple words: `IN` sends a value into the procedure, `OUT` brings one back, and `INOUT` does both. Set IN values with setXxx, register OUT parameters, and read them after the call runs.
Example02
JCode Cell
1DELIMITER $$
2CREATE PROCEDURE addProc(IN x INT, IN y INT, OUT z INT)
3BEGIN
4 SET z = x + y;
5END$$
6DELIMITER ;

Program 1: Calling a Procedure with IN and OUT Parameters

CallableStatement is the JDBC interface (child of PreparedStatement) used to call stored procedures. Here addProc(IN x, IN y, OUT z) adds two numbers and returns the sum.

In simple words: For an `OUT` parameter you must register it, execute, and only then read it. The order is registerOutParameter → execute → getXxx, because the value does not exist until the procedure runs.
Example03
JCode Cell
1import java.sql.*;
2 
3public class CallProcDemo {
4 public static void main(String[] args) throws Exception {
5 Connection con = DriverManager.getConnection(
6 "jdbc:mysql://localhost:3306/company", "root", "password");
7 
8 // 1. create the call
9 CallableStatement cst = con.prepareCall("{call addProc(?, ?, ?)}");
10 
11 // 2. set IN parameters
12 cst.setInt(1, 10);
13 cst.setInt(2, 20);
14 
15 // 3. register the OUT parameter
16 cst.registerOutParameter(3, java.sql.Types.INTEGER);
17 
18 // 4. execute
19 cst.execute();
20 
21 // 5. read the OUT value
22 System.out.println("Sum = " + cst.getInt(3));
23 con.close();
24 }
25}
Output
Sum = 30

Program 2: Procedure that Returns a ResultSet

Some procedures run a SELECT and return rows. In MySQL, use executeQuery() and read the ResultSet like normal.

Example04
JCode Cell
1-- Procedure: CREATE PROCEDURE getAllEmp() SELECT * FROM employee;
2 
3import java.sql.*;
4 
5public class CallProcResultSet {
6 public static void main(String[] args) throws Exception {
7 Connection con = DriverManager.getConnection(
8 "jdbc:mysql://localhost:3306/company", "root", "password");
9 
10 CallableStatement cst = con.prepareCall("{call getAllEmp()}");
11 ResultSet rs = cst.executeQuery();
12 while (rs.next()) {
13 System.out.println(rs.getInt(1) + " " + rs.getString(2));
14 }
15 con.close();
16 }
17}
Output
101 Rahul 102 Priya

Program 3: Procedure with a Salary OUT Parameter

A more realistic example: a procedure that looks up an employee and returns a value (OUT) back to Java.

Example05
JCode Cell
1-- Procedure: CREATE PROCEDURE getSal(IN id INT, OUT sal DOUBLE)
2-- SELECT salary INTO sal FROM employee WHERE id = id;
3 
4import java.sql.*;
5 
6public class CallProcOut {
7 public static void main(String[] args) throws Exception {
8 Connection con = DriverManager.getConnection(
9 "jdbc:mysql://localhost:3306/company", "root", "password");
10 
11 CallableStatement cst = con.prepareCall("{call getSal(?, ?)}");
12 cst.setInt(1, 101); // IN parameter
13 cst.registerOutParameter(2, java.sql.Types.DOUBLE); // OUT parameter
14 cst.execute();
15 
16 System.out.println("Salary = " + cst.getDouble(2));
17 con.close();
18 }
19}
Output
Salary = 50000.0
📝 Key Takeaways
  • Stored procedure = SQL statements stored in the database, called by name.
  • Separate programs: IN+OUT params, ResultSet-returning procedure, OUT salary.
  • CallableStatement (child of PreparedStatement) calls procedures.
  • IN parameters set with setXxx; OUT parameters registered with registerOutParameter and read with getXxx.
  • Batch updates: Statement (different queries) and PreparedStatement (same query, many values).
  • addBatch + executeBatch run many statements in one round-trip.

🧠 Test Your Knowledge

4 Questions
Progress: 0 / 4