在Java中执行多个SQL语句

Question

我想在 Java中 执行查询。

我创建一个连接。然后，我想执行一条INSERT语句，完成后关闭连接，但是我想通过连接执行一些插入语句，并在循环完成后关闭连接。

我能做些什么 ？

我的示例代码是：

public NewClass() throws SQLException {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
        System.out.println("Where is your Oracle JDBC Driver?");
        return;
    }
    System.out.println("Oracle JDBC Driver Registered!");

    Connection connection = null;
    try {
        connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl1", "test",
                "oracle");
    } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        return;
    }

    if (connection != null) {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * from test.special_columns");
        while (rs.next()) {
            this.ColName = rs.getNString("column_name");
            this.script = "insert into test.alldata (colname) ( select " +  ColName + "   from test.alldata2       ) " ;
            stmt.executeUpdate("" + script);
        }        
    }
    else {
        System.out.println("Failed to make connection!");
    }        
}

当执行select语句（"SELECT * fromtest.special_columns"）时，循环必须为两次，但是当（stmt.executeUpdate("" + script)）执行并完成时，则关闭连接并从类中返回。

Answer 1

以下示例使用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://localhost: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

来源：执行多个SQL语句