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.
Batch process |
0 Comments