Java Database

This article describes a SQL Compiler tool (SQLC) that generates Java classes from SQL statement and table metadata. In doing so SQLC decouples Java and SQL code and enforces a clear separation of concerns between database and Java code and division of labor between data modeler and Java developer. The article also ruminates about SQLC, O/R mapping frameworks (taking Hibernate as an example) and plain JDBC applicability in different contexts.

Introduction

Recently I worked on a Java application and needed to do a lot of database interaction. I didn't want to use plain JDBC for obvious reasons (that are nonetheless described below). I had already created a data model and didn't want to do double work by mirroring the data model in Java by hand.

So I looked around for a carpal tunnel friendly tool, something like the erstwhile FoxPro 2.6. To my surprise I found none. Nowadays everybody seems to care about flexibility but not simplicity.

After pondering for a while I preferred creative typing over mundane and wrote a tool that generates interfaces and methods from database metamodel. Lo and behold SQLC was born!

SQLC generates Java classes and interfaces from information provided by java.sql.Connection.getMetaData(), java.sql.PreparedStatement.getMetaData() and java.sql.PreparedStatement.getParameterMetaData() methods. It uses BCEL and helper classes and techniques described in XREF:ARTICLE_6207. SQLC-generated classes use Squirrel as a foundation, which minimizes the amount of generated code (see resources, don't mix up with Squirrel SQL client).

You may think: "Oh, yet another O/R mapping tool, why would I waste my time reading about it?" Well, SQLC is not an O/R mapping tool at all. Like spiritualists and materialists have different answers to the question "What is primary - spirit or matter?" SQLC and O/R mapping tools offer different answers to the question "What is primary - data or objects?" SQLC treats data as primary and only as data - i.e. no behavior. For example, a credit card has no behavior per se - it's just a tuple. Behavior belongs to the objects operating with the credit card, not the credit card itself.

SQLC also does something what other tools don't do - it compiles parameterized statements to Java methods.

This is a short summary of SQLC's advantages:

• Simplicity: SQLC requires little development time configuration and no deployment time configuration (no deployment descriptors at runtime).
• Robustness: Build time verification of SQL statements. SQLC uses the metadata information provided by the target database. Problems with SQL - syntax errors, invalid DB object names - are revealed at build time.
• Reusability: Compiled classes can be used by many applications working with the same database. Generated classes, for example, can be distributed as jar files. In application server environments engine classes can be mounted to the JNDI tree. Storing statements in the database allows you to reuse tuned statements in non-Java applications as well.
• Separation of concerns and division of labor: A data modeler models the database (DDL) and develops SQL statements (DML) optimal for the database. A Java developer uses generated classes/methods to access/modify data. He or she doesn't need to know SQL at all and only needs a superficial understanding of the underlying database structure, which will come from SQLC generated documentation.

  Corollaries of the previous point are:

• Modifiability and maintainability: Compiled classes define an interface between the database and Java code. Data modeler is free to modify DDL and DML - replace joins with nested selects, or include an execution plan to queries - without touching the Java code.
• Reduced resource demand: Lower- grade, hence cheaper, Java resources (developers) can be used. With SQLC Java developers don't need to use the JDBC API or any other tool-specific API, they use compiled methods, which, if named properly, are self-descriptive.
• Increased productivity according to Adam Smith.
• Testability: Statements can be tested independently of the Java application in the SQL console. Compiled classes can also be tested independently by, say, JUnit without complicated fixtures.

The following sections show how to use SQLC and then I'll compare SQLC, plain JDBC, and O/R mapping (using Hibernate as an example) applicability in different contexts.

Compilation

Figure 1 shows a sample model that will be used to generate classes and interfaces. We'll create a simple banking application capable of making fund transfers and collecting service charge from accounts with a low balance. The full source code of the sample application can be downloaded. See the references below.

The first step is to generate classes using an sqlc Ant task:

1.<sqlc
2.script="src/com/pavelvlasov/sqlc/samples/Bank.sql"
3. dir="sqlc_generated"
4. docDir="sqlcDoc"
5. package="com.pavelvlasov.sqlc.samples"
6. masterEngine="BankEngine"
7.>
8. <table/>
9.</sqlc>

In the snippet above classes are generated using Bank.sql script. In this case a Hypersonic temporary database is created, the script is executed to create database objects, and then Java classes are generated. This approach works fine if the DDL in the script is compatible with Hypersonic. If you'd like to generate classes straight from the target database (the preferred method) then instead of the script attribute nested <connection> element shall be specified. This is a sample connection element for Oracle:

10.<sqlc
11. dir="sqlc_generated"
12. docDir="sqlcDoc"
13. package="com.pavelvlasov.sqlc.samples"
14. masterEngine="BankEngine"
15.>
16. <connection
17. driverClass="com.inet.ora.OraDriver"
18. url="jdbc:inetora:server:port:DB"
19. user="DBUSER"
20. password="DBPASSWORD"/>
21. <table/>
22.</sqlc>

Please note that it uses ORANXO driver because the drivers provided by Oracle itself are not fully JDBC-compatible (see the compatibility section below).

The SQLC task shown above produces .class files in the sqlc_generated directory and HTML documentation in the sqlcDoc directory using table metadata. The <table/> element matches all the tables in the database.

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...