Have you written Java code for your database connections in JDBC that ends up being thrown away? Would using a new database software easily kill your product and set you back months at a time to port? Well, if so, keep reading as the goal of this article is to make you a better JDBC programmer.

Tip #1: ALWAYS close connections

I wrote an entry about closing database connections back in July so I won’t belabor the point. Suffice it to say, one of the most common mistakes JDBC programmers make is failing to close database resources like result sets, statements, and connections. Unfortunately, because of the nature of the problem a developer will often never see the error until it is discovered in a production environment since most developers test with one process at a time. In short, every time you open a connection you should close it sometime later and that close should be nearly bullet proof, as so:

Connection con;
try {
   con = ...
} finally {
   try { if(con!=null) {con.close();}}
   catch (Exception e1) {}
}

As mentioned in the post about the subject, you can replace the finally code with a call to a method such as closeConnection() which does the same thing.

Tip #2: Always use PreparedStatements over regular Statements

As you may be aware, PreparedStatements come with many advantages over regular Statements:

• Sanitizes your database inputs: Great Example
• Organizes your statements into set of input/output commands
• Performance boost: The pre-compiled statement can be reused multiple times

The mistake a lot of programmers make is when they say “Well, I have no inputs to the query, so I’m not going to use PreparedStatements”. First off, they may have inputs down the road they needed to ‘tack on’ to the query, and it will save a lot of time rewriting the code if the query is already structured as a PreparedStatement. Second, perhaps there are some hard-coded input values that you can’t imagine changing, but could change down the road. Better to use a PreparedStatement and a static object, than to hard-code it, as shown in this example:

Statement statement = con.createStatement();
statement.executeQuery("SELECT name FROM widgets WHERE type = 'WidgetB'");

versus:

final String widgetType = "WidgetB";
Statement pStatement = con.prepareStatement("SELECT name FROM widgets WHERE type = ?");
pStatement.setString(1,widgetType);
pStatement.executeQuery();

Sure, the first example is shorter, but the second gives you the freedom to easily change the Widget Type down the road as well as supporting additional inputs since the setup work is all ready done.

Tip #3: Be Prepared To Change Databases

It’s not a myth; database platforms do change. You may be working on a project using Oracle and you get a request from your manager for an estimate to port the code to MySQL. It can and does happen, the question is are you going to respond with “a couple of days/weeks” or “12-24 months”. Your answer should shed some light on how good your code really is.

Tip #4: Never Reference Database-Specific Libraries

If you ever find yourself writing a line of code such as this:

OracleCallableStatement cst = (OracleCallableStatement)conn.prepareCall ...

Stop! This is an example where a regular Callable Statement is likely called for, but you’re using a database-specific line in your code. The whole purpose of JDBC driver pattern is that you can drag and drop new JDBC driver libraries for MySQL, Oracle, etc into your application and the software will still compile and run just fine. Lines of code like the one above require you have the JDBC library available at compile-time and guarantee you’ll have to rewrite code to port to another language. In other words, writing a line of code like this is a developer admitting to themselves “I will definitely need to rewrite this line of code if I want to port to another database”.

Tip #5: Never use Database Stored Procedures*

Never, ever, write a Java application that relies, in large part, on stored procedures. Not only can they never be ported to a different database, 9 times out of 10, you can’t even recall exactly what they did when you wrote them. They fall into the same category of Perl code, which is WORN (write once, read never), since most of the time reading them is harder than rewriting them, mostly because TSQL/PSQL are not very pretty languages to work with.

* There some cases where stored procedures are allowed, such as reporting statistics or materialized views, but these are few and far between. The bulk of your main application logic, such as adding new users, performing basic transactions, and editing data should not rely on stored procedures in any way. Reporting is allowed here because often times performing metrics on millions of records in JDBC would require too many round trips over the network. In other words, reporting requires the kind of direct network access stored procedures guarantee since they run on the database itself. But if you are stuck using them, you should insulate them so they don’t interact with any of your java code directly, such as an independent nightly build of statistics.

Bonus Tip #6: Never put JDBC code inside JSPs

I started with 5 tips but our reader’s comments reminded me of a 6th tip: Never put JDBC code of any kind inside a JSP! The purpose of a 3-tiered architecture is to get good separation of layers, and putting JDBC code inside the presentation tier skips the middle layer all together. Some developers reduce the impact of this mistake by putting their connection code inside a Java file and only passing the result set to a JSP. This is still really bad! JSPs should have no JDBC code whatsoever since most of the time queries can be reused by multiple modules and JSPs don’t exactly lend themselves to resusability.