JDBC- STORED PROCEDURE - BATCH CallableStatement example in java


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

JDBC tutorial program- Insert/Store/save FILE in database

JDBC tutorial example- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


Difference between CLOB and CLOB data type in Oracle


JDBC tutorial program- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC tutorial program- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC tutorial program- Calling Oracle database FUNCTION - CallableStatement example in java


No comments:

Post a Comment