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?

did you do a commit? IDK if autocommit is on

OldProgrammer
– OldProgrammer

2020-05-18 20:00:16 +00:00
Commented May 18, 2020 at 20:00 — OldProgrammer
– OldProgrammer, Commented May 18, 2020 at 20:00
@OldProgrammer I did a connection.commit().

Ryan Conway
– Ryan Conway

2020-05-19 12:39:21 +00:00
Commented May 19, 2020 at 12:39 — Ryan Conway
– Ryan Conway, Commented May 19, 2020 at 12:39

MagelanM · Accepted Answer · 2020-05-18 21:17:43Z

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;
}

thank you for taking the time to respond. Unfortunately, this solution still did not solve the issue. The new data is not being inserted into the table.
the condition for the execution of executeBatch() was also wrong; code edited you need also a solution for the last loop pass, if the batchsize is not reached
@RyanConway Have you tested my second version of the example?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related