Java Mysql 简明教程

Java & MySQL - Batching with PrepareStatement Object

以下是一些使用批处理和 PrepareStatement 对象的典型步骤：

使用占位符创建 SQL 语句。
使用 prepareStatement() 方法创建 PrepareStatement 对象。
使用 setAutoCommit() 将自动提交设置为 false。
使用所创建的语句对象上的 addBatch() 方法向批处理中添加任意数量的 SQL 语句。
使用 executeStatement() 方法对已创建的语句对象执行所有 SQL 语句。
最后，使用 commit() 方法提交所有更改。

此示例代码基于先前章节中完成的环境和数据库设置编写。

将以下示例复制并粘贴到 TestApplication.java 中，如下进行编译和运行：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String INSERT_QUERY = "INSERT INTO Employees(first,last,age) VALUES(?, ?, ?)";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         PreparedStatement stmt = conn.prepareStatement(INSERT_QUERY,
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE)
      ) {
         conn.setAutoCommit(false);

         ResultSet rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);

         // Set the variables
         stmt.setString( 1, "Pappu" );
         stmt.setString( 2, "Singh" );
         stmt.setInt( 3, 33 );
         // Add it to the batch
         stmt.addBatch();

         // Set the variables
         stmt.setString( 1, "Pawan" );
         stmt.setString( 2, "Singh" );
         stmt.setInt( 3, 31 );
         // Add it to the batch
         stmt.addBatch();

         // Create an int[] to hold returned values
         int[] count = stmt.executeBatch();

         //Explicitly commit statements to apply changes
         conn.commit();

         rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);

         stmt.close();
         rs.close();

      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

现在让我们如下编译上述示例：

C:\>javac TestApplication.java
C:\>

运行 TestApplication 时，它会生成以下结果：

C:\>java TestApplication
ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 7, Age: 35, First: Sita, Last: Singh
ID: 8, Age: 20, First: Rita, Last: Tez
ID: 9, Age: 20, First: Sita, Last: Singh
ID: 10, Age: 30, First: Zia, Last: Ali
ID: 11, Age: 35, First: Raj, Last: Kumar

ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 7, Age: 35, First: Sita, Last: Singh
ID: 8, Age: 20, First: Rita, Last: Tez
ID: 9, Age: 20, First: Sita, Last: Singh
ID: 10, Age: 30, First: Zia, Last: Ali
ID: 11, Age: 35, First: Raj, Last: Kumar
ID: 12, Age: 33, First: Pappu, Last: Singh
ID: 13, Age: 31, First: Pawan, Last: Singh
C:\>