Friday, August 6, 2010

ADF Application with multiple Data Sources or Database connections

The Problem: ADF Application with multiple Data Sources or Database connections.

This is a pretty normal use-case, but can be a bit puzzling to new developers. Most apps have more than one database that they connect to. One way of dealing with this is to hide the fact there are multiple databases under the DB layer through the use of synonyms and DBlinks, but sometimes such access is not possible or desirable.  Here I discuss how to use multiple databases from ADF BC.

Solution: Multiple Root Application Modules

The basic element here is the notion of the Root Application Module. For starters, the Root Application Module defines the transactional context.  You need to create two application Modules, each one connecting to its own Database. You could also connect the very same application module to two different databases if you need to, more on that later.  Nesting one application module inside another to get access to it won’t work as nested application modules share the same root.
So the way to do this is to create a *new* _Root_ Application Module directly from the BC4J configuration. This ensures that the connection associated with that Application module’s configuration is used. The ADF model layer usually takes care of handling instances of you Application Modules, and here, you are doing it manually. This brings some risk, and be careful to release the Application Module after use.

Create Project

Start out by creating one or more model projects in your application. In most cases you’ll only require one project, but if you need to create the ADF BC objects from database tables, you will likely need two projects so you don’t have to switch connections in the same project at design time. The “Connection” you see in the Project Properties > Business Components (click on Business Components) is used only during design time to generate ADF BC objects from a DB. An Application Module’s Configuration(bc4j.xml) defines the connection it uses at run time .

Figure 1
The two model projects will house the Application Modules that connect to the two different Databases.

Configuration

Once the model projects are created, open the Configuration dialog box for the Application modules. The connection an Application Module uses at runtime is defined in the Application Module’s configuration and not the project properties.


      

The Manage Configurations dialog lists the Configurations available for that AM. The configuration for an AM encapsulates the parameters for the AM like the connection it uses. The configuration name is important as we’ll later use it to identify a particular named configuration and use that named configuration to create an Application Module instance. To specify the parameters inside the named configuration, select the configuration name, and choose Edit
  

I’m assuming that a Data Source will be used. If using a JDBC URL, the steps are the same. A valid Data Source name is defined for the first DB and the steps should be repeated the same for the second Application Module connecting to the second DB. These Data sources need to be available at runtime, so either they need to exist in the Application server you are deploying to, or you need to deploy them as part of your application.  I’m using the automatic deployment of DS provided by JDeveloper (this might not be suitable for production) so JDeveloper automatically provides a Data Source entry in this wizard based on the connections defined at the Application or IDE level. These Data sources have name that is the connection name appended with the two letters ‘DS’.   After editing the Data source, you have effectively created a Configuration called DB_One_ServiceLocal  (see screenshot) that uses the Data Source /jdbc/database_1DS  . This configuration can now be used anytime to create a root application module with the configured parameters (like an alternate Data Source ).

A note about Data Sources

See this for how to deploy a Data source manually.
This explains how to make use of JDeveloper’s automatic data source deployment.


Here is where most people get puzzled. After creating two Application Modules, you would be tempted to nest your second application module inside your first. This does not work, as the transaction context is defined for the root application module, and when you nest, the root application module does not change, and so the connection does not change. The connection used is the root Application Module’s connection. The solution is to create a new Root Application Module directly from the named BC4J configuration we created. This being a root application module on its own ensures that the connection associated with that Application module’s configuration is used. 

The Code

The following screenshot shows the classes generated by JDeveloper, nothing out of the ordinary.

The second app module exposes a method on its client interface that the first application module invokes.
Here is the method the second AM exposes on its client interface:
/**
   * Method exposed by this AM to provide the query results from its VO.
   * Since this AM's configuration connects it to the second database,
   * the VO query is executed agaist that DB
   *
   * @return
   */
   public String querySecondDB()  {
      getDBTwoVO().executeQuery();
      return ((DBTwoVORowImpl) getDBTwoVO().first()).getSampledata();
   }
   
Now in order for the first application module t access and query the second data base, it creates a new root application module instance of the second application module using the named configuration that connects it to the second database. Once the new root application module instance is obtained, the exposed method is invoked and depending on the scenario, the root application module instance is released. The method used is Configuration. createRootApplicationModule()
public String queryMulipleDBs(){
    getDBOneVO().executeQuery();
    String fromDBOne;
    String fromSecondDB;
    fromDBOne = ((DBOneVORowImpl) getDBOneVO().first()).getSampledata();
    // Create new root AppModule from the named Configuration
    DB_Two_Service service =  (DB_Two_Service)Configuration.createRootApplicationModule("sample.model2.services.DB_Two_Service", "DB_Two_ServiceLocal");
    // Query the second DB
    fromSecondDB = service.querySecondDB();
    Configuration.releaseRootApplicationModule(service,true); //releases and removes the AppModule
    return "from first DB : " + fromDBOne + " from second DB : " +fromSecondDB;

  }
   
If you have the queryMulipleDBs() method of the first Application Module exposed on its client interface, you can use the Business components test to validate the whole thing since with 11g, the Business components tester can work with Data sources as well.


A note about multiple Projects and Application Modules

Here we used a separate project and an Application Module to connect to our second DB. This however is a personal preference, I like this because it keeps the code easily readable and separates the artifacts cleanly. The real crux of the technique is an alternative root application module using a particular configuration. So ineffect you could have two ApplicationModules in the same project with thier configurations connecting to two DBs or you could have the same ApplicationModule definition with just an alternative Configuration(that connects to the other DB) and instantiate a new instance of the same ApplicationModule definition as a root AM, with the alternate Configuration using the same code. 
Configuration.createRootApplicationModule(
                                  "sample.model.services.DB_One_Service",
                                  "CONFIG_WITH_A_SECOND_DATASOURCE");
So here we have the first AM instantiating another root instance of itself with another Configuration, one that connects to another DataSource.

14 comments:

  1. Hi,
    Good article.
    We use the Vos from another AM(connecting to a different data source) as view accessor based lookups in the VOs.

    For this case, the second AM always refers to the context created by the first AM, which means it does not connect to its own AM database but the root's database.

    Any ideas on how this can be solved?

    -Soumya

    ReplyDelete
  2. Soumya,

    You encounter this because the second AM connecting to your second DS is not a root AM. The context used will always belog to the Root AM. This article describes how to to create a new Root AM instance and invoke a method on it so that the second DS is used.

    In your case, a declarative approach is difficult (nothing that I'm aware of ). One suggestion is this :
    In your VORowImpl, override the getter for your ViewAccessor and here Create new root AM instance and get the VO instance from the new root AM. I haven't tried it, but its something that just occurred to me. Let me know if it works out for you.

    ReplyDelete
  3. Hi all. I have a same problem. How i switch between multiple databases in runtime. (database structures are same each other). I haven't tried it.
    Any ideas on how this can be solved?

    -Habul

    ReplyDelete
    Replies
    1. Hello Habul,

      Were you able to achieve this in some way? Even I have the same problem and thinking on the best way to resolve this. Thanks!

      Rgds,
      Ashish

      Delete
    2. You should be able to do that with the steps above . Are you having any specific issues ?

      Delete
  4. Hi Habul,

    Did you try the approach described in this blog post ? Were you having trouble with any of the steps above ?

    ReplyDelete
    Replies
    1. Hi jeevan,

      In my case, I created the two Application Modules, onde to postgres and other to oracle. I didn´t find how you created the classes *_ServiceDefImpl.java, *_Service.java and *_ServiceClient.java. I'm using the JDeveloper 11g R2. Thanks.

      Delete
  5. Hi,

    We just apply this great article and it is working well but we have a question about how to lock the second DB (a table or a field) from being accessed until our application insert and commit it?
    Our scenario is to get record from the second DB then do a modification and commit.

    Thanks

    Jamil

    ReplyDelete
    Replies
    1. Hello Jamil,
      Glad to hear that you found this useful.
      Your usecase can be implemented easily using the locking modes available in the AM. NAvigate to your AppModule configuration (right click the appmodule, and select Configurations) and then edit the configuration. In the edit screen's "properties" tab you should see "jbo.locking.mode" property. This property controls the locking behaviour. The values are : pessimistic and optimistic. In pessimistic mode, the record is locked if a user makes a change in the UI and the lock is held until the user either commits or rolls back the transaction. If the user just sits idle or just closes the browser, the lock is held until the browser session times out after the timeout period set in the web.xml.

      Web applications should typically use optimistic locking though, and you should fully understand the implications of using pessimistic locking before you use it. The following articles will give you a better idea on whats involved and some smart ways to render locked records as disabled on the UI too.

      http://andrejusb.blogspot.com/2010/03/optimistic-and-pessimistic-locking-in.html (An example with the dofferences between the locking modes)

      http://andrejusb.blogspot.com/2010/11/immediate-row-level-lock-management-for.html (a nice way of handling immediate row locks ina real application)

      Delete
  6. Very useful article. Thanks for providing this information.

    ReplyDelete
  7. Thank You Jeevan, I am going to implement your technique. My blog will track my tech notes. http://itreeware.blogspot.com/2014/11/adf-fusion-using-1-dbase-connection-in.html

    ReplyDelete
  8. Hello,

    I am trying to replicate the same functionality which has been described in this post. But i am getting an error as oracle.jbo.ConfigException: JBO-33005: Configuration AppModuleAMLocal is not found.
    I am trying to access the ViewObject defined in firstapplicationmodule in the second application module.

    oracle.jbo.ConfigException: JBO-33005: Configuration AppModuleAMLocal is not found.

    Below is the code which is being used. Can you please let me know what i am doing Incorrect.

    public void Rerunjob(String username, String Parameters){
    //AppModuleAM am = null;
    ApplicationModule am =
    Configuration.createRootApplicationModule("model.AM.AppModuleAM","AppModuleAMLocal");


    String EmailAddr = "";
    ViewObject Vo = am.findViewObject("Contact1");
    Vo.setNamedWhereClauseParam("P_USERID", username);
    Vo.executeQuery();
    int i = Vo.getRowCount();
    if (i > 0) {
    Row row = Vo.first();
    if (row.getAttribute("EmailAddr") != null)
    {
    EmailAddr = (String)row.getAttribute("EmailAddr");
    }

    System.out.println("Email Addr is "+ EmailAddr);
    }
    Configuration.releaseRootApplicationModule(am, true);
    System.out.println(username);

    }


    ReplyDelete
  9. Hi, we managed to work with own usecase: Two AMs with a different DB type each one: first with Oracle, second one with an SQL92 DB.
    But we're experiencing some JBO-30003 error with the SQL92 AM related to http://www.oracle.com/technetwork/developer-tools/jdev/knownissues-097654.html#bc3. Tried solution from link and fixes it, but breakes Oracle AM connections.
    Any advice? Thanks in advance

    ReplyDelete
  10. There are available for all of the aspirants NEST Application Form 2017. Apply Online as soon as possible for the NEST Application Form.

    ReplyDelete