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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments