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.