code analyzers

>. I need something to find

> the not closing db resources..

In my code I have a central place I get connections. I have a flag that during testing wraps every connection with a ConnectionWrapper. Ever method of Connection is wrap and deligated to the real Connection method. Calls to getStatement(), preparedStatement(), etc., also return wrappers. Keep track of when close() is called in the wrappers and then check in the finalize() method to see if close has been called. If it hasn't then print the offending sql.

Caffeine0001a at 2007-7-13 5:04:06 >

Thank you for answer but I don;t think it is a good approach.. to rely on finalize methos for cleaning up resources or even to see if resources was closed.

Because I work with connection pool and that means that the objects (connections) will have all the time at least one reference (from the connection pool) ..so it will not garbage collected (only if the thread which manage the connection pool consider it has to reduce the number of active connections in the pool)

I have looked at p6spy and i think it is a great product for optimize the db access but not for debuging closing or not closing db resources. The p6spy interpose between "my application" and datasources managed by application server...and every sql which is executed will pass through p6spy (and p6spy will log)

So I think the only solution is to analyze the code.....if have another idea please let me know.

Thank you,

Alexandru Barbat

talleyrand2005a at 2007-7-13 5:04:06 >

Wrapping the Connection:

public Connection getConnection() throws SQLException {

Connection connection = dataSource.getConnection();

if (connectionWrapped) {

return new TempConnection(connection);

} else {

return connection ;

}

}

Connection Wrapper:

static int counter = 0;

static int nested = -1;

static public class TempConnection implements Connection {

Connection con;

public int id = ++counter;

boolean isClosed = false;

String lastQuery;

public TempConnection(Connection connection) {

nested++;

log("TempConnection");

con = connection;

}

public Connection getNativeConnection() {

return con;

}

protected void log(String msg) {

logIt(nested, id, -2, -2, msg, null);

}

protected void log(String msg, String msg2) {

logIt(nested, id, -2, -2, msg, msg2);

}

public void clearWarnings() throws SQLException {

log("clearWarnings");

con.clearWarnings();

}

public void finalize() {

if (!isClosed) {

log("################## Closing connection on finalized ############## lastQuery="+lastQuery);

logger.warning("############## Closing connection on finalized " + id + " lastQuery=" + lastQuery);

try {

close();

} catch (SQLException ex) {}

}

}

public void close() throws SQLException {

log("close");

if (isClosed) {

log(" Already closed ");

logger.warning("Warning connection already closed " + id + " lastQuery=" + lastQuery);

} else {

isClosed = true;

nested--;

con.close();

con = null;

}

}

public void commit() throws SQLException {

log("commit");

con.commit();

}

public Statement createStatement() throws SQLException {

log("createStatement");

return new TempStatement(this,con.createStatement());

}

public Statement createStatement(int param, int param1) throws SQLException {

log("createStatement");

return new TempStatement(this,con.createStatement(param, param1));

}

public Statement createStatement(int param, int param1, int param2) throws SQLException {

log("createStatement");

return new TempStatement(this, con.createStatement(param, param1, param2));

}

public PreparedStatement prepareStatement(String str) throws SQLException {

log("prepareStatement",str);

lastQuery = str;

return new TempPreparedStatement(this,con.prepareStatement(str), str);

}

//just wrap the rest of the methods the same way

public void setTypeMap(Map<String, Class><?>> map) throws SQLException {

log("setTypeMap");

con.setTypeMap(map);

}

}

Statement wrapper:

static int sCount = 0;

static int sNested = -1;

static public class TempStatement implements Statement {

Statement ps;

TempConnection con;

int id = ++sCount;

boolean isClosed = false;

String lastQuery;

public TempStatement(TempConnection con, Statement ps) {

sNested++;

this.con = con;

this.ps = ps;

log("TempStatement");

}

public Statement getNativeStatement() {

return ps;

}

protected void log(String msg) {

logIt(nested + sNested, id, (con == null ? -1 : con.id), -2, msg, null);

}

protected void log(String msg, String msg2) {

logIt(nested + sNested, id, (con == null ? -1 : con.id), -2, msg, msg2);

}

public void addBatch(String str) throws SQLException {

log("addBatch");

ps.addBatch(str);

}

public void finalize() {

if (!isClosed) {

log("################## Closing statement on finalized ############## lastQuery=" + lastQuery);

logger.warning("############## Closing statement on finalized " + id + " lastQuery=" + lastQuery);

try {

close();

} catch (SQLException ex) {}

}

}

public void close() throws SQLException {

log("close");

if (isClosed) {

log(" Already closed ");

logger.warning("Warning statement already closed " + id + " lastQuery=" + lastQuery);

} else {

sNested--;

isClosed = true;

ps.close();

ps = null;

con = null;

}

}

public ResultSet executeQuery(String str) throws SQLException {

log("executeQuery", str);

lastQuery = str;

return new TempResultSet(this, ps.executeQuery(str));

}

public int executeUpdate(String str) throws SQLException {

log("executeUpdate", str);

lastQuery = str;

return ps.executeUpdate(str);

}

public Connection getConnection() throws SQLException {

log("getConnection");

return con;

}

// just wrap the rest of the methods.

}

Follow the same pattern for ResultSet and PreparedStatement..

As a bonus I also use the following utility helper methods to close JDBC resources. It makes the try { } finally { } code look much cleaner.

static public void close(ResultSet resultSet) {

try {

if (resultSet != null) {

resultSet.close();

}

} catch (SQLException e) {

logger.log(Level.WARNING,"Error closing resultSet", e);

}

}

static public void close(Statement statement) {

try {

if (statement != null) {

statement.close();

}

} catch (SQLException e) {

logger.log(Level.WARNING,"Error closing statement", e);

}

}

static public void close(Statement statement, ResultSet resultSet) {

try {

close(resultSet);

} finally {

close(statement);

}

}

static public void close(Connection con, Statement statement, ResultSet resultSet) {

try {

close(statement, resultSet);

} finally {

try {

if (con != null) {

con.close();

}

} catch (SQLException e) {

logger.log(Level.WARNING,"Error closing connection", e);

}

}

}

static public void close(Connection con, Statement statement) {

close(con, statement, null);

}

Code example:

Connection connection = null;

PreparedStatement ps= null;

ResultSet rs = null;

try {

connection = getConnection();

....

} finally {

SQLUtil.close(connection,ps,rs);

}

Caffeine0001a at 2007-7-13 5:04:06 >