Using one ViewObject for Global Lookup Data (Part 3): Multi Language Support

In an earlier post I blogged about a how to use a DB table to hold look up data like gender, weekdays or title which can used in different locations as ‘List Of Value’ data (Using one ViewObject for Global Lookup Data (Part 2)).
This third part adds a use case where we add multi language support when we read data from the DB table. This is an enhancement of the use case implemented in part 2. The old use case could load data in one language only. Now we add the the language to the data in the db table to allow retrieval of language specific data.

To make the use case work, we add two columns to the existing table GENERALLOOKUP. The first one holds the language code and the second one an id which is unique in the type and language. We use this new id named ‘GROUPID’ in the LOV to show the data of the selected type.
The ‘GROUPID’ remains identical for each type and language so that we can enter data in different languages. For the language code we use the codes we get from a Locale class. A sample for the data of WEEKDAY Monday:

Sample for Multilanguage Entry

We start by changing the DB table GENERALLOOKUP we used in the sample introduced in part 2. The sql script setup_db_multilangual.sql which is part of the project workspace, adds the two mentioned columns. You find the link to download the workspace at the end of this post. The script holds the needed data for the multi language look up too.
The final DDL for the GENERALLOOKUP table is

  CREATE TABLE "GENERALLOOKUP" 
   (	
    "ID" NUMBER, 
	"TYPE" VARCHAR2(20 CHAR), 
	"DATA" VARCHAR2(255 CHAR), 
	"LANGUAGE" VARCHAR2(5 CHAR), 
	"GROUPID" NUMBER
   ) ;

Next we synchronize the existing EO with the new GENERALLOOKUP table to get the new attributes into the EO

Synchronize with DB

Here are some of the dialogs which you see when synchronizing the DB to the business objects

Differences between DB and EO

Changes done

Changes in EO

Now we have to add the new columns to the VO as well

VO before Adding the new Attributes

Selecting the new Attributes

Add the new Attributes to the selected Attributes

These changes now allow to use the VO GeneralLookup to select language dependent look up data from the db. We now a new view criteria, named TypeLookupByLanguageViewCriteria, to use the language information to only select data for one language and one type from the table.

Creat new ViewCriteria

Using two Bind Variables

This new view Criteria uses two bind variables to select only data of one type and one language. How to set these variables we see later in this post.
As we don’t want to break the old application we create a new VO based again on the existing Lookuptest entity object and name it ‘LookupMultiLanguageView’

Create new VO based on LookupTest EO

Next step is to set up the LOVs accessors for the attributes WeekdayId, GenderIs, PositionId and TitleID. Here we only show how to do this for the WeekdayId attribute. The images below showing that we not only set the bindType to ‘WEEKDAY’, but the bindLanguage variable too. Here we use a groovy expression to get the current language from the current locale

Setup the LOV for the Attribute WeekdayId

Image 3: Use GenerallookupView to select WEEKDAY

Groovy Expression to get Language

The real work is done in pictures 3, 4, 5 and 6 where we use the view criteria we’ve build earlier (TypeLookupByLanguageViewCriteria) to select the type and language from the GeneralLookupView.
In Image 3 we use the GenerallookupView as view accessor for the weekdayId. We rename the accessor to WeekdayMultiLanguageLOV for better understanding what the view accessor does. As the WeekdayMultiLanguageLOV can select any type in any language, we have to use the view criteria and set its bind variables to only get the data we want. In this case we set the bindType variable to ‘WEEKDAY’ and the bindLanguage variable to the current language used in the browser. As this language can change, we can’t use a static string like we used for the bindType. The language has to be calculated. For this we use a groovy expression:

oracle.adf.share.logging.ADFLogger LOGGER = oracle.adf.share.logging.ADFLogger.createADFLogger(source.getClass()); 
loc = adf.context.locale; 
if (loc == null) {
  LOGGER.info("Language not set -> en");
  return 'en';
} else {
  lang = loc.language;
  shortLang = lang.substring(0,2);
  if (!"#de#it#fr#en#".contains(shortLang)) {
    LOGGER.info("Language not recognized -> en");
    shortLang="en";
  } else {
    LOGGER.info("Language set to -> "+lang.substring(0,2));
    shortLang = lang.substring(0,2);
  } 
}

Yes, you can do more with groovy then just simple calculations like “sal *12”!
We use groovy expression like a java function to get the locale from the AdfContext (groovy: loc = adf.context.locale;) and from the locale we get the language (groovy: lang = loc.language;). Now, there are some checks to make, like is the locale is set and if the language found is one of the languages we support. In this sample we only support ‘en’, ‘de’, ‘it’ and ‘fr’ but you can add other languages too. As the language we get from the locale can look like ‘en_US’ or ‘de_CH’ we only use the first two characters (groovy: shortLang = lang.substring(0,2);). You can even use a logger to print out information into the log.

We can now test the switching of languages in the application module tester. Before we start the tester we make sure we can change the locale in the tester. Open the menu ‘Tools’->’Preferences’ and select the node ‘Business Components’, then select the ‘Tester’ node where you can add different languages for the tester.

Supply Languages to Tester

Running the tester

Show LOV Data for WEEKDAY

Select Italian

Select a Language not in the DB

Showing French Data

Select ‘French’

At first Locale is not set

Run Tester

Please notice the log output which is visible below the tester which shows the groovy log messages.

Finally we adjust the UI by adding a new page MultiLanguageLookup and hook it up with the existing LookupTest page.

Setup New Test Page

Then we need to setup the faces-config.xml to support multiple languages

Setup New Test Page

Now, if we run the application and change the browser language, reload the page we see the language change

Start with German

Show Data in Italian

Show Data in Italian

The sample used in this blog can be downloaded from the ADF-EMG Sample repository. The sample uses JDeveloper 11.1.1.7.0 and the HR DB schema.

JDeveloper & ADF: Carefully Select the Data Control Scope of Bounded Task Flows

A customer asked me to look into an error which showed up in one of their applications. Without going into the details of real use case I like to bring this issue to your attention as it might happen to you too.

Use Case
I set up a mock use case to show the problem as this: A page (.jspx or .jsf) includes one (or more) other task flows as a region. The base page retrieves some data using a method call exposed in the data control, which should be displayed in the region for information purpose.

Implementation
If the task flows share their data controls this is not a big deal to implement this. You can download the sample using the link provided at the end of this blog. The sample uses the HR schema and was build using JDev 11.1.1.5.0 and was additional tested with JDev 11.1.1.1.6.0 and JDev 11.1.2.2.0, which show the same behavior.

On the base page I set up an inputText which I use to pass a parameter to the method (callMeTest) which retrieves some info from the application module. The callMeTest method simply returns the passed parameter together with a ‘nice’ message:

    public String callMeTest(String name) {
        _logger.info("Called with: "+name);
        return "Call me Test "+name;
    }

The return value is then displayed on the base page at the bottom under the button and should be displayed in the region, under the table, too. The sample just uses one region. When no value is present ‘****’ is used as parameter. The image below shows the application after starting it.

Start Screen

After entering a value into the inputText field below the table and hitting the ‘callMeTest’ button underneath it, you see that the return value is displayed below the button on the base page and under the table inside the region. If the button is hit with another value, both values are updated.

After entering a value and hitting the button

Nothing unusual so far. Now, for some unknown reason, after a change to another part the application, that functionality did not work any longer. The value in the base page still updates after the call to the method, but the value in the region stopped updating.

Screen after next deployment

The reason for this behavior is a simple one. A look into the task flow properties of the region, revealed that the checkmark for the ‘Share data controls with calling task flow’ was not set, meaning that the task flow get its own data control frame. The result is that the method outcome from the base page is not visible in the region.

TaskFlow properties (1)

The question was how did this happen, when the change which results in the new deployment had nothing to do with this part of the application?

Analysis
It turned out, that when you create a new bounded task flow this property value is set to ‘default’. This is shown in the ‘Overview’ of the task flow as bluish shade in the check box. No checkmark is set and a look into the source reveals that the whole section

    <data-control-scope>
      <....../>
    </data-control-scope>

is just missing from the XML file.

TaskFlow after creation

Once you click on this property in the property editor you can just switch between ‘isolated’ or ‘shared’.

TaskFlow properties (2)

The only possible way to get the bluish shade back is to delete the section in the source view of the task flow.

TaskFlow properties in ‘Source Mode’

So this checkbox has three states ‘bluish’, ‘isolated’, and ‘shared’. Well, there is a forth state which you get if you open the property editor in source mode and select ‘default’. The following table shown all states together with their meaning:

State	Meaning
Bluish	Shared
Check marked	Shared
Unchecked not bluish	Isolated
Data-control-scope empty	Isolated

While the application works according to the meaning of the flag, I find it not intuitive how JDev handles this setting. The programmer who made the change to the application clicked this property, but meant to do so on a different task flow. So he unchecked the ‘Share data controls with calling task flow’ property as he remembered that it was not set before.

Recommendation
My advise is to set the flag whenever you create a task flow so you know which share mode the task flow uses. This avoids errors like the one described in this blog.

UPDATE
In the meantime Chris Muir raised the issue with the UX specialists (Oracle intern) and Bug 14390576 has been raised for this issue.
Thank you Chris!

The sample uses the HR schema and was build with JDeveloper 11.1.1.5.0, tested with 11.1.1.6.0 and 11.1.2.2.0 . You can download the sample work space form ADF Samples: Source Code

JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic

This blog post describes how to use the Application Module Tester provided together with JDeveloper to test all your business logic without having to write or design the client UI (view controller part of a Fusion Web Application).
I consider this a ‘best practice’ to do this in each project. It allows the developer(s) of the business logic to run, debug and test all business logic before the designers have worked out the UI. Changes to interfaces can be done without an impact to the UI this way.

One other advantage of using the Application Module Tester is that it starts up really fast compared to the long init phase the full web application needs.

Before going into detail lets talk about what this blog shows and how it’s implemented. A sample work space build in JDeveloper 11.1.2.1.0 using the HR schema as DB can be downloaded using the link provided at the end of the blog.
First of all you can test the data model you designed into the application module, meaning that you can execute the VO and walk over the result set or show the result as table

Data Model of the Application Module

The application module can implement service methods which your later use in the UI to archive a business need. In the sample the application module defines just one service method ‘howManyEmpEarnMoreThen’ which returns the number of employees who are earning more (or equal) to the given amount.

Service Method 'howManyEmpEarnMoreThen'

and the implementation of this method

    public oracle.jbo.domain.Number howManyEmpEarnMoreThen(oracle.jbo.domain.Number aSalary)
    {
        _logger.info("Call with salary: "+aSalary);
        EmployeesViewImpl lEmployeesView1 = this.getEmployeesView1();
        oracle.jbo.domain.Number n = lEmployeesView1.countEmpWihtSalaly(aSalary);
        return n;
    }

Next a VO might have some ViewCriteria attatched which you also want to test

ViewCriteria to Test

and the last thing to test are service methods which are exposed in the VO instead of the application module (‘countEmpWihtSalaly’ in the sample)

Exposed Methods in a View Object

and the implementation of the method

    public Number countEmpWihtSalaly(Number aSalary)
    {
        RowSet lCreateRowSet = this.createRowSet("counterView");
        ViewCriteriaManager lCriteriaManager = this.getViewCriteriaManager();
        String[] lAvailableViewCriteriaNames = lCriteriaManager.getAvailableViewCriteriaNames();
        for (String vcName :lAvailableViewCriteriaNames)
        {
            this.removeApplyViewCriteriaName(vcName) ;
        }
        lCreateRowSet.ensureVariableManager().setVariableValue("bindSal", aSalary);
        ViewCriteria lCriteria = this.getViewCriteria("EmpSalaryVC");
        this.applyViewCriteria(lCriteria);
        lCreateRowSet.executeQuery();
        long count = lCreateRowSet.getEstimatedRowCount();
        _logger.info("Count Emp wiht salay > " + aSalary + " = " + count);      
        lCreateRowSet.closeRowSet();    
        return new Number(count);
    }

Now let start up the the Application Module Tester and debug the business logic. To start the tester in debug mode, right click the application module facade and select ‘Debug’

Start the Application Module Tester in 'Debug' Mode

which shows the tester like

Running Tester

A double click on a ViewObject (e.g. EmployeesView1) opens one record in form mode. You can navigate the result set using the icons in the header

Navigate Result Set

One of the icons (the field glass) allows you to specify one or more of the available view criteria and hitting the ‘Find’ button after selecting a view criteria opens a dialog to enter bind variables

Specify View Criteria

Specify Bind Variables

The result of this is again shown as a result set with navigation. If you like to see the result a table you can right click on the VO and select ‘Show Table’.

Show Result as Table

So how do we execute the ‘countEmpWihtSalaly’ method which is exposed in the client interface of the VO?
If you look at the image above you’ll see the option ‘Operations’ which will open a new tab showing all available operations which are exposed to the client interface of the VO.

Available Operations

After selecting the method you can hit execute and the method is executed

Result of Operation

To access the public available methods from the application module you right click on the application module and select ‘Show’. After that you see a tab like the one for the VO operation.

Application Module Operations

Result of Operation

This might not look as much to you, but the advantage of the Application Module Tester is that you can debug the code in the application module methods or view object operation easily. You can set needed variables when calling the methods and use the outcome of the operations in calls to other operations. This way you can easily test the whole business logic without the need to have an UI present.

For more information you can check the Oracle® Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g Release 2 (11.1.2.1.0)

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on the HR db schema, from here: BlogBC4JTester.zip.doc
Please rename the file to ‘.zip’ after downloading it!

JDeveloper: Case Insensitive Search and Performance

A couple of days ago Frank Nimphius published a new ADF Insider Essential video about Search Forms Customization where he also showed how to implement case insensitive searches. While the tip how to do this is fine, he did not mention the bottleneck involved in doing so. Yesterday, while writing up this blog I came across this blog ‘ADF ViewCriteria performance impact’ by Raman Nanda who summarized the same issue. The last statement in his blog is the starting of this blog:

Note:Also don’t choose to ignore null values for predicates that are required and create proper indexes on the table structure depending upon how you filter results. For ex: If predicate is upper(ename)=upper(:bvar) then create a index on upper(ename) .

Lets start with a look on a view criteria definition in JDeveloper 11.1.2.1.0. the image below shows the definition of a simple view criteria to search for employees who’s names start with a given bind variable. When you first add items to the view criteria both check boxes ‘Ignore Case’ and ‘Ignore NULL Values’ are checked.

ViewCriteria Definition: Ignore Case and Ignore Null

A close look at the ‘View Object Where Clause’ part reveals that the ‘Ignore Case’ part of the query is not visible in JDev 11.1.2.1.0. This is a bug which I’m going to file in the near future. Before going into detail with the query lets uncheck the ‘Ignore NULL Values’ to see the final criteria:

ViewCriteria only 'Ignore Case'

Running the application module in the tester reveals the final query as (copied from the log window)

[104] SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,        
             Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         
             Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID,         
             Employees.ACTION_COMMENT 
             FROM EMPLOYEES Employees 
             WHERE ( ( ( (UPPER(Employees.LAST_NAME) LIKE UPPER( :bindLN || '%') ) ) ) )
[105] Bind params for ViewObject: [de.hahn.blog.vcinsesitivesearch.model.dataaccess.EmployeesView]VCISAppModule.EmployeesView1
[106] Binding param "bindLN": K

Here you see that the ‘Ignore Case’ results in calling SQL UPPER'(…) on the bind parameter as well as on the row attribute. When you copy this query and run it in a SQL worksheet it returns the right results. However, in this bog we are more interested in the execution plan of the query. This is shown in the below image:

Execution Plan Without Index

The interesting thing is that the result is reached by a full table scan, which you see as the option is ‘FULL’ for table access. This is not a problem if the table contains only a small number of rows, but if you work on large tables with 10000+ rows it’ll take ages (OK, it’s notable longer then you would expect) to execute.
If you only have defined the normal index on the LAST_NAME column

create index normal_ln_idx on employees (last_name);

the plan doesn’t change at all. This is exactly what Raman Nanda meant in his blog. You need to create a function based index on the LAST_NAME column. Here is the SQL to do so:

create index upper_ln_ix on employees (UPPER(last_name));

Running the query again after creation of the new index results in

Execution Plan with UPPER Index

As yo ucan see the table now is accessed via the ‘upper_ln_ix’ index we created. This speeds things up in large tables.

To summarize this blog: you should, as part of your testing, check the execution plans of the queries executed by your application. This can result in a huge improvement of the performance. You should ask your DBA to help you with this task. There are tools readily available to the DBA to help getting information about the queries executed by your application.

JDeveloper: Preventing return of large row sets on page load of VO using bind variable

Back in 2009 Andrejus Baranovskis blogged about how to prevent the execution of a (default) query when ADF loads a page here. This is sometimes necessary if the query defined for a page consumes a lot of time or return a lot of rows.
Lately some users reporting that the solution provided in the blog mentioned does not work. I have to confess, that I did not try out the method, so I can’t really comment on that.
In this blog I show a different way to archive this. The idea I implement is to add a part to the where clause of the VO which, when executed, return no rows. So I don’t prevent the execution but ensure that no row is returned.
To archive this I add the following where clause:

1 = 0

Now, if I add this as is, the query never will return any row. Instead I use a bind variable

1 = :bindDummy

This allows to use the bind variable to enable the query or in fact disable it. The full query defined in the VO looks like

SELECT Employees.ACTION_COMMENT, 
       Employees.COMMISSION_PCT, 
       Employees.DEPARTMENT_ID, 
       Employees.EMAIL, 
       Employees.EMPLOYEE_ID, 
       Employees.FIRST_NAME, 
       Employees.HIRE_DATE, 
       Employees.JOB_ID, 
       Employees.LAST_NAME, 
       Employees.MANAGER_ID, 
       Employees.PHONE_NUMBER, 
       Employees.SALARY
FROM HR.EMPLOYEES Employees
WHERE 1=:bindDummy

The next challenge is to control the bind variable from the UI. Here ADF Task Flows comes to help. A bounded task flow has a start activity which is executed whenever the bounded task flow is started. I use this start activity to set the bind variable to ‘0’ to prevent the return of any row. Then, on the page I have a button which sets the bind variable to ‘1’ and execute the query. This time I get the desired result.

adfc-config.xml

I use the EWPTest ViewId to start the sample. On this page I add a button ‘Show Emplyoees’ which navigates to the bounded task flow ‘show-emp-bft’. In this task flow the start activity is ‘ExecuteWithParams’ which sets the bind variable to ‘0’.

Bounded Task Flow 'show-emp-bft'

Here is the page def file for the start activity. As you see this method call activity call ‘ExecuteWithParams’ and sets the bind variable to ‘0’ thus preventing the return of any row from the query.

Start Activity in Task Flow 'show-emp-btf'

As a result the next page only shows an empty table.

Show Employees after entering the page

In the toolbar I placed a button ‘Execute with bindDummy set to 1′ which calls the executeWithParams’ method, this time with the bind variable set to ‘1’

ExecuteWithParams bind variable set to 1

As the result I get the desired result

Result after ExecuteWithParams with bind variable set to 1

The ‘Back’ button in the toolbar return from the task flow to the first page. If I hit the ‘Show employees’ again the bind variable is set to ‘0’ again and the query again does not return any row.

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on the HR db schema, from here: BlogExecWithParams_V2.zip.doc
Please rename the file to ‘.zip’ after downloading it!

Dump VO query and it’s parameter with their values

Based on a request on JDeveloper and ADF forum I wrote a small method to dump the query of a VO together with it’s bind variables (autom. inserted by the framework and user defined) and their values.
The method below gets the query in it’s actual state, checks the variables and dumps their names and values. I’m using a simple ‘System.out.println’ to dump the information for simplicity. If you like to use the method in a more general way (e.g. in a base class) I would recommend to use a ADFLogger. See Duncan Mills bloghere

    public void dumpQueryAndParameters()
    {
        // get the query in it's current state
        String lQuery = getQuery();
        //get Valriables
        VariableValueManager lEnsureVariableManager = ensureVariableManager();
        Variable[] lVariables = lEnsureVariableManager.getVariables();
        int lCount = lEnsureVariableManager.getVariableCount();
        // Dump query
        System.out.println("---query--- " + lQuery);
        // if variables found dump them
        if (lCount > 0)
        {
            System.out.println("---Variables:");
            for (int ii = 0; ii < lCount; ii++)
            {
                Object lObject = lEnsureVariableManager.getVariableValue(lVariables[ii]);
                System.out.println("  --- Name: " + lVariables[ii].getName() + " Value: " +
                                   (lObject != null ?  lObject.toString() : "null"));
            }
        }
    }

You can overwrite the executeQuery() method of the ViewObjectImpl class and call the method above like

    @Override
    public void executeQuery()
    {
        dumpQueryAndParameters();
        super.executeQuery();
    }

Executing a Query you should see output like

---query--- SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE ( ( (Employees.LAST_NAME LIKE ( :vc_temp_1 || '%') ) ) )
---Variables:
  --- Name: vc_temp_1 Value: gr%
  --- Name: bindHireDate Value: null

The nice part of this method is that it dumps user defined bind variables as well as automatically added variables by the framework. In the output above vc_temp_1 is a variable of a filter entered in an af:table component.