Oracle ADF BC Reusing SQL from Statement Cache

Oracle ADF BC by default is trying to reuse prepared SQL query from statement cache. It works this way when ADF BC runs with DB pooling off (jbo.doconnectionpooling=false). Normally we tune ADF application to run with DB pooling on (jbo.doconnectionpooling=true), this allows to release unused DB connection back to the pool when a request is completed (and in this case, statement cache will not be used anyway). If View Object is re-executed multiple times during the same request - in this situation, it will use statement cache too.

However, there are cases when for specific View Object you would want to turn off statement cache usage. There could be multiple reasons for this - for example, you are getting Closed Statement error after it tries to execute SQL for statement obtained from statement cache. Normally you would be fine using statement cache, but as I said - there are those special cases.

We are lucky because there is a way to override statement cache usage behavior. This can be done in View Object implementation class either for particular View Object or in the generic class.

After View Object was executed, check the log. If this is not the first execution, you will see log message - "reusing defined prepared statement". This means SQL will be reused from statement cache:

To control this behavior, override getPreparedStatement method:

We create new prepared statement in this method, instead of reusing one from the cache.

As a result - each time View Object is executed, there is no statement cache usage:

Download sample application from GitHub repo.

Database Change Notification Listener Implementation

Oracle DB could notify client, when table data changes. Data could be changed by third party process or by different client session. Notification implementation is important, when we want to inform client about changes in the data, without manual re-query. I had a post about ADS (Active Data Service), where notifications were received from DB through change notification listener - Practical Example for ADF Active Data Service. Now I would like to focus on change notification listener, because it can be used in ADF not only with ADS, but also with WebSockets. ADF BC is using change notification to reload VO, when configured for auto refresh - Auto Refresh for ADF BC Cached LOV.

Change notification is iniatilized by DB and handled by JDBC listener. There are two important steps for such listener - start and stop. Sample application - WebSocketReusableApp.zip, implements servlet DBNotificationManagerImpl:

I'm starting and stopping DB change listener in servlet init and destroy methods:

DB listener initialization code is straightforward. Data changes are handled in onDatabaseChangeNotification method. Listener is registered againt DB table executed in initial query:

In my example I'm listening for changes in Employees table. Each time, when change happens - query is re-executed to calculate new average salary values for jobs:

On first load, when servlet is initialized - listener is registered. Initial data read happens and listener is reported as started:

I can change value in DB, commit it:

DB change listener is notified and query is being executed to fetch new averages. Value for SA_REP is changed:

I hope it makes it more clear how to use DB change listener. In my future posts I will describe how to push changes to JET through WebSocket.