Write to a Database Using Multi Row Statement Insert Strategy

Updated: Apr 1, 2023

View on GitHub

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

Inserting multiple records to 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 .

Java Code listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import com.northconcepts.datapipeline.core.DataException;
import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.csv.CSVWriter;
import com.northconcepts.datapipeline.jdbc.JdbcReader;
import com.northconcepts.datapipeline.jdbc.JdbcWriter;
import com.northconcepts.datapipeline.jdbc.insert.MultiRowStatementInsert;
import com.northconcepts.datapipeline.job.Job;

public class WriteToDatabaseUsingMultiRowStatementInsert {

	public static void main(String[] args) {

		final String DATABASE_DRIVER = "org.hsqldb.jdbcDriver";
		final String DATABASE_URL = "jdbc:hsqldb:mem:aname";
		final String DATABASE_USERNAME = "sa";
		final String DATABASE_PASSWORD = "";
		final String DATABASE_TABLE = "CreditBalance";

		Connection connection;
		try {
			Class.forName(DATABASE_DRIVER);
			connection = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);

			createTable(connection);
		} catch (Throwable e) {
			throw DataException.wrap(e);
		}

		DataReader reader = new CSVReader(new File("example/data/input/credit-balance-insert-records.csv"))
							.setFieldNamesInFirstRow(true);
		DataWriter writer = new JdbcWriter(connection, DATABASE_TABLE, new MultiRowStatementInsert().setDebug(true))
							.setBatchSize(100); // set batch size

		Job.run(reader, writer);

		reader = new JdbcReader(connection, "Select * from CreditBalance;");
		writer = new CSVWriter(new OutputStreamWriter(System.out));

		Job.run(reader, writer);
	}

	public static void createTable(Connection connection) throws Throwable{
		PreparedStatement preparedStatement;
		String dropTableQuery = "DROP TABLE CreditBalance IF EXISTS;";
		String createTableQuery = "CREATE TABLE CreditBalance ("
			+ "Account INTEGER, "
			+ "LastName VARCHAR(256), "
			+ "FirstName VARCHAR(256), "
			+ "Balance DOUBLE, "
			+ "CreditLimit DOUBLE, "
			+ "Rating CHAR, "
			+ "PRIMARY KEY (Account));";

		preparedStatement = connection.prepareStatement(dropTableQuery);
		preparedStatement.execute();

		preparedStatement = connection.prepareStatement(createTableQuery);
		preparedStatement.execute();
	}
	
}

Code walkthrough

1. First, database connection parameters are specified.
2. Class.forName() is used to register a database driver. In this example hsqldb.jdbcDriver is registered.
3. A method createTable() creates a table CreditBalance in your database( drops it first if it already exists).
4. CSVReader is created corresponding to an input file credit-balance-insert-records.csv.
5. setFieldNamesInFirstRow(true) is invoked to specify that the names specified in the first row 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.
6. JdbcWriter is created to insert records to a database using MultiRowStatementInsert strategy.
7. setBatchSize(100) sets the number of records to chunk together in batch inserts to 100.
8. MultiRowStatementInsert allows JdbcWriter to bulk insert statements to reduce insert operation time.
9. setDebug(true) enables the MultiRowStatementInsert to log the generated SQL(disabled by default).
10. Data are transferred from CSVReader to JdbcWriter via Job.run() method to populate or update your database table with records obtained from the CSV file .
11. JdbcReader is created to obtain records from the specified database.
12. CSVWriter is created to write records to a CSV stream.
13. Data are transferred from JdbcReader to CSVWriter via Job.run() method. See how to compile and run data pipeline jobs.

JdbcWriter

Writes records to a database table. It extends DataWriter class and it's constructor takes Connection object, a table name and optionally a IInsert object. The optional third parameter specifies a strategy that is used to write records to a database. A method setBatchSize() in this class sets the number of records to chunk together in batch updates.

MultiRowStatementInsert

Insert strategy that bulks insert statements to minimize round trips and reduce insert operation time. A method setDebug()allows MultiRowStatementInsert to log the generated SQL(disabled by default). For this example the generated SQL will look something like this

• INSERT INTO CreditBalance (Account, LastName, FirstName,..) VALUES (val1, val2, val3), (val1, val2, val3), (val1, val2, val3),..

JdbcReader

Obtains records from a database query. It extends DataReader class and it's constructor takes Connection object and query string as a parameter. A method setBatchSize() can be used to set the number of records to chunk together in batch updates.

CSVReader

Obtains records from a Comma Separated Value (CSV) or delimited stream. It extends TextReader class and can be created using or Reader object. Passing true to method setFieldNamesInFirstRow() in this class enables the CSVReader to use the names specified in the first row of the input data as field names.

CSVWriter

Writes records to a Comma Separated Value (CSV) stream. It extends LinedTextWriter and it's constructor takes a File or a Writer object as parameter.

Console Output

Obtained records will be printed on the console.