Extending Application Module for ADF BC Proxy User DB Connection

In this post I will describe how to retrieve database connection for ADF BC proxy user connection. I will provide best practice as well, how to implement Common AM and extend your local AM with common functionality. In the next post will test how ADF BC proxy user approach works when database connection pooling is enabled. We are using proxy user connection approach, when there is requirement to login into database with Web user. Such connection can be established through JDBC Data Source proxy user:

Its often happens to see database connection for proxy user is retrieved from dummy prepared statement, as described on this blog- we are bypassing ADF BC offered functionality in this way:

However, there is another way - we can use standard ADF BC methods and retrieve database connection from ADF BC transaction factory. How to implement it in generic way? Download sample application - AMExtendApp.zip, I will describe how this sample is implemented.

I have two Model projects - one contains common classes together with common AM, another consumes common Model and contains actual Model implementation:

Common Model extends ADF BC database transaction factory:

Database transaction implementation class provides public method to retrieve database connection:

Common Model AM Implementation class contains overriden prepareSession method, where we are getting DB connection from transaction factory and opening proxy connection for current Web user. This is main method, where proxy connection is established:

Common Model AM Implementation class is declared to be Base Class for CommonModel AM:

This is visible from AM Component Class:

Make sure to set transaction factory for Common Model AM - TransactionFactory property in AM configuration:

We switch now to our local AM, from Model project. Select this AM to extend from CommonModule - it will inherit custom base classes with generic prepareSession method:

Thats all, no need to change anything for local AM - it will invoke prepareSession from common AM automatically. Just double check you have set JDBC Data Source for AM connection:

JDBC Data Source connects as APPUSER:

Web user connects as HR (same as DB schema name):

HR schema data is retrieved successfully through proxy APPUSER:

Select statement is executed for Employees table from HR schema:

When passivation/activation happens, we can see that PS_TXN table is created in APPUSER schema (as expected in proxy user schema):

One more hint, if you get DB transaction cast error from prepareSession:

Make sure to set custom transaction factory class for local AM as well. When extending AM from common AM, only custom classes and VO instances are retrieved, but not AM properties: