Batch Execution in Java


Kita dapat menggunakan fasilitas batch execution dari PreparedStatement untuk menginsert data dalam jumlah banyak ke dalam database. Batch execution mengumpulkan semua statement yang akan dilaksankan, kemudian ketika sudah terkumpul batch execution akan mengirimkan kumpulan statement secara bersamaan dalam satu kesatuan ke DBMS. Metode ini sangat diperlukan untuk mengurangi overhead yang diperlukan program untuk berkomunikasi dengan DBMS.

Dalam contoh kali ini, saya akan menggunakan batch execution untuk melakukan insert data dimensi waktu yang biasanya digunakan pada datawarehouse:

CREATE TABLE dim_time
(id_time INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tanngal INT NOT NULL, bulan VARCHAR(20) NOT NULL,
tahun INT NOT NULL)
ENGINE = MyISAM;

Sedangkan sourcecode java nya seperti ini:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Windu Purnomo
 */
public class TimeService {
    public Connection getConnection(){
        Connection connection = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            connection = DriverManager.getConnection(""
                    + "jdbc:mysql://localhost:3306/spbe-dw", "root", "");
        } catch (SQLException ex) {
            Logger.getLogger(MysqlUtilities.class.getName()).log(Level.SEVERE, null, ex);
        }
        return connection;
    }

    public void insertTime(){
        String insert = "INSERT INTO time-dim (tanggal, bulan, tahun) VALUES (?,?,?)";
        PreparedStatement statement = null;
        try {
            statement = getConnection().prepareStatement(insert);
            for(int i=1; i<13; i++){
                String bulan = "";
                int day = 0;
                switch(i){
                    case 1: bulan = "Januari"; break;
                    case 2: bulan = "Februari"; break;
                    case 3: bulan = "Maret"; break;
                    case 4: bulan = "April"; break;
                    case 5: bulan = "Mei"; break;
                    case 6: bulan = "Juni"; break;
                    case 7: bulan = "Juli"; break;
                    case 8: bulan = "Agustus"; break;
                    case 9: bulan = "September"; break;
                    case 10: bulan = "Oktober"; break;
                    case 11: bulan = "November"; break;
                    case 12: bulan = "Desember"; break;
                }

                switch(i){
                    case 1: day = 31; break;
                    case 2: day = 28; break;
                    case 3: day = 31; break;
                    case 4: day = 30; break;
                    case 5: day = 31; break;
                    case 6: day = 30; break;
                    case 7: day = 31; break;
                    case 8: day = 31; break;
                    case 9: day = 30; break;
                    case 10: day = 31; break;
                    case 11: day = 30; break;
                    case 12: day = 31; break;
                }
                for(int j=1; j<=day; j++){
                    statement.setInt(1, j);
                    statement.setString(2, bulan);
                    statement.setInt(3, 2010);
                    statement.addBatch();
                }
            }
            statement.executeBatch();
        } catch (SQLException exception) {
            exception.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException exception) {}
            }
        }
    }
}

Dalam sourcecode java di atas, ada beberapa statement penting yang berhubungan dengan batch execution:

1. Mengumpulkan statement ke dalam batch

statement.addBatch();

2. Eksekusi Batch

statement.executeBatch();

Selamat mencoba🙂

About windupurnomo

I'm interested in programming. I am active with several programming such as Java, C #, C, JavaScript, HTML. I'm also develop desktop application (Java Swing), Mobile Application (Android), and Web programming (ASP MVC).
This entry was posted in Basis Data, Java and tagged , , , , , , , , , , , . Bookmark the permalink.

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