如何从java执行多条SQL语句 [英] How to execute multiple SQL statements from java

问题描述

我想在一次执行中执行多个查询或作业.像这样的东西例如:

I want to execute the multiple queries or job in one execute. Something like this eg:

String query="select * from tab1;insert into tab1 values(...);update tab1..;delete from tab1...;"
Statement st = con1.createStatement();
ResultSet rs = st.executeQuery(query); 

或多个选择查询.查询将是动态的.

Or multiple select queries.Queries will be dynamic.

但我无法做到这一点.运行以分号分隔的多个查询的方法是什么.

But I am not able to do this.What is the way to run multiple queries separated by semi colon.

推荐答案

您可以使用以下示例使用 addBatch & 来实现executeBatch 命令可同时执行多个 SQL 命令.

you can achieve that using Following example uses addBatch & executeBatch commands to execute multiple SQL commands simultaneously.

批处理允许您将相关的 SQL 语句分组为一个批处理，并通过一次调用数据库提交它们.参考

Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database. reference

当您一次向数据库发送多条 SQL 语句时，可以减少通信开销，从而提高性能.

When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.

• JDBC 驱动程序不需要支持此功能.您应该使用 DatabaseMetaData.supportsBatchUpdates() 方法来确定目标数据库是否支持批量更新处理.如果您的 JDBC 驱动程序支持此功能，则该方法返回 true.
• Statement、PreparedStatement 和 CallableStatement 的 addBatch() 方法用于将单个语句添加到批处理中.executeBatch() 用于开始执行组合在一起的所有语句.
• executeBatch() 返回一个整数数组，该数组的每个元素代表相应更新语句的更新计数.
• 就像您可以将语句添加到批处理中一样，您可以使用 clearBatch() 方法.此方法删除您使用 addBatch() 方法添加的所有语句.但是，您不能有选择地选择要删除的语句.

• JDBC drivers are not required to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature.
• The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together.
• The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
• Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement to remove.

示例:

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);//inserting Query in stmt
      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());
   }
} 

参考 http://www.tutorialspoint.com/javaexamples/jdbc_executebatch.htm

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