Jason Baragry's Blog: The existential pleasures of software architecture

This entry shows how JavaDB can be used as an embedded database to act as a cache for services deployed in the Sun Application Server.

I was recently working on a project where we needed to wrap a legacy application as a reusable Service. This wrap&reuse situation is something we often encounter. For instance:

• A read-only Service must exist to return information to external clients
• The information changes quite slowly
• That data exists in a legacy backend system.
• The cost of communicating with the legacy system is relatively high. This can be because of both communication time and financial cost because the legacy system maybe remotely hosted.

So, I thought it would be a good chance to try JavaDB / Derby as an embedded database to cache the results of the legacy system calls in the Service itself to limit the amount of communication with the backend. This is a simplified version of how I got to work.

The first step is to get JavaDB installed on your machine. There is an article over at Linux-Mag that provides a simple guide for doing that.

When JavaDB is used in embedded mode there can only be on client connected to the DB at a time. So you need to create the table, copy the embedded database libraries to the appserver, then restart the appserver. When the appserver restarts it creates and maintains the client connection to the embedded database.

Start by making a table to store the cached data.

bash-3.00$ java -Dderby.system.home=/export/home/jb156719/DerbyDB org.apache.derby.tools.ij
ij version 10.1
ij> connect 'jdbc:derby:serviceCacheDB;create=true';
ij> create TABLE SERVICE_CACHE (id_num varchar(11) PRIMARY KEY, serviceData varchar(10000), timestamp TIMESTAMP);
0 rows inserted/updated/deleted
ij> select * from SERVICE_CACHE;
ID_NUM     |SERVICEDATA                                                             |TIMESTAMP 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------





0 rows selected
ij> quit;

Add the jar for embedded derby to the appserver library path

cp $DERBY_INSTALL/lib/derby.jar $AS_HOME/domains/domain1/lib/ext

Create the JDBC resource and connection pool in the App Server.

In the App Server console create a new Connection Pool in the JDBC Resources section. Provide a connection pool name, in this case it is serviceCachePool , and set the ResourceType to be javax.sql.DataSource

For the datasource classname, set the value to org.apache.derby.jdbc.EmbeddedDataSource

In the properties fields set the DatabaseName to the full path of the database you created previously using the ij tool. In this case it is /export/home/jb156719/DerbyDB/serviceCacheDB.

Create the JDBC resource using the newly created connection pool and make it available on the server you are using. The name in this example its jdbc/serviceCacheDB.

Create your Service. In this example, I've generated an J2EE-based webservice from a WSDL using Netbeans.

Add the necessary JDBC resource references to your Service. For the EJB-based webservice you need to modify the ejb-jar.xml and sun-ejb-jar.xml

Greg Sporar's blog entry has more details on adding these resources.

Then you just write your code to use the JDBC resource within your service. In this simple example, my service endpoint code checks if the data is in the cache, adds it to the cache if it is not, then returns the result.

import ...

public com.sun.services.HelloWorldServiceResponse sayHelloWorld(com.sun.services.HelloWorldServiceRequest recipientsListRequest) throws
            com.sun.services.HelloServiceFault,  java.rmi.RemoteException {
        




        String resultS = checkLocalCache(recipientsListRequest.recipient);
        if (resultS == null)
            putStringtoDB(recipientsListRequest.recipient);

        HelloRecipient hr = new HelloRecipient(recipientsListRequest.recipient + " " + resultS);
        HelloRecipient hrs[] = new HelloRecipient[1];
        hrs[0] = hr;
        com.sun.services.HelloWorldServiceResponse _retVal = new HelloWorldServiceResponse(
                hrs);
        return _retVal;
    }

private String checkLocalCache(String personName) {
        DataSource dataSource;
        try{
            dataSource = getCacheDB();
        }catch(NamingException e){
	    ...
        }
	...
        try{
            conn = dataSource.getConnection();
            String sqlQuery = "SELECT * FROM SERVICE_CACHE where id_num = '" + personName + "'";
            prpStmt = conn.prepareStatement(sqlQuery, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            rs = prpStmt.executeQuery();

            if (!rs.first())
                resultS = NO_CACHE_RESULTS;
            else {
                resultS = rs.getString(2);
                Timestamp ts = rs.getTimestamp(3);
                rs.close();

                long millisecs = System.currentTimeMillis();
                Timestamp tsOld = new java.sql.Timestamp(millisecs - STALE_CACHE_AGE);
                if (ts.before(tsOld)) {
                    sqlQuery = "delete FROM SERVICE_CACHE where id_num = '" + personName + "'";
                    prpStmt = conn.prepareStatement(sqlQuery);
                    prpStmt.executeUpdate();
                }
        }catch(SQLException e){
	   ...
        }finally{
	   // close the connection
        }
        if (resultS.startsWith(NO_CACHE_RESULTS)) {
            return (String) null;
        } else {
            return (resultS);
        }
    }

That's it. It was certainly fast enough for the solution I was working on. Of course, I've avoided all the intereting questions such as, "How does it benchmark compared to an external database?", "How will your caching strategy work when you have a load-balanced, clustered, HA solution?", "What happens when I want a read-write service?". But an embedded DB is certainly worth a try for speeding up simple "wrap & reuse" services.