For a project recently, I had the pleasure of working with both DBUtils and DBCP (Database Connection Pooling) from the Apache commons libraries. Both of these libraries together helped me to quickly create a simple, extensible DAO layer for my project. Both libraries include some great default features that I used right out of the box, without any configuration or fuss. In the post I’ll be talking about, and showing an example of using DBUtils. I will also show a quick and easy way to get a DataSource using DBCP.
Among other things DBUtils…
1. Removes the need to create or handle the life cycle of JDBC Connections or ResultSets. All you have to do is give it a DataSource object and it handles all of the connection opening/closing and data retrieval. As mentioned, you can get your DataSource using DBCP. Incidentally, you can also get a DataSource from your application container (Tomcat, JBoss, etc.), but I won’t be elaborating on that.
2. Automatically converts table rows to beans or Object arrays. This has got to be my favorite feature. If you create classes (Beans) whose property names match field names in a particular table (case-insensitive), DBUtils will automatically convert each row of the returned result set into a bean object. (Notice I used the general term “result set”, not the Java “ResultSet”, because I never have to deal with one.)
For instance, say you have a “Customers” table with the following fields:
- customerId int
- firstName varchar
- lastName varchar
- birthday datetime
Lets also say you have a Java class called Customers (you can actually call it anything) with the following fields and getters/setters conforming to the JavaBean specification:
- Integer customerId
- String firstName
- String lastname
- java.sql.Timestamp birthday
Lets use a very simple example. Let’s say you want to get a list of customers whose birthday falls between two dates. For example:
Query1
| SELECT * FROM Customers c WHERE c.customerId BETWEEN ’04/01/75′ AND ’04/20/75′ |
But, let’s make the query reusable. Let’s replace the dates with wildcard parameters.
Query1B
| SELECT * FROM Customers c WHERE c.customerId BETWEEN ? AND ? |
This allows us to insert any dates in place of the question marks. I’ll explain more later. Aside from this query you need three things: a DataSource, a QueryRunner, and a ResultSetHandler. You will find the latter two in the DBUtils package. Let’s assume for now that you already have your DataSource object. You can now create your QueryRunner object.
| DataSource dataSource = getDataSource(); QueryRunner qr = new QueryRunner(dataSource); |
Now that you have your QueryRunner created, you’ll need to create an instance of ResultSetHandler. Looking at the Javadocs you’ll notice that There are many implementations of the ResultSetHandler interface. I won’t go over them all, however, the two most useful to me have been BeanListHandler and ArrayListHandler. The former is used to convert table rows into beans and the latter returns a List<Object[]>, which reflects the structure of the underlying ResultSet. I’ll be using the BeanListHandler in my example.
| String sql = “select * from Customers c where c.customerId between ? and ?”; Object[] params = new Object[]{“04/01/75”, “04/20/75”}; ResultSetHandler rsh = new BeanListHandler(Customer.class); List<Customer> custList = (List<Customer>)qr.query(sql, params,rsh); |
So, what just happened? Well, first we created the query string with question marks as “wildcard parameters.” I also just happen to have created an Object array called params that has two Strings in it. It’s probably fairly obvious at this point that, the SQL string and the array, when passed into the query() method, are combined in much the same way as in the String.format() method, or the old C printf function. If you’re not familiar with either of those, basically, the objects in the array take the place of the parameters, in order. So, the first element in the array will take he place of the first question mark and so on. The query method actually returns a list of objects, which makes the cast I’m doing “technically” an unsafe cast. However, since you specified the type that you want to get back when you created the BeanListHandler, you can be sure of the type you’re getting back.
So, at this point, you should have your list of Customer objects, properly populated, and ready to go. Looks pretty good right? it is. However, it’s very simple. We’re assuming that our result sets will map to one table, but if you’ve done any database work, you know that’s not always the case. What object would this query map to?
Query2
| SELECT c.firstName, c.LastName, a.balance FROM Customers c, Accounts a WHERE c.customerId = a.customerId AND c.customerId = ? |
Your result set will look like this:
- Customers.firstName varchar
- Customers.lastName varchar
- Accounts.balance money
Your Customer class does not have a “balance” property, so if you used the BeanListHandler as in our previous example, you would not get the balance field at all. So, what are your options? Well, you could do one of two things. You could create an object for this specific result that has the fields you want (Maybe CustomerBalanceReport). Or you could use a different handler that allows variable fields and field types. In my project, I did the latter, using the ArrayListHandler in place of the BeanListHandler for my reports. The ArrayListHandler class has a no-arg constructor and is used in exactly the same way with the QueryRunner class as in the previous example. The only difference is that you will receive an ArrayList of Object arrays.
| /* * In this example we are assuming we already have the following objects * Customer c; – a customer instance * String sql; – the query 2 above. */ ResultSetHandler rsh = new ArrayListHandler(); List<Object[]> resultList = (List<Object[]>)qr.query(sql, c.getCustomerId(), rsh); |
Pretty similar to the other example, except for the difference in return types. Each Object in the Object array will contain three elements with the types String, String, and float (or possibly double) respectively. Something else I’ve demonstrated is that the query() method is overloaded. If you only have one replacement parameter, you can pass just that object in and it will map to the query(String sql, Object param, ResultSetHandler rsh) method.
I think that’s a good enough intro to DBUtils to get you started. But, how do you get a DataSource? Well, using DBCP you can create the following method in a controller class.
| /* * Create a DataSource based on database properties */ private static DataSource createDataSource() { BasicDataSource d = new BasicDataSource(); d.setDriverClassName(getProp(“db.driver”)); d.setUsername(getProp(“db.user”)); d.setPassword(getProp(“db.pwd”)); d.setUrl(getProp(“db.url”)); return d; } |
Pretty simple. And you can actually get a DataSource from your driver vendor. However, remember that the CP in DBCP stands for connection pooling. This is one of the advantages of using DBCPs DataSource over a typical vendor implementation. You can typically just use the BasicDataSource provided with the DBCP package and pass in the database connection info, which, in the example, I retrieve from a properties file. For instance getProp("db.driver") might return “com.microsoft.sqlserver.jdbc.SQLServerDriver”. This is a very simple way to get a DataSource. There are much more interesting and useful ways to automate DataSource acquisition. For instance, most application containers can inject a DataSource into your application for you. Tomcat actually uses the DBCP library to do this. You can also register your DataSource using JNDI and use a service locater to get it. I recommend using one of these methods for anything other than the simplest of apps. This was just an example to get you started trying out DBUtils with connection pooling.
A note about dependencies: DBUtils depends on Apache Commons Collections and DBCP depends on Apache Commons Pool.