ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

For Tomcat Developers, Aspire Comes in a JAR
Pages: 1, 2, 3, 4, 5

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.

import com.ai.application.interfaces.*;
import com.ai.application.utils.*;
import com.ai.data.*;
         
public void someFunction()
{
   IDataCollection col;
   try
   {
      Hashtable args = null;
      col = (IDataCollection)AppObjects.getObject("MYDATA",args);
      IIterator rowItr = col.getIIterator();
      for(rowItr.moveToFirst();!rowItr.IsAtTheEnd();rowItr.moveToNext())
      {  
         IDataRow dr = rowItr.getCurrentElement();
         String value = dr.getValue("col-one");
      }
   }
   catch(com.ai.application.interfaces.RequestExecutionException x)
   {
      AppObjects.log("Error: Could not get data collection. Factory error",x);
   }
   catch(com.ai.data.DataException x)
   {
      AppObjects.log("Error: Data related error",x);
   }
   finally
   {
      col.close();
   }
}

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:

Request.MYDATA.classname=com.ai.db.DBRequestExecutor2
Request.MYDATA.db=MyDataBaseAlias
Request.MYDATA.stmt=\
   Select * from table1

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.

Request.MYDATA.classname=com.ai.db.DBRequestExecutor2
Request.MYDATA.db=MyDataBaseAlias
Request.MYDATA.stmt=\
   Select * from table1 where col1-value={col1-key}

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:

Request.MYDATA.classname=com.ai.db.DBRequestExecutor2
Request.MYDATA.db=MyDataBaseAlias
Request.MYDATA.stmt=\
   Select * from table1 where col1-value={col1-key.quote}

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.

Database.name=MyOracleDB
Database.MyOracleDB.jdbc_driver=oracle.jdbc.driver.OracleDriver
Database.MyOracleDB.connection_string=jdbc:oracle:thin:@host:port:oracle_instance
Database.MyOracleDB.userid=user
Database.MyOracleDB.password=passwd

See how we have defined a database reference to an Oracle database. Let us proceed to define a database reference for an Access database.

Database.name=accessDB
Database.accessDB.jdbc_driver=sun.jdbc.odbc.JdbcOdbcDriver
Database.accessDB.connection_string=jdbc:odbc:ptr.mdb
Database.accessDB.userid=access_user
Database.accessDB.password=access_pw

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:

Request.MYDATA.classname=com.ai.db.StoredProcedureExecutor2
Request.MYDATA.db=MyDataBaseAlias
Request.MYDATA.stmt=\
   Call sp_get_table_data(?,{col1-key.quote})

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.

Pages: 1, 2, 3, 4, 5

Next Pagearrow