Implementation Details
Our application was initially written with a single database Helper class that all other classes used to access the database. When it came time to implement the JSPs, this single class became three. The base Helper class containing much of the business logic was made abstract, and two new classes extending it were written: a Client class for the applications to use, and a JSP class to be loaded into the database containing the static methods that would be wrapped with PL/SQL FUNCTIONs (all JSP methods must be declared static). The base Helper class had an abstract connect() method in which the connection to the database was to be established, and the Client and JSP classes both provided the correct means for achieving that connection. Although it's possible that the code to connect in a JSP can be exactly the same as on a client using JDBC (loading the driver dynamically and connecting), Oracle provides a simple default connection that makes this easy:
/**
* Connects to an Oracle instance using the default
* Connection, available when running inside the database
*/
protected void connect() throws SQLException, Exception
{
setConnection(new OracleDriver().defaultConnection());
}
The most challenging design decision was determining what data should be sent to the JSP and how. After evaluating using string representations of our objects using some form of markup language, the decision was made to simply use our existing classes that were already being used within the Helper class, group them into an array because we wouldn't need any Collection-like functionality in the JSP, and then serialize them directly to the database. Different databases will likely have different ways of doing this, but Oracle has some methods in their API that make it easy to get Readers and Writers to and from temporary BLOBs in the database, making the process of passing the arrays quite easy:
/**
* Takes a list of DatabaseObjects to be passed to a
* Java Stored Procedure called "db_object_jsp", which
* performs some logic on them and returns an array of
* boolean to indicate status on each.
*/
public boolean[] callJSP(List dbObjList)
throws SQLException, Exception
{
CallableStatement statement = null;
BLOB outBLOB = null;
BLOB inBLOB = null;
ObjectOutputStream objectOutStream = null;
ObjectInputStream objectInStream = null;
DatabaseObject[] transaction = null;
boolean[] results = null;
//Prepare a statement to the JSP
statement = getConnection().prepareCall("{? = call db_object_jsp(?) }");
statement.registerOutParameter(1,Types.BLOB);
//Create the transaction
transaction = new DatabaseObject[dbObjList.size()];
dbObjList.toArray(transaction);
//Send the transaction to the DB in a BLOB object
outBLOB = BLOB.createTemporary(getConnection(), false, BLOB.DURATION_SESSION);
objectOutStream = new ObjectOutputStream(outBLOB.getBinaryOutputStream());
objectOutStream.writeObject(transaction);
objectOutStream.close();
//Execute the statement
statement.setObject(2, outBLOB);
statement.execute();
//Read in the results and close the streams
inBLOB = (oracle.sql.BLOB)statement.getBlob(1);
objectInStream = new ObjectInputStream(inBLOB.getBinaryStream());
results = (boolean[])objectInStream.readObject();
objectInStream.close();
//Free the blob storage
outBLOB.freeTemporary();
//For some reason lost something along the way...
if(results.length != transaction.length)
{
throw new Exception("Incorrect number of results from JSP, " +
"recieved: " + results.length + " expected: " + transaction.length);
}
return results;
}
The JSP to read the BLOB into an array would look something like this:
/**
* Method called by Oracle to perform work on an
* array of DatabaseObjects
*/
public static BLOB databaseObjectJSP(BLOB dbObjBLOB)
throws Exception
{
ObjectInputStream objectInStream = null;
ObjectOutputStream objectOutStream = null;
DatabaseHelperJSP instance = null;
DatabaseObject[] dbObjArray = null;
BLOB outBLOB = null;
boolean[] results = null;
instance = new DatabaseHelperJSP();
instance.connect();
objectInStream = new ObjectInputStream(dbObjBLOB.getBinaryStream());
dbObjArray = (DatabaseObject[])objectInStream.readObject();
objectInStream.close();
//Do some work on the Array
results = instance.processDBObjs(dbObjArray);
//Send back the results
outBLOB = BLOB.createTemporary(instance.getConnection(), false, BLOB.DURATION_SESSION);
objectOutStream = new ObjectOutputStream(outBLOB.getBinaryOutputStream());
objectOutStream.writeObject(results);
objectOutStream.close();
instance.close();
return outBLOB;
}