dbutils

8 Jan

Overview

commons-dbutils is an Apache JDBC library that has been around since 2003. A number of projects use it to ensure that JDBC connections are properly closed. The main goals of the project are:

  • Small: You should be able to understand the whole package in a short amount of time.
  • Transparent: DbUtils doesn’t do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
  • Fast: You don’t need to create a million temporary objects to work with DbUtils.

I decided to fork the project and post it under SOP4J. The natural question is, “Why fork commons-dbutils, why not just contribute?” The answer is that I initially did contribute to dbutils, but have since stopped because of how hard it is to release new versions of commons-dbutils. Long story short, I gave up. However, I had already written version 2 of dbutils and wanted to make sure it saw the light of day. So instead of trying work through commons-dbutils and all its problems I simply decided to fork the project and “start over” with sop4j-dbutils.

What’s new in sop4j-dbutils?

The biggest feature that sop4j-dbutils (or commons-dbutilsv2) brings to the table is a fluent API with named parameter support. Other than that, a few bug fixes have been merged into the code and hopefully by hosting on GitHub anyone can make a pull request with a patch and/or file a bug.

How to use sop4j-dbutils

The basic workflow for using dbutils is to create a QueryRunner and then execute some statement using a ResultSetHandler. Here is a basic example that converts the first row to an Object[]. Notice the use of named parameters over question marks. This makes reading the statements MUCH easier than trying to match question marks.

Executing insert or update statements works exactly as you would expect.

For long running calls dbutils even provides async executors.

One of the most useful ResultSetHandlers is the Bean(List)Handler. It turns dbutils into a very primitive ORM. (Note if you want a true ORM, use the great Hibernate library.)

As always, the code for this library can be found on GitHub. If you have any questions, please post them in the comments. If you find and fix any bugs or have improvements, please fork and make a pull request.
Thanks for reading…

6 Replies to “dbutils

  1. thanks for doing this. I had a question – I need to use timeout for sql query. PreparedSQLStament exposes setQueryTimeout method. However, in SOP4J class AbstractExecutor the method is marked package scope only.

    Is there a way to specify a connection timeout for the query?

Leave a Reply

Your email address will not be published. Required fields are marked *