This is not the correct way of retrieving a connection. You’re retrieving the connection and assigning it as an instance (or worse, static) variable of the class. Basically, you’re keeping the connection open forever and reusing a single connection for all queries. This may end up in a disaster if the queries are executed by different threads. Also, when it’s been kept open for too long, the DB will reclaim it because it assumes that it’s dead/leaked.
You should acquire and close the connection in the shortest possible scope. I.e. in the very same try block as where you’re executing the query. Something like this:
public Entity find(Long id) throws SQLException {
Entity entity = null;
try (
Connection connection = dataSource.getConnection(); // This should return a NEW connection!
PreparedStatement statement = connection.prepareStatement(SQL_FIND);
) {
statement.setLong(1, id);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
if (resultSet.next()) {
entity = new Entity(
resultSet.getLong("id"),
resultSet.getString("name"),
resultSet.getInt("value")
);
}
}
}
return entity;
}
If you worry about connecting performance and want to reuse connections, then you should be using a connection pool. You could homegrow one, but I strongly discourage this as you seem to be pretty new to the stuff. Just use an existing connection pool like BoneCP, C3P0 or DBCP. Note that you should not change the JDBC idiom as shown in the above example. You still need to acquire and close the connection in the shortest possible scope. The connection pool will by itself worry about actually reusing, testing and/or closing the connection.