Please disable your adblock and script blockers to view this page

Search this blog

Saturday 27 July 2013

Implementing master/detail tree relation using af:Iterator and af:forEach for better UI designs - Oracle ADF

In ADF Faces tree and tree table is used for hierarchical representation of master/detail form of data,
in this tutorial i will show you that how can we design better UI's using master detail data.
af:iterator and af:ForEach both are used to iterate data as af:table for custom UI component-

here i am taking example of default HR Schema of Oracle database , Departments and Employees table to show master-detail relation

Using af:iterator-

  • Create a Fusion Web Application and prepare model for master detail relationship between departments and employees table, and in your page drop af:showDetailHeader from component palette
  • Go to page binding, add tree binding for Departments (Master) and Employees (Detail)

  • Surround showDetailHeader with an af:iterator,  and set value and var for iterator (master)

  •  Now change text of showDetailHeader to show department name on it, taking reference from iterator's var

  • master part is done, for detail part  drop two output text (for employee's first name and last name) inside showDetailHeader surrounding with an iterator (for detail part)

  • set the value and var for detail iterator taking reference from master iterator 

  • Now to show detail values in output text , set the value in both output text, taking reference from detail iterator

  • See the source of page for more clarification-

  • <af:iterator id="i3" value="#{bindings.Departments1.collectionModel}" var="departments">
                                    <af:showDetailHeader text="#{departments.DepartmentName}" disclosed="true" id="sdh1"
                                        <f:facet name="context"/>
                                        <f:facet name="menuBar"/>
                                        <f:facet name="toolbar"/>
                                        <f:facet name="legend"/>
                                        <f:facet name="info"/>
                                        <af:panelGroupLayout id="pgl2" layout="vertical">
                                            <af:iterator id="i2" var="emp" value="#{departments.Employees}">
                                                <af:separator id="s3"/>
                                                <af:panelFormLayout id="pfl1" rows="1">
                                                    <af:outputText value="#{emp.FirstName}" id="ot2"
                                                    <af:outputText value="#{emp.LastName}" id="ot3"
                                                <af:spacer width="10" height="10" id="s1"/>

  • Now Run your page to see UI of this master-detail relationship-

Using af:ForEach-

  •  Drop a panel accordion on page, by default it has one af:showDetailItem , surround it with af:ForEach and set items and var for ForEach

  • change text of showDetailItem to show department name on it, taking reference from ForEach's var 

  • Now drop Employees detail table from data control inside showDetailItem and set its value , taking reference from ForEach 

  • For more clarification see source of page-

  • <af:panelAccordion id="pa1" styleClass="AFStretchWidth" visible="true">
                                    <af:forEach items="#{bindings.Departments1.children}" var="dept">
                                        <af:showDetailItem text="#{dept.DepartmentName}" id="sdi1" inflexibleHeight="200">
                                            <af:table value="#{dept.children}" var="row"
                                                      emptyText="#{bindings.Employees3.viewable ? 'No data to display.' : 'Access Denied.'}"
                                                      fetchSize="#{bindings.Employees3.rangeSize}" rowBandingInterval="0"
                                                      filterVisible="true" varStatus="vs"
                                                      rowSelection="single" id="t1" styleClass="AFStretchWidth">
                                                <af:column sortProperty="#{}"
                                                           filterable="true" sortable="false"
                                                    <af:outputText value="#{row.EmployeeId}"
                                                        <af:convertNumber groupingUsed="false"
                                                <af:column sortProperty="#{}"
                                                           filterable="true" sortable="false"
                                                    <af:outputText value="#{row.FirstName}"
                                                <af:column sortProperty="#{}"
                                                           filterable="true" sortable="false"
                                                           headerText="#{bindings.Employees3.hints.LastName.label}" id="c3">
                                                    <af:outputText value="#{row.LastName}"
                                                <af:column sortProperty="#{}"
                                                           filterable="true" sortable="false"
                                                           headerText="#{bindings.Employees3.hints.Email.label}" id="c4">
                                                    <af:outputText value="#{row.Email}"

  • Now run your page to see its UI-

Download Sample workspace here- Download
Happy Jdeveloping..

Wednesday 24 July 2013

Implementing custom search form in ADF programmatically (Without using af:query)

Sometimes we need to implement custom search instead of using adf query component.
To implement custom search form , i have used a quite beautiful way to handle search and reset functionality for a View Object.
In this post i am taking Employees Table (Default HR Schema) to search and reset af:table.

  • First create a Fusion Web Application and create model (EO,VO and AM) using Employees table
  • Now suppose we have to search on 4 fields (EmployeeId, FirstName, Email, Salary) , so for this i have created a new read only ViewObject from dual
  •  This VO from dual is created to be used as Search form on page
  •  Now to make search effective on Employees VO , i have created 4 bind variable for corresponding fields in Employees ViewObject

  •  Now i have created a ViewCriteria using these 4 bind variables to re-query ViewObject  data
  •  This ViewCriteria will be executed each time when value of bind variable is changed, now this is the time to set value of bind variables, so i have dragged dual vo as a form on page, and added 2 button for Search and Reset
  •  To View Search Result , Employees Table is there on page
  •  Now to get value from Form Field to bean , i have created binding for all 4 fields in bean
  •  Now i have created ActionListener for Search button- in this code i have get value from page using component bindings and passed in Employees ViewObject's bind variable in order to execute viewCriteria.

  •     public void searchButton(ActionEvent actionEvent) {
            searchAMImpl am = (searchAMImpl)resolvElDC("searchAMDataControl");
            ViewObject empVo = am.getEmployees1();
            empVo.setNamedWhereClauseParam("EmpIdBind", empIdPgBind.getValue());
            empVo.setNamedWhereClauseParam("FirstNmBind", firstNmPgBind.getValue());
            empVo.setNamedWhereClauseParam("EmailBind", emailPgBind.getValue());
            empVo.setNamedWhereClauseParam("SalaryBind", salaryPgBind.getValue());

  • Once value is set in bind variable , view criteria is executed and Search result will be shown in resultant table- Run your application and see
  • To reset values in table and search form, see the managed bean code of Reset button

  •     public void resetButton(ActionEvent actionEvent) {
            searchAMImpl am = (searchAMImpl)resolvElDC("searchAMDataControl");
            ViewObject empVo = am.getEmployees1();
            ViewObject attrVo=am.getattr1();
            empVo.setNamedWhereClauseParam("EmpIdBind", null);
            empVo.setNamedWhereClauseParam("FirstNmBind", null);
            empVo.setNamedWhereClauseParam("EmailBind", null);
            empVo.setNamedWhereClauseParam("SalaryBind", null);

  • Click on reset button and page value are set to default

  • This is how we can implement custom search using ADF components, you can apply validations, auto suggest feature etc while using this custom search
          Cheers :-)  Download Sample App

Thursday 18 July 2013

JBO-27022-Failed to load value at index 'n' , java.sql.SQLException: Invalid column index in Oracle ADF

'Invalid column index' is the error occurs due to SQL Exception in ADF Faces.
and main reason behind this is mismatch between ViewObject's query and Attributes.
 suppose i have an application with default HR Schema and Employees Table.

  • See the query and attribute list of Employees ViewObject , when there is no mismatch between attributes and query
and query is -

  • Now i have removed a attribute from ViewObject query, then mismatch between VO Query and attribute list tends towards sql exception "Invalid Column Index"

 Query is- (First_Name is not there)

  •  Now when you try to run application, this error occurs, "Failed to load value at index n - java.sql.SQLException Invalid column index"- see error log 

  • oracle.jbo.AttributeLoadException: JBO-27022: Failed to load value at index 11 with java object of type java.lang.String due to java.sql.SQLException.
     at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(
     at oracle.jbo.server.ViewRowImpl.populate(
     at oracle.jbo.server.ViewDefImpl.createInstanceFromResultSet(
     at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(
     at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(
     at oracle.jbo.server.QueryCollection.populateRow(
     at oracle.jbo.server.QueryCollection.fetch(
     at oracle.jbo.server.QueryCollection.get(
     at oracle.jbo.server.ViewRowSetImpl.getRow(
     at oracle.jbo.server.ViewRowSetIteratorImpl.doFetch(
     at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(
     at oracle.jbo.server.ViewRowSetIteratorImpl.refresh(
     at oracle.jbo.server.ViewRowSetImpl.notifyRefresh(
     at oracle.jbo.server.ViewRowSetImpl.execute(
     at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(
     at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(
     at oracle.jbo.server.ViewRowSetImpl.executeQuery(
     at oracle.jbo.server.ViewObjectImpl.executeQuery(
     at oracle.adf.model.bc4j.DCJboDataControl.executeIteratorBindingIfNeeded(
     at oracle.adf.model.binding.DCIteratorBinding.executeQueryIfNeeded(
     at oracle.jbo.uicli.binding.JUCtrlHierBinding.getRootNodeBinding(
     at oracle.adfinternal.view.faces.model.binding.FacesCtrlHierBinding$HierCurrencyRowKeySet.getRowIterator(
     at oracle.adfinternal.view.faces.model.binding.CurrencyRowKeySet.size(
     at org.apache.myfaces.trinidad.model.RowKeySet.getSize(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(
     at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(
     at org.apache.myfaces.trinidad.component.UIXCollection.encodeEnd(
     at javax.faces.component.UIComponent.encodeAll(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeChild(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeAllChildren(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(
     at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(
     at javax.faces.component.UIComponent.encodeAll(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeChild(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeAllChildren(
     at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(
     at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(
     at javax.faces.component.UIComponent.encodeAll(
     at javax.faces.component.UIComponent.encodeAll(
     at oracle.adfinternal.view.faces.component.AdfViewRoot.encodeAll(
     at com.sun.faces.application.view.JspViewHandlingStrategy.doRenderView(
     at com.sun.faces.application.view.JspViewHandlingStrategy.renderView(
     at org.apache.myfaces.trinidadinternal.application.ViewDeclarationLanguageFactoryImpl$ChangeApplyingVDLWrapper.renderView(
     at com.sun.faces.application.view.MultiViewHandler.renderView(
     at javax.faces.application.ViewHandlerWrapper.renderView(
     at org.apache.myfaces.trinidadinternal.application.ViewHandlerImpl.renderView(
     at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._renderResponse(
     at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(
     at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.render(
     at javax.faces.webapp.FacesServlet.service(
     at weblogic.servlet.internal.StubSecurityHelper$
     at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(
     at weblogic.servlet.internal.ServletStubImpl.execute(
     at weblogic.servlet.internal.TailFilter.doFilter(
     at weblogic.servlet.internal.FilterChainImpl.doFilter(
     at oracle.adf.model.servlet.ADFBindingFilter.doFilter(
     at weblogic.servlet.internal.FilterChainImpl.doFilter(
     at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(
     at oracle.adfinternal.view.faces.activedata.AdsFilter.doFilter(
     at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(
     at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl._doFilterImpl(
     at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl.doFilter(
     at org.apache.myfaces.trinidad.webapp.TrinidadFilter.doFilter(
     at weblogic.servlet.internal.FilterChainImpl.doFilter(
     at Method)
     at weblogic.servlet.internal.FilterChainImpl.doFilter(
     at oracle.dms.servlet.DMSServletFilter.doFilter(
     at weblogic.servlet.internal.FilterChainImpl.doFilter(
     at weblogic.servlet.internal.RequestEventsFilter.doFilter(
     at weblogic.servlet.internal.FilterChainImpl.doFilter(
     at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(
     at weblogic.servlet.internal.WebAppServletContext$
     at weblogic.servlet.internal.WebAppServletContext.securedExecute(
     at weblogic.servlet.internal.WebAppServletContext.execute(
    Caused by: java.sql.SQLException: Invalid column index
     at oracle.jdbc.driver.OracleResultSetImpl.getString(
     at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getString(Unknown Source)
     at oracle.jbo.common.StringTypeSQLNativeImpl.getDataFromResultSet(
     at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(
     ... 97 more
    ## Detail 0 ## 
  • So when you see this type of log , first check your viewObjects and their query.
  • Always match attribute order in query of view object and in XML source, if there is mismatch in order, it may result in  great problem or data mistake
        See here is difference in order of attribute of vo and query- (Last_Name and Email)

  • In this case of mismatch in order, two things may happen-
  • First one- if Data type of mismatched attributes matches (as Last_Name and Email both are Var char) then value in both fields will be set in VO otherwise it will throw an exception.
  • See this scenario in given snap- Only due to wrong ordering ,value of Last_Name and Email got replaced

  • And if you have Impl classes for your Entity Object or View Object then also check indexing in  classes ,query and  XML source.
  • Invalid Column Index may also occur due to pl/sql (database) function call, always check it and its parameter indexing