In general pooling is suitable for any expensive limited resource. What about database connections ?
Establishing new database is time consuming
Utilize network infrastructure
Initializing a database connection session in the back end database
perform user authentication and many more
So it is reasonable to have valuable database connection in a pool.
sample source code to get a connection from connection pool
try {
Connection dbConnection = dataSource.getConnection();
dbConnection.setAutoCommit(false);
dbConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
return dbConnection;
} catch (SQLException e) {
String errMsg = "Error when getting a database connection object from the Identity data source.";
}
Why you should close database connection once operation is done ?
Yeah….! from common sense we know after closing database connection it will return back to the connection pool and so that others can use it. If you didn’t close it waste of valuable resource.
Let’s think bit more deep
try {
connection = dataSource.getConnection();
} finally {
connection.close();
}
What is wrong here ..? what happen dataSource.getConnection() throws an exception and connection assign to null and you are getting NPE instead of SQLException
OK……. but simple null check would do the job
Now you have to close statement too…
try {
connection = dataSource.getConnection();
statement=connection.createStatement();
} finally {
if (statement != null) statement.close();
if (connection != null) connection.close();
}
What if statement.close() throws an exception ?
Now it is being get complicated with different combinations.
See this Util method structure
public static void closeAllConnections(Connection dbConnection, ResultSet rs, PreparedStatement prepStmt) {
closeResultSet(rs);
closeStatement(prepStmt);
closeConnection(dbConnection);
}
public static void closeConnection(Connection dbConnection) {
if (dbConnection != null) {
try {
dbConnection.close();
} catch (SQLException e) {
log.error("Database error. Could not close statement. Continuing with others. - " + e.getMessage(), e);
}
}
}
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
log.error("Database error. Could not close result set - " + e.getMessage(), e);
}
}
}
public static void closeStatement(PreparedStatement preparedStatement) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
log.error("Database error. Could not close statement. Continuing with others. - " + e.getMessage(), e);
}
}
}
Common mistake could happen with prepared statement
- Never dereference prepared statement
prepStmt = conn.prepareStatement(sqlStmt1);
//do some stuff
prepStmt = conn.prepareStatement(sqlStmt2);
missed reference to prepared statement
- Use prepStmt.clearParameters() to clear all parameters if you want to execute same prepared statement with different set of parameters
- Use prepStmt.clearBatch() to clear all batch entries
