Write to a Database Using Multi Row Prepared Statement Insert Strategy

Updated: Apr 1, 2023

This example shows you how to use DataPipeline's JdbcWriter and MultiRowPreparedStatementInsert to write multiple records to a database at once using a single prepared statement.

A prepared statement is a feature used to execute the same SQL statements repeatedly with high efficiency. It also allows you to prevent SQL injection attacks by separating the SQL from the values.

In this demo code you are going read records from a CSV file and write those records to a database by chunking them in a batch of size 3. DataPipeline's MultiRowPreparedStatementInsert strategy creates prepared statements that allows you to insert several records at once.

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 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.MultiRowPreparedStatementInsert;
import com.northconcepts.datapipeline.job.Job;

public class WriteToDatabaseUsingMultiRowPreparedStatementInsert {

	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 MultiRowPreparedStatementInsert().setDebug(true))
							.setBatchSize(3); // 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

First, database connection parameters are specified.
Class.forName() is used to register a database driver. In this example hsqldb.jdbcDriver is registered.
A method createTable() creates a table in your database( drops it first if it already exists).
CSVReader is created corresponding to an input file credit-balance-insert-records.csv.
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.
JdbcWriter is created to write records to the specified database table using the specified strategy (i.e MultiRowPreparedStatementInsert for this example).
setDebug(true) enables the JdbcWriter to log the generated SQL(disabled by default). For thise example the generated SQL will look something like this INSERT INTO CreditBalance (Account, LastName, FirstName,....) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?).
setBatchSize(3) limits the number of write queries executed at once to 3.
Data are transferred from CSVReader to JdbcWriter via Job.run() method to populate your database table with records from the CSV file .
JdbcReader is created to obtain records from the specified database.
CSVWriter is created to write records to a CSV stream.
Data are transferred from JdbcReader to CSVWriter via Job.run() method. See how to compile and run data pipeline jobs.

MultiRowPreparedStatementInsert

Allows you to write multiple records to a database using prepared statement feature. It extends DataObject class and implements IInsert interface(you can also create your own custom insert strategy by implementing this interface). See how to Write to a Database Using Custom Jdbc Insert Strategy.

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.

JdbcReader

Obtains records from a database query. It extends DataReader class and it's constructor takes Connection object and query string as a parameter.

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.

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.

Console Output

Account,LastName,FirstName,Balance,CreditLimit,Rating
101,Reeves,Keanu,9315.45,10000.0,A
102,Butler,Gerard,90.0,1000.0,B
103,Hewitt,Jennifer Love,0.0,17000.0,B
104,Pinkett-Smith,Jada,49654.87,100000.0,A
105,Murray,Bill,789.65,5000.0,C

The output is exactly the same as the input.

All Tags | All Examples