SQLLoader

What is SQLLoader?

SQLLoader is a set of classes that allow SQL statements to reside outside of the actual JDBC persistence code. The statements can be stored in flat files, XML files or even in a database. SQLLoader provides the developer with a way of substituting variables in the SQL statements and then returns the statement with the variables substituted with their values.

Example
Template = Select * from Customer where CUSTOMER_ID = ${Customer_Id}
Key = Customer_Select
where ${Customer_Id} is the only variable name

The developer would setup a substitution list containing something like

Customer_Id=1234

The SQLLoader would locate the file containing the Template by the Key and replace any substitution variables with their respective values.

So the end result would be

Select * from Customer where CUSTOMER_ID = 1234

What are the advantages?

• The sql statements will be cached in memory to improve performanace
• The sql statements can be changed without editing a line of code (unless variables are added or renamed)
• The sql statements are more readable.
• Less code to setup the queries.
• Allows the validation of sub varibales existence in dev code during development by the turning on of a validation flag.
• DBA's can modify SQL statement's without looking at Java.
  

Disadvantages?

• SQL statements and code are in separate locations
• May be harder to debug problems in production. .

Template Structure

A Template can contain any text that a file can contain. However it doesn't make sense for the templates to contain SQL that is syntactically incorrect.

In most cases templates should contain a Template Variable (something in the template that will be replaced). A template Variable is defined by starting with ${ and ending in } e.g. ${CustomerId} A template variable will be replaced at run time by the developer creating an instance of SubstitutionList and putting the variable name and value into the substitution list.

Example

		Select every customer detail by the customer id in SQL is 
	  		select * from Customer where CUSTOMER_ID = 14
	
		So the template contains
			select * from Customer where CUSTOMER_ID = ${CustomerId}
		
		And the code is
			SubstitutionList subs = new SubstitutionList();
			subs.put("CustomerId" , 14);
			String query = loader.getSelect("Customer", subs);

		And query will equal select * from Customer where CUSTOMER_ID = 14
	  

File Structure

Under the URL where the sqlloader templates are stored the directory structure must adhere to this convention.

All SQLLoader templates have two file extentsions the last must allways be .sqlloader

The middle extentsion specifies the type of query contained with in the template so for almost all there will be templates such as

.XXX.select.sqlloader XXX.insert.sqlloader XXX.delete.sqlloader XXX.update.sqlloader which use the primary keys of the table XXX

Each related query will be under the same directory XXX.

If the statements are for table Customer and the primary key is not the selection criteria then the new middle extentsion is used as the descriptive text for the template.

Example

 		
		URL = /opt/SQLLoader 
		Table = Customer
		2 Ad-hoc queries getCustomersBySurname and updateCustomerAddressByUserName
		
		Then under the directory
				/opt/SQLLoader/Customer
		The following files will exist
			
			Customer.select.sqlloader
			Customer.update.sqlloader
			Customer.insert.sqlloader
			Customer.delete.sqlloader
			Customer.getCustomersBySurname.sqlloader
			Customer.updateCustomerAddressByUserName.sqlloader

URL Convention

The URL is divided into three sections each divided by a semi-colon

• Vendor
• Version
• Directory

So the full URL is Vendor:Version:Directory

For the Referenec implementation the URL can be IMRI:1.0:c:/temp

Source Code Examples

Source code examples can be found here

• SimpleExample Just to start you off ..... examples.im.framework.optional.sqlloader.simple.SQLLoaderSimpleExample
• Config SQLLoader with IMConfig ..... examples.im.framework.optional.sqlloader.config.SQLLoaderIMConfigUse
• Actual JDBC Use of SQLLoader returned query ..........examples.im.framework.optional.sqlloader.jdbc.SQLLoaderJDBCExample

Architecture of SQLLoader

The architecture of SQLLoader is based on the architecural principles of JDBC and many other Java APIs.

There exists 3 tiers to the architecture

• API
• Driver Manager API
• SPI

API

The API is interface used to gain access to the SQLLoader. The API is specified by the interface com.im.framework.optional.sqlloader.SQLLoader and it basically allows the developer to gain access to SQL statements and do substitutions on the templates.

The API has one get method per Select,Update, Insert and Delete statements and one special getStmt method which is documented under ad-hoc queries.

getSelect, getUpdate, getInsert and getDelete all have the same signature (String key, SubstitutionList subs)

To get the select statement for a customer the call will be

String query = loader.getSelect("Customer", subs);

So by the file storage protocol defined by SQLLoader under the URL there must be a directory with the same value of key and a file name key.XXX.sqlloader where XXX is the select, updare,delete or insert

The getStmt has the following signature

getStmt(String key, String subKey, SubstitutionList subs)

from the example under File Structure to retrieve the updateCustomerAddressByUserName query the call would be

String query = getStmt("Customer", "updateCustomerAddressByUserName", subs);

Driver Manager

The Driver Manager manages all registered drivers and hide the SPI from the developer. All queries from the perspective of the developer go through the Driver Manager. The Driver Manager class is com.im.framework.optional.sqlloader.SQLLoaderDriverManager

In order to recieve the API for a particular URL the developer asks the DriverManager to get a Driver for a URL

The Driver Manager iterates over all registered drivers and the first driver that can understand and handle the URL is returned.

The loader can then be retrieved from the driver.

So the code looks like

SQLLoader loader = SQLLoaderDriverManager.getSQLLoaderDriver(URL).getSQLLoader();

SPI

The SPI is the interface that all driver implementations must implement and is com.im.framework.optional.sqlloader.SQLLoaderDriver and publishes methods for debugging and retrieving templates and substituting variables with values.

Why use API, driver, driverManager and SPI?

• Architecture is very similar to JDBC and JCA and many other Java API's
• It allows the implementation of the SQLLoader to be replaced much easier than hard coding classes.
• It doesn't tie the implementation directly to a third party library like Freemarker