JDBC Best Practices
Posted : Friday May 15th, 2009

I have had a few posts before relating to some JDBC best practices but I thought I'd list the top five all in one post. These are good ideas for making your JDBC code safe, secure, stable and portable.

Properly Close Your Resources

I discussed this point in a previous entry you can find here but is bears repeating as this is the biggest source of bugs in deployed applications that I have seen.

You must always close all your JDBC resources (by calling close) in the opposite order that you create them. Failing to properly close your JDBC resources can lead to dire consequences including the total lockup/failure of your application. Or worse.

And properly closing means calling close. Setting JDBC resources to null is a major mistake and a sign of a very unprofessional programmer. Setting a JDBC resource to null is not equivalent to calling close (again for details see this).

Use a Connection Pool

Unless your application is a one connection loading/filtering/reporting stand-alone special using a connection pool is a good idea. For everything else use a connection pool, and in case it isn't clear if you have a J2EE project then that means you. When you have multiple connections, connection pools improve performance. And really enough said.

What I should add though is that you should use an existing pool, don't try and implement your own, which some people seem to try for some unknown reason. There are many existing, working implementations, including many free and open-source ones. These pools work properly, in managing stale and closed connections. Use them.

Use PreparedStatements

This is another point I have addressed repeatedly including the SDN Share site but it can't go mentioned enough.

Proper use of PreparedStatements makes your code more portable, *may* improve performance but most importantly makes your code safe. Any time you have any query with user supplied parameter it is a JDBC best practice to use a PreparedStatement (or CallableStatement) and bind the parameter at runtime to it. Doing anything else is a serious error and anyone who tells you differently (and sadly there are people who do) doesn't know what they are talking about.

Making your code safe from SQL injection attacks is a simple thing to do and it's always the right thing.

Don't use SELECT *

This practice is one that is not specific to JDBC really but a general best practice for SQL programming. When you have a SELECT query you should always specifically list the columns you want and not use *. Listing your columns means your code is protected against changes to the layout of the table (which does happen) and it's also more self-documenting.

The bottom line is that using SELECT * is just lazy and it makes your code more brittle than it needs to be so why do it. It just makes sense to take the 5 seconds and actually list the columns you want.

Keep your SQL Portable

Finally it's a JDBC best practice to make your SQL as vendor (aka database type) neutral as possible. You can't always be perfect, sometimes, especially with stored procedures it simply isn't possible, but you should try as much as you can to only use SQL that can be used on any SQL compliant database.

There are three advantages to this. One, your code will be easier to port if the database ever does change. Two, your code is less likely to run into problems on database upgrades. This is a point not too many people seem to consider but upgrades and updates do happen and syntax for vendor specific implementations can change between versions. Three, your code is easier to pick up and follow. In theory JDBC code can be read by any JDBC programmer, you don't want to have to have a SQL Server programmer because you heavily used Transact-SQL.

In line with the above, if you really do need to use vendor specific SQL then consider using stored procedures (CallableStatements) as much as possible. The idea here being that if you must use vendor specific syntax you can properly refactor the database specific code into the database and leave your generic JDBC code intact.

Tags

best  JDBC  practices 

Categories

Technical  Java  Database 

Comments