jeffery_the_wind jeffery_the_wind - 2 months ago 6
Java Question

Proper way to handle SQL database connections in java OOP style

I recently did a programming challenge. It was an OOP challenge to expose an API for managing a fictitious movie rental company. I chose to use sparkjava, and worked from a heroku template. I have posted the code to Github here, and invite anyone to query the URL provided in the README doc. The secret password is

secret
.

I received a lot of good feedback about things that need improvement. One objection is:


Handling connections directly vi the driver, without any handling for
closing connections properly.


So I am trying to figure out what this means, why its a problem, and how to fix it and make my code better.

For example I have this method, in the main class:

public static int validate_customer(String cust) throws SQLException, URISyntaxException {
int customer = 0;
try{
customer = Integer.parseInt(cust);
}catch (Exception e){
throw new SQLException("Invalid customer integer -> " + cust);
}

Connection connection = DatabaseUrl.extract().getConnection();
PreparedStatement stmt = connection.prepareStatement("SELECT count(*) from customers where id = ?;");
stmt.setInt(1, customer);
ResultSet rs = stmt.executeQuery();
rs.next();
if ( rs.getInt(1) < 1 ){
throw new SQLException("Invalid customer id -> " + customer);
}
rs.close();
stmt.close();
return customer;
}


Why is this an incorrect way to handle interaction with the DB? There are other methods there in the main class which interact with the DB but the technique is basically the same as this example.

To me, if there was a problem with the connection to the database, it would occur in the line:
Connection connection = DatabaseUrl.extract().getConnection();
and throw an
SQLException
. If somethings happens to the connection, and some part of the query doesn't work, an exception will be thrown from this
validate_customer
method. Why is this a problem in my code?

Answer

Handling connections directly vi the driver

This means you are not using a DB connection pool (like HikariCP), which means it's possible exhaust your connection limit very quickly. The Heroku example does not use a DB connection pool to limit dependencies in the example.

without any handling for closing connections properly.

This means the stmt.close(); is not a in a finally block, which means if an exception is thrown in the method the stmt will never be closed.