Gaim Gaim - 1 year ago 366
Java Question

JPA2: Case-insensitive like matching anywhere

I have been using Hibernate Restrictions in JPA 1.0 ( Hibernate driver ). There is defined

Restrictions.ilike("column","keyword", MatchMode.ANYWHERE)
which tests if the keyword matching the column anywhere and it is case-insensitive.

Now, I am using JPA 2.0 with EclipseLink as driver so I have to use "Restrictions" build-in JPA 2.0. I found
and method
, I have also found out how to make it matching anywhere ( although it is aweful and manual ), but still I haven't figured out how to do it case-insensitive.

There is my current aweful solution:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<User> query = builder.createQuery(User.class);
EntityType<User> type = em.getMetamodel().entity(User.class);
Root<User> root = query.from(User.class);

// Where
// important passage of code for question
query.where(builder.or("username", String.class)), "%" + keyword + "%"),"firstname", String.class)), "%" + keyword + "%"),"lastname", String.class)), "%" + keyword + "%")

// Order By

// Execute
return em.createQuery(query).
setMaxResults(PAGE_SIZE + 1).
setFirstResult((page - 1) * PAGE_SIZE).


Is there any function like in Hibernate driver?

Am I using the JPA 2.0 criteria correctly? This is awkward and uncomfortable solution in compare to Hibernate Restrictions.

Or can anybody help me how to change my solution to be case-insensitive, please?

Thanks a lot.

Answer Source

It may seem a little awkward at first, but it is type-safe. Building queries from strings isn't, so you notice errors at runtime instead of at compile time. You can make the queries more readable by using indentations or taking each step separately, instead of writing an entire WHERE clause in a single line.

To make your query case-insensitive, convert both your keyword and the compared field to lower case:

                    type.getDeclaredSingularAttribute("username", String.class)
            ), "%" + keyword.toLowerCase() + "%"
                    type.getDeclaredSingularAttribute("firstname", String.class)
            ), "%" + keyword.toLowerCase() + "%"
                    type.getDeclaredSingularAttribute("lastname", String.class)
            ), "%" + keyword.toLowerCase() + "%"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download