Bulk insert from a csv to a table in an Oracle DB using Java

Question

I am attempting to insert a table in an Oracle DB using java. I am reading a csv file line by line using OpenCSV. The csv is about 50000 rows and 9 columns. Here is some of my code:

            /* Create Connection objects */
            Class.forName ("oracle.jdbc.OracleDriver"); 
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@HoSt", "UsErNaMe", "PaSsWoRd");
            PreparedStatement sql_statement = null;
            String jdbc_insert_sql = "INSERT INTO METATADA_AUTOSYS"
                            + "(MACH_NAME,JOB_NAME,SCRIPT_COMMAND,APPLICATION_NAME,JOB_ID,STATUS,CREATE_DATE,LAST_START_DT,LAST_END_DT) VALUES"
                            + "(?,?,?,?,?,?,?,?,?)";
            sql_statement = conn.prepareStatement(jdbc_insert_sql);
            /* Read CSV file in OpenCSV */
            String inputCSVFile = "C:/Users/conwacx/Desktop/meta_auto_v3/Autosys_Metadata.csv";
            CSVReader reader = new CSVReader(new FileReader(inputCSVFile));         
            String [] nextLine; 
            int lnNum = 0; 
            int batchSize = 5000;
            //loop file , add records to batch
            try{    
                while ((nextLine = reader.readNext()) != null) {
                    lnNum++;
                    /* Bind CSV file input to table columns */
                    sql_statement.setString(1, nextLine[0]);
                    sql_statement.setString(2,nextLine[1]);
                    sql_statement.setString(3,nextLine[2]);
                    sql_statement.setString(4,nextLine[3]);
                    sql_statement.setString(5,nextLine[4]); //setInt(Integer.parseInt(nextLine[4].trim());
                    sql_statement.setString(6,nextLine[5]);
                    sql_statement.setObject(7, nextLine[5]);
                    sql_statement.setString(8,nextLine[7]);
                    sql_statement.setString(9,nextLine[8]);
                    sql_statement.addBatch();
                    // Add the record to batch
                    if (++batchSize % 5000 == 0){
                            sql_statement.executeBatch();
                    }



            }
            sql_statement.executeBatch();
        }
            catch(SQLException e){
                e.printStackTrace();
            }                 
            //Perform a bulk batch insert              
            int[] totalRecords = new int[7];
            try {
                    totalRecords = sql_statement.executeBatch();
            } catch(BatchUpdateException e) {
                    //handle exception for failed records here
                    totalRecords = e.getUpdateCounts();
            } catch(SQLException ex){
                    ex.printStackTrace();
            }
            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();
    }

I am not receiving an error while running this. It is printing: Total records inserted in bulk from CSV file 0 The table is not being updated with the new values in Oracle. Any suggestions?

Answer 1

Execute sql_statement.executeBatch() only once if your batch size is reached.
executeBatch() is returning an array with the results (how many rows are affected).
So you have to add each element of the array to compute the total count.
The condition for execute the batch is also wrong.

I cant proof, but i would change your example like this (only changed section):

public void insertData() throws ClassNotFoundException, SQLException, IOException {
  /* Create Connection objects */
  Class.forName("oracle.jdbc.OracleDriver");
  String jdbc_insert_sql = "INSERT INTO METATADA_AUTOSYS"
   + "(MACH_NAME,JOB_NAME,SCRIPT_COMMAND,APPLICATION_NAME,JOB_ID,STATUS,CREATE_DATE,LAST_START_DT,LAST_END_DT) VALUES"
    + "(?,?,?,?,?,?,?,?,?)";

  int totalRecords = 0;
  final int batchSize = 5000;
  /* Read CSV file in OpenCSV */
  String inputCSVFile = "C:/Users/conwacx/Desktop/meta_auto_v3/Autosys_Metadata.csv";
  try (CSVReader reader = new CSVReader(new FileReader(inputCSVFile))) {
    try (Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@HoSt", "UsErNaMe", "PaSsWoRd")) {
      try (PreparedStatement sql_statement = conn.prepareStatement(jdbc_insert_sql);) {
        String[] nextLine;
        int lnNum = 0;
        // loop file , add records to batch
        try {
          while ((nextLine = reader.readNext()) != null) {
            lnNum++;
            /* Bind CSV file input to table columns */
            sql_statement.setString(1, nextLine[0]);
            sql_statement.setString(2, nextLine[1]);
            sql_statement.setString(3, nextLine[2]);
            sql_statement.setString(4, nextLine[3]);
            sql_statement.setString(5, nextLine[4]);
            sql_statement.setString(6, nextLine[5]);
            sql_statement.setObject(7, nextLine[5]);
            sql_statement.setString(8, nextLine[7]);
            sql_statement.setString(9, nextLine[8]);
            sql_statement.addBatch();
            // Add the record to batch
            if (lnNum >= batchSize) {
              // Perform a bulk batch insert
              totalRecords += doExecute(sql_statement);
              lnNum = 0;
            }
          }
          // insert the last rows
          if ( lnNum >= 0 ) {
            totalRecords += doExecute(sql_statement);
          }
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      System.out.println("Total records inserted in bulk from CSV file " + totalRecords);
      /* COMMIT transaction */
      conn.commit();
    }
  }
}

private int doExecute(PreparedStatement sql_statement) {
  int totalRecords = 0;
  int[] results = null;
  try {
    results = sql_statement.executeBatch();
    for (int i = 0; i < results.length; i++) {
      totalRecords += results[i];
    }
  } catch (BatchUpdateException e) {
    // handle exception for failed records here
    results = e.getUpdateCounts();
    for (int i = 0; i < results.length; i++) {
      totalRecords += results[i];
    }
  } catch (SQLException ex) {
    ex.printStackTrace();
  }
  return totalRecords;
}