"JDBC" million data insertions

When using JDBC to connect to a database, inserting data one at a time becomes very slow if the amount of data being inserted is large. At this point, we need to use preprocessing.

Reviewing the Java development documentation, we can see:

Interface PreparedStatement

An object that represents a precompiled SQL statement.

The SQL statement is precompiled and stored in the PreparedStatement object. You can then use this object to execute the statement efficiently several times.

My understanding is that the PreparedStatement object is equivalent to a processing station before the SQL statement executes, and the SQL statement is populated with specific data.

The approximate use process is:

1: Use bufferedstring to build a statement template that inserts n data simultaneously;

2: Using the template to generate a string statement, the statement as a parameter to build a PreparedStatement object PST, meaning to use PST to preprocess the parameter statement;

3: Use the PST object call method to preprocess the parameter statement: PST.SETXX (Index,value) and other statements to fill the parameter statement with specific data;

4: Execute the specific SQL statement after PST processing: Pst.execute ();

5: Close the PST object;

Drawing comprehension:

code example:

Import java.net.*;
Import java.io.*;
Import java.sql.*;

public class Insert
{

public static void Main (string[] args)
{Connection conn=null;
try{
Class.forName ("Com.mysql.jdbc.Driver");
Conn=drivermanager.getconnection ("Jdbc:mysql://localhost:3306/inserttest", "root", "123456");
Conn.setautocommit (FALSE);

Construct a statement template that inserts 10,000 data simultaneously using a buffered string
StringBuffer sqlbuffer = new StringBuffer (
"INSERT into Test (COL1,COL2,COL3) values");
Sqlbuffer.append ("(?,?,?)");
for (int j = 2; J <= 10000; j + +) {
Sqlbuffer.append (", (?,?,?)");
}
Sqlbuffer.append (";");
String sql = new string (sqlbuffer);

The SQL statements are precompiled and stored in the PreparedStatement object. You can then use this object to execute the statement efficiently several times. When using this statement, you can change the specific data according to the subscript
PreparedStatement PST = conn.preparestatement (SQL);
System.out.println ("start!");
Long beginTime = System.currenttimemillis ();
for (int i = 0; i <; i++) {
System.out.println ("No." + (i+1) + "round ...");
for (int j = 0; J < 10000; J + +) {
Each time you insert, the specific data is assigned by PST.SETXXX (Index,value)
Pst.setint (3 * j + 1, (int) (Math.random () * 11) +1);
Pst.setint (3 * j + 2, (int) (Math.random () * 112) +2);
Pst.setint (3 * j + 3, (int) (Math.random () * 133) +5);
}
Each SQL statement inserts 10,000 data and executes 100 SQL
Pst.execute ();
}
Conn.commit ();
Pst.close ();
Long endTime = System.currenttimemillis ();
System.out.println ("end!");
SYSTEM.OUT.PRINTLN ("Total Time:" + (Double) (endtime-begintime)/1000 + "s");
System.out.println ();
}catch (Exception ex) {

}
}
}

"JDBC" million data insertions