O/R Mapping, SQL, Relation Orientation and Convenience
Ted Neward has broken out his commentary on object-relational mapping again. When he first posted this idea I disagreed -- I felt strongly that everyone tackling it, all the options, and various approaches, signaled a strong and healthy solution space. Over the intervening couple of years I have started to come around to Ted's point of view.
Ted sums up the problems very thoroughly, much more so than I want to take the time to do, so... go read his post.
Now, if you do want to use SQL-centric stuff in a convenient way, there are few options that give you the kind of convenience APIs that the O/RM tools provide. To take a trivial example of a one-parameter query:
RIFE provides a very nice API for accessing relational databases as... relational databases. Hynek recently posted. RIFE uses a builder approach to sql statements, like(to use Hynek's example):
Select select = new Select(datasource)
.from("customers")
.where("city", "=", city);
DbQueryManager manager = new DbQueryManager(datasource);
List<Customer> r = manager.executeFetchAllBeans(select, Customer.class);
This style is really nice when your queries are dynamic, such as the ubiquitous "advanced search" form. The example here chains everything, but you can quite easily wrap the where clauses in if statements to build up a statement programmatically rather than via string concatenation.
If you prefer to work directly with SQL, or have mostly static statements, then there are a couple more really good tools. My favorite is, well, one I wrote called jDBI, which lets you do things like:
Handle handle = DBI.open(datasource);
List<Customer> r = handle.createQuery("select * from customers where city = :city")
.bind("city", city)
.map(Customer.class)
.list();
jDBI is focused very tightly on being a JDBC convenience API, not a general framework, such as RIFE. RIFE, on the other hand, provides some higher level functions enabled by taking control over what exactly gets done, such as handling limit and offset even on databases which don't support them (via query munging and rownums on Oracle, or using scrolling cursors and only fetching the correct rows elsewhere).
The grandaddy of JDBC convenience libraries is Apache DbUtils. With DbUtils you would have:
QueryRunner runner = new QueryRunner(datasource);
List r = (List) runner.query("select * from customers where city = ?",
city,
new BeanListHandler(Customer.class));
DbUtils is a short-and-sweet tool which provides even less high-level functionality, but is extremely well tested and quite flexible. It hasn't been upgraded to take advantage of generics yet, but some people probably prefer that :-)
Spring provides a few convenience API's, mostly centered around the JdbcTemplate. Like much of Spring, it doesn't provide an alternative API for what it helps with, it instead focuses on making the common cases easy and smoothing the sharp edges.
SimpleJdbcTemplate t = new SimpleJdbcTemplate(datasource);
List<Customer> r = t.query("select * from customers where city = ?",
new ParameterizedRowMapper<Customer>() {
Customer mapRow(ResultSet rs, int index) {
Customer c = new Customer();
// map into the bean
return c;
}
}, city);
This uses the upcoming Spring 2.0 stuff :-) Surprisingly they don't seem to have a generic JavaBean mapping row mapper. Weird, but easy to write.
Finally, this doesn't really exist yet, but JDBC 4 (part of JDK 1.6) will include a spiffy looking convenience API that looks like an annotation based SQL-J. You define an annotated interface and the driver provides an implementation which does the queries.
public interface CustomerDao extends BaseQuery {
@Select("select * from customers where city = ?1")
DataSet<Customer> findCustomersFrom(String city);
}
CustomerDao cd = QueryObjectFactory.createQueryObject(CustomerDao.class, datasource);
DataSet<Customer> r = cd.findCustomersFrom(city);
DataSet extends List, so you can treat reasonably! I am quite looking forward to trying this out. There are only a limited set of annotations defined, so you cannot specify things like fetch size, but it is pretty nice looking anyway :-)
Anyway, it hasn't been an in-depth look, but a taste and starting point for folks who want something that tries to embrace the underlying relational database.
update Thu Aug 10 09:01:00 PDT 2006 -- add iBatis
It was kindly pointed out by Steven Grimm that I missed iBatis. D'oh! It definitely should be mentioned. iBatis is very configuration heavy so I don't really have time to post up the trivial example. Will do so later :-)