Type Safe Jdbc Wrapper

To solve the problem with string constants (e.g. in SQL-statements), as described in NoStrings, I implemented the following set of relatively simple tools/classes for Java:

Two tools to compile

a) SQL CREATE TABLE statements (using a simple jflex grammar),

b) the actual database schema (using JDBC and DatabaseMetaData?)

into java class files with properties (getter/setter methods) for the found columns. The classes also feature a getPrimaryKeyFieldNames() method (returning a Set of String of course) to represent the primary key as well as a getTableName() method returning the datbase table name (defined by an interface DbBean?).

All Names are mapped one to one, i.e. a column NAME is mapped to getNAME and setNAME.

The second part of this 'relational mapping tool' is a class, that wraps around a JDBC Connection and has the following methods:

load(DbBean?)
save(DbBean?)
delete(DbBean?)
select(Query, DbBean?)

the latter Query class is currently a wrapper for an SQL-SELECT String, but it would be straightforward to replace it e.g. with the Query from http://sodaquery.sourceforge.net/. Another way would be to use a construction like

new Query(UserDbBean?.class, new And(new Equals(UserDbBean?.FIRSTNAME, "gunnar"), new Equals(UserDbBean?.LASTNAME, "zarncke")));

Of course the tools would have to be extended to generate these additional fields of type Column.

-- GunnarZarncke

This jdbc wrapper can also provide sensible error handling instead of the useless SQLException. This might be done along the lines used in the SpringFramework: http://www.springframework.org/docs/api/org/springframework/jdbc/package-summary.html

ExpressionApiComplaints complains about this new-dot-new-dot style of API's.

I did this before in a project, and this is my preferred way to handle the database in Java. (For the same reasons of maintability, safety displayed in NoStrings) Additionaly what it does buy you is that you can run a TestCase that will compare the database schema as known by your code base, and the actual schema currently in use, and display the differences.

Together with QueryObjects? handling query building, execution and connection handling (a sort of MethodObject dedicated to Sql queries) and a visitor object to build result views, or objects, it brought a rather safe, thin modular layer above plain jdbc.

Another, more flexible but also more arduous way of achieving type-safe JDBC is iBatis (http://www.ibatis.com/), also mentioned in ObjectRelationalToolComparison. With this approach you must still type your own SQL statements (queries AND insert/delete/update), but they are stored in a separate (XML) file. ResultSets? are then automatically mapped to JavaBean-compliant objects (matching column and property names), although you can override that.

iBatis also provides some caching features, thread-safe modus of operation and some primitive support for relationships, but that doesn't influence the basic idea (and you can safely ignore relationship support if you don't need it).

-- BostjanDolenc?

Hum? iBatis seem to be the totally opposite idea, (described in NoStrings) by relying on external XML content. It even seem to encode the property lookups in the XML file itself! The idea of the TypeSafeJdbcWrapper is to exploit the java compilers and IDEs to do what should be validated here by a specific tool running against those external XML descriptions.

Note that TypeSafeJdbcWrapper is just a short step away from full-blown ObjectRelationalMapping tool. The only major feature that is "missing" is support for (inter-object) relationships (but it brings its own set of headaches).

-- BostjanDolenc?

I think the interesting point is that it doesn't over-emphasize full blown object-relational mapping, not attempting to fully bring the relations into the navigational world of objects