Bulk Load CSV File Data to Oracle Java Example Program

CSV Bulk Load to Oracle - Introduction

This tutorial discusses how to do bulk insert of CSV file data to Oracle in Java, by using OpenCSV  / JDBC approach with an example program. In the last post, we discussed how to do a row-by-row insert of CSV file data to Oracle using Open CSV / JDBC in Java. We mentioned that this row by row processing can be slow when the amount of data in the CSV file is huge. In such cases, following a bulk insert approach could be very helpful. Let us setup an input table / CSV file data and discuss how to perform bulk insertion in Java with suitable examples.

Input table / CSV File Data

We cannot produce a huge file here. We will have a short file but still cover the batch insert approach. Here is our input Oracle table / CSV file data.

CREATE TABLE CSV_2_ORACLE
(
USER_ID VARCHAR2(20),
USER_AGE NUMBER
)

CSV File data is provided below

A,40
B,34
C,50
D,30
E,44
F,35
G,60

Bulk Insert - CSV to Oracle - Java Example Program

We will follow standard batching approach available in JDBC to push the data in the CSV file as a bulk approach to the Oracle table. The complete Java program to accomplish this is provided below:

import java.io.*;
import au.com.bytecode.opencsv.CSVReader;
import java.util.*;
import java.sql.*; 
public class bulkInsertCSVtoOracle {  
        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 CSV_2_ORACLE"
                                + "(USER_ID, USER_AGE) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* Read CSV file in OpenCSV */
                String inputCSVFile = "inputdata.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));         
                String [] nextLine; 
                int lnNum = 0; 
                //loop file , add records to batch
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;
                        /* Bind CSV file input to table columns */
                        sql_statement.setString(1, nextLine[0]);
                        /* Bind Age as double */
                        /* Need to convert string to double here */
                        sql_statement.setDouble(2,Double.parseDouble(nextLine[1]));
                        // Add the record to batch
                        sql_statement.addBatch();
                }                       
                //We are now ready to perform a bulk batch insert              
                int[] totalRecords = new int[7];
                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 in bulk from CSV file " + totalRecords.length);                
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

We have used PreparedStatement again, but instead of executing the statement, we used addBatch method to batch the execution in bulk. Once we have done this for all the records, we call executeBatch() method to bulk load the records to Oracle table. Easy. There is also a section of code that you can use for exception handling. This example is consistent across all database platforms and you should be able to use it for any database provided you have the right driver JAR files viz Oracle, MySQL etc. The output of this program is shown below. As expected, it has loaded all the records in our CSV file.

Bulk Load CSV Data to Oracle - Java Open CSV JDBC Batch - Example Program - Output

That completes our tutorial on bulk loading of CSV data into Oracle in Java. If you have any questions, you can post it in the comments section. Stay connected to our blog.