A quick intro into SQLC

• A quick intro into SQLC
  • Sample application
  • Additional reading
  • Supported databases
  • Define sqlc task
  • Generation from table metadata
  • Generation from index metadata
  • Generation from foreign key metadata
  • Generate classes
  • Use generated classes
  • Benefits
  • Interface hierarchy
  • Generated interface implementations
  • Projection to subclasses of generated implementations
  • Projection to arbitrary classes
  • Database backed collections and JDBC resources leaks
  • Nullability and primitive types
  • Processing instead of projection
  • Dynamic queries
  • Documentation
  • Adding qualities
  • Conclusion

SQLC (SQL Compiler) generates Java classes from SQL statements using statement and table metadata obtained from the database.
It is implemented as an Ant task.
The primary goal of SQLC is to decouple Java code and SQL (or any other QL).

Sample application

SQLC sample application can be downloaded from Download page.
Unzip it and run build - it will generate database access classes, compile code and execute it.

Additional reading

You can read more about SQLC and the sample application in this article.

Supported databases

SQLC uses statement metadata, in particular parameter metadata. Thus database driver must implement getParameterMetadata() method. The table below contains results of my evaluations of some popular RDBMS.

Database	Version	Compatible with SQLC
Hypersonic	1.7.2 on JDK 1.4	Yes
Cloudscape	10	Yes
Firebird	1.5.1, JDBC driver 1.5	Yes
Oracle	ORANXO driver	Yes
	Drivers from Oracle 8.1.7, 9.2, 10g (classes12.zip, ojdbc14.jar)	No
MySQL	4.0.2, JDBC driver v. 3.1.4	No
Sun ODBC-JDBC bridge	Java 1.4	No

I will appreciate if you send me results of your evaluations to put to the table above.

Define sqlc task

SQLC resides in Common library and uses BCEL and ANTLR for code generation. If you have hgcommons.jar, bcel-5.1.jar and antlr.jar in the system classpath or in Ant lib directory then SQLC task can be defined as

Otherwise jars which are not in the classpath shall be specified in task definition classpath. SQLC connects to the target database during generation, therefore database driver shall also be present in the classpath

Generation from table metadata

SQLC can generate standard operations for tables using table metadata:

• Select all rows
• Select a row by primary key (if table has primary key)
  
• Delete all rows
• Delete row by primary key (if table has primary key)
• Insert row
  
• Update row by primary key
  

This behaviour can be turned on by adding table element to sqlc task.

Generation from index metadata

SQLC generates methods from table indices with names confirming with the following naming convention:
_SQLC_
Modes

Code	Method postfix	Generates
O	Ordered	Select ordered by the index.
E	EQ	Select and delete of rows with equal index columns
N	NE	Select and delete of rows with non-equal index columns
L	LT	Select and delete of rows with positions in the index less than specified in parameters
M	LE	Select and delete of rows with positions in the index less or equal than specified in parameters
G	GT	Select and delete of rows with positions in the index greater than specified in parameters
H	GE	Select and delete of rows with positions in the index greated or equal than specified in parameters

Generated methods follow the following pattern:

• Select: get
• Delete: delete

Example: Index on COMPILATION_UNIT table with name IX_COMPILATION_UNIT_SQLCE_PACKAGE will result in the following generated methods, assuming that the index is built on PACKAGE column of type CHAR or VARCHAR:

• getCompilationUnitPackage(String Package)
• deleteCompilationUnitPackage(String Package)
If contains _ then it is removed and next letter capitalized. E.g. COMPILATION_UNIT -> CompilationUnit.


Generation from foreign key metadata

SQLC generates methods from table foreign (imported) keys with names confirming with the following naming convention:
_SQLC
Generated methods follow the following pattern:

• Select: get
By

• Delete: delete
By
Example: Foreign key on ACCOUNT table with name FK_ACCOUNT_SQLCOWNER will result in the following generated method, assuming that the index is built on not nullable ACCOUNT_OWNER column of type INTEGER:

• getAccountByOwner(int AccountOwner)

Generate classes

This is a fragment of Jsel build file


	script="src/biz/hammurapi/jsel/impl/Hypersonic.sql"
	dir="sqlc_generated"
	docDir="sqlcDoc"
	package="biz.hammurapi.jsel.impl.sql"
	masterEngine="Engine"
>    	
	
		SELECT * FROM COMPILATION_UNIT WHERE ID=?
	

	
		SELECT * FROM COMPILATION_UNIT C
		WHERE REPOSITORY=? AND C.STORE_LEVEL=? AND
		EXISTS(SELECT * FROM COMPILATION_UNIT_SCAN S
		WHERE S.COMPILATION_UNIT_ID=C.ID AND
		S.REPOSITORY=C.REPOSITORY AND S.SCAN_ID=?)
	
		
	
		DELETE FROM COMPILATION_UNIT WHERE ID=?
		

This task generates classes Engine class and CompilationUnit interface in biz.hammurapi.jsel.impl.sql package. It also generates HTML documentation. The sample above uses Hypersonic in-memory database created using script file specified in script attribute. If you use another database, e.g. Oracle, then you should specify nested connection element in SQLC task.
It is also possible to keep statements in the database itself. In this case you need to set statementsQuery attribute. See documentation for details. This approach yields more clear separation of concerns between Java and DB and also provides more flexibility.


Use generated classes

Add generated classes and hgcommons.jar to classpath.


DataSource ds=...; // Obtain data source
SQLProcessor processor=new SQLProcessor(ds, null);
Engine engine=new Engine(processor);
CompilationUnit cu = engine.getCompilationUnit(33);
System.out.println(cu.getName());
engine.deleteCompilationUnit(88);

If you need transactional context


Connection con=...; // Obtain connection enrolled in transaction
SQLProcessor processor=new SQLProcessor(con, null);
Engine engine=new Engine(processor);
CompilationUnit cu = engine.getCompilationUnit(33);
System.out.println(cu.getName());
engine.deleteCompilationUnit(88);
... // do something else and commit transaction 

Benefits

As you have seen from the samples above, usage of SQLC is quite straightforward. This is a summary of benefits it gives:

• Classes are generated from metadata obtained from the target database. This approach helps to avoid situations when somebody invokes setString(1, "Hello") for numeric field.
• Generation at build time ensures that the database and the code are in sync. E.g. you have DEV and PROD database servers and application servers. You created a new table in DEV, but by some reason this change wasn't moved to PROD database. With SQLC this problem will be discovered during build time - the build will fail. Otherwise it would be discovered only in runtime. If the forgotten table participates in a rarely used use-case the problem may be discovered months after the code move when develpment team is already dismissed. If it happens, say, during year close and it will be A REALLY BIG PROBLEM.
• Probability of connection leakage in SQLC is much less comparing to traditional JDBC-based database programming. The only scenario of connection leakage in SQLC is obtaining database-backed collection, and not iterating over it till the end of connection. In this scenario iterator, which holds database resources, shall be explicitly closed or it will be closed in finalize().
  
• Decoupling of SQL from Java gives the following benefits:
  • It forces usage of parameterized statements, which are more performant than queries assembled in runtime.
    
  • Java and SQL can be owned by different teams. SQL tuning can be performed without touching Java code.
  • The same Java code may be used with different databases as long as compiled statements take the same number of parameters and return compatible results.
  • It makes easier to perform SQL code reviews - both manual and automated.
    

Interface hierarchy

SQLC generates inerfaces to represent resultsets. E.g. if you have a query named Person, which returns resultset with 3 columns: ID INT NOT NULL, LAST_NAME VARCHAR(50), FIRST_NAME VARHCAR(50), then SQLC will generate interface
Person {
int getId();
String getLastName();
String getFirstName();
}

There can be multiple queries returning same set of columns. SQLC reuses already defined interfaces. You can explicitly suggest SQLC interfaces to use using nested interface element. SQLC also generates interface hierarchy. E.g. if you have a query named UsaPerson, which returns all the columns of Person plus SSN VARCHAR(9), then SQLC will generate interface UsaPerson extends
Person {
String getSsn();
}

All generated interfaces extend biz.hammurapi.xml.dom.DomSerializable interface.

SQLC also discovers "Common denominator" interfaces. E.g. there are two queries, PersonUsa and PersonRussia:
public interface
PersonUsa {
int getId();
String getLastName();
String getFirstName();
String getSsn();
}

public interface PersonRussia {
int getId();
String getLastName();
String getFirstName();
String getPassportNo();
}

SQLC will find that

1. Both queries names start with word 'Person'. Word is defined as a sequence of charactes starting with capital letter.
   
2. Queries have common columns
And it will generate inerface Person. PersonUsa and PersonRussia will extend interface Person:
public interface
Person {
int getId();
String getLastName();
String getFirstName();
}

public interface PersonUsa extends Person {
String getSsn();
}

public interface PersonRussia extends Person{
String getPassportNo();
}



Generated interface implementations

SQLC generates implementations of interfaces. There are two kinds of generated implementations - Plain and Smart. Plain implementations are generated for queries and smart implementations are generated for tables. Plain implementation is a simple JavaBean with getters and setters. Smart implementation extends biz.hammurapi.sql.DatabaseObject and keeps track of modified fields.
update<table name>(<row interface>) and insert<table name>(<row interface>) engine methods detect whether parameter passed to them is instance of DatabaseObject and delegates update and insert to the parameter if so. DatabaseObject inserts/updates only modified fields, which allows, for example, to insert records into tables with automatically generated primary key without having to create a separate for it.


Projection to subclasses of generated implementations

Per each select SQLC generates several methods. For single-row select there are two methods - one returns automatically generated implementation and the other takes additional parameter of type java.lang.Class. If passed parameter is subtype of automatically generated implementation then it is instantiated. Otherwise an attempt to convert source object to target class is taken. Target class is searched for constructors taking source class and if one is found target class is instantiated. As the last resort constructor from String is sought and used after converting source object to String.
The same thing happens with elements of collections returned by the generated engine class.


Projection to arbitrary classes

For multirow selects SQLC generates 6 methods per select:

1. Projection to collection
2. Projection to collection of targetClass instances as described in the previous section.
3. Projection with conversion - biz.hammurapi.util.Converter instance shall be passed as a parameter to convert objects behind the scenes.
   
4. Projection to database backed collection
5. Projection to database backed collection of targetClass instances as described in the previous section.
6. Projection to database backed collection with conversion - biz.hammurapi.util.Converter instance shall be passed as a parameter to convert objects behind the scenes.
   

Database backed collections and JDBC resources leaks

Database backed collection is an implementation of java.util.Collection to iterate over big result sets without placing them to memory. The collection doesn't hold any JDBC resources or data items. For each invocation it goes to the database.
Iterators created by database backed collections hold open result sets and release them when there are no more elements in the result set. If client code doesn't intend to iterate till the end of the collection it must cast iterator to com.pavelvasov.sql.DataIterator and call its close() method.
It is also a good idea to close data iterators in finally blocks.
Failure to ensure proper iterator lifecycle leads to connection leaks. To troubleshoot such situations iterators have finalize() method, which outputs a message to console saying which SQL statement was leaked.


Nullability and primitive types

For columns of primitive type, such as integer, return type of generated getter depends on column nullability. For example, for integer non-nullable column ID int getId() will be generated. For nullable column return type would be java.lang.Integer.
The same applies to parameters, but by default all parameters are treated as non-nullable, unless hasNullableParameters attribute is set to true


Processing instead of projection

Sometimes you want to execute a query and take some action on each row without projecting this row to Java class. SQLC generates processXXX(..., RowProcessor) method along with getXXX methods for each query.


Dynamic queries

It is often needed to execute families of queries where list of columns doesn't change and only WHERE clause changes. One of examples is search for, say, account transaction, based on criteria set by user.
SQLC addresses this scenario by generating 2 static project() methods in interface implementations. These methods take SQL statement and parameterizer. Query result set is projected to a collection of instances of the declaring class.
Thus in SQLC you should define one of the dynamic queries to generate interface and implementation and then use project() to execute others.


Documentation

SQLC generates documentation in XML, which can be styled to HTML and also be used to generate higher-level classes, which use SQLC-generated classes) (e.g. JSP's which display table content).


Adding qualities to smart implementations

Smart implementations by default extend biz.hammurapi.sql.DatabaseObject. This class provides a number of services like serialization to and from XML, but it is impossible to anticipate and put all possible services in one class. There is a way to tell SQLC what base class to use for smart interface implementations. Just set smartBase attribute to base class name. hgcommons library provides one subclass of DatabaseObject - MeasuringDatabaseObject. This class provides an additional service - it measures intensity (number of invocations) and time of load, insert, update and delete operations.


Conclusion

There are many different approaches coping with alleviation of communication between Java and relational databases. Most of them concentrate on flexibility and have word "mapping" somewhere in description.
SQLC concentrates on simplicity and robustness. Give it a try and you'll be surprised how easy it will become to work with databases.