JDBC Foundation (iv) batch processing

batch processing is literally meant to process a batch of SQL statements at once.

look directly at the example:

1  PackageCom.cream.ice.jdbc;2 3 Importjava.sql.Connection;4 Importjava.sql.PreparedStatement;5 ImportJava.sql.ResultSet;6 Importjava.sql.Statement;7 8 Importorg.junit.Test;9 Ten /** One  *  A * Suppose the table test already exists: - * CREATE TABLE Test ( - * ID int primary KEY, the * Name varchar (a) -  * ); -  *  -  * @authorIce +  * -  */ +  A  Public classBatch { at     PrivateConnection Connection; -     PrivateStatement Statement; -     PrivatePreparedStatement prestatement; -     PrivateResultSet ResultSet; -  -     /** in * Insert 2 records into TTest, delete 1th - * Batch processing with statement because of different batch statements to      */ + @Test -      Public voidTestBatch1 () { the         Try { *Connection =jdbcutils.getconnection (); $statement =connection.createstatement ();Panax Notoginseng  -String SQL1 = "INSERT INTO Test (Id,name) VALUES (1, ' Tom ')"; theString sql2 = "INSERT INTO Test (Id,name) VALUES (2, ' Jack ')"; +String sql3 = "Delete from test where id=1"; A  the             //internally maintains a list that adds SQL statements to the list + Statement.addbatch (SQL1); - Statement.addbatch (SQL2); $ Statement.addbatch (SQL3); $  -             //perform batch processing -             intNum[] =Statement.executebatch (); the  -             //I is the number of rows affected by each statementWuyi              for(inti:num) theSystem.out.print (i + "")); -  Wu}Catch(Exception e) { - e.printstacktrace (); About}finally { $Jdbcutils.releaseresources (NULL, statement, connection); -         } -     } -  A     /** + * Insert 100 records into Test the * Because the statements are identical, only the parameters are different, using PreparedStatement -      */ $ @Test the      Public voidTestBatch2 () { the         Try { theConnection =jdbcutils.getconnection (); the  -String sql = "INSERT INTO Test (id,name) VALUES (?,?)"; in  thePrestatement =connection.preparestatement (SQL); the  About             //to insert 100 records the              for(inti = 0; I < 100; i++) { thePrestatement.setint (1, i + 1); thePrestatement.setstring (2, "No." + (i + 1)); + Prestatement.addbatch (); -             } the Bayi             //Execute Batch Statement the Prestatement.executebatch (); the              -Statement=connection.createstatement (); -Resultset=statement.executequery ("SELECT * FROM Test"); the              the             //print an Insert record to the console the              while(Resultset.next ()) { theSystem.out.print ("ID:" +resultset.getint ("id") + ""); -System.out.println ("Name:" +resultset.getstring ("name")); the             } the              the}Catch(Exception e) {94 e.printstacktrace (); the}finally { theJdbcutils.releaseresources (NULL, prestatement, connection); the jdbcutils.releaseresources (ResultSet, statement, connection);98         } About     } -}

The code uses the tool class Jdbcutils, which can be found in the JDBC Foundation (ii) connection to the database through the properties configuration file.

In the first example, batch processing is done using Statement because of the different number of statements in a batch.

In the second example, because the statements are identical , only the parameters are different and are handled using PreparedStatement .

It is worth noting that in the second example, if the number of batch statements is not 100, but hundreds of thousands of or millions, it is extremely time-consuming in MySQL and takes a few hours to process, but if you switch to an Oracle database, it will take only a few 10 seconds to complete.

JDBC Foundation (iv) batch processing