Java PreparedStatement：如何使用JDBC将数据插入到多个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22000915/
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
PreparedStatement: How to insert data into multiple tables using JDBC
提问by skip
Could somebody tell me whether the first stmt.close();required in the following JDBC code, for executing two different SQL queries against two different tables?
有人能告诉我stmt.close();以下 JDBC 代码中是否需要第一个，用于对两个不同的表执行两个不同的 SQL 查询？
public class MyService {
private Connection connection = null;
public void save(Book book) {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "password");
PreparedStatement stmt = connection.prepareStatement("INSERT INTO PUBLISHER (CODE, PUBLISHER_NAME) VALUES (?, ?)");
stmt.setString(1, book.getPublisher().getCode());
stmt.setString(2, book.getPublisher().getName());
stmt.executeUpdate();
stmt.close(); //1
stmt = connection.prepareStatement("INSERT INTO BOOK (ISBN, BOOK_NAME, PUBLISHER_CODE) VALUES (?, ?, ?)");
stmt.setString(1, book.getIsbn());
stmt.setString(2, book.getName());
stmt.setString(3, book.getPublisher().getCode());
stmt.executeUpdate();
stmt.close(); //2
} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }
finally { connection.close(); }
}
}
采纳答案by beny23
In my book, I would always recommend closing resources that have been opened to avoid possible leaks.
在我的书中，我总是建议关闭已打开的资源以避免可能的泄漏。
A slightly more modern way would be to use try-with-resources:
一种更现代的方法是使用try-with-resources：
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "password")) {
try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO PUBLISHER (CODE, PUBLISHER_NAME) VALUES (?, ?)")) {
stmt.setString(1, book.getPublisher().getCode());
stmt.setString(2, book.getPublisher().getName());
stmt.executeUpdate();
}
// stmt is auto closed here, even if SQLException is thrown
try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO BOOK (ISBN, BOOK_NAME, PUBLISHER_CODE) VALUES (?, ?, ?)");
stmt.setString(1, book.getIsbn());
stmt.setString(2, book.getName());
stmt.setString(3, book.getPublisher().getCode());
stmt.executeUpdate();
}
// stmt is auto closed here, even if SQLException is thrown
}
// connection is auto closed here, even if SQLException is thrown
回答by Mike B
It is not required but it is recommended. http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#close()
它不是必需的，但建议使用。http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#close()
The next line after the first close()in your code assigns a new value to the reference stmt, so the object you use to execute the first insert will be GC'd and closed eventually. It is good practice to go ahead and close it when you know you're done with it. This releases the JDBC resources immediately.
close()代码中第一行之后的下一行为引用分配了一个新值stmt，因此用于执行第一个插入的对象将被 GC 处理并最终关闭。当你知道你已经完成了它时，继续并关闭它是一种很好的做法。这会立即释放 JDBC 资源。
回答by Kakarot
Its good practice to close the Statement handle as it will release the JDBC & Database resources. You can read more about stmt.close() here
关闭 Statement 句柄是一种很好的做法，因为它会释放 JDBC 和数据库资源。您可以在此处阅读有关 stmt.close() 的更多信息
I would like to point out that its good to close your Statement object in finally block so that DB resources are released even if an Exception occurs.
我想指出，最好在 finally 块中关闭 Statement 对象，以便即使发生异常也能释放 DB 资源。
回答by Sarah
Yes, both the stmt.close() methods are necessary. You should always explicitly close the Statement or PreparedStatement object to ensure proper cleanup.
是的，这两个 stmt.close() 方法都是必要的。您应该始终明确关闭 Statement 或 PreparedStatement 对象以确保正确清理。
回答by OldCurmudgeon
It is a common misconception with statements that closing them releases all that preparing them built. This is wrong. The optimisation that results in the preparation of the statement is performed by the database. It is then stored/cached by the database and usually re-used next time the statement is prepared.
关闭它们会释放所有准备构建的语句，这是一种常见的误解。这是错误的。导致准备语句的优化由数据库执行。然后由数据库存储/缓存，通常在下次准备语句时重新使用。
As a result, prepared statements can be closed and prepared as often as you wish - the database will recognise the same statement next time around and recover the cached preparations it made last time - if it wishes to.
因此，准备好的语句可以根据您的需要随时关闭和准备 - 数据库将在下次识别相同的语句并恢复它上次所做的缓存准备 - 如果它愿意的话。
In summary - yes, statements should be closed - and no, this does notreduce the effectiveness of your queries.
总之-是的，声明应被关闭-并没有，这并不会减少您的查询的效率。
回答by Ivan Nieves
try{
String insertIntoCust = "insert into NORTHWIND_CUSTOMER(CUSTOMER_ID,FIRST_NAME,LAST_NAME,ADDRESS,CITY,STATE,POSTAL_CODE) values(?,?,?,?,?,?)";
pst = connect.prepareStatement(insertIntoCust);
pst.setString(1, txtCustomerId.getText());
pst.setString(2, txtFirstName.getText());
pst.setString(3, txtLastName.getText());
pst.setString(4, txtAddress2.getText());
pst.setString(5, txtCity.getText());
pst.setString(6, txtState.getText());
pst.setString(7, txtPostalCode.getText());
pst.execute();
String insertIntoOrder = "insert into NORTHWIND_ORDER(ORDER_ID,ORDER_DATE) values(?,?)";
pst = connect.prepareStatement(insertIntoOrder);
pst.setString(1, txtOrderId.getText());
pst.setString(2, txtOrderDate.getText());
pst.execute();
JOptionPane.showMessageDialog(null, "Saved");
}catch(Exception e){
JOptionPane.showMessageDialog(null, e);
}
With the code above, I could insert data into multiple tables with one button. The pst.execute need to be inserted in both queries; if not, the query that have the pst.execute is the table that will receive the data.
使用上面的代码，我可以一键将数据插入到多个表中。pst.execute 需要插入到两个查询中；如果没有，具有 pst.execute 的查询就是将接收数据的表。
