common mistake on statements

Most of the memory leaks that I have seen with Java occurred when developers forgot to close database statements. a mistake in closing logic propagates to many places.
Generally I keep my JDBC program structure as follows

PreparedStatement st=null, st2 = null ....
Connection con = null ....

try {
//your JDBC code goes here ....
con = ConnectionMgr.getConnection(); //get the connection from a connection broker class...
st = con.prepareStatement(
"INSERT INTO subscribers (name, email) VALUE (?, ?)");
st.setString(1, name);
st.setString(2, email);
st.executeUpdate();
..
..
..
}
catch(SQLException e) {
//handle or log exception
}
finally {
//ensure in finally all the resources are collected...
try {
if(st!=null) {
st.close();
}
if(st2!=null) {
st2.close()
}
if(con!=null) {
con.close();
}
}
catch(SQLException e) {
//handle or log exception ...
}
}

I have to write atleast 10 additonal lines to make sure whatever I have opened is closed properly.
I have heard people saying .. why to bother about closing the connections/statements the garbage collector is there to do the job :)
Dear friends the garbage collector is never going to do this for you .. if you are relying on GC to do this job for you then be prepared for worst things coming your way.
If you don't close the connections what is going to happen?
Every database has a timeout set on a connection. if the connection does not do any activity for that time period the database is going to expire the connection. so if you dont close the connection yourself the connection would be stake untill its clotimedout. so greater is the timeout value the higher is the time such orphaned connections are sitting in JVM, eventually its possible that your application runs out of limit for the maximum possible connections to the database.

However, the problem still remains of how to write code that closes the statements and result sets reliably. You want to always attempt to close the statements, whether an exception occurs or not. In the Jakarta Commons DbUtils project, functions are provided to ensure that your statements are also always closed.

JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

This is how you would use its QueryRunner:

import org.apache.commons.dbutils.QueryRunner;
import java.sql.*;

public class Database2 {
private QueryRunner queryRunner = new QueryRunner();
public int insertSubscriber(Connection con, String name, String email)
throws SQLException {
String sql = "INSERT INTO subscribers (name, email) VALUE (?, ?)";
Object[] params = { name, email };
return queryRunner.update(con, sql, params);
}
}

There are very good features available in DB utils e.g. for a given select query ResultSet you can associate the java beans and when you fire the query you get the collection of associated java objects. please have a look at Examples here.