Insert Multiple Rows to Oracle Using Select Union

This example shows you how to bulk insert multiple records to a database using DataPipeline's JdbcWriter and OracleMultiRowSelectUnionAllStatementInsert 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.

OracleMultiRowSelectUnionAllStatementInsert  is one of two oracle-specific strategies that DataPipeline supports.  It uses a UNION of SELECT statements to insert multiple records in a single SQL statement. Alternatively, you can also use OracleMultiRowInsertAllStatementInsert to perform multi-row insert in the 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.OracleMultiRowSelectUnionAllStatementInsert;
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 InsertMultipleRowsToOracleUsingSelectUnion {

    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 OracleMultiRowSelectUnionAllStatementInsert())
                .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 OracleMultiRowSelectUnionAllStatementInsert 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 OracleMultiRowSelectUnionAllStatementInsert 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 INTO tableName (Account,LastName,FirstName,Balance,CreditLimit,Rating)
    SELECT 101, 'Reeves', 'Keanu', 9315.45,10000,'A' FROM dual UNION ALL
    SELECT 102, 'Butler', 'Gerard', 90,1000, 'B' FROM dual UNION ALL
    ...
    SELECT 105, 'Murray','Bill', 789.65, 5000, 'C' FROM dual