An Oracle blog about Mobile Cloud Service

  • January 11, 2013

Making use of multiple independent ADF BC data sources

Chris Muir
Product Manager
An uncommon but valid customer use case for ADF BC is to have two root AMs that point at entirely two different connections/data sources/JNDIs within the same Model project.  For example maybe you need to connect to both an Oracle and SQL-Server database to show data from two different database systems.  Or maybe you even need to connect to the same Oracle database but two different schemas.

Now one solution to this problem is to solve it at the database level.  This really is the preferred route as the database has some awesome tools for solving these sorts of issues such as database links, gateways and more.  Remember that the database and these tools have all sorts of supports for tricky database issues such as distribution, 2 phase commits, all the sort of tricky issues that years of effort have gone into solving which to be basic, a normal Java programming team wont be able replicate.

However sometimes we're simply not going to be able to use those tools or we just want a simple ADF solution for getting data from two databases.

So how do we do this in ADF?

The solution is fairly simple, we need:

a) 2 separate database connections
b) 2 separate root AMs and associated VOs/EOs
c) Any page/fragment we drop the VOs/EOs, for the relating task flow we *must* use the <No Controller Transaction> option

In considering each of these parts to our solution:

a) It's not immediately obvious but we are free to use more than one database connection in our application as the following picture demonstrates:

When you have more than 1 database connection you must be mindful that via the Model project properties, the ADF Business Components options has a Connection poplist that defines the default database connection for the project to use at design time as can be seen here:

It's up to you when you run the various ADF BC wizards to ensure you have the right database connection selected.  

b) Once you've defined your two connections, you're now in a position to use the associated ADF BC wizards to create your various ADF BC components.  You will need to return and manage the default database connection at the Model -> ADF Business Components -> Connection project property to ensure you are connecting to the right database each time you run the wizards.  JDev wont give you any help here, so be careful!

In the following picture it shows the end results of creating a Model project, with a Regions EO/VO exposed via the HrAppModule connecting to the HR Oracle database schema, and another EO/VO Customers exposed via the OeAppModule connecting to the OE Oracle database schema:

Note there's an IDE bug 16032880 we need to be wary of here and double check the IDE hasn't introduced an error with the associated JNDI data sources for the AMs. Each change to the Connection option at the Model ADF BC project properties level, it will override all the data sources of the root AMs with the one you just picked.  If you locate the bc4j.xcfg file that holds the JNDIs, check that the data sources for the associated AM configurations are correct.  For example for the two AMs, I expect to see the following <Custom JDBCDataSource> entries for each configuration:

HrAppModuleLocal <Custom JDBCDataSource="java:comp/env/jdbc/HrConnDS"/>
HrAppModuleShared <Custom JDBCDataSource="java:comp/env/jdbc/HrConnDS"/>
OeAppModuleLocal <Custom JDBCDataSource="java:comp/env/jdbc/OeConnDS"/>
OeAppModuleShared <Custom JDBCDataSource="java:comp/env/jdbc/OeConnDS"/>

If you do discover an error simply repair this in the file, save all, and the issue will go away.  Ensure to test in the Business Component Browser to make sure the AMs are configured correctly and you can see data from both VOs.

c) Having sorted out the Model layer you can now move to the ViewController layer.  Typically you'll start creating pages and fragments in your unbounded task flow (UTF) and bounded task flows (BTF) making use of the VOs you've just exposed through the separate AMs.

In doing so you must be aware that the transaction options you pick for your BTFs can change the connection behaviour of your AMs you have just setup.  Here are the bounded task flows supported 4 transaction options:

Strictly speaking it's 3 options that are related, that being Always Begin New Transaction, Always Use Existing Transaction and Use Existing Transaction if Possible, plus the ability to turn this feature off known as <No Controller Transaction>.  You can read more about these options here (see the Task Flow Transaction Fundamentals paper).

There's a back end feature built into the task flow transaction management where if your application makes use of multiple ADF BC data controls mapping to separate root AMs, at runtime ADF BC will try to share connections amongst the root AMs at runtime.  While this may seem disastrous to what we're attempting to achieve here, this feature is very important for creating scalable applications where our architecture has forced us to create separate root AMs, typically we don't want one user to take out several database connections.

Luckily task flows still provide a solution for our use case that is counter to this described functionality.  Rather than picking one of the BTF transaction options, that being Always Begin New Transaction, Always Use Existing Transaction and Use Existing Transaction if Possible, instead pick the <No Controller Transaction> option.  The <No Controller Transaction> option doesn't override the connections of the root AMs and allows them to connect to any JNDI they define at runtime.

And that's all there really is to the solution.  You just need to make sure you have the right configurations for your root AMs, the Model project ADF BC connection, and the task flow transaction options.

Questionably what about the unbounded task flow?  It doesn't allow us to define any transaction options, what do we do if we use our VOs in pages/fragments of the UTF?  For all intent and purposes you can treat the UTF as using the <No Controller Transaction> option.

What about the task flow Share Data Controls with Calling Task Flow option?  What do we set that to?  The previous Task Flow Transaction Fundamentals paper details that in full, but to say, if you untick that option (known as an isolated data control scope) for the current BTF, a brand new instance of the current root AM and relating VOs/EOs will be instantiated for the current user for the life of the BTF.  If you select the check box (known as a shared data control scope), if an instance of the ADF BC AM is already being used in a previous BTF that called this BTF, simple the ADF BC AM data control will be shared.  If it doesn't yet exist it will be created for the first time.

Sample App

I've provided a sample application for you to see this behaviour based on the scenario described above.  It requires access to both the HR and OE sample Oracle database schemas.

When you run the app via the Splash.jsf page and navigate to the CombinedTaskFlow, back in JDev open the database navigator and locate both tables in the separate schemas, and the associated records that are currently showing in the app.  Now in the app you can change and commit each VO and watch the changes independently saved to the corresponding records in the separate schemas in the database.  Note you can change and commit the records independently, committing one will not commit the other.


It's worth while having a look at how things break too.  If you open the CombinedTaskFlow.xml in JDeveloper and change the BTF transaction options to Always Begin New Transaction, rerun the app, you'll notice on arriving at the CombinedTaskFlow you'll receive a database error "ORA-00942: table or view does not exist".  As described previously the other BTF transaction options combine/share the connection of the first root AM at runtime, in this case the HrConn.  As such as soon as the CustomersView attempts to query from the CUSTOMERS table in the database, as its root AM is now sharing the connection of the HR root AM it can't see the required tables.

Alternatively if you fix this change and return to the ADF Business Components Connection option under the Model project properties, and switch the connection to OeConn, this will modify all the JNDI connections in the relating bc4j.xcfg file to point at the OE schema.  On rerunning your app you'll again see "ORA-00942: table or view does not exist".  Again in this case be cognizant of bug 16032880 described earlier and how to fix the problem.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.