"Like" finders are case-sensitive

[Andrew Swan]

If the Pizza entity has a "name" field, and you generate the "findPizzasByNameLike" method, it looks like this:

public static Query Pizza.findPizzasByNameLike(String name) {
    if (name == null || name.length() == 0) throw new IllegalArgumentException("The name argument is required");        
    name = name.replace('*', '%');        
    if (name.charAt(0) != '%') {        
        name = "%" + name;            
    }        
    if (name.charAt(name.length() -1) != '%') {        
        name = name + "%";            
    }        
    EntityManager em = Pizza.entityManager();        
    Query q = em.createQuery("SELECT Pizza FROM Pizza AS pizza WHERE pizza.name LIKE :name");  // *
    q.setParameter("name", name);  // *
    return q;        
}

The above code searches for Pizzas with the given string in their name case-sensitively. So if the user enters "meat" as the search string, they will find "Ratmeat Surprise" but not "Meat Lovers". This is not what most users would expect. The search function in commonly used apps like Google, GMail, and MS Office is case-insensitive by default.

Therefore to match the most common use cases, the finders that Roo generates should be case-insensitive (or at least accept a boolean parameter that indicates whether to search case sensitively). The workaround is to copy-and-paste the above finder into your Pizza.java file, fix the compile errors, and change the starred lines to read:

    Query q = em.createQuery("SELECT Pizza FROM Pizza AS pizza WHERE LOWER(pizza.name) LIKE :name");
    q.setParameter("name", name.toLowerCase());

If you wanted to be really correct, you should also pass in the user's Locale so that the lower-casing can be done in a locale-specific way.

Should I log this as an improvement?

[Stefan Schmidt]

Hi Andrew,

Thanks for this improvement suggestion. Yes, it would be great if you could log this as an improvement ticket. Just one question, lower casing all search terms, would that then match "Meat" if the search term is "meat"?

Also, can you give an example on the integration of locales and where this is useful?

In the medium term I think the current usefulness of the Roo generated finders is somewhat limited with regards to some more advanced search requirements. So it might be better to look into creating an addon which integrates with Lucene / Compass / or Solr. What do you think? Maybe we should also open a Jira ticket for this to gauge community interest.

Cheers,
Stefan

[Andrew Swan]

Just one question, lower casing all search terms, would that then match "Meat" if the search term is "meat"?

Yes, it does, because in my version, both the where clause and the bind variable are lower-cased (you could uppercase them both and it would work equally well):

Query q = em.createQuery("SELECT Pizza FROM Pizza AS pizza WHERE LOWER(pizza.name) LIKE :name");
q.setParameter("name", name.toLowerCase());

The locale is important because upper- and lower-casing are locale-specific operations. For example, the lower case of "I" is "i" in English but a dotless "i" in Turkish (they have a small dotted "i" but it's a different letter, with a dotted uppercase version). String.toLowerCase() and String.toUpperCase() use the server's default Locale, but I guess as long as this is the same locale used by the database for its lower() and upper() functions, there shouldn't be any unfortunate consequences.

A domain-wide text search using something like Lucene sounds good, but IME users also want to be able to search by entering criteria against one or more specific domain fields, so the current finders will always have their place.

I've logged ROO-580 about case-insensitive finders.