How do I execute stored procedure?

Stored procedure are user-generated functions or procedures that, once created in the database, can be called by the client applications, such as Java application. In this example we’ll demonstrate how to use the JDBC java.sql.CallableStatement to call a stored procedure.

The store procedure in this example is just for inserting a record into table. Just like the PreparedStatement interface, in the CallableStatement we can pass the parameter to the procedure by calling the appropriate setXXX(index, value) method.

package org.kodejava.example.sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class CallableStatementDemo {
    private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
    private static String username = "kodejava";
    private static String password = "welcome";

    public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(url, username, password);

            // Create a CallableStatement to execute the CREATE_USERS procedure
            CallableStatement stmt = conn.prepareCall("{call CREATE_USERS (?, ?, ?, ?, ?, ?)}");

            // Defines all the required parameter values.
            stmt.setString(1, "kodejava");
            stmt.setString(2, "welcome");
            stmt.setString(3, "Kode");
            stmt.setString(4, "Java");
            stmt.setString(5, "Denpasar - Bali");
            stmt.setString(6, "webmaster[at]kodejava[.]org");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }
}

Below is the stored procedure that was executed in the code above.

CREATE OR REPLACE PROCEDURE CREATE_USERS (username IN VARCHAR2, password IN VARCHAR2, firstName IN VARCHAR2, lastName IN VARCHAR2, address IN VARCHAR2, email IN VARCHAR2) AS
BEGIN
    INSERT INTO users (username, password, first_name, last_name, address, email) VALUES (username, password, firstName, lastName, address, email);
END CREATE_USERS;

Wayan

Programmer, runner, recreational diver, live in the island of Bali, Indonesia. Mostly programming in Java, Spring Framework, Hibernate / JPA. Support me by donating.

Leave a Reply