• September 28, 2014
  • JDBC
  • Comments Off on JDBC CallableStatement Stored procedure batch update example

JDBC CallableStatement Stored procedure batch update example

The JDBC CallableStatement is used to execute the store procedure and functions. Let us study JDBC CallableStatement by batch update example.

Example:

insertEMPLOYEE Procedure

JDBC CallableStatement Stored procedure IN parameter example.
CREATE OR REPLACE PROCEDURE insertEMPLOYEE(
	   e_id IN EMPLOYEE.EMPLOYEE_ID%TYPE,
	   e_name IN EMPLOYEE.NAME%TYPE,
	   e_salary IN EMPLOYEE.SALARY%TYPE)
IS
BEGIN
 
  INSERT INTO EMPLOYEE ("EMPLOYEE_ID", "NAME", "SALARY") 
  VALUES (e_id, e_name, e_salary);
 
  COMMIT;
 
END;

JDBCTest.java

import java.sql.CallableStatement;
import java.sql.Connection;
import com.javawithease.util.JDBCUtil;
 
/**
 * This class is used to batch update in DB table
 * using CallableStatement.
 * @author javawithease
 */
public class JDBCTest {
	public static void main(String args[]){
		Connection conn = null;
		CallableStatement callableStatement = null;
		String proc = "{call insertEMPLOYEE(?,?,?)}";
		try{			
			//get connection
			conn = JDBCUtil.getConnection();
 
			//create callableStatement
			callableStatement = conn.prepareCall(proc);
 
			callableStatement.setInt(1, 7);
			callableStatement.setString(2, "Harish Yadav");
			callableStatement.setInt(3, 50000);
			callableStatement.addBatch();
 
			callableStatement.setInt(1, 8);
			callableStatement.setString(2, "Abhishek Rathor");
			callableStatement.setInt(3, 50000);
			callableStatement.addBatch();
 
			//execute query
			callableStatement.executeBatch();
 
			//close connection
			callableStatement.close();
			conn.close();
 
		     System.out.println("Records inserted successfully.");
		}catch(Exception e){
			e.printStackTrace();
		}
	}	
}

JDBCUtil.java

import java.sql.Connection;
import java.sql.DriverManager;
 
/**
 * This is a utility class for JDBC connection.
 * @author jawithease
 */
public class JDBCUtil {
	//JDBC and database properties.
	private static final String DB_DRIVER = 
		           "oracle.jdbc.driver.OracleDriver";
	private static final String DB_URL = 
		        "jdbc:oracle:thin:@localhost:1521:XE";
	private static final String DB_USERNAME = "system";
	private static final String DB_PASSWORD = "oracle";
 
	public static Connection getConnection(){
		Connection conn = null;
		try{
			//Register the JDBC driver
			Class.forName(DB_DRIVER);
 
			//Open the connection
			conn = DriverManager.
			getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
 
			if(conn != null){
			   System.out.println("Successfully connected.");
			}else{
			   System.out.println("Failed to connect.");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}	
}

Output:

Successfully connected.
Records inserted successfully.

Download this example.
 
Next Topic: JDBC batch processing.
Previous Topic: JDBC CallableStatement Stored procedure OUT parameter example.

Related Topics:

JDBC CallableStatement Stored procedure IN parameter example.
JDBC CallableStatement Stored procedure OUT parameter example.
JDBC CallableStatement Stored procedure batch update example.

 


Comments are closed.