Excel to Oracle - Batching - POI JDBC Example Program

Oracle Batching - Introduction

We are exploring different methods to insert data from Excel to Oracle in Java using Apache POI / JDBC . In part 1 of this post, we saw a basic JDBC Example of inserting data in Java. In Part 2, we discussed standard batching approach where we bulk inserted data from Excel to Oracle. In this post, we will discuss Oracle batching as the target database is Oracle. We use OraclePreparedStatement for this. Let us get started in this example. You should refer to Part 1 to get the input data / worksheet to work with the examples.

Excel to Oracle - Oracle Batching - Java POI JDBC Example Program

In Oracle batching, we use OraclePreparedStatement to set the batch size at the beginning where we create the prepared statement. Once this is done, you can read the data from the Excel file using POI normally. When you call statement.execute, the batch size is checked and executed only if the batch size is reached. Even if you have some records in your excel document, that does not meet a batch length, they will be processed when you commit the transaction. There is no change to the way you read data from the Excel workbook. The code example is provided below:

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.util.*;
import java.sql.*; 
import oracle.jdbc.*;
public class oracleBatching {  
        public static void main(String[] args) throws Exception{                
                /* Create Connection objects */
                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "hr", "hr");
                PreparedStatement sql_statement = null;
                String jdbc_insert_sql = "INSERT INTO XLS_POI"
                                + "(KEYWORD, TOTAL_COUNT) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                // we set batch size as 5. You should increase this 
                // depending on the number of rows in your Excel document
                ((OraclePreparedStatement)sql_statement).setExecuteBatch(5);
                /* We should now load excel objects and loop through the worksheet data */
                FileInputStream input_document = new FileInputStream(new File("xls_to_oracle.xls"));
                /* Load workbook */
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
                /* Load worksheet */
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
                // we loop through and insert data
                Iterator<Row> rowIterator = my_worksheet.iterator(); 
                while(rowIterator.hasNext()) {
                        Row row = rowIterator.next(); 
                        Iterator<Cell> cellIterator = row.cellIterator();
                                while(cellIterator.hasNext()) {
                                        Cell cell = cellIterator.next();
                                        switch(cell.getCellType()) { 
                                        case Cell.CELL_TYPE_STRING: //handle string columns
                                                sql_statement.setString(1, cell.getStringCellValue());                                                                                     
                                                break;
                                        case Cell.CELL_TYPE_NUMERIC: //handle double data
                                                sql_statement.setDouble(2,cell.getNumericCellValue() );
                                                break;
                                        }
                                       
                                }
                                
                                // though we call execute here, it is done only 
                                //when the batch size is reached.
                               try {
                                       sql_statement.execute();
                                } catch(BatchUpdateException e) {
                                //you should handle exception here if required
                                }
                }               
                input_document.close();
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                // unproccesed batch would get processed anyway.
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

This program does the same as the previous two, but is specific to Oracle database. When executed, it inserted all the five records in the Excel document to Oracle DB table successfully. 

That completes the tutorial series, where we explored different approaches to insert data from Excel to Oracle in Java using POI / JDBC. You can write an XLSX example on the same lines. We would have written an example for this for sure, make sure you check it out in the related post section below. If you have any questions, you can post it in the comments section.

This Series:Insert data from Excel to Oracle Using Java, Apache POI and JDBC
Keywords: Excel, Oracle, Excel to Oracle Using Java, XLS, XLSX, POI,  JDBC
All Parts:
Part - 1: Apache POI / JDBC - Basic Example to Insert Data from Excel Document
Part - 2: JDBC Bulk Insert Approach to Load data into Oracle from Excel
Part - 3: Oracle Batching - Excel data to Oracle - Java POI Example