JDBC: Batch processing improves SQL processing speed

When you need to insert or update a record in batches. The bulk update mechanism of Java can be used, which allows multiple statements to be submitted to the database batch processing at once. Usually more efficient than single-commit processing
The batch processing statements for JDBC include the following two methods:
Addbatch (String): Add SQL statements or parameters that require bulk processing;
ExecuteBatch (); Execute batch processing statements;
Typically we encounter two scenarios in which SQL statements are executed in bulk:
batch processing of multiple SQL statements;

A batch parameter for a SQL statement;

Test code:

Import Java.sql.connection;import java.sql.date;import Java.sql.preparedstatement;import Java.sql.Statement;import Org.junit.test;import Xuezaipiao1. jdbc_tools;/** * Add 100,000 records to the TEMP data table of Oracle * Test how to insert the shortest time */public class Jdbctest {/** * * 1. Use Statement. * Test Time: 35 535 */@Testpublic void testbbatchstatement () {Connection conn = null; Statement Statement = null; String sql = null;try {conn = Jdbc_tools.getconnection (); JDBC_TOOLS.BEGINTX (conn); Long beginTime = System.currenttimemillis (); statement = Conn.createstatement (); for (int i = 0; i<100000;i++) {sql = "INSERT into temp values (" + (i+1) + ", ' name_" + (i+1) + "', ' 1 March-June -15 ')"; Statement.executeupdate (sql );} Long endTime = System.currenttimemillis (); System.out.println ("Time:" + (Endtime-begintime)); JDBC_TOOLS.COMMIT (conn);} catch (Exception e) {e.printstacktrace (); Jdbc_tools.rollback (conn);} FINALLY{JDBC_TOOLS.RELASESOURCE (conn, statement);}} /** * Using PreparedStatement * Test time: 9717 */@Testpublic void testbbatchpreparedstatement () {Connectionconn = null; PreparedStatement PS = null; String sql = null;try {conn = Jdbc_tools.getconnection (); JDBC_TOOLS.BEGINTX (conn); Long beginTime = System.currenttimemillis (); sql = "INSERT into temp values (?,?,?)"; PS = conn.preparestatement (SQL);D ate date = new Date (new Java.util.Date (). GetTime ()); for (int i = 0;i<100000;i++) { Ps.setint (1, i+1);p s.setstring (2, "name_" +i);p s.setdate (3, date);p s.executeupdate ();//9717}long endTime = System.currenttimemillis (); System.out.println ("Time:" + (Endtime-begintime)); JDBC_TOOLS.COMMIT (conn);} catch (Exception e) {e.printstacktrace (); Jdbc_tools.rollback (conn);} Finally{jdbc_tools.relasesource (conn, PS);}} /** * Test Time: 658 */@Testpublic void Testbbatch () {Connection conn = null; PreparedStatement PS = null; String sql = null;try {conn = Jdbc_tools.getconnection (); JDBC_TOOLS.BEGINTX (conn); Long beginTime = System.currenttimemillis (); sql = "INSERT into temp values (?,?,?)"; PS = conn.preparestatement (SQL);D ate date = new Date (new Java.util.Date (). GetTime ()); for (iNT i = 0;i<100000;i++) {ps.setint (1, i+1);p s.setstring (2, "name_" +i);p s.setdate (3, date);//accumulate Sqlps.addbatch ();// When accumulating to a certain extent, execute once and clear the record if ((i+1)% 300==0) {ps.executebatch ();p s.clearbatch ();}} The total number of bars is not an integer multiple of the batch value, you also need to execute the IF (100000%! = 0) {ps.executebatch ();p s.clearbatch ();} Long endTime = System.currenttimemillis (); System.out.println ("Time:" + (Endtime-begintime)); JDBC_TOOLS.COMMIT (conn);} catch (Exception e) {e.printstacktrace (); Jdbc_tools.rollback (conn);} Finally{jdbc_tools.relasesource (conn, PS);}}}

JDBC: Batch processing improves SQL processing speed