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:

24 comments:

Til said...

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.

Andrejus Baranovskis said...

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

Andrejus

Til said...

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.

Andrejus Baranovskis said...

Hi Til,

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

Regards,
Andrejus

Andrejus Baranovskis said...

Hi,

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

Regards,
Andrejus

Til said...

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.

Andrejus Baranovskis said...

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

Til said...

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 .

Andrejus Baranovskis said...

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

Til said...

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

Andrejus Baranovskis said...

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

Til said...

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 .

Andrejus Baranovskis said...

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

Andrejus

Til said...

Thanks a lot Andrejus :)

Anonymous said...

hello
I wonder if you can do reports in adf11g

vicente said...

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

Thanks.
Regards

Andrejus Baranovskis said...

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

Andrejus

Ray said...

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.

Andrejus Baranovskis said...

Hi,

I correctly understood, it doesnt work only on MySQL?

Andrejus

Ray said...

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?

Andrejus Baranovskis said...

Unfortunately I dont have MySQL installed, not using it.

Andrejus

SreeVardhan said...

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.

venkat said...

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.

Sameera Samarasinghe said...

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