Inserções em Lote com JDBC: Um Guia JDBC Batch
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