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(); }
}
}
The T-SQL function OUTPUT, which was introduced in 2005, can be used to insert multiple values into multiple tables in a single statement. The output values of each row that was part of an INSERT, UPDATE or DELETE operation are returned by the OUTPUT clause.
No, you can't insert into multiple tables in one MySQL command. You can however use transactions. BEGIN; INSERT INTO users (username, password) VALUES('test', 'test'); INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!
Once a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again.
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
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 not reduce the effectiveness of your queries.
It is not required but it is recommended. 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.
Yes, both the stmt.close() methods are necessary. You should always explicitly close the Statement or PreparedStatement object to ensure proper cleanup.
Its good practice to close the Statement handle as it will release the JDBC & Database resources. You can read more about stmt.close() here
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With