在Java中执行多个SQL语句 [英] Execute multiple SQL statements in java

问题描述

我想在 Java 中执行查询.

我创建了一个连接.然后，我想执行一个INSERT语句，完成后，该连接被关闭，但是我想通过一个连接来执行一些插入语句，当循环完成后，再关闭连接.

I create a connection. Then I want to execute an INSERT statement, when done, the connection is closed but I want to execute some insert statement by a connection and when the loop is finished then closing connection.

我该怎么办?

我的示例代码是:

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 * from test.special_columns")时，循环必须为两次，但是执行(stmt.executeUpdate("" + script))时，则关闭连接并从类中返回.

When the select statement ("SELECT * from test.special_columns") is executed, the loop must be twice, but when (stmt.executeUpdate("" + script)) is executed and done, then closing the connection and return from the class.

推荐答案

以下示例使用addBatch& executeBatch命令以同时执行多个SQL命令.

Following example uses addBatch & executeBatch commands to execute multiple SQL commands simultaneously.

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());
   }
} 

结果: 上面的代码示例将产生以下结果.结果可能会有所不同.

Result: The above code sample will produce the following result.The result may vary.

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

来源:执行多个SQL语句

这篇关于在Java中执行多个SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！