Friday, April 30, 2010

Creating Simple Search form in Oracle ADF

Hi all,

Creating a search form in Oracle ADF is a matter of just few minutes just drag and drop the View Criteria and you are ready. Let me show you how easy it is. This post would be helpful for ADF newbies and people are coming from Oracle Forms background.

I assumed you have already setup the basic application with Emp EO, VO and Application module
from SCOTT schema in Oracle database. (if not check this post)

In this example:
- We will develop a simple search form which allows user to search employees records by entering Employee No OR by Partial Employee Name e.g Name Contains A .....'%A%'


Step Summary:
1) Define Bind Variables
2) Create View Criteria
3) Create Search Form by dragging and dropping Named View Criteria from Data Control to the Page
4) Refine the Layout

Step 1: Define Bind variables:

- Double click EMPVO in Model project go to query page.
- Expand the Bind variable accordion and click the Green button.


- Define 2 bind variable name and types as following make sure required check box is un-checked
  - EmployeeNo type as Number
  - EmployeeName type as String


Step 2: Create Named View Criteria:

- View Criteria is actually conditions on VOs which you usually define declaratively.
- Expand the View Criteria section in EMPVO page and and click the Green plus sign.

- Give a proper name to your View Criteria
- Click the ( ) Group press Add item and select values as shown in the slide make sure you have selected the bind variables EmployeeNo

- Select the ( )Group again  and Click "Add item" to add another condition and do as shown in the slide.

- Click OK and Save your Application.

Step 3 Create Search Form:

- Create a view activity on adfc-config page flow named as searchEmployees
- Create a simple stretched layout page with applied theme.
- Expand the Data Control panel and Drag and drop the Named View Criteria to the page and choose
Query > ADF Query Panel with Table as shown in the slide

- Your page would look like this

You are done with the search form at this stage right click and test the page

Refine Layout (Optional):

- At search form the labels of the bind variable in the search panel coming Empno and Ename which is not very informative to change the label we will changes the UI Hints in your entity which is EMP double click the EMPEO in the model project.
- Go to attributes page select the Empno field and set the Label under UI Hints as "Employee No" and set the display field as 5.
- Do the same for Ename as Employee name as width as 20

- To stretch the result table on runtime we will add a PanelCollection around our Table component.
- Goto the ViewController project right click the af:table component and Choose "Surround with" and select "PanelCollection" and click OK

- We just added a PanelCollection so we have to change the ResultComponentId reference in the querypanel.
- Select the querypanel component in the Structure window and set the property using the Edit and choose your af:table component

- Set the Headings of the PanelHeader as "Search Employee"

- Run the form enter Employee Number 7788 and press Search Result would be like this

- Enter 'A' in the Employee name fields and Press Search you will get similar results


Feel free to comment.

Download the Workspace
Note: Change the connection settings before running this sample workspace

Cheers,
Baig

60 comments:

  1. Hello Zeeshan Baig, I am newbie.
    This is good one, I tried it works fine. I have question.

    Why in Search region the Employee No and Name fields not shown at design time. When we run this application, these fields are shown correctly. Is there a way to see these fields at design and make some visual attribute changes to it.

    thanks
    Surendra

    ReplyDelete
  2. Hi Mandalam,
    ODF Render the query fields on runtime due to bind variables it is not possible to view on the design time. check this link might help you
    http://download.oracle.com/docs/cd/E12839_01/web.1111/b31973/af_query.htm#BGBHHAHF

    ReplyDelete
  3. Hi Zeeshan Baig,

    This is very good tutorial. It helps me a lot. I am new for JDeveloper. I make a URL-Data-Control by 11g which extract the data from XML. Is it possibly I can add search form with quick search: Like type in partial of the name and find full name which in my list. In Data Controls which project can I trigger to continue work on query page?

    Thanks for help!

    Jimmy Lee

    ReplyDelete
  4. Hi Jimmy,

    Thanks for comments.

    I haven't used URL Data control except just simple displaying data from CSV and XML. I hope searching also possible by custom methods. i will try to figure it out if any success i will let you know.

    Keep watching this blog.

    Take care,
    Baig

    ReplyDelete
  5. Hi Baig

    In a query panel, is it possible to change the query criteria fields dynamically, depending on one of the selected values of a criteria field ?
    For example, When a criteria field 'Master Org' value is selected as 001, another Criteria field values should be all the child Orgs of the org 001.

    Is it possible to customize / extend the ADF Query panel with Table to achieve this PPR ?
    Or is there any other better approach ?

    Thanks for any help.
    Sameer

    ReplyDelete
  6. Hi,

    are you talking abt cascading LOVs in query panel?

    i found some via google http://forums.oracle.com/forums/message.jspa?messageID=3952958#3952958

    ReplyDelete
  7. Thanks, This is very handy. How do I extend so that I can select a row and make it editable - either in same table or into a new page?

    ReplyDelete
  8. Hi Srikanth,

    You can simply create 2 view activities in task flow and draw a navigation between them.

    You can look into this example which i remember i hv created a navigation between 2 pages

    http://baigsorcl.blogspot.com/2010/10/display-images-in-carousel-item-in.html

    ReplyDelete
  9. hi, how could select multiple check boxes at once with another component adf, thank you very much for your help, Greetings

    ReplyDelete
  10. Hi,
    I am new with J Developer,
    This is very good tutorial. It helps me a lot.
    I have install J developer but previously i was using D2k so for J Developer i have to install 11g data base.
    am i right or wrong?
    i want to work on j developer. please guide me what are the things required for work in J-Developer ?

    Advance Thanks for help!

    Damy

    ReplyDelete
  11. Plz contact me using Contact me page. Jdeveloper can work with many databases but minimum requirement is i guess 9iR2.

    11g is good if you want to work with low RAM tehn go for Oracle 10g XE

    ReplyDelete
  12. Read your nice blog about the seach. This is what I needed. However, there were few more customizations which I need and need your help:

    1. Search button rename. Remove Reset field.

    2. I have a "Input Text with List of Values". We would want to send the "Text" entered in the first page and click on the search icon.

    ReplyDelete
  13. Hi Guru,

    You can do this by a simple method. Try drag and drop ExecuteWithParams operation from Data Control Collection after creating bind variables.

    In this way you don't need to hack af:query component and you would have only one button for search.

    Regards,
    ZB

    ReplyDelete
  14. hi
    nice tutorial , i have one question i want to develop a custom search with like start-date and end-date i want to display data b/w those date but in my db table i have only run_time with Timestamp data type,how to create two fields in view with out db and how to bind that dates to Run Time beacuse i have to display record b/w two run dates .
    can you please resolve this case please needful

    Thanks
    Shankar

    ReplyDelete
  15. Hi,

    If timestamp is view object attribute then ADF is intelligent enough to pick timestamp and it will display date picker with time option.

    As far as creating a search without Model/DB you have to create query component based on Java class a complete details can be found here http://download.oracle.com/docs/cd/E17904_01/web.1111/b31973/af_query.htm#BABDIHBA

    Hope it helps,
    ZB

    ReplyDelete
  16. Hi Baig,

    I want to implement the same with trinidad faces. I am unable to get the query panel.

    Can you please help

    Thnaks,
    Bm

    ReplyDelete
  17. I am not aware of Trinidad components i have to check.

    ReplyDelete
  18. Hi Baig,

    I want to create search form in ADF 11g without query component as query component is not fulfilling my business requirements especially visually arrangement of components. So what i did is, i added input text components for criteria on page and binded with managed bean of request scope. I added a button for search and inside actionListener, i created where clause with criteria entered and appended to ViewObject which was dropped on page as table to display records. It worked fine but there were some fields in criteria which should be inputListOfValues, so i replaced inputText with inputListOfValues and setting model property with corresponding attribute of VO (dropped as table and has attached view accessors). It worked fine but problem is that when there is no record in VO, these inputListOfValues do not function and give me error about NullPointerException. One way to resolve this by technique mentioned in http://jjzheng.blogspot.com/2010/06/build-custom-search-in-adf-11g.html but i was wondering how it will behave in activation and passivation of application module.

    I am using Jdeveloper 11.1.1.5.0

    Regards,
    Amer Sohail

    ReplyDelete
  19. Hi aamir,

    You can have some bind variables in your VO, drop ExecuteWithParams on the page from datacontrol and change the components types as you like.

    How many concurrent users you are expecting? i guess you don't have to worry about application module this you always tune.

    ZB

    ReplyDelete
  20. Hi,

    I have created a search form.My Form contains 4 search fields and first input text length is very i.e to the end of page I want to make it small.Can U pls let me know how to reduce its size ,in source i did not found any property to reduce size.

    Thanks,
    Srinivas.

    ReplyDelete
  21. Hi Srinivas,

    You can change the width by changing its width property in Entity/View object

    ZB

    ReplyDelete
    Replies
    1. dear Mr.baig ,

      good day ,

      how are you , i hope you are fine .

      i watched most of your video about oracle adf , i find it very usefull , god bless you .

      i want to ask how to create dynamic menu driven from database , wether to use menu item , tree or accordian panel .

      thank you .

      Delete
    2. Where are you setting the bind parameters to the form Eg: Emp No and Emp Name.

      It says no search fields added.


      Thanks,

      Delete
    3. This is search form it will display you fields on run time.

      Delete
  22. I have some requirement in adf search, i have three tables all the tables contains one of the column name as ID, and remaining are different fields. three tables are independent.
    what my requirement is in search panel with table, i want search to enter id filed and tables name as drop down or radio button to search ID details from the particular table.
    please let me know this is possible in adf search or give me some alternatives on the same.

    Regards
    siva

    ReplyDelete
  23. Hi
    Is there a way to see these fields at design.

    ReplyDelete
  24. Hi Baig,

    I've added a in variable and I would like to set it programmaticaly before opening a page, how can I implement that ? Thx in advance

    ReplyDelete
    Replies
    1. What kind of variable it is? in JSF you usually have Bean Scopes to handle variable life.

      If you better explain your use case then i can answer better.

      ZB

      Delete
  25. Hi Zeeshan,

    I am new on ADF, I have one query. How can we show only search button on search page, I don't want use Reset and Save Button. Please guide me thanx a lot.

    Nishant

    ReplyDelete
    Replies
    1. You can use ExecuteWithParams operations with Bind variable google it you will find many examples.

      ZB

      Delete
  26. Just want to say , THANK YOU! I'm able to follow your step by step successfully! a BIG thanks :)

    ReplyDelete
  27. Hi,

    I am new to ADF, The above article is very nice.

    I have a requirment to develop a search form which will
    get result from 4 different query and parameter is same for all query. Basically we can say its report type.
    When I pass parameter and click on search, it should fetch data from all 4 query and display separately.
    How can I achive it.

    from the above I understand how I can make it possible from one view object and one parameter.
    My case is 4 view object and one parameter(same for all 4 query),

    Could you please advice

    Thanks in advance

    ReplyDelete
    Replies
    1. Hi,

      Yes you can create one method in application module which takes input and set bind variable values to all view objects.

      Expose that method to data control and use on JSF Page.

      Hope it helps,
      ZB

      Delete
  28. Hi,

    Good Day.

    Is there a way with out creating Entity Object and View Object, directly we can use the PLSQL procedure for business logic and get the input parameter as search form and call the store procedure for fetching required data and display it in ADF page as report structure??

    Any advice will appreciate.

    ReplyDelete
    Replies
    1. Hi,

      Yes you can write call PL/SQL procedures and expose them as service method to data control to use on JSF pages.

      check this link http://www.baigzeeshan.com/2010/05/calling-plsql-procedure-and-function-in.html

      ZB

      Delete
  29. Hi Zeeshan,

    This example shows us that Search can be Personalized and Saved. Well and Good. How about the following scenario?
    I and my teammate have two logins to the application.
    Both use the same search page. I have personalized the search by adding more fields and saved the search and my colleague have saved a different criteria. We need to see this Saved Search Criteria the next time we logs back to the application.

    Please help.

    ReplyDelete
  30. Hi,

    Yes you can do this using MDS feature of ADF. Google you will find many example of same scenario.

    ZB

    ReplyDelete
  31. Hi Baig,

    Nice article, I have few question as per my requirement. I have 3 input box and table to search on. I am using Managed Bean and AM to do search. But as per requirements I can search on input text by filling 1 alone,2 alone, 1+2, 1+2+3, 2+3 values in input boxes.

    I have created a VC in which I have a group with 3 items and 3 bind variable with AND conditions. Now to search using above combination, what approach should I follow. Pls suggest.

    Thanks, Ank

    ReplyDelete
    Replies
    1. Hi,
      Use OR condition instead of AND with bind variables also try to simplify equation by running your command in SQL Developer then move to ADF.

      Hope it helps,
      Z

      Delete
  32. Hey, Can you please tell me how to perform search by joining 2 tables. Suppose I have dept and emp table and I want to display the emp details by searching through the deptname. Please suggest me a way to do so!! Thank you in advance.

    ReplyDelete
    Replies
    1. Hi,

      You can create view criteria using SQL EXISTS clause. Check this post http://one-size-doesnt-fit-all.blogspot.com/2011/02/adf-bc-creating-exists-view-criteria.html

      Zeeshan Baig

      Delete
  33. Hi, Can you tell me one solution. I am new to ADF. I have a login page, when I logged in it select user's profile id and set a loginformbean with few relevant information. It navigate's to main report seletion page in which one selection box shows list of reports available from an ADF viewobject and there is Another listbox which supposed to show reports already selecte by the user from an ADF Object which has a bindvariable in whereclause. When I test in from AppModule passing value it works fine. Can you tell me how to pass this bindvariable (say userid which I defined in View Object query)? Shall I programetically locate ViewObject and set this variable and then execute it once and then do the navigation the main report page? I am not getting user's selected report.

    ReplyDelete
    Replies
    1. Hi,

      When you expand your ViewObject instance in Data control you can your bind variables in ExecuteWithParams operation. Just drag and drop your bind variable or assign value to it programitcally through Attribute Bindings.

      Hope it helps,
      Z

      Delete
  34. Hi,
    can you help me to make the all the fields in search panel as mandatory.Means after enter the dept id and emp id only,the search button should be visible.

    ReplyDelete
    Replies
    1. Hi,

      You can create search form creating bind variables in VO then drop Execute with params operation from data control.

      Zeeshan

      Delete
  35. Hi,
    Thanks for the post.

    How to change the fields labels in the search form. The search form shows the same label in the view object which is not user friendly. for example the in Employee table. Instead of (Fname) I need to have in the label (First Name). Note, I could change the fields names in the table but not in the search form .

    ReplyDelete
    Replies
    1. Hi,
      Change the Display Name property of attribute you can also change the order of fields in the attributes tab of View object by click Set Source order.

      Zeeshan

      Delete
  36. Hi,
    I am using af:inputListOfValues to display a popup window.Is there any way to adjust the size of the popup window? Kindly advise..

    Thanks in advance...

    Regards
    Dileep

    ReplyDelete
  37. Hi,
    Can we convert the search input text boxes (employee no/employee name) to LOV and data fetched into them from database table.

    Thanks in advance !!

    Regards,
    Binu

    ReplyDelete
    Replies
    1. Yes you can change the attribute type in View Object.

      Delete
  38. Hi Baig,
    I am new in learning the ropes on ADF development and was working to implement a search screen. It would have more than one search fields that a user can select to view the data set. I have designed the fields as selectoneChoice LOV item in the left half of the screen. These are based on view objects defined on seperate queries each based on more than one table. When user clicks the search button the LOV values are to be assembled and passed to invoke a stored procedure. This db object would populate a custom table with data set based on search criteria. The data set would be shown on the right half of the page wherein user would be able to select certain records for further processing. Is there a simple way to implement this or is it too cumbersome functionality I have thought of?

    ReplyDelete
    Replies
    1. Hi,

      If you can afford the performance on the page then you can do it using custom application module methods. Write a custom method to call your stored procedure and pass parameters to it. Build your page on your custom table.

      To me this does not sound like a good idea to build a search criteria on runtime and display to users. Search should be simple and quick. If it is a requirement then I would say work with your architect to provide pre-populated data and build your application on that table.


      Cheers,
      Zeeshan

      Delete
  39. hi,

    i used this code to get data using first name and email

    SELECT EMPLOYEES.EMPLOYEE_ID EMPLOYEE_ID,
    EMPLOYEES.FIRST_NAME,
    EMPLOYEES.LAST_NAME,
    DEPARTMENTS.DEPARTMENT_NAME DEPARTMENT_NAME, DEPARTMENTS.MANAGER_ID MANAGER_ID

    FROM
    EMPLOYEES INNER JOIN DEPARTMENTS ON EMPLOYEES.EMPLOYEE_ID = DEPARTMENTS.DEPARTMENT_ID

    WHERE
    EMAIL = :p_email OR FIRST_NAME = :p_firstname

    what can i add to this code to display all data records if the first name and email is left empty?

    ReplyDelete
    Replies
    1. Easy solution would be... write a query with DECODE for example where email = decode( nvl(:p_email,'null'),'null',email,:p_email)

      Delete
  40. Hi,

    Can we add more than one tables in the resultcomponentids? if we want to use single search form to populate more than one resulting tables.

    ReplyDelete
    Replies
    1. Hi,

      In a modern / web application it is not ideal to have multiple search forms on single page. You always filter requests as efficient you can. If you really have to do you have to manually set the bind variable to multiple VO you can do easily by creating custom form based on managed beans.

      Regards,
      Zeeshan

      Delete
  41. Hi Zeeshan Baig,
    When any saved search value is selected(which is saved previously)
    Is it possible to clear all the search fields including saved search soc after clicking on reset button (Jdev 12.1.3, using file based MDS repository)

    ReplyDelete