Batch Insert Excel to Oracle - POI JDBC Example Program

Batch Insert Excel to Oracle - Introduction

In the last part of this series, we explained how to insert data from Excel to Oracle in Java using JDBC / Apache POI, one row at a time. For performance reasons, you may have to perform the insert from Excel to Oracle as a batch insert job. This is especially useful when the number of records in your Excel document is ranging in thousands. There are two approaches you can follow for batch inserts, when it comes to batch insert. These are done through arrays in Java. These are standard batching and oracle batching. In this post, we will see how to insert your excel data to Oracle through standard batching, with an example program.

Standard batching - Excel to Oracle - Java POI JDBC Example Program

Before you go ahead, you should get a copy of the input table / input spreadsheet data from our basic JDBC example insert. (part - 1). In standard batching approach, we use PreparedStatement just as in Part 1. We loop through the Excel data normally. However, we don't insert data for every row we read from Excel. We rather, execute the insert job as a batch. This is shown 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.*; 
public class xlsToOracleStandardBatch {  
        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 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;
                                        }
                                       
                                }
                // Add the row to a batch processing - standard batch processing example
                sql_statement.addBatch();
                }
                //We are now ready to perform a bulk batch insert
                //our Excel has only 5 records, you should set this suitably
                int[] totalRecords = new int[5];
                try {
                        totalRecords = sql_statement.executeBatch();
                } catch(BatchUpdateException e) {
                        //you should handle exception for failed records here
                        totalRecords = e.getUpdateCounts();
                }
                System.out.println ("Total records inserted : " + totalRecords.length);
                /* Close input stream */
                input_document.close();
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

This program successfully inserted the five records into our Oracle table. You may get exceptions depending on your dataset. In that case, you have to handle your exceptions suitably. You should go for a batch mode of insert depending on the number of records that you have in your input Excel workbook. You can read more about batching and understand at the following link.

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