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