JDBC Batch Processing And Stored Procedure Tutorial

This tutorial provides a complete understanding of JDBC Batch Processing and Java Stored Procedure with sample Java examples:

In the JDBC Exception Handling tutorial of the JDBC tutorial series, we learned ways to handle SQL Exceptions with the help of programming examples.

In this tutorial, we will discuss methods to do batch processing in Java using JDBC driver. We will also learn about how to create stored procedures and call it from a Java program.

Let’s start with understanding batch processing and its advantages.

JDBC Batch Processing

It is the process of executing several SQL statements in one transaction. This process reduces communication time and increases performance. It makes processing a large amount of data much easier.

Advantages Of Batch Processing

Batch Processing aims at improving performance and data consistency.

Performance

Consider the scenario where multiple (say 5) entries are to be added to a table from a JAVA program. The straightforward approach will be opening a connection to the database, writing INSERT queries, and executing each query using Statement or PreparedStatement.

This approach will increase the network trips to the database and as a result, it will lead to poor performance. Using Batch processing, we can perform this operation in one call.

Data Consistency

In some cases, we need to insert/update data in multiple tables. This will lead to an interrelated transaction where the sequence of queries being inserted or updated is important. Any errors that occur during execution would result in a rollback of the data inserted by previous queries if any.

Example:

#1) Table ‘EMPLOYEE_DETAILS’ has 3 columns: ID, Name, and Role of the employee.

statement.execute("INSERT INTO EMPLOYEE_DETAILS(ID, NAME, ROLE) "
+ "VALUES ('1','EMPLOYEE_NAME1','ROLE1')");

#2) Table ‘EMPLOYEE_ADDRESS’ has 2 columns: EMP ID and Address

statement.execute("INSERT INTO EMPLOYEE_ADDRESS( EMP_ID, ADDRESS) "
 + "VALUES ('1','ADDRESS1')");

A problem may occur in the above example when the first statement executes successfully, but the second statement fails. In this situation, there is no rollback of the data inserted by the first statement. This leads to data inconsistency.

We can achieve data consistency by committing the transaction at the end or performing a rollback in case of any exceptions. But, in order to achieve this, the DB has to be hit repeatedly for every statement.

On the other hand, in batch processing, only when all the queries inside a batch executes successfully, the data will be committed. Otherwise, it won’t.

How To Perform Batch Processing

We can perform batch processing using the addbatch() and the executeBatch() methods which are available in both Statement and PreparedStatement classes.

In this tutorial, all programs are written in Java. We have used Java 8 version and Oracle DB.

=> Click here to download the Oracle software

=> Click here to download the Java version 8

In the following example, we will see how to do batch processing elaborately. It has the step-by-step Java installation process.

Data in the EMPLOYEE table before inserting data:

Java Program

package com.STH.JDBC;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ExecuteBatch_Example {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//Inserting the data in EMPLOYEE Table using the following query
String insert_query1 = "insert into employee values(?,?,?)";
Class.forName("oracle.jdbc.driver.OracleDriver");  
Try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{ PreparedStatement pstatemnt1 = conn.prepareStatement(insert_query1);

//Setting values for the 1st person in EMPLOYEE Table
pstatemnt1.setInt(1,10001);
pstatemnt1.setString(2, "Bush");
pstatemnt1.setString(3, "William ");

//Adding the 1st insert query into batch
pstatemnt1.addBatch();

//Setting values for the 2nd person in EMPLOYEE Table
pstatemnt1.setInt(1,10002);
pstatemnt1.setString(2, “Bush");
pstatemnt1.setString(3, "George");

//Adding the 2nd insert query into batch
pstatemnt1.addBatch();

//Setting values for the 3rd person in EMPLOYEE Table
pstatemnt1.setInt(1,10003);
pstatemnt1.setString(2, "Bond");
pstatemnt1.setString(3, "James");

//Adding the 3rd  insert query into batch
pstatemnt1.addBatch();

//Executing the executeBatch method
int No_of_Afffected_Rows[]=  pstatemnt1.executeBatch();
//After inserting the data, displaying no. of rows affected
System.out.println("No of rows affected = " +No_of_Afffected_Rows.length);}
catch (SQLException e) {
e.printStackTrace();}
} }		

OUTPUT:

Data in EMPLOYEE Table after insertion of the data:

Explanation:

In the above program, we have inserted data of 3 employees in one call using batch operation.

1. Create one insert query to pass the column values.
2. Open the connection and create a preparedStatement object using the connection object and call the prepareStatement method.
3. Then set the values for the 1^st employee using setXXX methods and call the addBatch() method to add the new row in the batch.
4. Like this, add the values for 2^nd and 3^rd employees. After adding the queries into the addBatch() method, we should call the executeBatch() method using the preparedStatement object.
5. executeBatch() method inserts the 3 employee’s data in one call.
6. Check the EMPLOYEE table whether the data has been inserted properly or not.

Java Stored Procedures

A Stored Procedure is a group of SQL statements that form a single unit and perform a specific task. They will be used to perform a set of operations or questions to execute on a database server. It can be compiled and executed with different parameters and results.

Each procedure has its unique name to be referred to. This subprogram unit is stored as a Database object in DB.

A subprogram is nothing but a procedure, and it should be created manually as we want and store it as a DB object.

Stored Procedures are standalone blocks of a program that we can store in DB. By using the name of the stored procedure, we can call and execute it. It is mainly used to execute a process in PL/SQL. The procedure may have nested blocks or may be nested inside the other blocks.

The stored procedure has 3 parts:

1. Declaration part (optional): In this part, we can declare the variables, constants, cursors, etc., It is an optional part. Based on the requirements, we can use it.
2. Execution part: This part contains the main business logic of the procedure. Normally it will have a block of SQL statements.
3. Exceptional Handling part (optional): In this part, we can handle the exception which may occur due to the Execution part code. It is also optional.

Based on the requirements, we can create a procedure. We can pass or retrieve the values from the parameters.

There are three types of parameters available in Stored Procedures. They are:

1. IN: It is used to pass the input value to the stored procedure. The stored procedure will use the input parameter in the program as a read-only variable. The value cannot be changed inside the subprograms. Oracle uses IN as the default mode of the parameter. It is the default parameter.
2. OUT: It is used to return or get the value from the Stored Procedure after the execution. It is a read-write variable inside the subprograms. The value can be changed inside the subprograms.
3. IN/OUT: It is used to pass the input values to the stored procedure and return or get the values from the procedure as well. It is both readable and writable. We can read and modify it.

RETURN

We will use the return keyword to give the control back to the main program, such as the Java program. When the procedure finds the RETURN keyword, it will exit from the execution and skip the code or statement after it.

How To Call Stored Procedure From Java

We have a CallableStatement interface in Java to call Stored Procedure. CallableStatement interface object can be created using the prepareCall() method of the Connection interface and after that, we should call executeQuery() method to execute the Stored Procedure in Java program.

Before writing the Java program to implement this, we should create stored procedures to use it in the program.

The following is the syntax to call stored procedures in the Java program:

Steps To Create Stored Procedures

#1) Create the procedure in the DB server. Here we are using Oracle DB.

#2) Syntax for creating full procedure:

We can write the stored procedure in Java code as well.

#3) Pass the IN and OUT parameters to use it in the procedure.

#4) AS/ IS keyword should be mentioned. If we are adding another procedure in the new procedure, use the IS keyword or else AS keyword if the procedure is standalone.

#5) Declare the variables, it’s not mandatory, based on the requirement we can create.

#6) Then BEGIN the procedure using the BEGIN keyword and then write the SQL statements or queries to be performed in the procedure.

#7) Next, we can handle the exception in the Exception part. It is also not mandatory to mention.

#8) End the procedure by mentioning the END keyword and the procedure name.

We can create the procedure in Oracle and save it with a unique name and call that procedure from the Java program. We can create the procedure and call that procedure in Java as well.

Create a procedure in Oracle, Save it, and call the procedure in Java Program.

#1) Open the Database server. Here we are using the Oracle DB server.

#2) Right-click on the Procedure folder and click on the New Procedure option.

#3) It will ask for the name of the procedure and parameter details.

Note: We can give the parameter details while writing the procedure as well.

#4) Write the procedure using the steps which we have already discussed in this tutorial and save the procedure with the unique name.

The procedure in the screenshot will display the output in DB. We can change it to display it in the Java program also. For that, we need to use OUT parameters.

#5) Run the procedure by clicking the run button

#6) Enter the value in the Input Value column. It will display the data for the given value.

Till now, we have seen how to create and execute the procedure in the DB console itself.

Create the procedure in DB. Call it and display the data in the Java console.

Create the following procedure using the above steps and save it with the name “DISPLAY_EMPLOYEE_DETAILS”.

Java Stored Procedure Example Program

package com.STH.JDBC;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class StoredProcedureExample {
public static void main(String[] args) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");  
//Connecting to Oracle DB	
Try
(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{
// Creating prepared Statement
CallableStatementCallStmt = conn.prepareCall("{call DISPLAY_EMPLOYEE_DETAILS(?,?,?,?)}");
//Passing Input Parameter
CallStmt.setInt(1,1001);
//Retrieving the Output Parameters values
CallStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
CallStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
CallStmt.registerOutParameter(4, java.sql.Types.VARCHAR);
//Calling the execute to execute the procedure and retrieve the data
CallStmt.execute();
System.out.println("First Name: "+ CallStmt.getString(2)+"\n Last Name: "+ CallStmt.getString(3) + "\n Email: "
+ CallStmt.getString(4));
}catch (SQLException e) {
e.printStackTrace();
} } }

OUTPUT:

Explanation:

In the above program, what we have done is we have created one procedure and saved it in Oracle DB. Then called that procedure using CallableStatement and displayed the data in Java Console.

1. Create the procedure and save it in Oracle DB.
2. In the Java program, open the DB connection and call the prepareCall method using the connection and CallableStatement objects.
3. Pass the input parameter value using the setXXX method.
4. Retrieve the output parameter values using the registerOutParameter method. In this method, we should pass the parameter index value and the data type of the parameter. It will store the values in the parameter index.
5. Using the getXXX methods, we can retrieve the data and display it in the Java console.

Create the procedure and execute it in the Java program itself.

Java Program

package com.STH.JDBC;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class StoredProcedureExample1 {
public static void main(String[] args) throws ClassNotFoundException {
String Stored_Procedure =  "CREATE OR REPLACE PROCEDURE UPD_EMPLOYEE_DETAILS \n" 
				+"( \n" +
				" PARAM1 IN NUMBER,\n " +
				" PARAM2 IN NUMBER \n "+
				") IS\n "+
				" BEGIN \n"+
				"UPDATE EMPLOYEE_DETAILS SET EMPNUM= PARAM2 WHERE 					
 EMPNUM = PARAM1;\n"+
				"COMMIT;\n"+
				"END UPD_EMPLOYEE_DETAILS;\n";
Class.forName("oracle.jdbc.driver.OracleDriver");  
//Connecting to Oracle DB	
try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{// Creating prepared Statement
Statement stmt = conn.createStatement();
CallableStatementCallStmt = conn.prepareCall("{call UPD_EMPLOYEE_DETAILS(?,?)}");
stmt.execute(Stored_Procedure);
//Setting the values to pass the procedure
CallStmt.setInt(1,1010);
CallStmt.setInt(2, 10010);
//Calling executeUpdate method to update the values using Procedure
CallStmt.executeUpdate();
System.out.println(" Successfully Updated  "  );
}
catch (SQLException e) {
e.printStackTrace();
} } }

OUTPUT:

Data in Employee_details table before the execution of the program:

Data in Employee_details table after the execution of the program: 

Explanation:

In the above program, we store the procedure code as a string.

 
String Stored_Procedure = "CREATE OR REPLACE PROCEDURE UPD_EMPLOYEE_DETAILS \n" 
+"( \n" +" PARAM1 IN NUMBER,\n " +" PARAM2 IN NUMBER \n "+ ") IS\n "+" BEGIN 
\n"+"UPDATE EMPLOYEE_DETAILS SET EMPNUM= PARAM2 WHERE EMPNUM = 
PARAM1;\n"+"COMMIT;\n"+"END UPD_EMPLOYEE_DETAILS;\n";

1. Open the Oracle DB Connection and create the statement object using the connection object.
2. Call the createStatement method using statement object, because we are creating the procedure in Java code.
3. Call the procedure by using the syntax {call UPD_EMPLOYEE_DETAILS(?,?) to prepareCall method of the CallableStatement.
4. Since we are creating the procedure in Java code, we have to execute that “Create procedure Code”.
5. To execute that procedure, call the execute method using Statement object “stmt.execute(Stored_Procedure)”. This will create the procedure temporarily in the DB.
6. The scope of the procedure is the end of the execution of the program. After that, it won’t be available. Using setXXX methods, set the values to update in the Employee_Details table.
7. Call the executeUpdate method using callableStatement object. This method will update the values in the Empoyee_Details tale.
8. Check the Employee_details table whether the data has been updated properly.

Points to Remember:

• Batch processing improves performance and maintains data consistency.
• Executing several SQL statements in one transaction is known as Batch processing.
• Stored Procedure is the block of SQL statements used to perform business logic.
• We can pass the input parameter using IN keyword to the procedure and the OUT keyword for the output parameter.
• We can create a procedure in the DB server itself and temporarily using Java code also.

Frequently Asked Questions

Q #1) Which interface should be used to perform batch processing in JDBC?

Answer: Java package has Statement and PreparedStatement interfaces, which provide the methods to do batch processing.

Q #2) How do batch updates work in JDBC?

Answer: A JDBC batch update is a bunch of updates grouped and sent to the Database in one go rather than sending the updates one by one. Thus, it reduces the network traffic in the Database.

Q #3) How does Batch processing increase the performance?

Answer: Batch processing sends the data to the Database at one time (only 1 round trip) rather than sending one by one, and the Database might be able to execute some statements in parallel. Like this, it is increasing the performance of the application and saving time.

Q #4) What are the parameters accepted in stored procedures in JDBC?

Answer: There are three types of parameters–IN, OUT, and INOUT parameters. The IN parameter is to get the input value. The OUT parameter is for retrieving the output value. The INOUT parameter is used for both input and output.

Q #5) What are the methods available to execute a stored procedure in JDBC?

Answer: Using CallableStatement Interface, we can call the procedure. CallableStatement interface provides three methods to execute the stored procedures.

The three methods are:

• executeUpdate(): Use this method if the procedure doesn’t return any return values.
• executeQuery(): Use this method if the procedure returns only one resultset.
• execute(): Use this method if the procedure returns many resultsets or an unknown number of resultsets.

Conclusion

We have covered batch processing and stored procedures in this tutorial. In the modern world, high performance, data consistency, and reusability are the buzzwords for any popular application. Both batch processing and stored procedures play a very important role in implementing these features. Knowledge of these is inevitable for every software engineer.

<<PREV Tutorial | NEXT Tutorial>>