Batch Inserts Using JDBC Prepared Statements

You can load batches of data into Vertica using prepared INSERT statements—server-side statements that you set up once, and then call repeatedly. You instantiate a member of the PreparedStatement class with a SQL statement that contain question mark placeholders for data. For example:

PreparedStatement pstmt = conn.prepareStatement(

"INSERT INTO customers(last, first, id) VALUES(?,?,?)");

You then set the parameters using data-type-specific methods on the PreparedStatement object, such as setString() and setInt() (see Command Reference for Prepared Statements in JDBC for a list of these methods). Once your parameters are set, call the addbatch() method to add the row to the batch. When you have a complete batch of data ready, call the executeBatch() method to execute the insert statements.

Behind the scenes, the batch insert is converted into a COPY statement. When the defaultAutoCommit connection parameter is disabled, Vertica uses the same COPY command to load batches until either the transaction is committed, the cursor is closed, or a non-insert statement is executed. If you are loading multiple batches, you should disable the defaultAutoCommit property of the database to make the load more efficient.

The following example demonstrates using a prepared statement to batch insert data.

import java.sql.*;

import java.util.Properties;

public class BatchInsertExample {

public static void main(String[] args) {

try {

Class.forName("com.vertica.Driver");

} catch (ClassNotFoundException e) {

System.err.println("Could not find the JDBC driver class.");

e.printStackTrace();

return;

}

Properties myProp = new Properties();

myProp.put("user", "ExampleUser");

myProp.put("password", "password123");

Connection conn;

try {

conn = DriverManager.getConnection(

"jdbc:vertica://VerticaHost:5433/ExampleDB", myProp);

// establish connection and make a table for the data.

Statement stmt = conn.createStatement();

stmt.execute("CREATE TABLE customers (CustID int, Last_Name" +

" char(50), First_Name char(50),Email char(50), " +

"Phone_Number char(12))");

// Some dummy data to insert.

String[] firstNames = new String[] {"Anna","Bill","Cindy","Don",

"Eric"};

String[] lastNames = new String[] {"Allen","Brown","Chu","Dodd",

"Estavez"};

String[] emails = new String[] {"aang@example.com",

"b.brown@example.com","cindy@example.com","d.d@example.com",

"e.estavez@example.com"};

String[] phoneNumbers = new String[] {"123-456-789","555-444-3333",

"555-867-5309","555-555-1212",

"781-555-0000"};

// Create the prepared statement

PreparedStatement pstmt = conn.prepareStatement(

"INSERT INTO customers (CustID, Last_Name, First_Name, Email, " +

"Phone_Number) VALUES(?,?,?,?,?)");

// Add rows to a batch in a loop. Each iteration adds a

// new row.

for (int i=0; i < firstNames.length; i++) {

// Add each parameter to the row.

pstmt.setInt(1,i+1);

pstmt.setString(2, lastNames[i]);

pstmt.setString(3, firstNames[i]);

pstmt.setString(4, emails[i]);

pstmt.setString(5, phoneNumbers[i]);

// Add row to the batch.

pstmt.addBatch();

}

// Batch is ready, execute it to insert the data

pstmt.executeBatch();

// Print the resulting table.

ResultSet rs = null;

rs = stmt.executeQuery("SELECT CustID, First_Name, " +

"Last_Name FROM customers");

while(rs.next()){

System.out.println(rs.getInt(1) + " - " + rs.getString(2).trim()

+ " " + rs.getString(3).trim());

}

// Cleanup

stmt.execute("DROP TABLE customers CASCADE");

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

The result of running the example code is:

1 - Anna Allen

2 - Bill Brown

3 - Cindy Chu

4 - Don Dodd

5 - Eric Estavez