Stored Procedures in JDBC - Java Example

What is a Stored Procedure?

A stored procedure is a group of SQL statements that are stored in the database. You can call this reusable procedure to perform tasks like inserting, updating, deleting, or retrieving data.

Advantages of Stored Procedures

Improves performance (precompiled queries)
Promotes code reusability and readability
Centralizes business logic in the database
Improves security with permission control

Student Table Structure

CREATE DATABASE studentdb;
USE studentdb;

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

1️⃣ Stored Procedure for Insert

DELIMITER //
CREATE PROCEDURE InsertStudent(IN studentName VARCHAR(100))
BEGIN
  INSERT INTO students(name) VALUES(studentName);
END //
DELIMITER ;

Java Code to Insert Using Stored Procedure

import java.sql.*;

public class JdbcInsertProcedureExample {
  public static void main(String[] args) {
    try {
      Connection con = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/studentdb", "root", "your_mysql_password"
      );
      CallableStatement stmt = con.prepareCall("{call InsertStudent(?)}");
      stmt.setString(1, "Anjali");
      stmt.execute();
      System.out.println("Data inserted using stored procedure.");
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Output after Insert

ID | Name
-----------
1  | Anjali

2️⃣ Stored Procedure for Select

DELIMITER //
CREATE PROCEDURE GetAllStudents()
BEGIN
  SELECT * FROM students;
END //
DELIMITER ;

Java Code to Select Using Stored Procedure

import java.sql.*;

public class JdbcSelectProcedureExample {
  public static void main(String[] args) {
    try {
      Connection con = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/studentdb", "root", "your_mysql_password"
      );
      CallableStatement stmt = con.prepareCall("{call GetAllStudents()}");
      ResultSet rs = stmt.executeQuery();

      System.out.println("ID | Name");
      while (rs.next()) {
        System.out.println(rs.getInt("id") + " | " + rs.getString("name"));
      }
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Output after Select

ID | Name
-----------
1  | Anjali

← Previous: jdbc batch processing java Next: Call Stored Proceure in java →