I'm happy to say, my friend from Spain - Rowan (nickname), started her own blog in Spanish. Blog title is Oracle Fusion Donosti, if to translate word donosti from Spanish, it means - my city. Its nice initiative, Oracle is becoming more and more international, with a help from community. I wish, Spanish speaking Oracle users will find it useful and Rowan will keep posting.
On her blog, Rowan will post thoughts from daily work with Oracle, etc. She will post translations of my posts as well (Thank You), there is a first one already translated - JDeveloper 11g - CRUD EN COMPONENTE FORMULARIO.
Sunday, February 22, 2009
Saturday, February 21, 2009
Workaround for LOV on Primary Key Attribute
There is a problem with LOV component, when it is defined on Primary Key attribute - registered Bug #8275169. Until problem will be fixed, workaround can be as following - to define second dummy transient attribute for Primary Key attribute and use it on the page. However, during commit - in doDML method on Entity to substitute real attribute value with value from transient attribute.
For example, Entity with Primary Key attribute DepartmentId:
I have defined LOV for this attribute in VO and on runtime trying to use LOV:
However unsuccessfully, error is generated:
You can download sample application - LOVPrimaryKey.zip, where described workaround is implemented.
To give you more light about workaround, I will explain it in detail.
First, Primary Key attributes always are with Mandatory validation rule, however I removed this rule from DepartmentID and declared it for my transient attribute DepartmentId_Dummy. It is done in order to remove unnecessary check, since we are creating mirror attribute:
In getter method for transient attribute I need to return real attribute value during first load in order to display not blank values but correct ones:
In overridden doDML method value from transient attribute is copied back to real attribute:
DepartmentId attribute Display Hint is set to Hide - this means will not be visible in ViewController:
Transient attribute will be visible with user friendly label:
When developer will drag and drop Data Control on the page, wizard will not list original DepartmentId attribute, because it was set as Hide:
Now LOV will work, however I strongly believe this bug will be fixed in next build and there will be no need to apply this workaround !
Spanish Summary:
Este artículo se describe un Tip para solucionar un bug que se produce cuando se utiliza una Lista de Valores (LOV) para recuperar un dato en un formulario.
For example, Entity with Primary Key attribute DepartmentId:
I have defined LOV for this attribute in VO and on runtime trying to use LOV:
However unsuccessfully, error is generated:
You can download sample application - LOVPrimaryKey.zip, where described workaround is implemented.
To give you more light about workaround, I will explain it in detail.
First, Primary Key attributes always are with Mandatory validation rule, however I removed this rule from DepartmentID and declared it for my transient attribute DepartmentId_Dummy. It is done in order to remove unnecessary check, since we are creating mirror attribute:
In getter method for transient attribute I need to return real attribute value during first load in order to display not blank values but correct ones:
In overridden doDML method value from transient attribute is copied back to real attribute:
DepartmentId attribute Display Hint is set to Hide - this means will not be visible in ViewController:
Transient attribute will be visible with user friendly label:
When developer will drag and drop Data Control on the page, wizard will not list original DepartmentId attribute, because it was set as Hide:
Now LOV will work, however I strongly believe this bug will be fixed in next build and there will be no need to apply this workaround !
Spanish Summary:
Este artículo se describe un Tip para solucionar un bug que se produce cuando se utiliza una Lista de Valores (LOV) para recuperar un dato en un formulario.
View Criteria and CRITERIA_AUTO_EXECUTE Property
I'm writing today blog post, based on information found in following document - Known Issues for JDeveloper and ADF 11g (11.1.1.0.1). In current ADF release there is no option to control View Criteria automatic execution feature using Expression Language, auto execution can be set only decloratively in Edit View Criteria wizard, Control Hints section. However, its possible to control auto execution mode programmatically, using CRITERIA_AUTO_EXECUTE property. I will describe how to use this property and how it can be applied.
Download sample application - ViewCriteriaAutoExecuteHint.zip, where I'm conditionally rendering Query Criteria (af:query) component with auto execute set to On or Off.
At first, take a look into Employees VO, you will find there EmployeesViewCriteria. Criteria is defined without auto execution, this means by default on page load it will not bring any results:
However, lets say according to functional requirements our page will accept input parameter, this means if parameter will not be Null Criteria should bring results by default and no results otherwise. This means, we need to set View Criteria execution mode conditionally. How to do this - in current release it can be achieved programmatically:
In backing bean method we can access AM and retrieve VO. Criteria is accessed through ViewCriteriaManager and auto execution parameter (true, false) is applied. Same code can be implemented in ADF BC Model layer, however in this sample I'm using it in backing bean, because I will invoke it from ADF Task Flow.
Sample application contains following ADF Task Flow structure:
1. adfc-config.xml - Unbounded ADF Task Flow
Includes main page and two ADF Task Flow Call activities.
2. task-flow-definition-departments - Bounded ADF Task Flow with Shared Scope
Includes page with Departments table, ADF Task Flow Call activity to pass parameter to Employees ADF Task Flow and ADF Task Flow Return activity.
3. task-flow-definition-employees - Bounded ADF Task Flow with Isolated Scope
Includes page with Employees Table and Criteria, Router activity, three Method Call activities and ADF Task Flow Return activity.
For this ADF Task Flow I have defined Isolated Scope, because BUG #8263481. According to this bug, in current release Query Criteria (af:query) is not rendered correctly when passing parameters to Shared Scope ADF Task Flow.
Based on defined logic, when passed parameter is Null, Router activity navigates to notQueryAuto Method Call (where 'false' value is passed to Backing bean method to set CRITERIA_AUTO_EXECUTE property). Otherwise, when parameter is not Null, queryAuto Method Call is invoked (where 'true' value is passed to Backing bean method to set CRITERIA_AUTO_EXECUTE property). In this case, next is invoked ExecuteWithParams method, this method is setting filter parameter for View Criteria. And at last, page is opened.
On runtime, in main page we have two options - to open Employees ADF Task Flow without parameter or with.
By pressing 'Employees' button, it will be opened with Null value and Criteria will be rendered with CRITERIA_AUTO_EXECUTE = false property:
By pressing 'Departments' button, user will get a table with Departments where he can select any row and press 'Employees' button.
Means - DepartmentId from selected row will be passed to Employees ADF Task Flow and Criteria will be rendered with CRITERIA_AUTO_EXECUTE = true property:
Spanish Summary:
En este post se muestra como lograr la ejecucion automatica en los View Objects. Para ello Andrejus desarrolla un ejemplo con 2 tablas : una de departamento y otra asociada a los empleados. El efecto de ejecucion automatica se notará cuando ustede seleccione una fila de la tabla empleados y luego se mostrará la tabla de los empleados asociados a ese departamento los cuales se cargaron automaticamente.
Download sample application - ViewCriteriaAutoExecuteHint.zip, where I'm conditionally rendering Query Criteria (af:query) component with auto execute set to On or Off.
At first, take a look into Employees VO, you will find there EmployeesViewCriteria. Criteria is defined without auto execution, this means by default on page load it will not bring any results:
However, lets say according to functional requirements our page will accept input parameter, this means if parameter will not be Null Criteria should bring results by default and no results otherwise. This means, we need to set View Criteria execution mode conditionally. How to do this - in current release it can be achieved programmatically:
In backing bean method we can access AM and retrieve VO. Criteria is accessed through ViewCriteriaManager and auto execution parameter (true, false) is applied. Same code can be implemented in ADF BC Model layer, however in this sample I'm using it in backing bean, because I will invoke it from ADF Task Flow.
Sample application contains following ADF Task Flow structure:
1. adfc-config.xml - Unbounded ADF Task Flow
Includes main page and two ADF Task Flow Call activities.
2. task-flow-definition-departments - Bounded ADF Task Flow with Shared Scope
Includes page with Departments table, ADF Task Flow Call activity to pass parameter to Employees ADF Task Flow and ADF Task Flow Return activity.
3. task-flow-definition-employees - Bounded ADF Task Flow with Isolated Scope
Includes page with Employees Table and Criteria, Router activity, three Method Call activities and ADF Task Flow Return activity.
For this ADF Task Flow I have defined Isolated Scope, because BUG #8263481. According to this bug, in current release Query Criteria (af:query) is not rendered correctly when passing parameters to Shared Scope ADF Task Flow.
Based on defined logic, when passed parameter is Null, Router activity navigates to notQueryAuto Method Call (where 'false' value is passed to Backing bean method to set CRITERIA_AUTO_EXECUTE property). Otherwise, when parameter is not Null, queryAuto Method Call is invoked (where 'true' value is passed to Backing bean method to set CRITERIA_AUTO_EXECUTE property). In this case, next is invoked ExecuteWithParams method, this method is setting filter parameter for View Criteria. And at last, page is opened.
On runtime, in main page we have two options - to open Employees ADF Task Flow without parameter or with.
By pressing 'Employees' button, it will be opened with Null value and Criteria will be rendered with CRITERIA_AUTO_EXECUTE = false property:
By pressing 'Departments' button, user will get a table with Departments where he can select any row and press 'Employees' button.
Means - DepartmentId from selected row will be passed to Employees ADF Task Flow and Criteria will be rendered with CRITERIA_AUTO_EXECUTE = true property:
Spanish Summary:
En este post se muestra como lograr la ejecucion automatica en los View Objects. Para ello Andrejus desarrolla un ejemplo con 2 tablas : una de departamento y otra asociada a los empleados. El efecto de ejecucion automatica se notará cuando ustede seleccione una fila de la tabla empleados y luego se mostrará la tabla de los empleados asociados a ese departamento los cuales se cargaron automaticamente.
Saturday, February 7, 2009
Improving Performance in ADF Applications - Page Load Time in Query Forms
I have noticed, in documentation or sample applications, very basic and at the same time important problem usually is not discussed - initial Web page load time. When developer creates Web page with ADF framework, most often implementation is done in default way - on page load database is queried and row data is shown to the user. However, such default approach is very dangerous in large applications. In this post I will focus on Query type forms, CRUD type forms will describe in next post.
Yes, there are options and you need to tune ADF BC components - Advanced View Object Techniques. However, its not enough - may be someone will not agree, but only ADF BC tuning will not help you too much with application performance. Significant improvement in performance can be achieved with clever functionality design in your ADF application. In this post, I will provide one suggestion for Query type forms.
Query type forms usually contain Query area and Results area. In ADF 10g, we were hiding results on initial page load, however query still was executed in the background. In ADF 11g, this case is significantly improved and we can prevent initial query invocation - means eleminate not needed select to database. Logically thinking - without database query we will get faster page load, and its true.
Let's check a default case, when page is loaded and data is shown. In this case, on page load, ADF framework will execute SQL queries for all databound components available on the page:
SQL queries will be executed even for LOV components. This means, if your page contains 1 results table and 10 LOV's, database will receive 10 + 1 = 11 SELECT statements. Since its initial page load, there will be full SELECT for results table. Yes..., it can take some ~15 minutes just to load and present page to the user, if there are at least some 100000 rows in results table and 1000 - 5000 rows in each LOV table. And its not related to ADF BC tuning at all. Who needs a system, where page will be loaded 15 minutes? Answer is simple - no one :-)
I'm happy, in ADF 11g there is standard solution for this problem. You need to use Query Criteria in your Query type forms. Query Criteria allows you to declaratively specify not to query dataset automatically:
With such configuration, on page load ADF framework will not send any SQL queries to database and page will be rendered in blank mode:
This means, page will be rendered immediately even if there are lots of records in database.
And only when user will press Search button, SELECT statement will be sent to database to query required data:
[1051] EmployeesView1 ViewRowSetImpl.doSetWhereClause(-1, vc_temp_1, A)
[1052] ViewObject: EmployeesView1 close prepared statements...
[1053] ViewObject: EmployeesView1 Created new QUERY statement
[1054] EmployeesView1>#q computed SQLStmtBufLen: 555, actual=457, storing=487
[1055] SELECT Employees.EMPLOYEE_ID,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.EMAIL,
Employees.PHONE_NUMBER,
Employees.HIRE_DATE,
Employees.JOB_ID,
Employees.SALARY,
Employees.COMMISSION_PCT,
Employees.MANAGER_ID,
Employees.DEPARTMENT_ID FROM EMPLOYEES Employees
WHERE ( ( ( ( UPPER(Employees.FIRST_NAME)
LIKE UPPER( :vc_temp_1 || '%') )
OR ( :vc_temp_1 IS NULL ) ) ) )
[1056] Bind params for ViewObject: EmployeesView1
[1057] Binding param "vc_temp_1": A
LOV queries will be executed at that time as well (only once):
[1160] SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
Download sample application - ADFPerformance.zip
Spanish Summary:
En este post, se muestra unos tips para mejorar la ejecución de las consultas usando Jdeveloper 11g. Para lograr este propósito Andrejus nos muestra de que manera podemos usar a los Query Criteria para inicializar la consulta de datos con valores vacios y evitar recargar los formularios de datos.
Yes, there are options and you need to tune ADF BC components - Advanced View Object Techniques. However, its not enough - may be someone will not agree, but only ADF BC tuning will not help you too much with application performance. Significant improvement in performance can be achieved with clever functionality design in your ADF application. In this post, I will provide one suggestion for Query type forms.
Query type forms usually contain Query area and Results area. In ADF 10g, we were hiding results on initial page load, however query still was executed in the background. In ADF 11g, this case is significantly improved and we can prevent initial query invocation - means eleminate not needed select to database. Logically thinking - without database query we will get faster page load, and its true.
Let's check a default case, when page is loaded and data is shown. In this case, on page load, ADF framework will execute SQL queries for all databound components available on the page:
SQL queries will be executed even for LOV components. This means, if your page contains 1 results table and 10 LOV's, database will receive 10 + 1 = 11 SELECT statements. Since its initial page load, there will be full SELECT for results table. Yes..., it can take some ~15 minutes just to load and present page to the user, if there are at least some 100000 rows in results table and 1000 - 5000 rows in each LOV table. And its not related to ADF BC tuning at all. Who needs a system, where page will be loaded 15 minutes? Answer is simple - no one :-)
I'm happy, in ADF 11g there is standard solution for this problem. You need to use Query Criteria in your Query type forms. Query Criteria allows you to declaratively specify not to query dataset automatically:
With such configuration, on page load ADF framework will not send any SQL queries to database and page will be rendered in blank mode:
This means, page will be rendered immediately even if there are lots of records in database.
And only when user will press Search button, SELECT statement will be sent to database to query required data:
[1051] EmployeesView1 ViewRowSetImpl.doSetWhereClause(-1, vc_temp_1, A)
[1052] ViewObject: EmployeesView1 close prepared statements...
[1053] ViewObject: EmployeesView1 Created new QUERY statement
[1054] EmployeesView1>#q computed SQLStmtBufLen: 555, actual=457, storing=487
[1055] SELECT Employees.EMPLOYEE_ID,
Employees.FIRST_NAME,
Employees.LAST_NAME,
Employees.EMAIL,
Employees.PHONE_NUMBER,
Employees.HIRE_DATE,
Employees.JOB_ID,
Employees.SALARY,
Employees.COMMISSION_PCT,
Employees.MANAGER_ID,
Employees.DEPARTMENT_ID FROM EMPLOYEES Employees
WHERE ( ( ( ( UPPER(Employees.FIRST_NAME)
LIKE UPPER( :vc_temp_1 || '%') )
OR ( :vc_temp_1 IS NULL ) ) ) )
[1056] Bind params for ViewObject: EmployeesView1
[1057] Binding param "vc_temp_1": A
LOV queries will be executed at that time as well (only once):
[1160] SELECT Departments.DEPARTMENT_ID,
Departments.DEPARTMENT_NAME,
Departments.MANAGER_ID,
Departments.LOCATION_ID
FROM DEPARTMENTS Departments
Download sample application - ADFPerformance.zip
Spanish Summary:
En este post, se muestra unos tips para mejorar la ejecución de las consultas usando Jdeveloper 11g. Para lograr este propósito Andrejus nos muestra de que manera podemos usar a los Query Criteria para inicializar la consulta de datos con valores vacios y evitar recargar los formularios de datos.
Subscribe to:
Posts (Atom)