如何在 Java 中同时在数据库上执行多个 SQL 命令

问题描述

如何同时在数据库上执行多个 SQL 命令？

解决方案

以下示例使用 addBatch 和 executeBatch 命令同时执行多条 SQL 命令。

import java.sql.*;

public class jdbcConn {
   public static void main(String[] args) throws Exception {
      Class.forName("org.apache.derby.jdbc.ClientDriver");
      Connection con = DriverManager.getConnection(
         "jdbc:derby://:1527/testDb","name","pass");
      
      Statement stmt = con.createStatement(
         ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      
      String insertEmp1 = "insert into emp values(10,'jay','trainee')";
      String insertEmp2 = "insert into emp values(11,'jayes','trainee')";
      String insertEmp3 = "insert into emp values(12,'shail','trainee')";
      con.setAutoCommit(false);
      
      stmt.addBatch(insertEmp1);
      stmt.addBatch(insertEmp2);
      stmt.addBatch(insertEmp3);
      
      ResultSet rs = stmt.executeQuery("select * from emp");
      rs.last();
      System.out.println("rows before batch execution= "+ rs.getRow());
      stmt.executeBatch();
      con.commit();
      
      System.out.println("Batch executed");
      rs = stmt.executeQuery("select * from emp");
      rs.last();
      System.out.println("rows after batch execution = "+ rs.getRow());
   }
} 

结果

以上代码示例将生成以下结果。结果可能有所不同。

rows before batch execution = 6
Batch executed
rows after batch execution = = 9