5 Tips to be a Good JDBC Programmer

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'");


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

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.

12 thoughts on “5 Tips to be a Good JDBC Programmer

  1. Hi Scott,

    nice article. I don’t know if you have seen Spring’s JDBC template. It’s probably a pattern that has been implemented by many people before, but the application of the template/callback patterns make JDBC programming a breeze. In addition to everything you’re mentioning above, the JDBC template gives you exception translation as well, essentially preventing you from ever having to read another ORA error code again.

    Disclaimer: I do work for SpringSource

  2. Spring-iBatis and plain-iBatis also works very well – specially with existing database’s

    I do agree – don’t use stored procedure unless really required to do some massive batch operations.

  3. Hi Alef,

    Thanks for your comments. I do agree Spring makes a lot of JDBC programming easier, unfortunately the vast majority of JDBC developers still work with straight JDBC connections outside of any good framework. In fact, some JDBC programmers code from within JSPs (the horror!). Perhaps I should add that as a 6th tip.

  4. Thanks Daniel,

    It’s true batch PreparedStatement’s are excellent for bulk operations, but I tend to file those features under the category of ‘advanced JDBC’. The majority of beginner-to-intermediate JDBC programmers can get by without knowing much about them; unless of course your application is highly dependent on bulk operations.

  5. I particularly like tip #2. An additional reason I like is the ability to pass in dates as objects rather than having to deal with database formatting.

  6. As a former backend developer I have three comments:
    – About Tip #3 & #5: In my experience repository never change “almost specific situations”, and this is always because of the money related to this change. So why not design to take advantage of that special feature Oracle or MySQL has.
    – About Persistence Frameworks:
    My first contact with Spring was because of the nice abstraction that brings JdbcTemplate. These kind of frameworks: SpringJdbc, iBatis, Hibernate, JPA, addresses diferent kind of problems (sometimes overlaps), but they bring a different level of abstraction, away from a bunch of boiler code that implies the use of JDBC.
    – You forgot *TESTING*. This is a must in the persistence frameworks that I mentioned before.

  7. I’m torn when it comes to tips #4 and #5. On one hand, I agree that it can be asking for trouble to rely on proprietary RDBMS features. On the other hand, why pay for vendor-specific features unless you plan on leveraging them?

  8. “Jim d.” wrote: “why pay for vendor-specific features unless you plan on leveraging them?”

    My response is simple: “why pay for vendor-specific features?” If a lower cost (or free) solution meets the needs of the business, use it.

    I am happy to pay for speed, robustness, scalability and general quality, but I want the ability to change vendor when a different product offers a better combination of these things.

    Vendor-specific database features are simply the old “embrace and extend” technique to ensure lock-in, and the way to avoid that has always been to code to common standards.

  9. Much of your post makes sense, but #5…

    > you can’t even recall exactly what they did when you wrote them.

    If you cannot recall that, then you probably cannot recall what table X and table Y were supposed to mean. Then you might as well just dump all data into a single clob and full-text search for it… 😉

    Seriously, that is really not a good reason not to use stored procedures.

  10. Pingback: Mistakes Java Developers Make when Writing SQL – Deep Coding Experience and Knowledge for Sharing

Leave a Reply

Your email address will not be published.