Insert batch txt records into MySQL with JAVA

Question

Insert batch txt records into MySQL with JAVA

Navigation

#1 by (0 votes)

0

I am working in JAVA and I need to save the records that are in a file.txt to my MySQL database, the problem is that they are many records and inserting them one by one takes a long time. I need to know how to apply the methods addBatch and executeBatch in the following code

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.BufferedReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class BD { 
private String driver = "com.mysql.jdbc.Driver";
private String cadenaConexion="jdbc:mysql://127.0.0.1/log";
private String usuario="root";
private String contraseña=""; 
public BD(){
    try {
        Class.forName(driver);//cargar el driver que se ha creado 
        con=DriverManager.getConnection(cadenaConexion,usuario,contraseña);
        System.out.println("Conectado con BD");
    }catch(Exception e){
        System.out.println("No se ha podido establercer una conexion");

    }   

}

private PreparedStatement psInsertar;
private Statement st;
private Connection con;

public void insertar (Datos d) throws SQLException {
st=con.createStatement();
psInsertar = con.prepareStatement("INSERT INTO  LOG_PG509 (fecha,hora,milisegundo,secuencia,banco,suscriptor,codrespmov,tiempo_total,tiempo_pic,tiempo_movilnet,servicio,detalle_excepcion)"+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
psInsertar.setString(1, d.getfecha());
psInsertar.setString(2, d.gethora());
psInsertar.setString(3, d.getmilisegundo());
psInsertar.setString(4, d.getsecuencia());
psInsertar.setString(5, d.getbanco());
psInsertar.setString(6, d.getsuscriptor());
psInsertar.setString(7, d.getcodrespmov());
psInsertar.setString(8, d.gettiempo_total());
psInsertar.setString(9, d.gettiempo_pic());
psInsertar.setString(10, d.gettiempo_movilnet());
psInsertar.setString(11, d.getservicio());
psInsertar.setString(12, d.getdetalle_excepcion());
psInsertar.executeUpdate();
}
}

The code shown above successfully connects to the database.

Thanks

java batch

asked by Javier Valero 03.09.2018 в 15:28

source

1 answer

Error with TableSorter - JQuery How to generate an invoice in java netbeans

score 0 · Answer 1

Modifying your insertar method so that you receive a list of Datos

public void insertar (List<Datos> lista) throws SQLException {
    String sql = "INSERT INTO  LOG_PG509 (fecha,hora,milisegundo,secuencia,banco,suscriptor,codrespmov,tiempo_total,tiempo_pic,tiempo_movilnet,servicio,detalle_excepcion)"+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement ps = connection.prepareStatement(sql);
    for (Datos d : lista){
        psInsertar.setString(1, d.getfecha());
        psInsertar.setString(2, d.gethora());
        psInsertar.setString(3, d.getmilisegundo());
        psInsertar.setString(4, d.getsecuencia());
        psInsertar.setString(5, d.getbanco());
        psInsertar.setString(6, d.getsuscriptor());
        psInsertar.setString(7, d.getcodrespmov());
        psInsertar.setString(8, d.gettiempo_total());
        psInsertar.setString(9, d.gettiempo_pic());
        psInsertar.setString(10, d.gettiempo_movilnet());
        psInsertar.setString(11, d.getservicio());
        psInsertar.setString(12, d.getdetalle_excepcion());

        ps.addBatch();
    }
    ps.executeBatch();
    ps.close();
}

Source