JDev 12c: Change Label depending on Data in Field

A question on OTN forum JDev & ADF caught my attention. A user ask how to change the label of a field in an af:query depending on data entered in another field of the af:query.

This is an interesting problem as it can be used in other use cases, e.g. in forms, too.

Use case

Before going into detail on how this is implemented, let’s look at the use case in detail. Starting with a normal af:query component showing a search form for locations

We want to change the label of the ‘State’ field depending on the selected Value of the ‘CountryId’ field. The page is simply created by dragging the named criteria ‘All Queryable Attributes’ onto the page as ‘Query with Table’.

To make the UI more interesting we use an af:selectOneChoice to select the country. Depending on the selected country we like to show different labels for the ‘State’ field. If we select the ‘United States of America’ as country, the label should show ‘US States’, if we select ‘Germany’ we want to see ‘Bundesland’ and for Switzerland we want to show ‘Kanton’. For the remaining countries we show ‘State’.

Here we see that the label changed to ‘Kanton’ for the country Switzerland. Selecting the USA will change the label to ‘US State’


To implement this we only need to add some groovy script to the model project. To be precise we add groovy to the attribute label of the view which is used in the UI for the af:query.

Adding the groovy to the view will guarantee that the UI always shows the effect. In the sample (download instructions below) you’ll find a second page where the view is dropped as a af:form. Running this page you’ll see the same effect.

OK, let’s have a look at the groovy script

if (CountryId == 'US') {
  return 'US State';
} else if (CountryId == 'DE') {
  return 'Bundesland';
} else if (CountryId == 'CH') {
  return 'Kanton';
} else if (CountryId != null) {
  return CountryId + ' State';
} else {
  return 'State';

The script checks for specific countries and depending on which country is currently selected it return a special label. For country ‘DE’ it return ‘Bundesland’, for country ‘CH’ it returns ‘Kanton’. For other countries we return the country code we get from the country attribute and add ‘State’ to it. A special case we have to handle is if country is null or empty. In this case we return ‘State’.

Below we see that we add the script to the attributes ‘UI Hint’ tab as ‘Label’. This is only possible in 12c, so if you are using an earlier version, you have to use java code to get the same result.

This is all we have to do. The sample which is build using JDev can be downloaded from BlogChangeQueryLabel. The sample uses the HR DB schema.

The power of calculated fields in ADFbc

Lately I saw a couple of posts on the OTN JDev & ADF forum where users tried to add redundant data into their data model and store it to the DB table. One common use case here is to have the result of a calculation as an attribute of a table.

In general you should be very careful when doing this. This is error prone and will you get into trouble almost every time. If you do add an attribute for such a calculation to a table in the DB, you have to think of the integrity of the data. Let’s look into the use case and the integrity problem.

Use Case

We have a table in the DB which holds start and end for multiple data types like integer, data and timestamp:


We use the different start and end attributes to calculate the difference between start and end.

We do have the option to add attributes to the table and calculate the difference using a trigger in the DB each time the data is inserted or updated. Problem here is that the user will see the result only after the insert or update is done. For web pages this isn’t a good design.

Another option is to add the fields but do the calculation in the business component layer in ADFbc and store them in the DB together with all other changes done to the data. The your see the calculation, but other applications won’t see them until you store the record.

Problem with storing redundant data in a DB table

Both options have one flaw. When you store the result of a calculation in the DB, what happens if someone, person or program, changes one of the attributes used in the calculation?

Assume STARTINT is set to 5, ENDINT is set to 10. The result of the calculation is 5. This result we store in an attribute in the DB table. Now a bad programmer who does not know about the calculation, changes the ENDINT to 15 and commits the change.

When the other program looks at the data again the data is inconsistent. Which of the values is correct? The result? The STARTINT value? The ENDINT value? Or is the calculation simply wrong?

In this simple use case it’s fairly easy to find the problem. In more complex use cases where other workflows depend on the numbers it’s not as easy.

This leads to the solution shown in this post: don’t store results of calculations in the DB if possible. Do the calculation when they are  needed.

There are cases where storing the result would be the better way to archive the whole use case, but this has to be decided on the use case and weighted against the complications. Most simple use cases don’t need to store the results and should not.

The remainder of this post we see how to implement such calculated fields using ADFbc.

Implementing calculated fields in ADFbc using Groovy

We start with creating a new Fusion Web Application and building the ‘ADF Business Components from a Table’. The sql script to create the table is

 Insert into CALCULATION (ID,STARTINT,ENDINT,STARTTIME,ENDTIME,STARTTIMESTAMP,ENDTIMESTAMP) values ('1','1',null,to_timestamp('24-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('26-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),null,null);
 Insert into CALCULATION (ID,STARTINT,ENDINT,STARTTIME,ENDTIME,STARTTIMESTAMP,ENDTIMESTAMP) values ('2','4','6',to_timestamp('31-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('05-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),null,null);

We use the HR DB schema to add the table, but it can be added to any schema you want. The CALCULATION table consists of some start and end values of different types to later show how to work with them. To work with the table we add two records resulting in the following data


I don’t show the steps to create the basic application from the wizards as the application is available via the link GitHub base application.

Once you downloaded and unzipped the workspace you should see the base application as it will be created by following the wizard.


The first step is to create a transient field in the Calculation EO to hold the result of the calculation of the difference of STARTINT and ENDINT. The difference here  is, that we store the result in the EO as transient attribute which is not stored into the DB.

The real work is shown in the third image above ‘edit expression…’. Here we enter a Groovy expression to calculate the difference between STARTINT and ENDINT as

if (Endint == null) 
  {return 0} 
  {return Endint-Startint}

The Groovy expression uses the attribute names from the EO not the ones from the DB table. First we check if the Endint is given, if not we return 0. If there is an Endint we return the (Endint-Startint).

We then add notifications to the calculated attribute whenever the attributes Startint or Endint change to recalculate the Durationint attribute (lower half of the dialog). Next we set the AutoSubmit  property of the Startint and Endint attributes to true to make sure we get the new values when we calculate the result.

Finally we add the new calculated attribute to the VO. We can now test the application module using the application module tester:

We now add a index page to the View Controller project to add an UI to the application. We can just drag the CalculationView1 and drop is as an ADFForm with navigation and submit onto the page.

In the resulting form we set the Startint and Endint fields to autosubmit=’true’ to make sure the new values are submitted. As the Durationint field isn’t updateble we set it to read only.

Running the application will show you

The application in this state can be downloaded from GitHub (feature/calculated_int_field).

To show that this can be done with other data types we can use the other attributes of the table. As the way to do this is the same I spare to give detailed instructions. You can download the final application from GitHub (final).

All samples yre using the HR DB schema and table called CALCULATION. The needed SQL code to create the table and to insert data to the table is posted in here.

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

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

    "ID" NUMBER, 
   ) ;

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

Synchronize with DB

Synchronize with DB

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

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

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.

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’

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

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");
  } 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

Supply Languages to Tester

Running the 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

Setup New Test Page

Then we need to setup the faces-config.xml to support multiple languages
Setup New Test Page

Setup New Test Page

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

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

Using Groovy Expression to set a Primary Key with a Sequence Number

Just set up a workspace for JDeveloper to show how to use a Groovy expression to set the primary key of an entity object. Chris Muir blogged about this back in 2009 here ADF BC: Using Groovy to fetch sequence numbers for EO/VO attribute default values. There exists a white paper Introduction to Groovy Support in JDeveloper and Oracle ADF 11g which covers Groovy support in JDeveloper.
So, I don’t cover the basic here but only show how to use this in a sample application. The application uses the HR schema and allows you to insert a new employee. The workspace which you can download (see at the end of this blog) is build using JDeveloper
I use the EMPLOYEES_SEQ defined in the HR schema to set the PK of the new employee, EMPLOYEE_ID to the next available sequence number.

Employee Sequence

Employee Sequence

Now we can open the Employee EO from the model layer. Double click on the Employees EO to open the properties inspector for the EO and select the ‘Attribute’ section.
Employees Attributes

Employees Attributes

Now select the EmployeeId in the attributes to get to the attributes properties.
EmployeeId Properties

EmployeeId Properties

Here we can add a default value as literal, expression or as SQL. We select the ‘Expression’ radio button and click on hte pencel on the right side of the input field.
Edit Expression Editor

Edit Expression Editor

Here we enter the Groovy expression to get the next sequence number

(new oracle.jbo.server.SequenceImpl("EMPLOYEES_SEQ",adf.object.getDBTransaction())).getSequenceNumber()

After submitting the dialog with OK you should set the ‘Refresh Expression Value’ to ‘true’ and the ‘Updatable’ LOV to ‘While New’.

Finished Dialog

Finished Dialog

A word of caution here: JDev saves the setting for the refresh condition in the xml file, but the next time you open the dialog again the ‘Refresh Expression Value’ value is gone! I’ll file a bug for this later.
This wraps up the the model layer of the app. You can test your work with the Application Module Tester. When you create a new record you’ll see that the EmployeeId is set to the next sequence number.
Oracle ADF Model Tester

Oracle ADF Model Tester

The ViewController project is pretty simple. It consists of an ADF form with with navigation buttons and a button to call a bounded task flow to create the new employee.
Task Flows

Task Flows

The bounded task flow first calls the CreateInsert operation to create a new record which is then displayed in the form. As you notice, the EmployeeId is an af:outputText element, so that you can’t change it.
Running Application

Running Application

The workspace for JDeveloper can be downloaded from here: Workspace BlogPKwithGrooy.zip
After downloading the file, remove the suffix ‘.doc’ and rename it to ‘ BlogPKwithGrooy.zip’, as the file is a Zip file.