Batch update or insert in data base

how to insert bulk data from a CSV file into an Oracle database using JDBC. Here’s a step-by-step explanation and a few important considerations:

Explanation of the Code:

Importing Libraries:

· The code uses au.com.bytecode.opencsv.CSVReader for reading the CSV file. Ensure you have the OpenCSV library in your project.

· Java SQL package is used for database connection and operations.

Database Connection:

· The code connects to an Oracle database using JDBC. The connection URL is specified as jdbc:oracle:thin:@//localhost:1521/demo.

· Replace the connection details ("kartik", "kartik") with your actual database username and password.

Prepared Statement for Batch Insert:

· A PreparedStatement is created for inserting data into the employee table.

· The SQL statement "insert into employee (name, city, phone) values (?, ?, ?)" is used, with placeholders for the name, city, and phone number.

Reading and Inserting Data:

· The CSV file is read line by line using CSVReader.

· Each line is split into columns (nextLine[0], nextLine[1], nextLine[2]), which are then set into the PreparedStatement.

· The PreparedStatement is added to a batch.

Executing Batch Insert:

· The batch is executed after every 100 records (as defined by batchSize).

· The code handles exceptions using a BatchUpdateException to track the success or failure of each record in the batch.

Exception Handling:

· The exception handling logic checks the result of each update. It increments counters (successCount, failCount, notAavailable) based on the outcome.

Closing Resources:

· After all records are processed, the PreparedStatement and Connection are closed, and the transaction is committed to the database.

Error Handling:

· It's good practice to include more detailed logging, especially within the catch block, to understand why specific records failed.

· Consider using a logging framework like Log4j or SLF4J for better logging control.

Resource Management:

· The code can be enhanced by using the try-with-resources statement to ensure that PreparedStatement, Connection, and CSVReader are closed automatically.

Transaction Management:

· The transaction should ideally be rolled back in case of any failure unless you want partial inserts in the database.

Performance Optimization:

· Depending on the size of the CSV file and the database load, you might need to tune the batchSize or explore bulk loading utilities provided by the database (e.g., Oracle SQL*Loader) for very large datasets.

java

package com.kartik.pdf;

import java.io.*;

import au.com.bytecode.opencsv.CSVReader;

import java.sql.*;

public class BulkDataInsertCSVtoDataBase {

public static void main(String[] args) {

String inputCSVFile = "inputCSVData.csv";

String dbUrl = "jdbc:oracle:thin:@//localhost:1521/demo";

String dbUser = "kartik";

String dbPassword = "kartik";

String sql = "INSERT INTO employee (name, city, phone) VALUES (?, ?, ?)";

final int batchSize = 100;

int successCount = 0;

int failCount = 0;

int notAavailable = 0;

try (Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);

PreparedStatement stmt = conn.prepareStatement(sql);

CSVReader reader = new CSVReader(new FileReader(inputCSVFile))) {

conn.setAutoCommit(false); // Start transaction

String[] nextLine;

int count = 0;

while ((nextLine = reader.readNext()) != null) {

stmt.setString(1, nextLine[0]);

stmt.setString(2, nextLine[1]);

stmt.setInt(3, Integer.parseInt(nextLine[2]));

stmt.addBatch();

if (++count % batchSize == 0) {

try {

int[] totalRecords = stmt.executeBatch();

countSuccessFail(totalRecords, successCount, notAavailable, failCount);

} catch (BatchUpdateException e) {

int[] totalRecords = e.getUpdateCounts();

countSuccessFail(totalRecords, successCount, notAavailable, failCount);

}

int[] remainingRecords = stmt.executeBatch(); // Execute remaining batch

countSuccessFail(remainingRecords, successCount, notAavailable, failCount);

conn.commit(); // Commit transaction

System.out.println("Batch Insert Complete. Success: " + successCount + ", Fail: " + failCount + ", Not Available: " + notAavailable);

} catch (Exception e) {

e.printStackTrace();

}

private static void countSuccessFail(int[] records, int successCount, int notAavailable, int failCount) {

for (int record : records) {

if (record >= 0) {

successCount++;

} else if (record == Statement.SUCCESS_NO_INFO) {

notAavailable++;

} else if (record == Statement.EXECUTE_FAILED) {

failCount++;

}

This version includes better resource management and transaction handling, ensuring that the database interaction is more reliable and easier to maintain.