PreparedStatement: How to insert data into multiple tables using JDBC

java, jdbc, prepared-statement, sql

Solution

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

Problem

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

Original source