JDBC Bath – Inserções em lote com JDBC

A API JDBC permite a execução de comandos em lote (batch), e assim, é possível que enviar vários comando de UPDATE, INSERT e DELETE em um único objeto do tipo batch, ao invés de executar individualmente vários comandos do tipo execute() ou executeUpdate() da interface java.sql.Statement ou java.sql.PreparedStatement. Entretanto esse tipo de objeto não permite instruções SELECT.

Para inserir uma instrução, por exemplo, INSERT se usa o método addBatch(). Após todas instruções serem inseridas, o método executeBatch() realiza a ação com o banco de dados. No final limpamos o batch com o comando clearBatch().

Já aconteceu de algumas vezes eu ler em tutoriais ou artigos, algumas pessoas citando que as inserções em lote seriam mais rápidas e de menor consumo de memória que inserções normalmente usadas com os comando execute(), por exemplo. Resolvi então fazer um pequeno teste para ver qual seria a diferença de tempo entre usar uma inserção normal e uma inserção em lote.

Para realizar tal teste usei o banco de dados MySQL e gerei algumas milhares de linhas de inserts em um arquivo, e a partir desse arquivo fiz as inserções no banco de dados.

1. Classe de conexão JDBC

Na Listagem 1, temos a classe de conexão JDBC com um método que será usado para gerar a tabela USERS no banco de dados.

package br.wp.mballem.lote;

import java.sql.*;

public class ConnectionDataBase {
    private static final String URL_MYSQL = "jdbc:mysql://localhost/agenda";
    private static final String DRIVER_CLASS_MYSQL = "com.mysql.jdbc.Driver";
    private static final String USER = "root";
    private static final String PASS = "";

    public static Connection getConnection() {
        System.out.println("Conectando ao Banco de Dados");
        try {
            Class.forName(DRIVER_CLASS_MYSQL);
            return DriverManager.getConnection(URL_MYSQL, USER, PASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return null;
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (conn!= null) {
                conn.close();
            }

            if (stmt!= null) {
                stmt.close();
            }

            if (rs!= null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void createTable() {
        Connection connection = getConnection();
        PreparedStatement stmt = null;
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                "  ID_USER bigint(20) NOT NULL AUTO_INCREMENT," +
                "  FIRST_NAME VARCHAR(255) NOT NULL," +
                "  SURNAME VARCHAR(255) NOT NULL," +
                "  AGE INT NOT NULL," +
                "  EMAIL VARCHAR(255) NOT NULL," +
                "  CONSTRAINT PK_USER PRIMARY KEY (ID_USER)" +
                ");";
        try {
            stmt = connection.prepareStatement(sql);
            stmt.execute();
            System.out.println("Create Tables Ok!");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, stmt, null);
        }
    }
}

2. Classe de testes

A Listagem 2 apresenta a classe de testes, onde possui alguns métodos como, o método gerarArquivoSQL() para gerar o arquivo com os inserts, o save() que realiza inserção normal no banco e o saveBatch() onde usamos então a inserção em lotes.

package br.wp.mballem.lote;

import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Teste {

    private long start;

    private static final String INSERT_SQL =
            "insert into USERS (FIRST_NAME, SURNAME, AGE, EMAIL) " +
            "values ('Ana Maria', 'de Souza', 30, 'ana@email.com');";

    public static void main(String[] args) {
        //ConnectionDataBase.createTable();

        //new Teste().gerarArquivoSQL();

        //new Teste().save();

        //new Teste().saveBatch();
    }

    private void gerarArquivoSQL() {
        File file = new File("c:\\insert.sql");
        try {
            file.createNewFile();
            FileWriter fileWriter = new FileWriter(file, true);
            PrintWriter printWriter = new PrintWriter(fileWriter);
            for (int i = 0; i < 200000; i++) {
                printWriter.println(INSERT_SQL);
            }
            printWriter.flush();
            printWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private List; lerArquivoSQL() {
        File file = new File("c:\\insert.sql");
        List inserts = new ArrayList();
        try {
            FileReader fileReader = new FileReader(file);
            BufferedReader bufferedReader = new BufferedReader(fileReader);
            String linha = "";
            while ((linha = bufferedReader.readLine()) != null) {
                inserts.add(linha);
            }
            fileReader.close();
            bufferedReader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return inserts;
    }

    private void save() {
        //Abre a conexao
        Connection conn = ConnectionDataBase.getConnection();
        Statement stmt = null;
        //Cria um lista para receber os inserts do arquivo
        List list = lerArquivoSQL();
        try {
            //inicializa o objeto statement
            stmt = conn.createStatement();
            start = System.currentTimeMillis();
            for (String s : list) {
                stmt.execute(s);
            }

            calculaTempo(System.currentTimeMillis() - start);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionDataBase.close(conn, stmt, null);
        }
    }

    private void saveBatch() {
        //Abre a conexao
        Connection conn = ConnectionDataBase.getConnection();
        Statement stmt = null;
        //Cria um lista para receber os inserts do arquivo
        List list = lerArquivoSQL();
        try {
            //inicializa o objeto statement
            stmt = conn.createStatement();
            start = System.currentTimeMillis();
            //faz um for na lista e adiciona no método addBatch()
            // cada insert que veio do arquivo
            for (String s : list) {
                stmt.addBatch(s);
            }
            //faz o insert em lote no banco pelo método executeBatch()
            stmt.executeBatch();
            //limpa o objeto stmt
            stmt.clearBatch();

            calculaTempo(System.currentTimeMillis() - start);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionDataBase.close(conn, stmt, null);
        }

    }

    private void calculaTempo(long time) {
        long sec = time / 1000;
        long min = time / (60 * 1000);
        long hour = time / (60 * 60 * 1000);

        if (hour > 0) {
            System.out.println("Total da operacao " + hour + "hs");
        } else if (min > 0) {
            System.out.println("Total da operacao " + min + "min");
        } else if (sec > 0) {
            System.out.println("Total da operacao " + sec + "s");
        }
    }
}

Conclusão

Comecei a fazer os testes com 20.000 linhas no arquivo insert.xml, porém os resultados eram idênticos, mais ou menos 1 a 2 segundos. Aumentei gradativamente o numero de linhas até chegar a 200.000 e então, foi onde os resultados passaram a ser diferentes. As inserções em lote tiveram uma variação entre 19 e 20 segundos e com inserções normais o tempo ficou praticamente cravado em 18 segundos.

É claro que isso vai depender muito da máquina onde for executado o código, então se possível, quando você realizar esses testes, descreva os resultados nos comentários do blog.

Saiba mais

• MySql http://www.mysql.com/
• JDBC tutorial Oracle http://download.oracle.com/javase/tutorial/jdbc/index.html