Saturday, May 22, 2010

Yes-No Check Box in Query Criteria

There are several posts on internet where it is described how to implement Yes-No (Y-N) type check box using ADF Faces boolean check box component. However, when you want to use Yes-No check box in Query Criteria, there is one additional trick should be applied - I will explain it today.

Download sample application - YesNoSearchBox.zip. In order to run this sample, you need to create additional column in Regions table from HR schema. Make sure you name this column correctly - YES_NO:


For demonstration purpose, I have created static View Object to bring Yes-No values LOV:


Next, I have defined Choice List type LOV for YesNo attribute:


For the same YesNo attribute, make sure that Check Box is specified as Control Type attribute. This will render Check Box in JSF page:


On runtime, YesNo attribute is rendered in Query Criteria and table column:


While it works well, it is not working in Query Criteria. The problem is that Query Criteria block is applying true/false for check box, even it is based on Yes-No LOV component in the Model. Then of course, it is trying to query database with true/false values - and result set is empty, because it expects Y or N.

In order to fix this, make sure you have defined Bind Variable for YesNo attribute in View Criteria:


And we need to create View Object Implementation class, where ADF BC executeQuery() method will be overriden:


This method is accessing View Criteria row and retrieving YesNo criteria attribute value. Retrieved value is compared to true and and Bind Variable value is set to Y. Otherwise, if YesNo criteria attribute value is not equal true, we set Bind Variable value to NULL - this will return all rows:


Querying only those rows with Y:


All rows:

at

26 comments:

  1. TilMay 25, 2010 at 9:51 AM

    thanks Andrejus great post
    but i have a question
    it is required from me not to make search criteria filter automatically i mean when i write some thing in search criteria and didn't press save and go another page when i cam back to the page that have search criteria it will filter with the data in its search criteria when the page load i want
    1. not to make the search criteria execute when the page open if there data in search criteria
    0r
    2. empty the search criteria before go back to the seach criteria page

    is this possible
    thanks in advance.

    ReplyDelete
  2. Andrej BaranovskijMay 25, 2010 at 9:59 AM

    Sounds like too many requirements, can you explain more simple? :)

    Andrejus

    ReplyDelete
  3. TilMay 25, 2010 at 10:51 AM

    hi Andrejus ,
    i have two jsff pages on task flow
    view page and edit page

    the view page have search criteria

    when i write some thing in the search criteria and didn't press search and go to the edit page

    when i came page to the view page from the edit page by pressing back button the search criteria in the view page is executed with the values in it .

    i want when enter the view page if the search criteria have values in it not to be executed untill i press search .

    if this is not clear i can make example and send to u .

    thanks in advance.

    ReplyDelete
  4. Andrej BaranovskijMay 25, 2010 at 11:44 AM

    Hi Til,

    Its clear now, I will look into it and will post update, probably tonight.

    Regards,
    Andrejus

    ReplyDelete
  5. Andrej BaranovskijMay 25, 2010 at 9:40 PM

    Hi,

    You will get blank edit page, if you go to edit with doing query first - iterator will be empty.

    Regards,
    Andrejus

    ReplyDelete
  6. TilMay 26, 2010 at 8:36 AM

    Thanks Andrejus but i think you didn't got what i mean

    This is the example that i mean

    http://rapidshare.com/files/391651084/HRTest.rar.html


    If you Run the index page you will get the table will all rows in it.

    if you write brazil in the Country Name and didn't Press Search but go to edit page .
    when you came back from the edit page to the main page you will see that it will filter the table in the viewAll page with brazil i don't want that

    i want when i cam back from the edit page if i didn't press search the page remains as i left it.

    i wish you run this example and test this .
    thanks in advance.

    ReplyDelete
  7. Andrej BaranovskijMay 26, 2010 at 9:09 AM

    Hi Til,

    I checked your sample app. You need to set Query Automatically = false UI Hint for View Criteria in View Object. Then it will query only when user will press Search.

    Its always recommended to use Query Automatically = false for better performance.

    Regards,
    Andrejus

    ReplyDelete
  8. TilMay 26, 2010 at 9:19 AM

    Thanks Andrejus but this problem happens also when i
    set Query Automatically = false
    i tried this
    the solution i guess will help this is not the better but i have i want to call Reset button in search criteria before i enter the page
    How could i do this .

    ReplyDelete
  9. Andrej BaranovskijMay 26, 2010 at 9:30 AM

    Hi,

    I tried with Query Automatically = false and automatic query is not happening on your sample, its why I suggested it :)

    I type Brazil, press Add New Row buttons - another task flow region is opening with Edit and there I press Cancel - no automatic query.

    Please check better ;)

    Andrejus

    ReplyDelete
  10. TilMay 26, 2010 at 10:19 AM

    Thanks Andrejus :)
    but the senario i want you to make is that

    1)keep all fields empty
    2)press search this will make to view all queries
    3)type Brazil in the country name
    3)press ass button
    4)press cancel

    this will filter the criteria with Brazil

    :)

    this is the trick

    ReplyDelete
  11. Andrej BaranovskijMay 26, 2010 at 10:38 AM

    Hi Til,

    I see - initial specification was incorrect :)

    Yes, I reproduced your problem. In order to fix it, make sure you have Query Automatically = false and additionally in viewAllCountriesPageDef - make sure CountriesVOCriteriaQuery is set with TrackQueryPerformed=Page. It will not bring any results, when returning from Edit region.

    Regards,
    Andrejus

    ReplyDelete
  12. TilMay 26, 2010 at 10:50 AM

    Thanks Andrejus :) this is good
    i want to ask you last question
    is it possible to call reset
    every time i enter the viewall page to get all rows or no.

    thanks a lot
    Great post .

    ReplyDelete
  13. Andrej BaranovskijMay 26, 2010 at 11:05 AM

    Yeah, its possible to do this programmatically. I have new blog post scheduled for this weekend, where I will describe it ;)

    Andrejus

    ReplyDelete
  14. TilMay 26, 2010 at 12:32 PM

    Thanks a lot Andrejus :)

    ReplyDelete
  15. AnonymousMay 28, 2010 at 2:26 AM

    hello
    I wonder if you can do reports in adf11g

    ReplyDelete
  16. UnknownAugust 17, 2010 at 1:34 PM

    Hello Andrejus,
    I´m using adf query component...
    How can i add a LOV with autosuggest behaviour inside the query criteria?

    Thanks.
    Regards

    ReplyDelete
  17. Andrej BaranovskijAugust 17, 2010 at 5:57 PM

    Hi, you can't add it inside af:query.

    Andrejus

    ReplyDelete
  18. RayApril 20, 2011 at 9:37 PM

    Hi Andrejus,

    I know this post is a bit old, but hopefully you'll get notification of this comment.

    I tried to implement this in MySQL to varying success.

    The checkbox search seams to work, but it apparently breaks the rest of search capability. For example, you can't search w/ RegionName. I verify that you still can search w/ RegionName using an Oracle db (at least w/ Express Edition).

    I reimplemented your example in MySQL (slight variations in names as when I tried to just refactor to MySQL it didnt work), and is available here

    https://rapidshare.com/files/458398787/YesNoSearchBox_MySQL.zip

    I've also included a sql file to build and populate the region table.

    Thanks.

    ReplyDelete
  19. Andrej BaranovskijApril 20, 2011 at 9:46 PM

    Hi,

    I correctly understood, it doesnt work only on MySQL?

    Andrejus

    ReplyDelete
  20. RayApril 20, 2011 at 10:04 PM

    That's correct. I used your project in Oracle Express Edition and it works (though I had to fix a line of code)

    In MySQL, you can search with the Yes-No check box, and the correct rows are returned. If you type in a RegionName to search again, no rows are ever found. RegionName HAS to be blank, which pretty much leaves the Check Box as the only way to search which isn't very useful.

    Ideas?

    ReplyDelete
  21. Andrej BaranovskijApril 20, 2011 at 10:19 PM

    Unfortunately I dont have MySQL installed, not using it.

    Andrejus

    ReplyDelete
  22. SreeVardhanMay 7, 2013 at 4:34 PM

    Hi Andreju,
    I've the same use in my application. When I check the check box first, its setting the value for the first time. If I uncheck, its not unchecking the value in the model. How can I get the new values for these check boxes in my VOImpl.

    Thank You.

    ReplyDelete
  23. venkatAugust 26, 2013 at 10:34 AM

    Great post Andrejus,

    I was implementing a similar usecase. but the only difference is in my case, the YES_NO column is saved as a Number datatype(Oracle DB). And, the possible values it can have are 1/0.

    But when i deploy and go to the JSF page, i am getting following Error.

    JBO-25009. Can not convert from java.lang.Boolean to oracle.jbo.domain.Number.

    I Guess, there is some type cast exception because, i made YESNO view attribute to be rendered as checkbox.

    Also, i did not understand the importance of LOV . In my case i did not have LOV in my project.

    How to get rid of this JBO-25009 error.

    ReplyDelete
  24. UnknownApril 4, 2014 at 12:55 PM

    Hi Andrejus,
    I have a acquirement to give auto suggest behavior to an attribute which is in a query criteria. There are so many posts on how to do it in forms.
    Is there a way to do this?

    Regards!
    Sameera

    ReplyDelete
  25. rahulOctober 2, 2016 at 11:19 AM

    HI
    can I have this application getting page not found error upon clicking the clink to download the application.

    Thanks for your time.

    ReplyDelete
  26. Andrej BaranovskijOctober 2, 2016 at 12:48 PM

    I have updated download link, try now.

    Andrejus

    ReplyDelete