This entry kicks off a series of entries where I’ll examine some of my favorite tools for development. Some of them will be long, some short, but all of them will highlight software I use to make my life a bit easier.
A large, large chunk of the development I do is taking data from a relational database to a an HTML screen, and back again. Often there are business rules for transforming the data, or validation rules, but making sure the data is stored safely and consistently is a high priority, and that means a relational database.
However, I do much of my work in java, which means that the relational-OO impedance mismatch is a common problem. One common way to deal with it is to use an OR tool–something like OJB or JDO. These tools provide object models of your database tables, usually with some help from you. You then have the freedom to pretend like your database doesn’t exist, and use these objects in your application. The OR framework takes care of the dirty work like SQL updates and caching.
Every convenience has its price, however, and OR mapping tools are no exception. The same abstraction that lets you pretend that you’re simply dealing with objects means that you cannot easily examine the SQL that is generated. In addition, the way that you’re using the objects may cause performance issues, because you’re treating the data as objects, rather than rows.
It’s much the same issue as calling methods over the network via RMI or accesing files via NFS: the abstraction is great and means that programmers don’t have to think about the consequences of remote access. But the failure of the abstraction can be catastrophic, all the more so because the programmer was not expecting to have to deal with the grotty details under the abstraction (that’s the whole point, right?).
OR tools do not fail often, or have many catastrophic failure modes, but they sure can be slow. With open source software, you can dig around and see how SQL is being generated, but that’s tedious and time consuming. With commercial products, you don’t even have that option. (Some OR tools may have their own ‘Show me the SQL’ switch–I haven’t run into them.)
Enter p6spy. p6spy can be used in place of any JDBC driver. You point it to the the real driver and it passes on any configuration or SQL calls to that driver. But p6spy logs every SQL statement passed to it and every result set passed back. (A fine non object oriented example of the Decorator pattern.)
It took me about 15 minutes to figure out how to use p6spy, the software is open source with decent documentation, the latest version has data source support, and it scratches an itch that most, if not all, java developers will have at some time. With p6spy, you can find out what that OR tool is doing under the covers–it’s an easy way to peel back some of the abstraction if needed.