Implementing Java DB as an Embedded Cache to Support Service

Introduction

This article shows how Java DB can be used as an embedded database to act as a cache for services deployed in the Sun Java System Application Server. This example is based on the following common scenario:

• 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 communication time and financial cost because the legacy system maybe remotely hosted.

Therefore, it is beneficial to cache the results of the Service calls in the Service itself to limit the amount of communication with the legacy system.

Step by Step Instructions

1. The first step is to get Java DB installed on your machine. This article provides a simple guide for doing that.

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

   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;

3. Add the jar for embedded derby to the appserver library path.
   cp $DERBY_INSTALL/lib/derby.jar $AS_HOME/domains/domain1/lib/ext

4. 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

   

   Figure 1

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

6. 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.

   

   Figure 2

7. 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.

   

   Figure 3

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

9. Add the necessary JDBC resource references to your Service. For the EJB-based webservice you need to modify the ejb-jar.xml (see Figure 4) and sun-ejb-jar.xml (see Figure 5) files.Greg Sporar's blog entry has more details on adding these resources.

   

   Figure 4
   

   Figure 5

10. 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);
            }
        }