Phobos, DB Pooling And Connections

For those who have followed my data examples in the past, and have tried
them out, here is a recent issue I ran across. I have designed what
some would call a data facad, crud module, business object. Whatever,
you want to call it, it handles all of the data access for a particular
table. I was testing this on my laptop from the Netbeans environment,
and every time a page was requested five times, the Phobos runtime would
go into an endless loop. However, when you stopped the run time, a null
pointer exception would be thrown on this line of code:

conn =
java.sql.DriverManager.getConnection("jdbc:apache:commons:dbcp:oConn");

What this line does, is in my previous data examples. After doing some
checking around on Google about Java data pooling, it finally dawned on
me that I was running out of connections. I was closing the
connections, but the code was not getting executed. Here is an example
of the offending code in the module method which is called.

return obj;
stmt.close();
rset.close();
conn.close();

The code is never run, because execution stops at the return statement.
Some might think this would be obvious, but this behavior is not the
same in an ejs file or controller. My assumption was that it would get
run, and so we should get the data back asap. We could the worry about
cleanup code. However, all cleanup code needs to be run before the
return statement. It should look like this.

stmt.close();
rset.close();
conn.close();
return obj;

Small change, but big results. If we abstract our data access into
modules, it is fast and easy to clean this up versus having it scattered
in scripts.
Below is an example of a method of the crud class. I will hopefully be
posting all of the code for the data layer soon.

Happy Coding!

var stmt = null;
var rset = null;
var strSql = null;
// oConn is the connection pool set in application startup
conn =
java.sql.DriverManager.getConnection("jdbc:apache:commons:dbcp:oConn");
strSql = 'SELECT * FROM article WHERE ARTICLE_PK = ?';
stmt = conn.prepareStatement(strSql);
stmt.setInt(1, articleId); // articleId is passed in
rset = stmt.executeQuery();
// Call method used to generate a json object of record set
obj = this.generateJson(rset);
obj.status = true;
stmt.close();
rset.close();
conn.close(); //clean up
return obj;