PreparedStatement：如何使用JDBC将数据插入多个表 [英] PreparedStatement: How to insert data into multiple tables using JDBC

问题描述

有人可以告诉我，以下JDBC代码中是否需要第一个 stmt.close（）; ，以便针对两个不同的表执行两个不同的SQL查询？

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?

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

推荐答案

在我的书中，我总是建议关闭已经打开的资源以避免可能的泄漏。

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 (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

这篇关于PreparedStatement：如何使用JDBC将数据插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！