JDBC Batch Processing

Sometimes, we need to insert a lot of data at once or one time to update, delete a lot of data, so in order to improve efficiency, we might as well use the JDBC batch to complete .

See below for a small example.

Package testbatch;
Import java.sql.*;

public class Testbatch {
public static void Main (string[] args) throws SQLException {
Connection Con=null;
Statement Stmt=null;
try {
Class.forName ("Com.mysql.jdbc.Driver");
String url = "Jdbc:mysql://localhost:3306/test?useunicode=true&characterencoding=utf8";
String user = "root";
String password = "root";
con = drivermanager.getconnection (url, user, password);
/*
* By default, the JDBC driver runs in autocommit mode, that is, all commands sent to the database run on their own
* In the transaction. This is convenient, but the cost is that the program runs at a higher cost. We can use batch operations to reduce
* This overhead because multiple database update operations can be performed in a single batch operation. However, a batch operation requires that a transaction cannot be in a self-
* Dynamic commit mode. To do this, we will first disable autocommit mode:
* Con.setautocommit (FALSE);
*/
Con.setautocommit (FALSE);
The Excute () method of statement can only execute one SQL statement, but the SQL statement to be executed using the Addbath () method is added,
The Excutebath () method is then executed to execute multiple SQL statements in a single method call, which can improve execution efficiency.
Stmt=con.createstatement ();
Stmt.addbatch ("INSERT into test1 values (1, ' demo1 ')");
Stmt.addbatch ("INSERT into test1 values (2, ' Demo2 ')");
Stmt.addbatch ("INSERT into test1 values (3, ' Demo3 ')");
Stmt.executebatch ();

/*
* For PreparedStatement, use Addbatch () method without parameters. The ExecuteBatch () method returns an array of update counts,
* Each value corresponds to a command in a batch operation. A batch operation may throw an exception of type batchupdateexception,
* This exception indicates that at least one command in a batch operation failed.
*/
PreparedStatement ps=con.preparestatement ("INSERT into test values (?,?)");
Ps.setint (1, 1);
Ps.setstring (2, "demo1");
Ps.addbatch ();

Ps.setint (1, 2);
Ps.setstring (2, "Demo2");
Ps.addbatch ();

Ps.setint (1, 3);
Ps.setstring (2, "Demo3");
Ps.addbatch ();

Ps.executebatch ();
System.out.println ("suceess");
Ps.close ();
} catch (Exception e) {
E.printstacktrace ();
}finally{
// Stmt.close ();
Con.close ();
}

}

}

advantages of using batch processing:

1. The execution of multiple SQL statements, sharing a connection resource. In the database operation, connection resources is very valuable, database maintenance from a certain point of view, is to reduce the number of database connections, reduce the pressure on the db. Creating a data connection consumes resources much more than using a database connection. This is also the meaning of database connection pool existence.

2. Batch processing has always been more efficient than one-by-one, and the larger the number of records to be processed, the more obvious the advantages of batch processing. DB operations that handle large batches of the same business logic can be simplified and efficiently processed using batch processing.

3. In a single time period, improve the throughput between the application and the DB, and shorten the DB response time. Most applications have DB operations, which can cause the db to become unusable for a long period of time if the SQL statement is improperly manipulated, or increase the DB resource usage, affecting the application and eventually being dragged down by DB. Shorten the response time of the DB, provide application performance, and reduce the db pressure, which is very helpful for maintaining high-performance applications.

JDBC Batch Processing