Jdbc Procedure Tutorial

PL/SQL, Stored Procedures in MySQL, and CallableStatement in Java (2026)

PL/SQL, Stored Procedures in MySQL, and CallableStatement in Java

In this tutorial, you will learn the basics of PL/SQL, why stored procedures are useful, the difference between Statement, PreparedStatement, and CallableStatement in JDBC, and how to create and call MySQL stored procedures from a Java program.

What is PL/SQL?

PL/SQL is designed for seamless processing of SQL statements, enhancing the security, portability, and robustness of the database. Similar to other database languages, it gives more control to programmers through the use of loops, conditions, and object-oriented concepts. The full form of PL/SQL is "Procedural Language extensions to SQL".

Why Stored Procedures?

  • Stored procedures are fast.
  • Stored procedures are portable.
  • Stored procedures are safe because the query is written in the database layer.
  • High productivity.
  • Tight integration with SQL.
  • Tight security.
  • Support object-oriented programming concepts.

Statement vs PreparedStatement vs CallableStatement

In JDBC, there are three types of statements used for different kinds of queries or procedures.

Statement PreparedStatement CallableStatement
It is the parent interface of PreparedStatement. It is the parent of CallableStatement. It is a subclass of PreparedStatement.
Used for non-parameterized queries. Used for parameterized queries. Used for procedures or functions.
Not a precompiled statement. It is a precompiled statement. It is also a precompiled statement.
Performance is slow because it compiles every time. Performance is faster than Statement because it is precompiled. Performance is faster than PreparedStatement.
Used for DDL queries. Used for any type of query. Used for procedures and functions.
Used when an SQL query executes only once. Used when an SQL query executes multiple times. Used for procedures and functions.

Types of CREATE PROCEDURE Statements

A CREATE PROCEDURE statement can be written in four ways depending on how parameters are used:

  1. CREATE PROCEDURE pro_name () ... — when you do not want any input from the user.
  2. CREATE PROCEDURE pro_name ([IN] param_name type) ... — when you want to get input from the user.
  3. CREATE PROCEDURE pro_name ([OUT] param_name type) ... — when you want to get a value out of the procedure.
  4. CREATE PROCEDURE pro_name ([INOUT] param_name type) ... — when you want both input and output values.

Procedure vs Function

Procedure Function
It does not have any return type. It must have a return type.
It is used for business logic. It is used for calculation.
A function can be called from a procedure. A procedure can never be called from a function.
It supports IN or OUT parameters. It supports only IN parameters.
Try-catch block can be used in a procedure. Try-catch block cannot be used in a function.
It can return zero or more values. It returns exactly one value.

1. How to Create a Procedure in MySQL for Insert and Call It from Java

First, create the stored procedure in MySQL:

mysql> DELIMITER $$
mysql> create procedure myinsert(IN roll int, IN name varchar(20), IN marks float)
    -> BEGIN
    -> insert into student values(roll, name, marks);
    -> END $$
Query OK, 0 rows affected (0.07 sec)
mysql> DELIMITER ;

Create the Dao class to define the database connection:

Dao.java

import java.sql.Connection;
import java.sql.DriverManager;

public class Dao {
    private static Connection con;

    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydatabase", "root", "root");
        } catch (Exception e) {
            System.out.println(e);
        }
        return con;
    }
}

CallableInsert.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Scanner;

public class CallableInsert {

    public void insert(int roll, String name, float marks) {
        try (Connection con = Dao.getConnection();) {
            CallableStatement cs = con.prepareCall("call myinsert(?,?,?)");
            cs.setInt(1, roll);
            cs.setString(2, name);
            cs.setFloat(3, marks);
            cs.executeUpdate();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Enter roll number");
        int roll = scanner.nextInt();
        System.out.println("Enter name");
        String name = scanner.next();
        System.out.println("Enter marks");
        float marks = scanner.nextFloat();

        new CallableInsert().insert(roll, name, marks);
        scanner.close();
    }
}

Output:

Enter roll number
2
Enter name
ramesh
Enter marks
78.7
success

2. How to Create a Procedure in MySQL for Update and Call It from Java

mysql> DELIMITER $$
mysql> create procedure myupdate(IN roll int, IN name varchar(20), IN marks float)
    -> BEGIN
    -> update student set name=name, marks=marks where roll=roll;
    -> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

CallableUpdate.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Scanner;

public class CallableUpdate {

    public void update(int roll, String name, float marks) {
        try (Connection con = Dao.getConnection();) {
            CallableStatement cs = con.prepareCall("call myupdate(?,?,?)");
            cs.setInt(1, roll);
            cs.setString(2, name);
            cs.setFloat(3, marks);
            cs.executeUpdate();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Enter roll number");
        int roll = scanner.nextInt();
        System.out.println("Enter name");
        String name = scanner.next();
        System.out.println("Enter marks");
        float marks = scanner.nextFloat();

        new CallableUpdate().update(roll, name, marks);
        scanner.close();
    }
}

Output:

Enter roll number
2
Enter name
uu
Enter marks
76
success

3. How to Create a Procedure in MySQL for Delete and Call It from Java

mysql> DELIMITER $$
mysql> create procedure mydelete(IN r int)
    -> BEGIN
    -> delete from student where roll=r;
    -> END $$
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;

CallableDelete.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.Scanner;

public class CallableDelete {

    public void delete(int roll) {
        try (Connection con = Dao.getConnection();) {
            CallableStatement cs = con.prepareCall("call mydelete(?)");
            cs.setInt(1, roll);
            cs.executeUpdate();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Enter roll number");
        int roll = scanner.nextInt();

        new CallableDelete().delete(roll);
        scanner.close();
    }
}

Output:

Enter roll number
2
success

4. How to Create a Procedure in MySQL for Fetch All and Call It from Java

mysql> DELIMITER $$
mysql> create procedure myfetchall()
    -> BEGIN
    -> select * from student;
    -> END $$
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;

CallableFetchAll.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

public class CallableFetchAll {

    public void fetchAll() {
        try (Connection con = Dao.getConnection();) {
            CallableStatement cs = con.prepareCall("call myfetchall()");

            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getFloat(3));
            }

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String[] args) {
        new CallableFetchAll().fetchAll();
    }
}

Output:

1	deep	56.7

5. How to Create a Procedure in MySQL for Fetch One and Call It from Java

mysql> DELIMITER $$
mysql> create procedure myfetchone(IN r int)
    -> BEGIN
    -> select * from student where roll=r;
    -> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

CallableFetchOne.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Scanner;

public class CallableFetchOne {

    public void fetchOne(int roll) {
        try (Connection con = Dao.getConnection();) {
            CallableStatement cs = con.prepareCall("call myfetchone(?)");
            cs.setInt(1, roll);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getFloat(3));
            }

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Enter roll number");
        int roll = scanner.nextInt();
        new CallableFetchOne().fetchOne(roll);
    }
}

Output:

Enter roll number
1
1	deep	56.7

Frequently Asked Questions

What is PL/SQL?

PL/SQL stands for Procedural Language extensions to SQL. It is designed for seamless processing of SQL statements and enhances security, portability, and robustness of the database. It gives programmers more control using loops, conditions, and object-oriented concepts.

Why are stored procedures used?

Stored procedures are fast, portable, and safe because the query is written in the database layer. They provide high productivity, tight integration with SQL, tight security, and support object-oriented programming concepts.

What is the difference between Statement, PreparedStatement, and CallableStatement?

Statement is used for non-parameterized queries and is not precompiled. PreparedStatement is used for parameterized queries and is precompiled, so it is faster. CallableStatement is a subclass of PreparedStatement used to call stored procedures or functions.

What is the difference between a procedure and a function?

A procedure does not have a return type and is used for business logic, while a function must have a return type and is used for calculations. A function can be called from a procedure, but a procedure cannot be called from a function. Procedures support IN, OUT, and INOUT parameters, while functions only support IN parameters.

How do I call a stored procedure from Java?

Use CallableStatement from the java.sql package. Obtain a Connection, call con.prepareCall("call procedure_name(?,?,?)"), set the input parameters with setInt, setString, or setFloat, then call executeUpdate for DML operations or executeQuery for SELECT statements.

Conclusion

In this tutorial, you learned the fundamentals of PL/SQL, why stored procedures are useful, the differences between Statement, PreparedStatement, and CallableStatement, and how to create and call MySQL stored procedures for insert, update, delete, fetch-all, and fetch-one operations using JDBC.

Related topics: PL/SQL Basics, MySQL Stored Procedures, JDBC CallableStatement, PreparedStatement vs Statement, Procedure vs Function, Java Database Programming, JDBC Batch Processing

Tags:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)