Thursday, June 24, 2010

Manually Assign new value to sequence column

Sometime we need to manully assign a new value to a sequence column. For example, we have a department table with primary key column department_id, which is based on a sequence "department_sequence".

When we try to create a new record in the department table, we will do the following in the AMImpl.java:

*************************************************************
DepartmentVOImpl vo = getDepartmentVOImpl();
vo.clearCache();
Row row = vo.createRow();

SequenceImpl sequence = new SequenceImpl("department_sequence, getDBTransaction());
Number dept_id = sequence.getSequenceNumber();

row.setAttribute("DepartmentId", dept_id);

//set other attribute values...

vo.insertRow(row);
getDBTransaction().commit();
*************************************************************

Wednesday, June 23, 2010

Post Ordering Issue

I have a previous post about the post ordering constraints error. The situation in that post might be somewhat special: the association between two entity objects (master and detail) is a DBSequence in the master table.

Here is a working solution to the generic post ordering issue.

Steve Muench has an article "Forcing a New Dept to Post Before a New Emp Without Composition", which presented two ways to solve the post ordering contraints:

1. Override postChanges() method in the detail/child entity object Implementation java file. For example, if Dept and Emp table, then write below codes in EmpImpl.java file:



2. An even simpler way:


Steve also has a very good article on "Most Commonly Used Methods in ADF Business Components" which lists all common used methods in EO, VO, AM, etc.

Monday, June 21, 2010

Setup SSL Certificate for PayFlow Gateway in Weblogic 10.3

PayFlow Pro is a gateway provided by PayPal Inc for payment transactions, like credit card transaction. To use payFlow gateway in ADF application, you have to install the payFlow SDK from Paypal. You can find info in here.

After installation of payFlow processor you can download the payflow Java Library and write codes to process the transaction.

But before that, you have to set up the SSL on your server to make it work. The SSL is installed on your development server to recognize the PayFlow's transaction process server, therefore the SSL has to be installed into the trust keystore on the weblogic server.

For test and development purpose, the SSL certificate can just added to the default existing keystore in Weblogic server. The steps are here:

1. download the SSL certificate that can recognize PayFlow server. It can be downloaded from either A or C link.The download link is here.

2. Use keytool command to import the certificate into the existing trust keystore for development use. The syntax of the command is (assuming the certificate file is put under jdk directory \jre\lib\security in JDeveloper install directory. This directory is arbitrary):

*JDeveloper_Install_Directory*\jdk*version*\jre\bin\keytool -import -alias paypal -keystore *JDeveloper_Install_Directory*\wlserver_10.3\server\lib\DemoTrust.jks -trustcacerts -file *JDeveloper_Install_Directory*\jdk160_14_R27.6.5-32\jre\lib\security\72fa7371.cer

DemoTrust.jks is the default trust keystore. "72fa7371.cer" is the downloaded certificate. You will be prompted to enter the keystore password to import, the password for DemoTrust keystore is "DemoTrustKeyStorePassPhrase"

3. The DemoTrust Keystore is already use in weblogic server by default so nothing needs to be changed in weblogic admin console. But if you want to see the configuration in weblogic console, go to server, under configuration tab, there are Keystore tab and SSL tab, that's where you setup the identity keystore and trust keystore.

4. Restart the weblogic server and you shall be able to use the paypal payFlow process to process any credit card transaction using your weblogic server.

In case of production instance, the custom keystore has to be created and configured in Weblogic console. Here are steps for production instace:

1. Create the private key for standalone weblogic server and send the key to CA (e.g.: Verisign) for certificate.

2. Create an identity keystore for weblogic server using java keytool command.

3. Import key/certificate pair into the identity keystore using weblogic command ImportPrivateKey (keytool command cannot do this step)

4. Import Verisign certificate which could recognize payflow process server to weblogic trust keystore (The current certificate is noted on paypal’s development site, name is “72fa7371.cer”)

5. Change the settings in Weblogic Console to use the custom identy keystore and trust keystore.

Useful links:
A. ***Important Notice Regarding The Payflow Gateway***
https://www.x.com/docs/DOC-1675

B. ***PayFlow gateway documents***
https://www.x.com/docs/DOC-1444

C. ***Certificate download link from Verisign***
https://www.verisign.com/support/roots.html

D. ***Certificate Install Instructions***
https://knowledge.verisign.com/support/ssl-certificates-support/index?page=content&id=AR212&actp=LIST
http://www.geocerts.com/install/weblogic_8

E. ***Configure keystores in Weblogic Admin Console***
http://download.oracle.com/docs/cd/E14571_01/apirefs.1111/e13952/taskhelp/security/ConfigureKeystoresAndSSL.html

F. ***KeyTool Command***
http://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html
http://www.informit.com/articles/article.aspx?p=407886&seqNum=2
http://publib.boulder.ibm.com/infocenter/wbihelp/v6rxmx/index.jsp?topic=/com.ibm.wbia_adapters.doc/doc/sap_xi/sapximst85.htm

G. ***No identity key/certificate entry was found***
http://objectmix.com/weblogic/564019-ssl-custom-keystores-question.html

H. ***Weblogic ImportPrivateKey Command***
http://download.oracle.com/docs/cd/E14571_01/web.1111/e13749/utils.htm#i1176073

I. ***Setup Weblogic Server Command Enviorment***
http://download.oracle.com/docs/cd/E14571_01/web.1111/e13749/weblogicserver.htm#i1008498

Thursday, June 10, 2010

Implement Cascading lists of values in ADF 11g

In many cases, we will have to use cascading lists of values, also called dependent lists of values in our ADF application. Cascading lists of values, in its name, means one set of list of values are dependent on selected value on another set of list of values.

How does it work is apply view criteria to a model driven list of value.

Here I present one example in HR schema. Let's say we have a list of departments, when one department value is selected, a second list of values - manager will be set to managers within the selected departments, a third list of values - employees will be set to employees under the selected manager.

First, we create our base VO - EmployeesVO (can be created from Entity object also)


Second, we create 3 list of values for 3 attributes we are interested in EmployeesVO: DepartmentId, ManagerId, EmployeeId.

Each list of value needs a view accessor (data source for the LOV). 

DepartmentId attribute needs a view accessor from DeptListVO, which is based on query "select department_id, department_name from departments". 



































ManagerId attribute needs a view accessor from EmployeesVO itself with additional attributes: Firstname and LastName.


ManagerId is based on selected departmentId, so we have to create a view criteria on its view accessor - EmployeesVO.

Then we have to add the view criteria to the view accessor. In the "Bind Parameter Values" section, type in the value "DepartmentId' using Groovy script.


EmployeeId attribute needs a view accesor from EmployeesVO like managerId. Create a LOV based on EmployeesVO view accessor.




































Employee is based on selected managerId. So a view criteria needs to be applied on its view accessor - EmployeesVO.


































Similarly, we will add the view criteria into the view accessor.
Now we create the UI for the list of values.

Please note for the UI to work with dependent lists of values, the properties of the parent LOV has to be set as: "AutoSubmit: true" and the partial trigger property of its child LOV has to be set as its parent LOV id.

Finally it works as this:

1. select a department id.


2. the manager Id then displays only the ones under the selected department.


3. the employees then display only the ones under the selected manager.

Wednesday, June 9, 2010

Build Custom Search in ADF 11g

Although ADF 11g provides us with declarative search component for a search functionality, in many cases it still cannot satisfy the business requirements. Here I am presenting a way to build a custom search page without using build-in search component in ADF 11g. This example was built under HR schema to search employees using tables: employees, departments, jobs, locations, countries. Normally, we will catch the user input in managed beans and send the user input back to our model, and explicitly run the executeQuery() against the iterator that binds to the results table.

here I did something differently....
I am using another VO with transient attributes to catch the search criteria user inputs, and send these inputs into the VO iterator which binds the search results table. So when user inputs search criteria, the changes will retain in a VO Row and I will user getter method to catch the changes and send the changes to the VO that I can run executeQuery() to do the search.

To begin, I have wrote the query for the search:
select  EMP.EMPLOYEE_ID,
        EMP.LAST_NAME || ', ' || EMP.FIRST_NAME Name,
        EMP.EMAIL,
        EMP.PHONE_NUMBER,
        EMP.HIRE_DATE,
        JOB.JOB_TITLE,
        EMP.SALARY,
        MGR.FIRST_NAME || ' ' || MGR.LAST_NAME Manager,
        DEPT.DEPARTMENT_NAME,
        LOC.CITY || ' ' || COUNTRY.COUNTRY_NAME ADDRESS
from    employees emp,
        jobs job,
        employees mgr,
        departments dept,
        locations loc,
        countries country
where   EMP.JOB_ID = JOB.JOB_ID
  AND   EMP.MANAGER_ID  = MGR.EMPLOYEE_ID (+)
  AND   EMP.DEPARTMENT_ID  = DEPT.DEPARTMENT_ID (+)
  AND  DEPT.LOCATION_ID = LOC.LOCATION_ID (+)
  AND  LOC.COUNTRY_ID = COUNTRY.COUNTRY_ID (+)
  AND  EMP.EMPLOYEE_ID = :1
  AND  UPPER(EMP.FIRST_NAME) LIKE '%' || UPPER(:2) || '%'
  AND  UPPER(EMP.LAST_NAME) LIKE '%' || UPPER(:3) || '%'
  AND  UPPER(EMP.EMAIL) LIKE '%' || UPPER(:4) || '%'
  AND  EMP.HIRE_DATE = TO_DATE(:5, 'YYYY-MM-DD')
  AND  JOB.JOB_TITLE = :6
  AND  EMP.SALARY BETWEEN :7 AND :8
  AND  UPPER(MGR.FIRST_NAME || ' ' || MGR.LAST_NAME) LIKE '%' || UPPER(:9) || '%'
  AND  DEPT.DEPARTMENT_NAME = :10
  AND  LOC.CITY = :11
  AND  COUNTRY.COUNTRY_NAME = :12
 
The where clause will not included in the VO and will be attached to the query at run time.
First, create our criteriaVO. Choose option "Rows populated programatically, not based on a query".
Then manually create attribute in this VO, note you have to choose option "updatable: always", because we will create an empty row on this VO in order to hold user inputs.



After creating all the attributes in CriteriaVO, we will have to create the ResultVO which is the base VO for the search result data model. Since it's a search query and we are not about to edit the data, we will use read-only VO based on query option.


In the query panel, we will just put the query without where clause, since we will attach the where clause at run time with other search parameters and user inputs.


After creating the two VOs, we will create the initiate Search method and execute search method in Application Module.

The first method we created was the initPage() method. One thing worthy to note, to avoid the resultsVO gets executed when the page renders, we use VO method executeEmptyRowSet() to reduce the rendering time.



We also inserted an empty row in the CriteriaVO because when the page renders, we will have to let the user to input in the search critiera. In the meantime, I have created a RowSetIterator at run time to delete any existing rows left over in the previous user inputs (if user want to do a reset, clear the search inputs and do an another search)

The second method is the executeQuery() method:

There is one important method in executeSearch we used: "setNestedSelectForFullSql(false)" to suppress the inline view for expert mode queries. Please check Steve Muench's article about it.

Then you will need to expose the two methods to the client interface.

Next would be the UI implementation part.

I have created a bounded task flow for the search functionality. The default activity is the initPage() method we defined in the application module, which will avoid ResultsVO automatic query and create an empty row in CriteriaVO. After execution of initPage(), is Employee search page fragment.


In the Search page, we have search criteria, search results table, and a search button and reset button.



The Search button binds with the action method "executeSearch" and reset button binds with "initPage" action method.

Please check the action methods bind to the button in the actionListener: #{bindings.executeSearch.execute} and #{bindings.initPage.execute}.

If let's say, we don't have actionListener binding to #{bindings.initPage.execute}, instead we will have a navigation flow to initPage() action activity in the task flow for the reset button. This will lead to unexpected situations that the reset button will not work (not clearing out the user input in the search criteria). This is because the execution sequences between action and actionListener in the button property inspector.

The final search page looks like this:





We can use "#{bindings.SearchResultsIterator.estimatedRowCount}" to show number of rows that have been returned against the search.

 Downlink for the applications: CustomEmpSearch.zip