JAVA-MANI.BLOGSPOT.COM

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.


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;