Thursday, August 23, 2012

Sample Application for Switching Application Module Data Sources

I was preparing proof of concept for such scenario, where ADF application was working with multiple data sources. Requirement was to be able to select data source on runtime, before login. We had several DB users, different DB security applied to each of them - separate data source was defined. Different data source per business site, not really per business user - this would be too much. I would like to post and describe prepared sample application. Data Source runtime switch is implemented based on concept described in Jobinesh blog - Modifying the Application Module's JDBC DataSource at run time.

Download sample application - UserDataADFBCSample_v3.zip. This sample is based on application from my previous posts, it is enhanced with Data Source selection from login page.

Data Source runtime switch is implemented basically in two steps:

1. In the Model there is custom class that implements EnvInfoProvider. Specifically one method is overriden - getInfo(String, Object). This method reads data source name from session scope (its where we set it during login) and puts it into AM configuration:


2. Each Application Module must be set with proper configuration for jbo.envinfoprovider property. This property must point to our custom EnvInfoProvider class (see above):


Default Data Source name defined for Application Module is still important, ADF BC will use it in case if no custom Data Source name was provided:


I'm getting list of Data Sources from static VO:


This VO is exposed through Application Module Data Control. Keep in mind, even VO is static - AM still we be initialized on VO access. This means AM will establish connection to database, at that time when we will load static VO data. Custom Data Source name will not be set yet - default Data Source name will be used. Instance of static VO with list of Data Sources:


UI list with Data Sources can be created by drag and drop of DataSourcesView1 instance from Data Control into JSF page. Select Single Selection -> ADF Select One Choice option:


Choose display attribute - press OK and choice list with Data Source names will be generated for you:


Once choice list was created, go to Login page definition and create Name attribute binding (based on same iterator as Data Source choice list):


This will allow us to access selected Data Source name from Login bean - doLogin() method:


Here how it looks on runtime - user selects Data Source from the list:


User logs in into application - Application Module configuration is initialized with Hr DB data source and data is rendered:


From the log we can see that after login, session cookie was changed. This happens because there are two Web sessions created always - before and after login (its how it works by default in ADF). This allows us to set custom Data Source name after login action is done. Remember - before login, Application Module is initialized with default Data Source:


Hr Test DB - is non existing Data Source, I'm using it only for test purpose -  application should fail, to prove Application Module is using Data Source selected during login:


Indeed application breaks:


From the log we can see that Application Module was trying to connect with jdbc/HrTest Data Source - correct as expected:


25 comments:

Unknown said...

Hi , God Bless You for this nice article .
In My Case I need to :
1 - Get DEPT data from one MSSQL datasource and create DEPT Entity Object .
2 - Get EMP data from another MSSQL datasource and create EMP Entity Object.
3 – Create a new View Object base on these two Entity Objects and create a connection between them.
Dear Andrejus Baranovskis
Your solution is good for switching between two datasources at runtime but I need to work simultaneously with two datasource at runtime .
In php or .net we could create two different classes for connecting two different datasources at runtime and working with them concarrently. We could create relations in record sets or datatables.How we can overcome this challenge in Oracle ADF ?

Best Regards
Amir Husain Meysami
From : Fedosi Squre , Tehran , IRAN , ASIA

Andrejus Baranovskis said...

Hi,

May be this post will help you - http://andrejusb.blogspot.com/2012/07/lov-implementation-with-adf-bc-service.html

Andrejus

Siva Sankar said...

Hi Andrejus,

I have some requirement,but I have four sachems like DEV,INT,TEST and PROD like that,
when the login time i will select schema name its not a datasource.how can we handle this situation based on the selected username and env(schema) how to dynamically connect the particular schema.how to bind from the single UI to access based on the user and env(schema selected).

Ganesh Parthasarathi said...

Hi, to run the sample you offered, which jsf implemention is required.

When i opened the project in jdev, got compilation errors for faceCTx.

Then added jsf-api-2.0.4.jar.

But deployment fails with "Caused By: java.lang.IllegalStateException: Application was not properly initialized at startup, could not find Factory: javax.faces.context.FacesContextFactory".

I'm using jdev, 11.1.1.6 along with integrated server.


I tried to create my own project like yours, but the url ""/adfAuthentication?success_url=/faces/main"", gives 404 error. Not sure what is causing the issue. I just hard coaded user to default weblogic/weblogic1. Authentication does not throw any errors.

Andrejus Baranovskis said...

Hi,

You can run this sample app with ADF 11g R2. If you want to run it with ADF 11g R1 - you should copy paste web.xml and weblogic-application.xml content from another ADF 11g R1 application. Also change all Facelet files to be JSPX.

Andrejus

Ganesh Parthasarathi said...

Hi, i tried with Jdev R2. And getting following exception with integrated server.

java.lang.IllegalStateException: was not present on this page; tag oracle.adfinternal.view.faces.unified.taglib.UnifiedDocumentTag@76ee18encountered without an being processed.
at org.apache.myfaces.trinidad.webapp.UIXComponentELTag.setProperties(UIXComponentELTag.java:82)
at javax.faces.webapp.UIComponentClassicTagBase.findComponent(UIComponentClassicTagBase.java:690)
at javax.faces.webapp.UIComponentClassicTagBase.doStartTag(UIComponentClassicTagBase.java:1311)
at org.apache.myfaces.trinidad.webapp.UIXComponentELTag.doStartTag(UIXComponentELTag.java:67)
at oracle.adfinternal.view.faces.unified.taglib.UnifiedDocumentTag.doStartTag(UnifiedDocumentTag.java:50)
at jsp_servlet.__login_jspx._jspService(__login_jspx.java:99)

Andrejus Baranovskis said...

It works on my machine, something wrong on your environment.

Andrejus

Ganesh Parthasarathi said...

i just created a new jspx page and tried it was working. Then just changed the url to point to login.jspx, it started to work.. i did not change anything..

Anyway the functionality that i wanted got it working. Thanks a lot..

Andrejus Baranovskis said...

Very cool - I call this ADF magic :)

Andrejus

Don Kleppinger said...

I don't understand how this works with AM pooling. An AM is configured for a datasource. If you change the datasource on an AM and it gets checked back into the pool and then gets passivated when another user gets it who had selected a different datasource at login will the AM reconfigure each time its' checked out of the pool.

Using more than one datasource is as simple as configuring one app module for one datasource and another app module for a different datasource which we do in our application. But I don't understand how switching the datasource on the fly like you described will work with AM pooling.

Andrejus Baranovskis said...

Having different AM's per data source is quite primitive, often new data source can be created - we are not going to create new application module and redeploy app each time.

Your concern is false. This approach is stress tested with AM activation/passivation. When AM is checked back from the pool - then Data Source is reapplied again.

Andrejus

Don Kleppinger said...

When you have a page that displays data from two different databases you have to use a separate app module. You can't query two different databases from the same app module. Unless you created use a database view in the database itself that used a external link to the external database.

Andrejus Baranovskis said...

You describe different use case - accessing different DB's from the same session. Use case presented in this blog is different - different database access for all AM across entire session.

Andrejus

Anonymous said...

Hi !!!
I tried to create WebService on ADF BC
with 10 diffrent(IP) Oracle DB with same functionality for each of them. I use APP module for WS. How I can switch him for correct IP

Thanks for Your help

Anonymous said...

Hi,
Nice Post.
To enhace this more further. How can we get list of JDBC Data sources available in weblogic in the ADF Application.
Thanks
Syed

Andrejus Baranovskis said...

By using WebLogic API this should be possible or by accessing MBean...

Andrejus

DanteVick said...

hi Andre can we get the same database connection wizard on our page ,i mean dynamic database for our fusion application at runtime we change the connection and it will reflect the changes at the back end or I would like to know what happens when we changes database connection in application sources which file get affected ,how it changes and the flow of the database connection .

Andrejus Baranovskis said...

Hi,

Are you using Oracle Fusion Applications? Or you have custom made ADF application?

Andrejus

DanteVick said...

hi Andrejus, I have created two ADF fusion web applications one is using oracle Database connection with EO VO ,Am and other one is using Sql Server connection.
Both databases containing the same table name and structure in them,
And now I have created a third ADF fusion web application which is a main application in which i haven't created any adf business component,in that main application i have created one jspx page and i have added jars of previous two applications in main application through dynamic region i have dragged their task flows in my main application page as a link.
according to our databases i also created the DataSources in weblogic which we are using in our application.
when i make my main application run then the oracle connection TF link is working but when i clicked on Task Flow link which contains SQL server connection the table i have used in the page fragment of that task Flow .("Doesn't throwing any error or exception ")but is not displaying records and message coming "Access Denied".
Suggest a Solution .........

DanteVick said...

Is that possible to access different databases in single Fusion web Application ,in the above mentioned.
If yes, then how?
If Not in the way i am trying then suggest me the way by which I can access dual databases from same application

CountZero said...

There’s one thing worth noticing. If you create your entities with New Entity Object wizard instead of Business Components from Tables - schema object prop of Entity gets prefixed with database schema name. You get HR.COUNTRIES instead of only COUNTRIES and it propagates down when creating (or updating) view objects. And you can’t have two db schemas sharing name on one db server – switching DS in that case will give you query errors. Otherwise great article, very helpful. Keep up good work!

Andrejus Baranovskis said...

Thanks for your update. Makes sense, developers should make sure EO's universal naming (without schema prefix).

Andrejus

Есөн-Эрдэнэ said...
This comment has been removed by the author.
Logan Applesamy said...

Hi Andrejus. This posting is great. But could the data source be switched whilst in the application i.e: after login has happened.

Regards
Logan Applesamy

Andrejus Baranovskis said...

Hi Logan,

Yes, this should be possible - but I don't have ready sample app to share. I would need to implement it and test.

Regards,
Andrejus