In this tutorial we will learn how to call Oracle database STORED PROCEDURE and executing BATCH CallableStatement program/example in java JDBC.
JDBC tutorial - What is java.sql.CallableStatement in java
The java.sql.CallableStatement is an interface which can be used to execute SQL stored procedures in java.
In this post we will execute batch statement by using following methods >
- addBatch()
- executeBatch()
--Before executing java program execute these database scripts >
create table EMPLOYEE(ID number(4), NAME varchar2(22), SALARY number(8), CREATION_DATE date);
--Procedure accepts id, name, salary and creation_date as IN parameter inserts them in EMPLOYEE table
create or replace PROCEDURE MYPROC_EMPLOYEE_INSERT_IN(
p_id IN EMPLOYEE.ID%TYPE,
p_name IN EMPLOYEE.NAME%TYPE,
p_salary IN EMPLOYEE.SALARY%TYPE,
p_creation_date IN EMPLOYEE.CREATION_DATE%TYPE)
IS
BEGIN
INSERT INTO EMPLOYEE (ID, NAME, SALARY, CREATION_DATE)
VALUES (p_id, p_name, p_salary, p_creation_date);
COMMIT;
END;
|
--If table already exists then execute the DROP command >
drop table EMPLOYEE;
Must read : Execute database STORED PROCEDURE - OUT parameter and IN OUT parameter || call FUNCTION from java
Example/ Full Programs JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class CallableStatementExampleProcedureIN {
public static void main(String... arg) {
Connection con = null;
CallableStatement callableStmt = null;
try {
// registering Oracle driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
callableStmt = con.prepareCall("{call MYPROC_EMPLOYEE_INSERT_IN(?,?,?,?)}");
//First batch
callableStmt.setInt(1, 11);
callableStmt.setString(2, "ankit");
callableStmt.setString(3, "12000");
callableStmt.setDate(4, new java.sql.Date( new java.util.Date().getTime() ));
callableStmt.addBatch();
//second batch
callableStmt.setInt(1, 12);
callableStmt.setString(2, "javaMadeSoEasy");
callableStmt.setString(3, "13000");
callableStmt.setDate(4, new java.sql.Date( new java.util.Date().getTime() ));
callableStmt.addBatch();
// submit batch statements to database Stored procedure
callableStmt.executeBatch();
System.out.println("Stored procedure executed successfully, "
+ "data has been inserted in Employee table");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
if(callableStmt!=null) callableStmt.close(); //close CallableStatement
if(con!=null) con.close(); // close connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/*OUTPUT
Connection established successfully!
Stored procedure executed successfully, data has been inserted in Employee table
*/
|
Before execution of above program EMPLOYEE table will look like this >
After execution of above program EMPLOYEE table will look like this >
In this tutorial we learned how to call Oracle database STORED PROCEDURE and executing BATCH CallableStatement program/example in java JDBC.
RELATED LINKS>
Oracle 11g and SQL Developer installation and setup on 32/64bit windows - explained step by step with screenshots
java.sql.PreparedStatement - using executeUpdate and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE