Reading Data Using SQL

We have come to a place in Aspire where the practical benefits of this .jar file are most obvious. Let us see how difficult is it to read the same set of rows from a database instead. Sample code follows first.

As I was getting ready to explain this code, I realized it is identical to the previous file version, including imports. Nothing has changed. That is true. No code needs to be changed to switch between reading a file to reading an SQL statement. That means you can switch your datasources easily and uninvasively. This sense of declarative fluidity permeates the architecture of this .jar file. Following the file example, Aspire should translate the symbolic name of MYDATA to an SQL statement. Here is the config file permitting this transaction:

I have used here an Aspire component called Com.ai.db.DBRequestExecutor2 instead of the FileReader component. DBRequestExecutor2 takes two arguments in the config file -- db and stmt. db points to the database alias. stmt points to the SQL statement. The select statement is trivial. The immediate question is, how do we deal with arguments? Here is an example of a SQL statement that uses arguments.

where col1-key is a key from the hashtable of arguments that are passed in. The string representation of the value of col1-key will be literally substititued into the SQL statement; the resulting SQL statement will be the one that gets executed. If the column happens to be a string, you can do the following:

Notice the .quote after your key. There is a whole science behind the .quote, but for now it is sufficient to say that for strings we use the .quote. This also ensures that if the string value has embedded quotes, they will be doubled or escaped to suit the database. Also, if the key happens to be null, the .quote will place the database null value there without the quotes.

Just like in the FileReader, the programmer is completely unaware of the database connections, JDBC, etc. As a result, this approach is less error-prone. So far there is one thing
left unsaid. MyDataBaseAlias is a symbolic name for a database reference. These symbolic names are called database aliases and are mentioned in config files.

Defining Databases

It has become a common practice now to define database connection details in config files. So the following data source definition should come as no surprise.

You can define any number of databases this way. Aspire goes one step further and can selectively associate a datasource (or database reference) to a database alias. Ultimately, it is this alias that is
used throughout. This allows us to swap databases between test, development,
production, etc. with a single change. Here is how we can define the database
alias MyDatabaseAlias.

Database.alias.MyDatabaseAlias = MyOracleDB

Collecting Data Using Stored Procedures

Let us continue with the fluidity of external declarative approach for data access. Let us replace the SQL calls with a stored procedure for Oracle. Again, clients stay intact; no code change. The config file will change for MYDATA, pointing to a stored procedure executor. The config file follows:

The component StoredProcedureExecutor2 is specific to Oracle. Oracle has special requirements for calling stored procedures. Inside of the Oracle stored procedure you will have to use REFCURSOR as your first argument to the procedure. This component supports only result sets that come back, and not individual variables. Read Oracle's JDBC documentation for writing stored procedures with REFCURSORS. You can also refer to "A JSP Architecture for Oracle Stored Procedures" in JavaReport (View code from this issue). For Microsoft SQL Server, the previous component DBRequestExecutor2 should work.