Plain Old SQL Statements in Java
Lot's of Java developers want to avoid writing SQL or even avoid directly accessing with a relational data store. I, on the other hand, want to use SQL in my Java programs (which are mostly database utilities) but I want it to be as easy to use SQL in Java as it is in Python, Perl or PHP.
Recently, I decided to use Java rather than Perl as my language of choice for database utilities. Perl is quick to write, but tends to be easier to write than to read. I played with Python for a while, but in the end decided that by using Java I would have an easier time distributing by stuff and it could more easily be re-used inside of my company, which has Java developers but not many Perl people. So developed a set of JDBC wrappers that would let me use SQL easily within my utilities.
My Design :
- Require no configuration files (no XML mappings for instance). All the data access logic should be right there in the code. Although I did end up allowing SQL statement to be held in an XML file just to avoid the whole messy string handling involved in really long SQL statements.
- Easy processing of result sets, leveraging Java collections (which were not around when JDBC was first speced).
- Make it easiest to follow best practices. For instance, make it very easy to use bind variables, re-use cursors, etc.
- Allow interoperability with the underlying JDBC objects so that I could use these classes without worrying that I would run into a brick wall.
- Work well with dynamic SQL.
- Where appropriate, avoid some of the tedium involved with DML and DDL.
- Cache prepared statements so that you don't have to worry about which prepared statements to keep open and when to close.
- Be RDBMS neutral.
This was a bit of a learning experience - had not programmed in Java for quite a while and I tried to follow best Java practice such as creating Junit tests, JavaDoc and ant builds. If anyone's interested, here's the latest versions:
I don't really expect anyone else to use this, posting it was I guess one of the ways to enforce a bit of discipline on myself as regards quality. However, it will be embedded in most of my database utilities so I guess it will see some use in our internal benchmarking routines and the like.