java中执行多条SQL语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24157194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute multiple SQL statements in java
提问by saeed.sh
I want to execute a query in Java.
我想在Java 中执行查询。
I create a connection. Then I want to execute an INSERTstatement, 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.
我创建了一个连接。然后我想执行一个INSERT语句，完成后，连接关闭，但我想通过连接执行一些插入语句，当循环完成然后关闭连接。
What can I do ?
我能做什么 ？
My sample code is :
我的示例代码是：
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!");
}
}
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.
"SELECT * from test.special_columns"执行select语句( )时，必须循环两次，但是当( stmt.executeUpdate("" + script))执行完毕，则关闭连接，从类中返回。
采纳答案by MinhD
Following example uses addBatch& executeBatchcommands to execute multiple SQL commands simultaneously.
以下示例使用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());
}
}
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
Source: Execute multiple SQL statements
来源：执行多条SQL语句
回答by Nick Holt
In the abscence of the schema or the data contained in each table I'm going to make the following assumptions:
在没有每个表中包含的模式或数据的情况下，我将做出以下假设：
The table special_columnscould look like this:
该表special_columns可能如下所示：
column_name
-----------
column_1
column_2
column_3
The table alldata2could look like this:
该表alldata2可能如下所示：
column_1 | column_2 | column_3
---------------------------------
value_1_1 | value_2_1 | value_3_1
value_1_2 | value_2_2 | value_3_2
The table alldatashould, after inserts have, happened look like this:
alldata插入后，该表应该如下所示：
colname
---------
value_1_1
value_1_2
value_2_1
value_2_2
value_3_1
value_3_2
Given these assumptions you can copy the data like this:
鉴于这些假设，您可以像这样复制数据：
try (
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl1", "test", "oracle")
)
{
StringBuilder columnNames = new StringBuilder();
try (
Statement select = connection.createStatement();
ResultSet specialColumns = select.executeQuery("SELECT column_name FROM special_columns");
Statement insert = connection.createStatement()
)
{
while (specialColumns.next())
{
int batchSize = 0;
insert.addBatch("INSERT INTO alldata(colname) SELECT " + specialColumns.getString(1) + " FROM alldata2");
if (batchSize >= MAX_BATCH_SIZE)
{
insert.executeBatch();
batchSize = 0;
}
}
insert.executeBatch();
}
A couple of things to note:
有几点需要注意：
MAX_BATCH_SIZEshould be set to a value based on your database configuration and the data being inserted.- this code is using the Java 7 try-with-resourcesfeature to ensure the database resources are released when they're finished with.
- you haven't needed to do a
Class.forNamesince the service providermechanism was introduced as detailed in the JavaDoc for DriverManager.
MAX_BATCH_SIZE应该根据您的数据库配置和插入的数据设置一个值。- 此代码使用 Java 7 try-with-resources功能来确保数据库资源在完成后被释放。
- 您不需要执行任何操作，
Class.forName因为在DriverManager的 JavaDoc 中详细介绍了服务提供者机制。
回答by Mark Rotteveel
There are two problems in your code. First you use the same Statementobject (stmt) to execute the select query, and the insert. In JDBC, executing a statement will close the ResultSetof the previous execute on the same object.
您的代码中有两个问题。首先，您使用相同的Statement对象 ( stmt) 来执行选择查询和插入。在 JDBC 中，执行一条语句将关闭ResultSet之前在同一对象上执行的语句。
In your code, you loop over the ResultSetand execute an insert for each row. However executing that statement will close the ResultSetand therefor on the next iteration the call to next()will throw an SQLExceptionas the ResultSetis closed.
在您的代码中，您循环遍历ResultSet并为每一行执行插入。但是，执行该语句将关闭 the ResultSet，因此在下一次迭代中，调用next()将SQLException在ResultSet关闭时抛出 an 。
The solution is to use two Statementobjects: one for the select and one for the insert. This will however not always work by default, as you are working in autoCommit(this is the default), and with auto commit, the execution of any statement will commit any previous transactions (which usually also closes the ResultSet, although this may differ between databases and JDBC drivers). You either need to disable auto commit, or create the result set as holdable over commit (unless that already is the default of your JDBC driver).
解决方案是使用两个Statement对象：一个用于选择，一个用于插入。然而，autoCommit这在默认情况下并不总是有效，因为您正在工作（这是默认值），并且使用自动提交，任何语句的执行都将提交任何先前的事务（这通常也会关闭ResultSet，尽管这可能因数据库和JDBC 驱动程序）。您要么需要禁用自动提交，要么将结果集创建为可保留的提交（除非这已经是您的 JDBC 驱动程序的默认设置）。
