Insert Data into Oracle From Excel JDBC - POI Example Program

Excel to Oracle - JDBC - Introduction

In this post, we will take you through a simple example, in which we will read data from a .XLS spreadhsheet (string and number) using Apache POI library, and then batch insert that data into an Oracle database using JDBC connection.  We will utilize a technique in JDBC called batching, and we will use both standard batching and Oracle batching to insert data to our table, using the XLS input file.In later posts, we will write this into a servlet also. First, let us get started with a basic example.

Oracle Table Setup - Excel Input Data

To work with the examples in this tutorial series, we need a small Oracle table and an input .XLS file. We can then work out and test different approaches to insert data into this table. So, create the following table using the script provided:

CREATE TABLE XLS_POI
(
KEYWORD VARCHAR2(20),
TOTAL_COUNT NUMBER
)

For testing purposes, we will create an XLS sheet with five rows. Here is our input sheet.

Insert XLS Data to Oracle Using POI and JDBC - Input Excel Data

XLS to Oracle - Standard JDBC Insert - PreparedStatement - Java Example

We will examine different approaches to insert XLS data to Oracle. The first of them is the standard JDBC insert using PreparedStatement. Here is a Java program (fully commented) that uses PreparedStatement / JDBC / Apache POI to insert data from Excel to Oracle:

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 xlsToOracle {  
        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;
                                        }
                                       
                                }
                //we can execute the statement before reading the next row
                sql_statement.executeUpdate();
                }
                /* Close input stream */
                input_document.close();
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

The output of this program, as expected, inserted all the rows from the Excel document to Oracle. You should try and improve this code by handling exceptions properly. The code we have provided is to be used as a reference example only.

Insert XLS Data to Oracle Using POI and JDBC -Output Example

Ok, so we got a basic program now to insert data from XLS to Oracle using Java, JDBC and Apache POI. When the number of records is large, say in thousands, you should try and batch process this instead of following this approach. In the next tutorial, we will discuss how to batch insert records from Excel to Oracle using JDBC / Apache POI library. Use the navigation links below to get to the next article.

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