Criteriabuilder lower() vs Java toLowerCase()

Question

According to this Link

Predicate lcSurnameLikeSearchPattern = criteriaBuilder.like( criteriaBuilder.lower(Person_.surname), searchPattern.toLowerCase());

This seems like Hibernate would generate an SQL that would look something like

LOWER(PERSON.SURNAME) LIKE 'searchPattern'

Where searchPattern would have been lowered in Java using whatever implementation toLowerCase would provide. Whereas the query LOWER would use Oracle implementation. For ASCII characters, I'm guessing things are pretty simple, but would there ever be discrepancies on international UTF characters?

How would I get JPA to lower both operands of LIKE in the query? So the generated query looks like

LOWER(PERSON.SURNAME) LIKE LOWER('searchPattern')

What does that even mean? Did you read my entire post or did you just read the title and decided to post a comment? — BKaun, Jan 28 '16 at 03:24
Marco means that a lot of questions can be answered by trying (and reading the documentation and javadoc), if `criteriaBuilder.lower(Person_.surname)` generates `LOWER(PERSON.SURNAME)`, then you can assume (and validate by trying) that `criteriaBuilder.lower(searchPattern)` might generate `LOWER('searchPattern')`. Given the [answer by Vlastimil](http://stackoverflow.com/a/35081558/466862) this is not correct, but it might have given you an insight in what to do. — Mark Rotteveel, Jan 29 '16 at 10:03
Thanks. I did ultimately find the answer. When I posted this, I had searched and read for days. This is not a trivial concept by any means, and any search for the topic mostly returns the same toLowerCase solution. I would not have asked here, if I could have discovered the answer myself. The fact that I asked here means I was desperately seeking an answer. Then to be told to try it myself without any hints or pointers seems like a smug, useless and unproductive comment. I understand that no one owes me anything, but if someone cannot help without being smug, they're best to remain silent. — BKaun, Jan 30 '16 at 20:44

score 9 · Accepted Answer · answered Jan 29 '16 at 09:57

You can use two Expressions as like method parameters and literal to wrap a String into Expression:

Predicate lcSurnameLikeSearchPattern = criteriaBuilder.like(
  criteriaBuilder.lower(Person_.surname),
  criteriaBuilder.lower(criteriaBuilder.literal(searchPattern))
);

CriteriaBuilder#like(Expression,Expression)

CriteriaBuilder#lower(Expression)

CriteriaBuilder#literal(T)

I ommit Person_.surname definition as it is from your example.

Criteriabuilder lower() vs Java toLowerCase()

1 Answers1