ADF Query Component

one of the components I find very helpful in ADF is the ADF:QueryComponent.

since you can simply drag the ViewCriteria to the page and add it as a component, and it will be rendered based on the design time metadata.

but it can be a bit stressful, since you cannot edit it as freely as other components.

one such example is the operations allowed in the advaned mode :equal, not equal, is blank ,......

and if for some reason you want to change the text of the operation.

you can achieve this by doing the following:

1. open the desire View Object.

2. define a viewCriteria as needed.

3. in the source mode of the View Object add the following:

 <CompOper
          Name="Between"
          Oper="BETWEEN"
          ToDo="-1"
            MinCardinality="0"
            MaxCardinality="0"/>
          <CompOper
          Name="NotBetween"
          Oper="NOTBETWEEN"
          ToDo="-1"
            MinCardinality="0"
            MaxCardinality="0"/>
          <CompOper
          Name="GreaterThan"
          Oper=">"
          ToDo="-1"
            MinCardinality="0"
            MaxCardinality="0"/>
          <CompOper
          Name="GreaterThanEq"
          Oper=">="
          ToDo="-1"
            MinCardinality="0"
            MaxCardinality="0"/>
          <CompOper
          Name="LessThan"
          Oper="&lt;"
          ToDo="-1"
            MinCardinality="0"
            MaxCardinality="0"/>
          <CompOper
          Name="LessThanEq"
            Oper="&lt;="
          ToDo="-1"
            MinCardinality="0"
            MaxCardinality="0"/>
            <CompOper
          Name="IsNotBlank"
            Oper="ISNOTBLANK"
          ToDo="1"
            MinCardinality="0"
            MaxCardinality="0"
            OperDesc="Employee Does Have ID"/>

inside ViewCriteriaItem.

ToDo=-1 indicates that the operator will not be added, 1 the operator will be added.

OperDesc= the string that will be used in the select item from which you select your operation

for a list of operators see:

http://docs.oracle.com/cd/E15051_01/apirefs.1111/e10653/constant-values.html#oracle.jbo.common.JboCompOper

Adding busniess rule validation at runtime

Recently I came across the following link:

http://mahmoudoracle.blogspot.com/2012/07/add-validation-at-runtime-in-adf.html#.Up8iF-KGPu3

and tried to test it myself, there were some issue but it worked out eventually.

 I modified the requirements to achieve the following:

a. add an expression validator at runtime with custom message.

b. remove the added validator at runtime.

here are the detailed steps to do it:

1.  create a new ADF application.
2. in the model project create new business components from tables - will use the employees table from the HR schema.
3. open the entity and generate :EntityObjectImpl and EntityDefImpl
4. in the entityDefImpl add the following code:

     String addExpressionValidator(String attributeName, String groovyExpression,
                                String errorMessage) {
        AttributeDef attributeDef = findAttributeDef(attributeName);

        JboExpressionValidator jboExpressionValidator =
            new JboExpressionValidator(false, groovyExpression);
        jboExpressionValidator.setErrorMsgId("dynamic_Message");
        Map map = new HashMap();
        map.put("0", errorMessage);
        jboExpressionValidator.setErrorMsgExpressions(map);
        ((AttributeDefImpl)attributeDef).addValidator(jboExpressionValidator);
        jboExpressionValidator.setName(jboExpressionValidator.getName()+System.currentTimeMillis());
        return jboExpressionValidator.getName();

    }

    void removeExpressionValidator(String attributeName,
                                   String validatorName) {
        AttributeDefImpl attributeDef =
            (AttributeDefImpl)findAttributeDef(attributeName);
        List validators = attributeDef.getValidators();
        JboAbstractValidator targetValidator = null;
        for (Object _validator : validators) {
            targetValidator = (JboAbstractValidator)_validator;
            if (targetValidator.getName().equalsIgnoreCase(validatorName)) {
                break;
            }
        }
        attributeDef.removeValidator(targetValidator);

    }

  5.in the bundle used for the messages of the model add the following:

dynamic_Message={0}

6. expose the above method as a viewobject method.

 7, design the apge as you see fit

now at runtime  

as you can see I added the folloing:

attribute name:Salary

Expression:newValue<=oldValue*1.5

message: "new salary value("+newValue+") should be less than or equal to old salary value("+oldValue+"*1.5)"

please note the message, the adf processes this message as a groovy expression.

ADF Tree

In this post I will try to show how to implement the following requirements:

1. database-driven tree.
2. either render the node as a text output or a link depending on the values of the database record.
3. for the disclosed node, if it has a child node and the child node is a leaf, hide the disclose icon next to it.
4. once you click on a node in the tree, the path to the node will be printed in the screen.
5. if you click on a node, it will collapse if it was expanded and vice versa.

how to implement a database-driven tree:

Database part:

you can use the following table or some other table similar to it:

TreeMenu:

id : number,primary key

parent_id : number, foreign key (self reference to the same table and uses id the source), id of the parent node

Adesc: varchar2(xxx), the text to be displayed for the node in the tree.

Link: varchar2(xxx), the link to be used for leaf nodes.

ADF model project part:

after that use  create business components from tables wizard of JDeveloper, you will get:

one entity object, TreeMenu.

one associoation, based on the foreign key constraint.

one view object:TreeMenu

one View Link , based on the association.

I suggest you change the names of the successor for the association and the view link to avoid confusion in the future.

next, open the TreeMenu view object in the editor in the overview tab, and go to the query  part,and define a new View Criteria, let's name it isRoot.

and use the following condition : parent_id is null, we will use this view criteria to differentiate between root nodes and others.

next, open the application module in the editor in the overview tab and go to the Data Model and add one TreeMenu view object to the data model and name the instance: RootNodes, then choose the RootNodes instance in the data model and click the Edit button on the upper left part of the data model and choose the isRoot viewCriteria to be applied to this instance, this will insure that only root nodes will be fetched for this instance.

after that, add a child node to the RootNodes instance using the View link, and name the instance ChildrenNodes.

with this the model project is ready.

ADF view controller:

create a new page, expand the data controls palette of the JDeveloper and you should see your application module data control with RootNodes instance with ChildrenNodes listed as a detail.

click and drag the RootNodes to the page and choose to render it as an ADF tree.

add a sub node to the tree, and select the attributes you want to display

after that edit the source of the page to be similar to:
<af:tree value="#{bindings.RootNodes.treeModel}" var="node"
               selectionListener="#{bindings.RootNodes.treeModel.makeCurrent}"
               rowSelection="single" id="t1">
        <f:facet name="nodeStamp">
          <af:panelGroupLayout id="pgl1">
            <af:outputText value="#{node.Edesc}" id="ot1"
                           rendered="#{empty node.Link}"/>
            <af:goLink text="#{node.Edesc}:#{node.Link}" id="gl1"
                       destination="#{node.Link}"
                       rendered="#{!empty node.Link}" targetFrame="_blank"/>
          </af:panelGroupLayout>
        </f:facet>
      </af:tree>

with this you should get an output similar to:

as you can see from the above image, initially all nodes have a disclose icon next to them that you can click to disclose the node and show child records. but I find it annoying that each node have this icon, even if this node is a leaf and has no children but will disappear once you click on it.

I searched around the internet to see if it's possible to hide this icon through some config, but did not find any declarative approach so I had to use the programmatic solution which I will describe now:

• on the tree component of the page define a rowDisclosureListener
• in the rowDisclosureListener add the following code:

    public void disclosureListener(RowDisclosureEvent rowDisclosureEvent) {
       
        RichTree tree=(RichTree)rowDisclosureEvent.getSource();
        RowKeySet rks=rowDisclosureEvent.getAddedSet();
        if(rks!=null&&rks.size()>0){
            Iterator iter=rks.iterator();

            while(iter.hasNext()){
                Object rowKey=iter.next();
                tree.setRowKey(rowKey);
                JUCtrlHierNodeBinding rowData=(JUCtrlHierNodeBinding)tree.getRowData();
                if(rowData!=null&&rowData.getChildren()!=null){ // Iterate through the children of the expanded node and check if they have children
                    for(Object child:rowData.getChildren()){
                        JUCtrlHierNodeBinding childNode=(JUCtrlHierNodeBinding)child;
                        if(childNode.getChildren()==null||childNode.getChildren().size()==0){ // Child node is a leaf.  Add it to the disclosed rows, to that the ADF tree will not display a disclosure icon
                            tree.getDisclosedRowKeys().add(childNode.getKeyPath());
                        }
                    }
                }
            }
           
        }
    }

rerun the application, now child nodes won't have the disclose icon next to them from the start.

when I was looking for a way to hide the disclose icon, I came across this requirement:
when I click on a node in the tree I want to print the path from the root node to the click node on the screen.

I will implement this requirement by doing:

• on the tree define a selectionListener.
• in the selectionListener add the following code:

        RichTree tree1=(RichTree)selectionEvent.getSource();

        JUCtrlHierNodeBinding start = null;

        tree1.setRowKey(selectionEvent.getAddedSet().iterator().next());

        start = (JUCtrlHierNodeBinding)tree1.getRowData();

         

        JUCtrlHierNodeBinding parent=start.getParent();

        ZamerTreeMenuViewRowImpl  r = (ZamerTreeMenuViewRowImpl)start.getRow();

        path+="/"+r.getAdesc();

        while(parent!=null){

            r = (ZamerTreeMenuViewRowImpl)parent.getRow();

            if(r==null){

                break;

            }

            path="/"+r.getAdesc()+path;

            parent=parent.getParent();

        }

• in the bean define a path variable and create a getter for it.
• modify the page, by adding an output text to display the path,surround the output text with panel group layout and set partial triggers to rerender on tree changes. it will look similar to:

 <af:panelGroupLayout id="pgl2" layout="vertical" partialTriggers="t1">

        <af:outputText value="#{Test2.path}" id="ot2"/>

        <af:tree value="#{bindings.RootNodes.treeModel}" var="node"

                 selectionListener="#{Test2.selectionListener}"

                 rowSelection="single" id="t1"

                 rowDisclosureListener="#{Test2.disclosureListener}">

          <f:facet name="nodeStamp">

            <af:panelGroupLayout id="pgl1">

              <af:outputText value="#{node.Edesc}" id="ot1"

                             rendered="#{empty node.Link}"/>

              <af:goLink text="#{node.Edesc}:#{node.Link}" id="gl1"

                         destination="#{node.Link}"

                         rendered="#{!empty node.Link}" targetFrame="_blank"/>

            </af:panelGroupLayout>

          </f:facet>

        </af:tree>

      </af:panelGroupLayout>

the output will be similar:

I remember that in the earliest project I worked on, we had to implement a tree in a page where we had to expand/collapse the tree when we click on the tree item itself. to do this in ADF do the following :

• change the page to look like:

<af:panelGroupLayout id="pgl2" layout="vertical" partialTriggers="t1">

        <af:outputText value="#{Test2.path}" id="ot2"/>

        <af:tree value="#{bindings.RootNodes.treeModel}" var="node"

                 selectionListener="#{Test2.selectionListener}"

                 rowSelection="single" id="t1"

                 rowDisclosureListener="#{Test2.disclosureListener}"

                 binding="#{Test2.treeMenu}">

          <f:facet name="nodeStamp">

            <af:panelGroupLayout id="pgl1">

              <af:commandLink text="#{node.Edesc}" id="cl1"

                              rendered="#{empty node.Link}"

                              actionListener="#{Test2.toggleNode}"/>

              <af:goLink text="#{node.Edesc}:#{node.Link}" id="gl1"

                         destination="#{node.Link}"

                         rendered="#{!empty node.Link}" targetFrame="_blank"/>

            </af:panelGroupLayout>

          </f:facet>

        </af:tree>

      </af:panelGroupLayout>

• modify the code in the bean to:

package testing;

import java.util.Iterator;

import javax.faces.event.ActionEvent;

import model.businessObjects.view.ZamerTreeMenuViewRowImpl;

import oracle.adf.view.rich.component.rich.data.RichTree;

import oracle.jbo.uicli.binding.JUCtrlHierNodeBinding;

import org.apache.myfaces.trinidad.event.RowDisclosureEvent;

import org.apache.myfaces.trinidad.event.SelectionEvent;

import org.apache.myfaces.trinidad.model.RowKeySet;

public class Test2 {

    private RichTree treeMenu;

    public Test2() {

    }

    private String path = "";

    public void disclosureListener(RowDisclosureEvent rowDisclosureEvent) {

        RichTree tree = (RichTree)rowDisclosureEvent.getSource();

        RowKeySet rks = rowDisclosureEvent.getAddedSet();

        if (rks != null && rks.size() > 0) {

            Iterator iter = rks.iterator();

            while (iter.hasNext()) {

                Object rowKey = iter.next();

                tree.setRowKey(rowKey);

                JUCtrlHierNodeBinding rowData = (JUCtrlHierNodeBinding)tree.getRowData();

                if (rowData != null && rowData.getChildren() != null) { // Iterate through the children of the expanded node and check if they have children

                    for (Object child : rowData.getChildren()) {

                        JUCtrlHierNodeBinding childNode = (JUCtrlHierNodeBinding)child;

                        if (childNode.getChildren() == null || childNode.getChildren().size() == 0) { // Child node is a leaf.  Add it to the disclosed rows, to that the ADF tree will not display a disclosure icon

                            tree.getDisclosedRowKeys().add(childNode.getKeyPath());

                        }

                    }

                }

            }

        }

    }

    public void setPath(String path) {

        this.path = path;

    }

    public String getPath() {

        return path;

    }

    public void selectionListener(SelectionEvent selectionEvent) {

        //#{bindings.RootNodes.treeModel.makeCurrent}

        RichTree tree1 = (RichTree)selectionEvent.getSource();

        JUCtrlHierNodeBinding start = null;

        tree1.setRowKey(selectionEvent.getAddedSet().iterator().next());

        start = (JUCtrlHierNodeBinding)tree1.getRowData();

        updatePath(start);

    }

    private void updatePath(JUCtrlHierNodeBinding start) {

        String path = "";

        JUCtrlHierNodeBinding parent = start.getParent();

        ZamerTreeMenuViewRowImpl r = (ZamerTreeMenuViewRowImpl)start.getRow();

        path += "/" + r.getAdesc();

        while (parent != null) {

            r = (ZamerTreeMenuViewRowImpl)parent.getRow();

            if (r == null) {

                break;

            }

            path = "/" + r.getAdesc() + path;

            parent = parent.getParent();

        }

        this.path = path;

    }

    public void toggleNode(ActionEvent actionEvent) {

        RichTree tree1 = getTreeMenu();

        JUCtrlHierNodeBinding start = null;

        start = (JUCtrlHierNodeBinding)tree1.getRowData();

        org.apache.myfaces.trinidad.model.RowKeySet newRowSet = tree1.getDisclosedRowKeys().clone();

        boolean added = newRowSet.add(start.getKeyPath());

        if (!added && start.getChildren() != null && start.getChildren().size() > 0) {

            newRowSet.remove(start.getKeyPath());

        }

        new RowDisclosureEvent(tree1.getDisclosedRowKeys(), newRowSet, tree1).queue();

        updatePath(start);

    }

    public void setTreeMenu(RichTree treeMenu) {

        this.treeMenu = treeMenu;

    }

    public RichTree getTreeMenu() {

        return treeMenu;

    }

}

now, upon clicking on an item in the tree( not the disclose icon) the tree will either expand or collapse based on the item you clicked.

List All Dirty Entities in the current transaction

I was asked once if it was possible to list all dirty entities in the transaction regardless of the entity type.

After some thinking and reading, I reached the solution I am about to describe:

1)override the default DBTransactionImpl2 as described in:Extending ADF framework base classes

2)override the getTransactionPostListenersList and make it public.

the mentioned function returns a list that contain classes that implements the TransactionPostListener interface, such as :ViewObjectImpl,EntityImpl.

you can test the object by using the following code :

  transactionPostListener instanceof EntityImpl

to see if it's an Entity.

please note that if you find a ViewObject in the list that it does not necessarily have a dirty row.

simply iterating the rows of the ViewObject will add it to the list.

some other functions that may be of value are:

--addTransactionStateListener; by testing the method, found that this is the method used to add the ViewObject to the list returned by :getTransactionPostListenersList.

--addTransactionPostListenerNoCheck: by testing the method, found that this is the method used to add the Entity to the list returned by :getTransactionPostListenersList.

-- removeTransactionPostListener: by testing the method, found that this is the method used to remove the Post Listener from the list returned by :getTransactionPostListenersList.

Session TimeOut

First of all, I would like to point out that this article has more to do with J2EE than ADF.

In some projects you may face a requirement that the application should have a session timeout set to a certain value that is persisted somewhere and a user of the system with admin privileges should be able to set this value through one of the screens.

Of course, you can simply set the session timeout in the web.xml of the application. but this method will be static and if you want to modify the session timeout you will have to modify the web.xml then restart the application.

If you want the session timeout to be set dynamically then you can use the following code:

    int timeInSeconds=2;//add your custom logic here to retrieve the timeout value

    ((HttpSession)FacesContext.getCurrentInstance().getExternalContext().

     getSession(false)).setMaxInactiveInterval(timeInSeconds);

add the above code, so that it will always be called for all the users after the login. either use Phase listener or sessionListener.

ADF: Customizing the History columns

 ADF developer will often be faced with a requirement that dictates that each some table in the database should have some audit info ( who created the record and when, who made the last changes and when).

Luckily, ADF comes with out-of-the-box support for these requirements, all you have to do is set the desired column as a history column of the desired type in the entity and the framework will take care of filling the values. There are some default types that are already defined.

Okay, so far so good. But what if you have to append something to the username or if you have to use the given name of the user(that you can get by implementing your own code to map the user name to the given name),how can you override the framework behavior?

you can acheive this by overriding the EntityImpl class with your own Class.

as for how to override the EntityImpl class check: Extending ADF Framework Base Classes.

now in your Custom EntityImpl Class, add the following code:

    protected Object getHistoryContextForAttribute(AttributeDefImpl attributeDefImpl){

        if(attributeDefImpl.getHistoryKind()==AttributeDefImpl.HISTORY_CREATE_USER){

            //add your Custom logic here

        }else{

            return super.getHistoryContextForAttribute(attributeDefImpl);

        }

    }

doing so will let you set the value of the history column with your own value.

Extending ADF framework base classes

If you have been reading on the ADF framework best practices, you will often read that it's recommended to extend some of the base classes of the framework; so that you can inject your code as required later on in the application development cycle without having to make a huge amount of changes.

in this article I will talk about how to do that.

first, let's start with the tough one: DBTransactionImpl2.

you can override the DBTransactionImpl2 with your custom class by doing the following:

           1)create a new java class that extends the DBTransactionImpl2 class. let's name

                 it MyCustomTransactions.

           2) create a new java class that extends the DatabaseTransactionFactory. let's name 

                it MyCustomTransactionFactory.

           3) in the MyCustomTransactionFactory add the following code:

                        public DBTransactionImpl2 create(){

                       //        return super.create();

                         return new MyCustomTransactions();

                       }

               as you can see from the above code we return an instance of 

               our MyCustomTransactions class.

           4)go to the application module, open the Configuration tab and 

              edit the desired configuration.

           5)in the Edit Business Components Configuration popup that appeared,

               go to the Properties tab. 

           6)change the value of the TransactionFactory property to the fully qualified name of 

                the MyCustomTransactions class, i.e.: <package name>.ClassName

with this the framework will now use our custom class instead of the default one. So, when you call the getTransaction() method from the application module, you will get an instance of the custom class.

now that we are done with overriding the DBTransactionImpl2 class, I will explain how to override the other classes.

this can be achieved simply  by:

           1) writing classes that extend the following: 

                oracle.jbo.server.EntityCache

                oracle.jbo.server.EntityImpl

                oracle.jbo.server.EntityDefImpl

                oracle.jbo.server.ViewObjectImpl

                oracle.jbo.server.ViewRowImpl 

                oracle.jbo.server.ViewDefImpl

                oracle.jbo.server.ApplicationModuleImpl

                oracle.jbo.server.ApplicationModuleDefImpl

           2)open the project properties of the model project.

           3)from the tree to left of the Project Properties popup 

                 expand the Business Components node,

                 then  expand  the Base Classes sub-node .

           4) now direct the JDeveloper to your base classes.

this is it. 

Of course, you don't have to override all the base classes if you feel that it's not necessary.

I will write about some uses in future articles.

              

ViewObject: getFilteredRows

there is a function :getFilteredRows(RowQualifier rowQualifier) in the api of the ViewObjectImpl class.

you can create a RowQualifier simply by using:

new RowQualifier("attribute = '"+val+"'");

The allowed conditions can be only simple operators like =, <>, > and <. It does not take other functions like NVL, IS NULL, IS NOT NULL etc. This is not specified in any documentation.

Example:

HR schema, table:Employees.

in the EmployeesViewImpl add the following:
    public Row[] getEmployeesWithJob(String job){
        RowQualifier qualifier=new RowQualifier("JobId ='"+job+"'");
        return getFilteredRows(qualifier);

    }

then call the above method from your client code:
appModule.getEmployeesView1().getEmployeesWithJob("AD_VP");

this will return a Row array with the rows that matches the condition.

but after some experimenting I found that this code may introduce a sql injection threat, for example:
appModule.getEmployeesView1().getEmployeesWithJob("AD_VP' OR ''='");

this will return all rows in the view object.
so if the input of the function is supplied by the user ,this will be a serious security risk.

tested on JDeveloper 11.1.1.5

Edited on:19/9/2013:
concerning the underlined paragraph above,
I jumped to conclusions when I tried it,   I am sorry about that.
here is my explanation after I did some more digging:
using the tracing tools of the oracle database; I think it's safe to conclude the following:
1)the filtering is done in memory, the resulting query is not modified to include the condition.
2)as result of 1, I don't think it's correct to call this threat an SQL injection threat. However, this does not mean the issue of injection is not there.
3)as a result of 1, all rows of the table will be fetched into memory. This may result in performance issues, specially for table with a huge number of rows.

Entities Association - not a composition

master detail relations in relational databases can be described either as a composite or not a composite relationships.

a Master - detail composite means that the detail cannot exist on it own without a master.

a Master - detail non-composite means that the detail can exist in the database without a master.

in ADF this can be configured in the Assocaiton between two entities ,relationship tab under the behavior sub-header, by checking the composition association checkbox.

   1)this will have some effects on the behavior of the framework, some of these are: the order of posting changes to the database, the master will be posted first.
   2) if a detail is changed then the master will be validated again.
   3)if the ids are assigned by triggers, the foreign key in the detail will be changed to the new id of the master assigned by the trigger.

but what will happen if the relationship is not a composition?

this might causes some issues, specially since the framework posts the new records to the database in the order of their creation.

so consider the following scenario: you create a detail record , create a master record, assign the detail to be a child record of the master record then commit.

here is a code sample that does the above scenario:

        System.out.println(Test.class+".main>>starting");

        ApplicationModuleImpl module=(ApplicationModuleImpl)Configuration.createRootApplicationModule("model.businessObjects.applicationMod.AppModule","AppModuleLocal");

        System.out.println(Test.class+".main>>after getting application module");

        ViewObject deptVO= module.findViewObject("DepartmentsView1");

        ViewObject empVO= module.findViewObject("EmployeesView3");

        EmployeesViewRowImpl empRow=(EmployeesViewRowImpl)empVO.createRow();

        DepartmentsViewRowImpl deptRow=(DepartmentsViewRowImpl)deptVO.createRow();

        empRow.setLastName("zamer");

        empRow.setEmail("ahmad.alzamer@gmail.com");

        empRow.setHireDate(new Date());

        empRow.setJobId("AD_VP");

        empRow.setDepartmentId(deptRow.getDepartmentId().getSequenceNumber());

        

        deptRow.setDepartmentName("testing");

        module.getTransaction().commit();

        System.out.println(Test.class+".main>>emp:"+empRow.getEmployeeId());

        System.out.println(Test.class+".main>>dept:"+deptRow.getDepartmentId());

the above code is built on the HR schema and the association between the two entities is not a composite one. For the record, I overrode the doDml method in both entities to print to the output console the name of the class

if we run the above code we will get the following:

class model.testing.Test.main>>after getting application module

class model.businessObjects.entity.EmployeesImpl.doDML>>

Exception in thread "main" oracle.jbo.DMLConstraintException: JBO-26048: Constraint "EMP_DEPT_FK" is violated during post operation "Insert" using SQL statement "BEGIN INSERT INTO EMPLOYEES(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) VALUES (:1,:2,:3,:4,:5,:6) RETURNING EMPLOYEE_ID INTO :7; END;".

at oracle.jbo.server.OracleSQLBuilderImpl.doEntityDML(OracleSQLBuilderImpl.java:568)

at oracle.jbo.server.EntityImpl.doDMLWithLOBs(EntityImpl.java:8535)

at oracle.jbo.server.EntityImpl.doDML(EntityImpl.java:8467)

at model.businessObjects.entity.EmployeesImpl.doDML(EmployeesImpl.java:25)

at oracle.jbo.server.EntityImpl.postChanges(EntityImpl.java:6733)

at model.businessObjects.entity.EmployeesImpl.postChanges(EmployeesImpl.java:33)

at oracle.jbo.server.DBTransactionImpl.doPostTransactionListeners(DBTransactionImpl.java:3286)

at oracle.jbo.server.DBTransactionImpl.postChanges(DBTransactionImpl.java:3089)

at oracle.jbo.server.DBTransactionImpl.commitInternal(DBTransactionImpl.java:2093)

at oracle.jbo.server.DBTransactionImpl.commit(DBTransactionImpl.java:2374)

at model.testing.Test.main(Test.java:35)

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

ORA-06512: at line 1

as you can see framework tried to post the employee record first; since it was created first.

in order to change this behavior, we should override postChanges to ensure that the master record is posted first.

here is the code to override the postChanges in the EmployeesImpl class:

    public void postChanges(TransactionEvent transactionEvent){

        if(getPostState()==STATUS_NEW||getPostState()==STATUS_MODIFIED){

            if(getBelongToDepartment().getPostState()==STATUS_NEW){

                getBelongToDepartment().postChanges(transactionEvent);

            }

        }

        super.postChanges(transactionEvent);

    }

the output after the run is:

class model.testing.Test.main>>after getting application module

class model.businessObjects.entity.DepartmentsImpl.doDML>>

class model.businessObjects.entity.EmployeesImpl.doDML>>

Exception in thread "main" oracle.jbo.DMLConstraintException: JBO-26048: Constraint "EMP_DEPT_FK" is violated during post operation "Insert" using SQL statement "BEGIN INSERT INTO EMPLOYEES(EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) VALUES (:1,:2,:3,:4,:5,:6) RETURNING EMPLOYEE_ID INTO :7; END;".

at oracle.jbo.server.OracleSQLBuilderImpl.doEntityDML(OracleSQLBuilderImpl.java:568)

at oracle.jbo.server.EntityImpl.doDMLWithLOBs(EntityImpl.java:8535)

at oracle.jbo.server.EntityImpl.doDML(EntityImpl.java:8467)

at model.businessObjects.entity.EmployeesImpl.doDML(EmployeesImpl.java:25)

at oracle.jbo.server.EntityImpl.postChanges(EntityImpl.java:6733)

at model.businessObjects.entity.EmployeesImpl.postChanges(EmployeesImpl.java:35)

at oracle.jbo.server.DBTransactionImpl.doPostTransactionListeners(DBTransactionImpl.java:3286)

at oracle.jbo.server.DBTransactionImpl.postChanges(DBTransactionImpl.java:3089)

at oracle.jbo.server.DBTransactionImpl.commitInternal(DBTransactionImpl.java:2093)

at oracle.jbo.server.DBTransactionImpl.commit(DBTransactionImpl.java:2374)

at model.testing.Test.main(Test.java:35)

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

ORA-06512: at line 1

we still get integrity constraint error, since the id of the master is changed -by the trigger- but the foreign key is not.

to fix this error we override two functions in the departmentsImpl class:

    RowSet empsInDept=null;

    @Override

    public void postChanges(TransactionEvent transactionEvent){

        if(getPostState()==STATUS_NEW){

            empsInDept=(RowSet)getEmployeesInDept();

        }

        super.postChanges(transactionEvent);

    }

    @Override

    protected void refreshFKInNewContainees(){

        if(empsInDept!=null){

            while(empsInDept.hasNext()){

                EmployeesImpl employee=(EmployeesImpl)empsInDept.next();

                employee.setDepartmentId(getDepartmentId().getSequenceNumber());

            }

            empsInDept.closeRowSet();

        }

        

        super.refreshFKInNewContainees();

    }

we override the functions:
   1)postChanges : to save a rowSet temporarily ,so that we have a way to access the child record after posting the current row to the database. since after posting the row will have a new id and we will not be able to fetch the child records.
   2)refreshFKInNewContainees: this function is called by the framework to refresh the values of the foreign keys in child records with the new value assigned by the triggers, but this function will only affect composite relations; so we override it to update the values in a non-composite relation.

the output after the run is:

class model.testing.Test.main>>after getting application module

class model.businessObjects.entity.DepartmentsImpl.doDML>>

class model.businessObjects.entity.EmployeesImpl.doDML>>

class model.testing.Test.main>>emp:4

class model.testing.Test.main>>dept:6

ADF Select One Choice 2

it seems when you build a model driven LOV and disply it on an output page you can use the following EL expression:

#{!empty bindings.DepartmentId.selectedValue? bindings.DepartmentId.selectedValue.attributeValues[1]:'no choice selected'}

to display any of the attributes of the view rows used to create the LOV. 

please pay attention to the use of <<empty bindings.DepartmentId.selectedValue>> , this is used since if no choice is made(empty list item is the currently selected item) then  <<bindings.DepartmentId.selectedValue>> will return an empty string, so if you don't have this test , then you might get an exception complaining that the String class does not have a property named attributeValues.

the above expression gets the second attribute of the view row used to define the model driven LOV on the departmentId attribute.

I cannot think of any use for such functionality right now, but hopefully it can be of use to anyone of the reader of this blog. 

ADF Select Once Choice

building ADF Select One Choice using model driven list is quite easy once you try it. but sometimes you get business requirements that makes you lose some sleep over it.

I recently came across thread discussion on the oracle forum -sorry I don't have the link to it- where the author of the thread was requested to disable some of the items in the Select items.

Some will suggest that instead of showing the items in the list and have them disabled, you can easily remove them from the list, which can be easily done by using a view criteria.

But there are times where the developer is forced to do as the requirements ask. so we still have to show the items and somehow disable them.

here is my solution:

the first steps of the solution is the same as if you are trying to create a normal model driven list.

eventually, you will have something similar to the following in the source of the page:

             <af:selectOneChoice value="#{bindings.DepartmentId.inputValue}"

                                label="#{bindings.DepartmentId.label}"

                                required="#{bindings.DepartmentId.hints.mandatory}"

                                shortDesc="#{bindings.DepartmentId.hints.tooltip}"

                                id="soc2">

              <f:selectItems value="#{bindings.DepartmentId.items}" id="si1"/>

            </af:selectOneChoice>

edit the source to look something like this:

            <af:selectOneChoice value="#{bindings.DepartmentId.inputValue}"

                                label="#{bindings.DepartmentId.label}"

                                required="#{bindings.DepartmentId.hints.mandatory}"

                                shortDesc="#{bindings.DepartmentId.hints.tooltip}"

                                id="soc1" valuePassThru="true"

                                autoSubmit="true"

                                valueChangeListener="#{TestB.testListener}">

                         <!--f:selectItems value="#{bindings.DepartmentId.items}" id="si1"/-->

                       <af:forEach items="#{bindings.DepartmentId.items}" var="item">

                             <af:selectItem label="#{item.label}" id="selectItem1"    value="#{item.value}" disabled="#{item.value%2 eq 0}"/>

                       </af:forEach>

            </af:selectOneChoice>

with this code you will generate the select items yourself using the model driven list.

now all that remains is editing the disabled attribute of the selectItem tag to disable the select item as per your requirements.

ViewObject CreateRow() VS CreateAndInit()

both the methods (CreateRow() and CreateAndInitRow() ) mentioned above create and returns a new record.but the difference lies in the input parameter the createAndInitRow method takes.

the CreateAndInitRow takes a parameter of type NamedValuePair and uses those values to override the default values defined for the view object at design time.

to show the impact of this difference consider the following business case:

using HR schema. we have the following:

1)employees: have manager_id,department_id

2)departments :have department_id, manager_id

when adding a new employee to a department, the manager_id of the employee is set to the manager_id of the department.

this is achieved by defining a view accessor in the employee named Department. and setting the default value of the manager_id attribute of the employeesView to expression with the value:

    Department.ManagerId

and the default value of the departmentId to 10(otherwise the above expression will throw a nullpointerexception ).

to test the above I wrote:

        Number empId=new Number(546);

        vo= module.findViewObject("EmployeesView1");

        NameValuePairs nvp=new NameValuePairs();

        nvp.setAttribute("EmployeeId",empId);

        nvp.setAttribute("LastName","zamer");

        nvp.setAttribute("Email","zamer@gmmail.com");

        nvp.setAttribute("HireDate",new Date());

        nvp.setAttribute("JobId","AD_VP");

        nvp.setAttribute("DepartmentId",new Number(20));

        ViewRowImpl newRow=(ViewRowImpl)vo.createAndInitRow(nvp);

        vo.insertRow(newRow);

        newRow=(ViewRowImpl)vo.createRow();

        Number empI2=new Number(547);

        newRow.setAttribute(0,empI2);

        newRow.setAttribute(2,"zamer2");

        newRow.setAttribute(3,"zamer@gmmail.com2");

        newRow.setAttribute(5,new Date());

        newRow.setAttribute(6,"AD_VP");

        newRow.setAttribute("DepartmentId",new Number(20));

        vo.insertRow(newRow);

        module.getTransaction().commit();

        String [] attrNames= newRow.getAttributeNames();

        vo.executeQuery();

        while(vo.hasNext()){

            Row row=vo.next();

            if(empId.equals(row.getAttribute(0))||empI2.equals(row.getAttribute(0))){

            System.out.println(test.class+".main>>row:"+attrNames[0]+":"+row.getAttribute(0)+":"+attrNames[1]+":"+row.getAttribute(1)+":"+attrNames[2]+":"+row.getAttribute(2)+":"+attrNames[3]+":"+row.getAttribute(3)+":"+attrNames[4]+":"+row.getAttribute(4)+":"+attrNames[5]+":"+row.getAttribute(5)+":"+attrNames[6]+":"+row.getAttribute(6)+":"+attrNames[7]+":"+row.getAttribute(7)+":"+attrNames[8]+":"+row.getAttribute(8)+":"+attrNames[9]+":"+row.getAttribute(9)+":"+attrNames[10]+":"+row.getAttribute(10)+":"+attrNames[11]+":"+row.getAttribute(11)+":"+attrNames[12]+":"+row.getAttribute(12));

            System.out.println();

            }

        }

the following was printed to the output:

class testing.test.main>>row:DepartmentId:10:DepartmentName:Administration:ManagerId:200:LocationId:1700

class testing.test.main>>row:DepartmentId:20:DepartmentName:Marketing:ManagerId:201:LocationId:1800

class testing.test.main>>creating

class testing.test.main>>row:EmployeeId:546:FirstName:null:LastName:zamer:Email:zamer@gmmail.com:PhoneNumber:null:HireDate:1970-01-01:JobId:AD_VP:Salary:null:CommissionPct:null:ManagerId:201:DepartmentId:20:DeptName:null:EmpVideo:null

class testing.test.main>>row:EmployeeId:547:FirstName:null:LastName:zamer2:Email:zamer@gmmail.com2:PhoneNumber:null:HireDate:1970-01-01:JobId:AD_VP:Salary:null:CommissionPct:null:ManagerId:200:DepartmentId:20:DeptName:null:EmpVideo:null

you can see that that department 10 has manager 200  and  department 20 has manager 201  

when we used createAndInitRow (employee id:546) the employee had the manager id:201

while  when we used createRow (employee id:547) the employee had the manager id:200

this can be explained as follows:

since the expression is on a persistent attribute, the framework computes the default value, and this happens only once (at the creation of the record) using the available values at the time.

so since the department id at the creation of the record is the default value of 10 then the accessor will fetch that department.

as for CreateAndInitRow, the passed parameters will be used to override the default values, then any of the remaining attributes expressions(the attributes not included in the NamedValuePair ) will be evaluated ,so since the department id was changed to 20 , then the accessor will fetch department with the id 20.

so you can think of it this way,

createAndInitRow  set the default values of the new row at runtime using the named value pair passed to it.

oracle Database session tracking

Hello everyone,

Today I would like to describe the DBMS_APPLICATION_INFO package in the oracle database.

here are some of the procedures it has:

SET_ACTION Procedure	Sets the name of the current action within the current module
SET_CLIENT_INFO Procedure	Sets the client_info field of the session
SET_MODULE Procedure	Sets the name of the module that is currently running to a new module

for the full description refer to the link:

DBMS_APPLICATION_INFO

with this you can set some information in the current session, which can be queried from the v$session from the sys schema:

select module,action,client_info from v$session

this can be helpful to identify and trace database sessions, specially if you are using connections pools.

after getting the connection from the pool, the first thing to do is to set the client_info ,module or action fields  using  this package for example:set the module to the application name and set the client_info to user name. this way it will be easier for you or the dba to identify which session you want to trace.

here is an example:

recently we had a certain page in our production environment that was slow to load, the problem could not be reproduced in test environment, after some poking around we suspected that the issue might be caused by database. but since the application uses a connection pool ,we had several sessions connected to the database.and did not know which session to trace,so by using this package we set the value of the client_info to be the user name. after that we managed to trace the session ,used TKPROF on the output file and by inspecting the resulting file found that the issue was caused by loading an entire table(40,000+ record) into memory then searching them. after fixing the issue the page loaded fast.

Oracle DB XML generation

As you can tell from the title this is article is not related to ADF, but to the oracle database.

the use case: 

    generate xml document based on a certain requirement in oracle database.

for example :using the HR schema, list all department id, name and all of it's employees as well as listing each employee name and job.

something like :   1     dept1

                                     10    emp1 clerk

                                     11    emp2  it

                           2    dept2

the solution:

there are several xml functions in the oracle database that can be used to generate and format xml documents. I will use them to generate the needed xml.

there are two solutions we found that could solve the issue:

1) simple :

SELECT DBMS_XMLGEN.getxml ('select d.department_name,d.department_id,cursor(select first_name ||'' ''||last_name name,job_id from employees e where E.DEPARTMENT_ID=D.DEPARTMENT_ID) employees from departments d ') FROM DUAL;

will  return something like:

<?xml version="1.0"?>

<ROWSET>

 <ROW>

  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>

  <DEPARTMENT_ID>10</DEPARTMENT_ID>

  <EMPLOYEES>

   <EMPLOYEES_ROW>

    <NAME>Jennifer Whalen</NAME>

    <JOB_ID>AD_ASST</JOB_ID>

   </EMPLOYEES_ROW>

  </EMPLOYEES>

 </ROW>

 <ROW>

  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>

  <DEPARTMENT_ID>20</DEPARTMENT_ID>

  <EMPLOYEES>

   <EMPLOYEES_ROW>

    <NAME>Michael Hartstein</NAME>

    <JOB_ID>MK_MAN</JOB_ID>

   </EMPLOYEES_ROW>

   <EMPLOYEES_ROW>

    <NAME>Pat Fay</NAME>

    <JOB_ID>MK_REP</JOB_ID>

   </EMPLOYEES_ROW>

  </EMPLOYEES>

 </ROW>

.

.

.

.

.

</ROWSET>

as you can see the  DBMS_XMLGEN.getxml generate the output based on whatever sql query is passed into it as input and the generated output tags are based on the column names.

still there are tags that are named row,rowset that you cannot control their names.

of course ,you can use the replace function to change those tags.

but what if the data returned from the query contains data that have strings similar to these tags?

this led me to look for another solution.

2) more complex:

DBMS_XMLGEN function is not the only xml function provided by oracle. There are handful of other functions, which we will use in the following solution:

SELECT SYS_XMLAGG (

          SYS_XMLGEN (

             XMLFOREST (

                D.DEPARTMENT_NAME,

                D.DEPARTMENT_ID,

                (SELECT SYS_XMLAGG (

                           SYS_XMLGEN (XMLFOREST (E.FIRST_NAME, E.JOB_ID),

                                       XMLFormat ('Employee')),

                           XMLFormat ('List'))

                   FROM employees e

                  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) "Employees"),

             XMLFormat ('Department')),

          XMLFormat ('Departments'))

          "xml query"

  FROM departments d;

which will produce :

<?xml version="1.0"?>

<Departments>

<Department>

  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>

  <DEPARTMENT_ID>10</DEPARTMENT_ID>

  <Employees>

    <List>

      <Employee>

        <FIRST_NAME>Jennifer</FIRST_NAME>

        <JOB_ID>AD_ASST</JOB_ID>

      </Employee>

    </List>

  </Employees>

</Department>

<Department>

  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>

  <DEPARTMENT_ID>20</DEPARTMENT_ID>

  <Employees>

    <List>

      <Employee>

        <FIRST_NAME>Michael</FIRST_NAME>

        <JOB_ID>MK_MAN</JOB_ID>

      </Employee>

      <Employee>

        <FIRST_NAME>Pat</FIRST_NAME>

        <JOB_ID>MK_REP</JOB_ID>

      </Employee>

    </List>

  </Employees>

</Department>

.

.

.

</Departments>

of course you can always write a lot of pl/sql code to read the result and concatenate the result to create the desired output.