Batch Insert with JDBC


Hi,

In the current project I’m working on, we had a requirement to import some data from an Excel Spreadsheet and import them into the database. So, bascially we need to read from the spreadsheet and insert into the database. Initally we started using single insert statements, but we realized that wouldn’t be a good choice, for performance reasons. If we have a spreadsheet with too much data, we could decrease the database performance with so many single statements executed.

JDBC offers support for batch statements, follows a simple example of batch insert using prepared statements:


package com.xicojunior.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;


import com.xicojunior.factory.ConnectionFactory;

public class BatchInserter {
	
	public static void main(String args[]) throws SQLException{
		Connection conn = null;
		PreparedStatement statement = null;
		
		try {
			conn = ConnectionFactory.getConnection();
			statement = conn.prepareStatement("INSERT INTO USER values(?,?,?)");
			
			
			statement.setInt(1, 3);
			statement.setString(2, "User01");
			statement.setString(3, "user1@usersmine.com");
			
			statement.addBatch();
			
			statement.setInt(1, 4);
			statement.setString(2, "User02");
			statement.setString(3, "user2@usersmine.com");
			
			statement.addBatch();
			
			int[] results = statement.executeBatch();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally{
			
			conn.close();
			statement.close();
		}
		
	}
	
}

This code could be changed for example for some other batch operations like update.

Thanks and see you on the next post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s