It is common to make use of stored procedures to encapsulate business logic that operates on data. There are fairly well documented benefits of doing this despite, also, the known drawbacks. For one thing these procedures represent a chunk of work with minimal infrstructural accoutrements. For example you don't need to worry about transactions or connections. You just write the logic. The connections and transactions are handled by the container, in this case the database.

At times a database may not support stored procedures or even when they do due to the complex nature of manipulation you may need to use the power of Java to write that logic. But when we step into java, you start needing to know about transactions and connections, statements, result sets etc.

This article shows how to imitate stored procedure like logic in Java while borrowing the same connection agnostic benefits that are inherent in stored procedures.

The examples and the methodology shown here is borrowed from Aspire. Nevertheless it should be easy enough to emulate in your own data access mechanisms.

An example to lead off the discussion

I want to insert two rows into table 1 and then delete a row from table 2. Let me provide psuedo code for a stored procedure

create procedure sp_proc1(arg1, arg2, arg3)
{
	//Action1
	delete from table2
	where table2.column1 = arg1;
	
	//Action2
	insert into table1 (column1) values (arg2);
	
	//Action3
	insert into table1 (column1) values (arg3);
}  

In Aspire these three actions are represented as three independent transactions as follows

request.Action1.classname=com.ai.db.DBRequestExecutor2
request.Action1.db=(my-database)
request.Action1.query_type=update
request.Action1.stmt=\
	delete from table2 \
	where table2.column1 = {arg1}

request.Action2.classname=com.ai.db.DBRequestExecutor2
request.Action2.db=(my-database)
request.Action2.query_type=update
request.Action2.stmt=\
	insert into table1 (column1) values (arg2)

Or you can get fancy define the third action in XML if you like. Aspire also allows for child/attribute equivalency

<request name="Action3" db="my-database">
    <classname>com.ai.db.DBRequestExecutor2</classname>
	<query_type>update</query_type>
	<stmt>
		insert into table1 (column1) values (arg3);
	</stmt>
</request>	

Writing the procedure in Java

public class SpProc1 extends com.ai.parts.DBProcedure
{
	protected abstract Object executeDBProcedure(String requestName, Hashtable arguments) 
		throws DBException
	{
	   try
	   {
			//Assuming arg1, arg2, and arg3 are passed in
			execute("Action1",arguments);
			execute("Action2",arguments);
			execute("Action3",arguments);
			return new Boolean(true);
		}
		catch(RequestExecutionException x)
		{
			//You can deal with exception here or at the each execution level
			throw new DBException("Error",x);
		}
	}
	
	private Object execute(String symbolicName, Hashtable arguments)
	   throws RequestExecutionException
	{
		return AppObjects.getObject(symbolicName, arguments);
	}
}

Calling SpProc1

To call SpProc1 define an entry in your config file

request.MyJob.classname=SpProc1
request.MyJob.db=(my-database)

This symbolic name "MyJob" could be tied to an incoming http request where the form arguments are inherently passed to MyJob. Or if you want to invoke MyJob programmatically you will do the following

performMyJob()
{
	//Prepare your args
	String arg1 = "abc";
	int arg2 = 10;
	Date today;
	
	//Place them in a hashtable
	Hashtable ht = new Hashtable();
	ht.put("arg1", arg1);
	ht.put("arg2",new Integer(arg2));
	ht.put("arg3",today);
	
	//Invoke the symbolic name
	return AppObjects.getObject("MyJob",ht);
}

A real world example

Consider an incoming html form submission

http://host/webapp?request_name=SaveAttributes
attributes=a1,a2,a3,a4,a5
a1=10
a2=This is sample text
a3=7/10/2004
a4=Another sample text
a5=more of the same

You want to insert these attributes into an attribute table

foreach attribute a in attributes
insert into attribute-table (attribute-name, attribute-value)
(a, value-of(a))
end;

request.SaveAttributes.classname=SaveAttributesProcedure
request.SaveAttributes.db=(my-database-name)
request.SaveAttributes.query_type=update

request.SaveAttributes.redirectURL=(target success url)

public class SaveAttributesProcedure extends DBProcedure
{
   protected Object executeDBProcedure(String requestName, Hashtable urlArgs)
         throws DBException
   {
   	   try 
	   {
		   String attributes = urlArgs.get("attributes");
		   Vector v = Tokenizer.tokenize(attributes,",");
		   for(int i=0; i < v.size;i++)
		   {
				String attributeName = (String)v.get(i);
				String attributeValue= (String)urlArgs.get(attributeName);
				urlArgs.put("attributeName",attributeName);
				urlArgs.put("attributeValue",attributeValue);
				
				AppObjects.getObject("InsertSingleAttribute",urlArgs);
		   }
		}
		catch(RequestExecutionException x)
		{
		   throw new DBException("Error",x);
		}
   }
} 

We need to define what "InsertSingleAttribute" will look like

request.InsertSingleAttribute.classname=com.ai.db.DBRequestExecutor2
request.InsertSingleAttribute.query_type=update
request.InsertSingleAttribute.stmt=\
	insert into attribute-table (attribute-name, attribute-value) \
	values({attributeName},{attributeValue})

References

1. Aspire How To

2. Aspire How To Advanced

3. Writing Factory Parts in Aspire