0

First question for my first job in the industry, please be gentle.

I'm currently doing quite a lot of work with a MySQL database in Java using the java.sql library, working on code that was handed down to me. The existing way it handles executing an SQL statement comes in two varieties:

Connection connection = DriverManager.getConnection(...);
// code code code

Statement stmt = connection.createStatement();
ResultSet res = stmt.execute("SQL code here");
// code code code
stmt.close();
connection.close();

or

Connection connection = DriverManager.getConnection(...);
// code code code

ResultSet res = connection.createStatement().execute("SQL code here");
// code code code
connection.close()

The first option is much more common in the code I was given. A lot of different Statement objects being made for one or two queries and immediately closed.

The second option, at least to my eye, seems a lot cleaner and I would like to implement it, but I do worry that this does not close the Statement object being created. Would the second option actually cause any problems in terms of execution time, memory usage, etc. or would it actually improve/not change anything?

EDIT: the connection objects are being opened at the beginning of the program, stay open throughout, and close at the end.

C Howell
  • 51
  • 6
  • 1
    You seem to be ignoring a **very** important aspect here, which is closing the resources you're using. Both codes are wrong in that aspect. – Kayaman Feb 01 '18 at 14:39
  • @Kayaman As in closing the connection object too? – C Howell Feb 01 '18 at 14:42
  • Yes, closing the connection too. Unless you immediately reuse the connection (which is rare), or in **very** simple code where you have a single connection in use, such as a desktop app that never needs to use 2 connections at the same time. – Kayaman Feb 01 '18 at 14:44
  • There are two connections being made at a time max. One is made near the start of the program and closed near the end, and one is being opened and closed for separate DB's on the server while the first is being maintained. I find it sort of convoluted personally so if that's not a smart way to do it I'm definitely all ears. I apologize, I'm not extremely experienced. – C Howell Feb 01 '18 at 14:50
  • Aren't you using connection pooling? – Kayaman Feb 01 '18 at 14:52
  • Just looked, it seems the program is being executed with jdk1.7, which I believe is Java 7, correct? – C Howell Feb 01 '18 at 15:06
  • Not closing statements may also consume memory (and maybe other resources) both in your Java application and in your database until your application closes the connection. That is generally a bad thing. – Mark Rotteveel Feb 01 '18 at 15:15
  • Yes, Java 7. That's where the `try-with-resources` was introduced. – Kayaman Feb 01 '18 at 15:18
  • So the better way to handle these is the first option? Creating a Statement object, using it, and closing it? – C Howell Feb 01 '18 at 15:32
  • The duplicate question shows the standard idiom. – Kayaman Feb 01 '18 at 15:33

0 Answers0