How to define an ILike filter (case insensitive) ?

Vaadin 6 / 7 / 8
1

Hello,

I am using SQLContainer and performing a search using a filter like this :

            Container.Filter filter2 = new Or(
                    new Like("client", "%"+event.getText()+"%")
                    ,new Like("agent", "%"+event.getText()+"%")
                    ,new Like("email", "%"+event.getText()+"%")
            );

This works ok, except that I’d like the comparisions to be case insensitive.

I have seen that the Like class has a method setCaseSensitive(false) , but the resulting sql where clause is " upper(field) like ‘%value%’ " . Instead I want to obtain " field ilike ‘%value%’ " (the postgresql database supports ILIKE so I’d like to use it).

The thing that i really don’t understand is how exactly are the filters Or/And/Like/… converted into the corresponding SQL where clauses when the container is SQLContainer. When looking at the source code of Like , I am not seeing anything related to sql (https://github.com/vaadin/vaadin/blob/master/server/src/main/java/com/vaadin/data/util/filter/Like.java) .

So how could I implement my own ILike filter ?

Thanks,
Adrian

2

Hi,

take a look at
LikeTranslator
; that contains the part that creates the SQL. You’ll need a new ILike filter, which you can then match in ILikeTranslator’s translatesFilter. Add an instance of ILikeTranslator to QueryBuilder with addFilterTranslator and you should be able to use your ILike filters in code just like other filters.

Hope this helps,
Olli

3

Hi Olli ,
I’ve followed your tips and successfully implemented the ILike filter .

Many thanks for the help!
Adrian

4

No problem, glad it worked out (I didn’t actually test it or anything :)!

-Olli