Insert Multiple Rows to Oracle Using Insert All

This example shows you how to bulk insert multiple records to a database using DataPipeline's JdbcWriter and OracleMultiRowInsertAllStatementInsert strategy.

Inserting multiple records into a database at once is useful for avoiding lots of individual network message round-trips and reduces the amount of data being logged in the transaction log.

OracleMultiRowInsertAllStatementInsert is one of two Oracle-specific strategies that DataPipeline supports.  It uses the INSERT ALL...INTO statement to add multiple records using a single SQL statement . Alternatively,  you can also use OracleMultiRowSelectUnionAllStatementInsert to perform multi-row insert in your Oracle database.

Input CSV

Account,LastName,FirstName,Balance,CreditLimit,Rating
101,Reeves,Keanu,9315.45,10000,A
102,Butler,Gerard,90,1000,B
103,Hewitt,Jennifer Love,0,17000,B
104,Pinkett-Smith,Jada,49654.87,100000,A
105,Murray,Bill,789.65,5000,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.jdbc.JdbcWriter;
import com.northconcepts.datapipeline.jdbc.insert.OracleMultiRowInsertAllStatementInsert;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.transform.BasicFieldTransformer;
import com.northconcepts.datapipeline.transform.TransformingReader;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class InsertMultipleRowsToOracleUsingInsertAll {

    private static final String DATABASE_DRIVER = "oracle.jdbc.OracleDriver";
    private static final String DATABASE_URL = "jdbc:oracle:thin:system/oracle@localhost:1521:xe";
    private static final String DATABASE_USERNAME = "system";
    private static final String DATABASE_PASSWORD = "oracle";
    private static final String DATABASE_TABLE = "CreditBalance";

    public static void main(String[] args) throws Throwable {
        Class.forName(DATABASE_DRIVER);

        Connection connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);
        createTable(connection);

        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-insert-records.csv"))
                .setFieldNamesInFirstRow(true);
        reader = transform(reader);

        DataWriter writer = new JdbcWriter(connection, DATABASE_TABLE, new OracleMultiRowInsertAllStatementInsert())
                .setDebug(true) // log generated SQL
                .setBatchSize(3); // set batch size

        Job.run(reader, writer);

        connection.close();
    }

    public static void createTable(Connection connection) throws Throwable {
        String createTableQuery = "CREATE TABLE CreditBalance ("
                + "Account NUMBER(10),"
                + "LastName VARCHAR(256),"
                + "FirstName VARCHAR(256),"
                + "Balance NUMBER(19,4),"
                + "CreditLimit NUMBER(19,4),"
                + "Rating CHAR,"
                + "PRIMARY KEY (Account)"
                + ")";

        Statement statement = connection.createStatement();
        statement.executeUpdate(createTableQuery);

        statement.close();
    }

    public static DataReader transform(DataReader reader) {
        return new TransformingReader(reader).add(
                new BasicFieldTransformer("Account").stringToInt(),
                new BasicFieldTransformer("FirstName"),
                new BasicFieldTransformer("LastName"),
                new BasicFieldTransformer("Balance").stringToFloat(),
                new BasicFieldTransformer("CreditLimit").stringToFloat(),
                new BasicFieldTransformer("Rating").stringToChar());
    }
}

Code Walkthrough

1. Register database driver oracle.jdbc.OracleDriver.
2. Create a table named CreditBalance in the connected Oracle database.
3. Read records from an input CSV file credit-balance-insert-records.csv.
4. setFieldNamesInFirstRow(true) is invoked to specify that the first row of the input CSV file should be used as field names (disabled by default). If this method is not invoked, the fields would be named as A1, A2 by default.
5. Create JdbcWriter to insert records into a database using OracleMultiRowInsertAllStatementInsert strategy.
6. If no insert strategy is specified, PreparedStatementInsert strategy will be used by default.
   
7. Transform each field type to match its target column type.
8. setBatchSize(3) sets the number of records that will be chunked together in a batch to 3.
9. setDebug(true) enables the OracleMultiRowInsertAllStatementInsert to log the generated SQL (disabled by default).
10. Transfer records from CSVReader to JdbcWriter via Job.run() method to populate your database table with records obtained from the CSV file. See how to compile and run data pipeline jobs.

Generated SQL

INSERT ALL
    INTO tableName (Account, LastName, FirstName, Balance, CreditLimit, Rating) VALUES (101, 'Reeves', 'Keanu', 9315.45, 10000, 'A')
    INTO tableName (Account, LastName, FirstName, Balance, CreditLimit, Rating) VALUES (102, 'Butler', 'Gerard', 90, 1000, 'B')
    ...
    INTO tableName (Account, LastName, FirstName, Balance, CreditLimit, Rating) VALUES (105, 'Murray', 'Bill', 789.65, 5000, 'C')
SELECT 1 FROM dual