ADF Model: Generating and using 'in' clause in VO sql statement

We know how to create view criteria declartively, execute it programatically and use the query results as needed. But, creating a view criteria that uses 'in' clause is not possible declaratively. So, here we'll see how to form a query criteria that uses 'in' clause and also meets the performance standards.

Here, we'll see how to to form a query statement to use list of values using SQL 'in' clause.

Requirement: For example, we have a list of employee nos and we need to form an SQL like 'select * from emp where empno in (empno1, empno2, empno3, and so on)'. Here, we should be able to form a query that can accept 'n' (where 'n' can be dynamic) no. of employee nos and use bind variables instead of hard coded the query.

Solution: We don't have declarative way of forming query using 'in' clause. So, we have to do it programatically.

For e.g., if the Empno list has 4 employee ids, we have to form the query like

select * from emp where empno in (:empno1,:empno2,:empno3,:empno4)

OR

select * from emp where empno in (:1,:2,:3,:4)

In the above SQL stmts, the first one uses the named bind parameters while the second one uses positional parameters.

To achieve the above requirement, generate the 'in' clause programatically using the following methods.

Forming 'in' clause with named bind parameters:

    private String getInClauseWithParamNames(List ids) {
        //logic to form the in clause with multiple bind variables
        StringBuffer inClause = new StringBuffer();
        for (int i = 1; i < ids.size() + 1; i++) {
            inClause.append(":empno" + (i));
            if (i < ids.size()) {
                inClause.append(",");
            }
        }
        return inClause.toString();
    }

Forming 'in' clause with positional bind parameters:

    private String getInClause(List ids) {
        //logic to form the in clause with multiple bind variables
        StringBuffer inClause = new StringBuffer();
        for (int i = 1; i < ids.size() + 1; i++) {
            inClause.append(":" + (i));
            if (i < ids.size()) {
                inClause.append(",");
            }
        }
        return inClause.toString();
    }

Use the generated 'in' clause with dynamic bind variables in the SQL stment and set the where clause programatically with vo.setWhereClause() method. Now, pass values for the bind variables progamatically and execute the query. Sample code is given below:

Using 'in' clause with named bind parameters:

    public Row[] getEmployees1(List empIds) {
        ViewObjectImpl empVO = this.getEmpVO();
        String inClause = getInClauseWithParamNames(empIds);
        //setting the where cluase to use the generated in clause
        empVO.setWhereClause("EmpEO.EMPNO in (" + inClause + ")");
        //clearing all existing where clause params if any
        empVO.setWhereClauseParams(null);
        //setting values for all bind variables one by one in the in clause
        for (int i = 0; i < empIds.size(); i++) {
            //defining the named bind variables programatically
            empVO.defineNamedWhereClauseParam("empno" + (i + 1), null, null);
            //setting the value for each named bind variable
            empVO.setNamedWhereClauseParam("empno" + (i + 1), empIds.get(i));
        }
        empVO.setRangeSize(-1);
        //executing the query
        empVO.executeQuery();
        //returning the rows from query result
        return empVO.getAllRowsInRange();
    }

Using 'in' clause with positional bind parameters:

    public Row[] getEmployees(List empIds) {
        ViewObjectImpl empVO = this.getEmpVO();
        String inClause = getInClause(empIds);
        //setting the where cluase to use the generated in clause
        empVO.setWhereClause("EmpEO.EMPNO in (" + inClause + ")");
        //clearing all existing where clause params if any
        empVO.setWhereClauseParams(null);
        //setting values for all bind variables one by one in the in clause
        for (int i = 0; i < empIds.size(); i++) {
            //setting the value for each positional bind variable
            empVO.setWhereClauseParam(i, empIds.get(i));
        }
        empVO.setRangeSize(-1);
        //executing the query
        empVO.executeQuery();
        //returning the resultant rows
        return empVO.getAllRowsInRange();
    }

Sample method that forms list of empnos, calls the above methods, gets the required results and prints the results:

    public void sampleMethod() {
        //Forming a list of employee ids
        List<Long> empIds = new ArrayList<Long>();
        empIds.add(new Long(7499));
        empIds.add(new Long(7521));
        empIds.add(new Long(7566));
        empIds.add(new Long(7654));
        empIds.add(new Long(7698));
        empIds.add(new Long(7788));
        
        //Get employee rows from list of empIds
        //1. Using positional parameters
        //Row[] empRows = getEmployees(empIds);
        //2. Using named bind parameters
        Row[] empRows = getEmployees1(empIds);

        //iterating through the employee rows and printing the emp name
        for (int i = 0; i < empRows.length; i++) {
            Row empRow = empRows[i];
            System.out.println("Emp Name " + (i + 1) + ": " +
                               empRow.getAttribute("Ename"));
        }
    }

The above code is self-explanatory. You can download the sample application from here. Once downloaded, run/debug the DemoAM and execute the sampleMethod. You'll get the following result:

If you look at the log window, you can see the SQL query statements generated as below at runtime.

Generated SQL stmt with named bind variables:


Generated SQL stmt with positional bind variables:

This query statement uses bind variables instead of hard coded the statement and results in a prepared statement at runtime. Hence, the stmt will be compiled only once and the same will be reused for multiple calls. So, this is the most performant way of generating and executing the SQL programatically.

ADF UI - Implementing Date Effective Search with Example

After learning how to create date-effective objects (i.e., Creating Date-Effective EO, Creating Date-Effective Associations and VOs), now we'll see how to implement date-effective search.

Sample Use Case:
Please go through my previous post to see the sample example use cases of performing date-effective operations on 'Job' object. So, here the requirement is to search for a job effective as of the given date. Sample application illustrating this example can be downloaded from here. Before running the example, you need to create the required tables in DB. The sql script for these table can be downloaded from here.

Implementation Steps:
1. Create the date-effective EO (JobEO) and date-effective VO (JobVO). Marking the JobVO will create a new transient attribute called SysEffectiveDate in the VO attributes.

2. Create a view criteria 'JobSearch' and add the required attributes as query criteria items on which you want to perform the search. As a best practice, use bind variables for all the view criteria items(attributes).

3. In addition to those attributes, add SysEffectiveDate in the query attributes and bind it to the bind variable SysEffectiveDateBindVar of type Date(if this bind variable is not already exist, create a new bind variable with same name and associate it to SysEffectiveDate). Here, the bind variable name that binds SysEffectiveDate should be SysEffectiveDateBindVar as this is the bind variable name generated at runtime by ADF for SysEffectiveDate. Otherwise, it'll throw run time exception.

4. Now, you're done with model part of defining view criteria with SysEffectiveDate. Now, implement search and search results in a jsff with this view criteria.

5. To test the functionality, create multiple job records with different date-effective updates and try to search for a required job records specifying the Effective Date in the search criteria. You'll get the job records which match the query criteria as of the given effective date. Here are the sample screen shots.

6. If the effective date (SysEffectiveDate) is not provided, it'll return the rows effective as of today (i.e.,the system date on which search is made). Screen shot below.

Enjoy!!!

ADF Model: Creating Date Effective Association and Date Effective VO

To learn the basics of date-effectivity in ADF, please go through my post Learning basics of Date Effectivity in ADF. To learn how to create date-effective EO, please go through my post Creating Date Effective EO.

Creating Date Effective Association:
To have the basic idea of association between EOs, please go through my post ADF Model: Creating Entity Association. But, by default the association created is not date-effective. But, if you're creating association between two EOs in which at least on of them is effective-dated, then you should mark the association as 'Effective Dated'. To make the association date-effective,

1. Open Association -> Goto 'Relationship' tab -> Behavior -> Check 'Effective Dated Association' checkbox.

Marking the association 'Effective Dated Association' will take effective date into consideration while searching, inserting and updating the records.

Creating Date Effective VO:
Creating date effective VO is same as creating normal VO. In addition, we need to

1. Mark the VO as date-effective by setting EffectiveDated='true' for the VO. You can find this property in 'General' tab property inspector.

Specifying the above property for the VO will generate a new transient attribute called SysEffectiveDate in the VO.

2.Optional: Change the data type of SysEffectiveDate attribute to 'java.sql.Date' from 'oracle.jbo.domain.Number'. We'll often find it easier with native Java sql data types instead of using Oracle's jbo datatypes. It is recommended to use java native sql type(java.sql.Date) for all date type attributes in the EO.

Marking the VO effective dated, will support date-effective updates for a single record.

ADF Model: Creating Date Effective EO

Date-effectivity is an excellent feature available in Jdeveloper 11g. To learn the basics of date-effectivity in ADF, please go through my post Learning basic of Date Effectivity in ADF. With Jdev 11g, we can create date-effective objects and do date-effective operations using simple API calls. Before going into all those details, the first requirement would be creating Date-Effective EO.

The first requirement to create a DE-EO based on a table, the table should have two date columns to represent Effective Start Date(ESD) and Effecitve End Date(EED) of the record and these columns should be marked as primary keys along with id column. In other words, we need to define a composite key based on the id column and ESD and EED columns.

Creating Date Effective EO is same as creating normal EO. In addition, we need to do the following steps to make it date-effective.
1. Mark the EO as Date Effective by specifying the attribute Effective Date Type = 'EffectiveDated'. You can find this property in 'General' tab property inspector.

This will generate a new transient attributte called 'SysEffectiveDate' in the EO. Please find the screenshot below:

2. We need to specify which columns represent effective-date columns by checking the Check 'Effective Date' check box and selecting 'Start' and 'End' radio buttons for the effective-date columns which represent Effective Start Date and Effective End Date. Find the screen shots below:

3. Optional: Change the data type of SysEffectiveDate attribute to 'java.sql.Date' from 'oracle.jbo.domain.Number'. We'll often find it easier with native Java sql data types instead of using Oracle's jbo datatypes. It is recommended to use java native sql type(java.sql.Date) for all date type attributes in the EO.

That's it. Now, your EO is date-effective and supports date-effective operations on it's rows.

ADF Model: Getting attribute values from parent VO to child VO and vice versa using view link accessors

In this post, let us see how to access parent VO attributes from child VO and child VO attributes from parent VO using view link. To have the basic idea about view links and how to create them, you can go through my blog post 'ADF Model: Creating View Link'.

Example Use Case:
For example we have two VOs DeptVO and EmpVO and both are linked via foreign key 'DeptId' using the view link EmpVOToDeptVO. Here, this relationship depicts the parent-child relationship using the foreign key DeptId. In other words, for a given current EmpVO(child) row, I need to know the DeptName from DeptVO(parent). Similarly, for a given current DeptVO(parent) row, I need to know all the empVO(child) rows. Sample application demonstrating this example can be downloaded from here.

Implementation Steps:
1. Create EmpVO and DeptVO and generate RowImpl classes for both of these two VOs.

2. Now, create a new view link say DeptVOToEmpVO between these two VOs via foreign key DeptId.

3. In the view link definition, select options to generate accessors in both source and destination VOs. i.e., in DeptVO and EmpVO.

4. Checking the above options will generate accessor methods in EmpVORowImpl and DeptVORowImpl. The accessor's return type in each VO is based on the type of relationship between the VOs. In other words, as the relationship between Dept and Emp is 1-to-many, the accessor in DeptVORowImpl will return multiple Emp rows(i.e, the return type of the accessor will be RowIterator). And, the accessor in EmpVORowImpl will return a single row (as an employee can be in only one dept).

If you observe the source of EmpVO and DeptVO, you can also see that a tag is added in each of these VOs for the viewLinkAccessor.

5. Now, you can use these accessors to get reference EmpVO from  DeptVO and vice versa. You can also get attribute values from the same. Sample codes below:

Sample method in EmpVORowImpl to get the dept name.

  public String getDeptNameViaViewLink() {
      //Getting reference to deptVO row using the view link accessor getDeptVO1()
      Row deptRow = this.getDeptVO1();
      //Getting the attribute 'Dname' value from the deptRow.
      return (String)deptRow.getAttribute("Dname");
  }

Sample method in DeptVORowImpl to get the list of employees in the dept.

  public List getEmpNamesViaViewLink() {
      //Getting reference to empVO row using the view link accessor getEmpVO()
      RowIterator empRowIterator = this.getEmpVO();
      //Creating an empty List to store all emp names
      List empNames = new ArrayList();
      //iterating through all employee rows
      while(empRowIterator.hasNext()){
          //getting emp row one by one from the iterator
          Row empRow = empRowIterator.next();
          //adding emp name to the empNames list
          empNames.add(empRow.getAttribute("Ename"));
      }
      //returning all empNames corresponding to the current dept
      return empNames;
  }

How to call/use these view link accessor methods in AMImpl methods?
This should be now pretty straightforward. Here is the sample AMImpl method which prints emp names in each dept. The code is self-explanatory.

    public void sampleMethod() {
        //getting reference to deptVO
        ViewObjectImpl deptVO = this.getDeptVO();
        //setting range size to -1 to get all dept rows
        deptVO.setRangeSize(-1);
        //getting all dept rows
        Row[] deptRows = deptVO.getAllRowsInRange();
        //iterating through all dept rows
        for (int i = 0; i < deptRows.length; i++) {
            //getting reference to each dept row. Note that we're type casting the VO reference type to DeptVORowImpl.
            DeptVORowImpl deptRow = (DeptVORowImpl)deptRows[i];
            //printing dept name
            System.out.println("Employees in dept: " +
                               deptRow.getAttribute("Dname"));
            //For each dept row, getting reference to empVO which contains all employees corresponding to current dept
            RowIterator empRows = deptRow.getEmpVO();
            //iterating each emp row
            while (empRows.hasNext()) {
                Row empRow = empRows.next();
                //printing emp name from each row
                System.out.println(empRow.getAttribute("Ename"));
            }
        }
    }

Here is the sample output in console on running the above AMImpl method.

That's it. Now, you got the idea how to use view link accessors to get the values of child attributes from parent and vice versa. Enjoy!

ADF Model: Executing view accessor programatically

In this post, I'll show how to execute a VO(or it's view criteria) added as a view accessor in another VO.

Sample Use case:
For example we have two VOs DeptVO(based on only DeptEO) and EmpDeptVO(based on empEO and DeptEO) and the DeptVO is added a view accessor in EmpDeptVO and we need to programmatically execute this view accessor to get the Deptno for the passed Dname (assuming it DeptVO has a view criteria that takes Dname as a parameter or bind variable) and set the DetpNo for the newly created row in EmpDeptVO. You can download the sample application from here.

Implementation Steps:
1. Create DeptVO based on DeptEO and create a view criteria "findByDeptName" that queries based on the bind variable 'Bind_Dname'.

2. Create EmpDeptVO and add the DeptVO as view accessor (DeptVA) and select the view criteria "findByDeptName" in the VA definition.

3. Generate RowImpl class for EmpDeptVO. The generated class name will be EmpDeptVORowImpl.

4. Now, write a method say "getDeptIdFromViewAccessor" in EmpDeptVORowImpl that takes 'Dname' as parameter that executes the view accessor DeptVA and returns the DeptId for the passed 'Dname'. Code below:

    public Object getDeptIdFromViewAccessor(String deptName) {
        //here getDeptVA is the getter for the view accessor 'DeptVA' in EmpDeptVO
        RowSet rowSet = this.getDeptVA();
        //setting the range size to -1 to get all the rows
        rowSet.setRangeSize(-1);
        //passing the value for bind parameter for the view accessor
        rowSet.setNamedWhereClauseParam("Bind_Dname", deptName);
        //executing the view accessor
        rowSet.executeQuery();
        //storing the first row in the row set in deptRow (there can be multiple rows in the row set based on the criteria)
        Row deptRow = rowSet.first();
        //if the dpetRow is not null, returning the Deptno
        return (deptRow != null) ? deptRow.getAttribute("Deptno") : null;
    }

5. Call the above EmpDeptVORowImpl method in AmImpl's method say "testExecuteViewAccessor" passing the Dname for which the DeptId is required. Now, set this deptId to the newly created EmpDeptVO row in AMImpl method. Code below:

    public void testExecuteViewAccessor(){
      ViewObjectImpl empDeptVO = this.getEmpDeptVO();
      Row row = empDeptVO.createRow();
      empDeptVO.insertRow(row);
      EmpDeptVORowImpl empDeptRow = (EmpDeptVORowImpl)empDeptVO.getCurrentRow(); 
      //calling the EmpDeptVORowImpl method to get the deptId for the deptName 'accounting'
      Object deptId = empDeptRow.getDeptIdFromViewAccessor("accounting");
      System.out.println("DeptId from view accessor: "+deptId);
      //setting the deptId to the current row's 'Deptno' attribute.
      empDeptRow.setDeptno((Number)deptId);
    }

Here is the sample output on running this method using AM tester.

That's it. Now you know how to execute the view accessor(VA) programatically, how to pass the parameters for the view criteria selected in VA definition, how to get and use the results in AMImpl methods.

PS: DeptId and Deptno are used interchangeably in this post.

ADF Model - Beginner: Exposing AMImpl methods as client interface

If you're using ADF BC, you would write your application logic in AMImpl methods in model project. But, if you want to use/call these methods in UI project, you should generate client interface for these methods. Here, let me show how to do that.

For example let's take a DemoAMImpl has two methods 'getAllDepts' and 'getEmployeesInDept', to expose them in client's interface, follow the below steps:

 1. Open application module DemoAM -> Click on 'Java' tab -> Click on 'Edit'(pencil) icon beside the 'Client Interface' section.

2. In the 'Edit Client Interface' section, shuffle the methods you want to expose th right side and click 'OK'.

3. Now, you can see the added methods under the 'Client Interface' Section.

4. Now, if you expand the 'Data Controls' palette, you can see the newly added methods there so that you can drag and drop them into your UI project task flows or you can add them as methodAction bindings in pagedef.

Sample method binding in task flow:

ADF Model: Creating Duplicate Row

One of the common requirements in many applications is to duplicate a record of an existing record. Here, I'm going to give you an efficient approach to duplicate a record. Here is the sample application that demonstrates how to create a duplicate row.

Sample UseCase:
Let's take a simple example to create a duplicate record of an existing employee record.  First, the user searches for the employee and selects the employee record from the search results and clicks on button 'Duplicate Record' to duplicate the selected record. And, then the search results table will be refreshed with the newly created (in other words duplicated) row (could be identified by '_DUP' suffix). You can select the record and click on 'View Record' to see the data of the record.


Implementation:
Below is the simple method that duplicates the current emp row.

public void duplicateEmpRecord() {
        ViewObjectImpl empVO = this.getEmpDeptVO();
        Row empCurrentRow = empVO.getCurrentRow();
        String[] empAttrs = empCurrentRow.getAttributeNames();
        //Skip copying the primay key attributes or any attributes which you want to skip
        String[] skipAttrs = new String[] { "Empno", "Ename" };
        List skipAttrList = Arrays.asList(skipAttrs);
        //creating a new duplicate row
        Row dupRow = empVO.createRow();
        //copying all attributes one by one
        for (int i = 0; i < empAttrs.length; i++) {
            String empAttrName = empAttrs[i];
            //For demo purpose, just adding '-DUP' suffix for the original Emp Name.
            if ("Ename".equals(empAttrName))
                dupRow.setAttribute(empAttrName,
                                    empCurrentRow.getAttribute(empAttrName) +
                                    "_DUP");
            int attrIndex = dupRow.getAttributeIndexOf(empAttrName);
            //Checking if the attribute is in the skip attribute list and the attribute is updatable
            if (!skipAttrList.contains(empAttrName) &&
                dupRow.isAttributeUpdateable(attrIndex))
                //Setting the value for the attributes
                dupRow.setAttribute(empAttrName,
                                    empCurrentRow.getAttribute(empAttrName));
        }
        //Inserting the duplicate row
        empVO.insertRow(dupRow);
    }

Explanation: In the above code, we're getting the Emp current row and getting attribute names for the row and iterating through these attributes, to set the attribute values to the newly created row(duplicate row). This will avoid manually specifying each and every attribute name to copy manually. And one thing to note here is that all attributes in the VO may not be updatable. So, we're checking whether a particular attribute in the VO is updatable and setting the value for it only if it's updatable. And, we want to avoid setting values for primary key attributes and unique attributes because setting the same value for these attributes will through 'too many objects match the priamry key...' exception. To avoid that, we're maintaining a list of attributes which we want to skip setting the values. You can see the if condition in the loop. We will be setting values for these attributes manually.

So, this method of copying all attributes to new row is automatic and efficient as we're not manually setting attribute values one by one.

Hope, this would be helpful.

ADF Model: Generating unique autogenerated number for primary key EO attribute

When we create a table and want to insert data, we must make sure that the value we give for the primary key should be unique. So, checking the existing data in table and using unique number would be obviously painful. To generate unique id for the primary keys, we have different methods for different DBs. For example, in MYSQL we use 'auto increment' fields in SQL statement while creating a table.

E.g.,

CREATE TABLE `classes` ( `class_id` INT( 3 ) NOT NULL AUTO_INCREMENT,
 `class_name` VARCHAR( 25 ) NOT NULL , UNIQUE ( `class_id` ) );

Coming to Oracle SQL, we write triggers and sequences to generate auto-increment number for primary keys.

E.g.,

CREATE SEQUENCE class_seq
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER class_id_insert_trigger
BEFORE INSERT
ON classes
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT class_seq.nextval INTO :NEW.CLASS_ID FROM dual;
END;
/

But, Oracle ADF has a easy way to generate auto-increment numbers for the primary key fields in EO. To make an EO attribute as an auto-increment field:

1. Create a new DB connection with name ROWIDAM_DB with the same database connection parameters of your original application DB. This DB connection will be used to generate the unique id for the entity attribute.

2. The SQL type of the EO attribute should be numeric data type. Specify default value for the EO attribute as the expression "oracle.jbo.server.uniqueid.UniqueIdHelper.getUniqueId();"

That's it! Now, you don't need to worry about setting unique id/number for the primary key attributes. ADF will automatically generate a unique-id for these fields.

ADF Model: Creating View Link

A View Link is created between two VOs to link them via foreign key. Please follow the below steps (screen shots) to create a View Link.

1. Right click any package and select 'New View Link..'

2. Give package and name for the view link. Click 'Next'.

3. Select source and destination VOs and the select the attribute based on which you need to link the VOs. If there is already association defined between the base EOs of the selected VOs, you can select the source and destination associations and click on 'Add'. The source and destination attributes will be shown at the bottom. Now, click 'Next'.

4. Select the check boxes to generate accessors in each of the VOs. Click 'Next'.

5. Click 'Next' until the 'Summary' stop, check the summary and click 'Finish'.

ADF Model: Creating and Running Application Module (AM)

Creating application module is easy. Follow the below steps (screen shots) to create and run application module.

1. Right click any package and select 'New Application Module' -> 'Create Application Module' windown opens.

2. Give package, name for AM and click 'Next'.

  

3. In the 'Data Model' stop, shuffle the VOs you want to add to AM to right side so that they'll be added to the application module. Click 'Next'.

4. In 'Application Modules' stop, you can add other application modules to the AM. i.e., you can nest the application modules. Now, click 'Next'.

5. In 'Java' stop, you can select to generate the application module implementation class (AMImpl).

6. Click 'Next' to see the summary and 'Finish'.

7. Now, you can see the created AM under the specified package. To run the AM, right click the AM and click 'Run'/'Debug'.

8. Now, the application module runs and opens the window where you can see the VO data. You can add, modify, remove VO rows/data and commit.