How to strip accents in JPA Criteria API

Question

I'm using JPA Criteria API, and I want to select occurrences where I have a column data like a given string, this is how I do it in SQL :

 lower(CONVERT(myTable.lib, 'US7ASCII')) like lower('%'+myString+'%')

For example I have a row where myTable.Lib = 'métier', so when the value of myString is 'met' it should select return that row since CONVERT(myTable.Lib, 'US7ASCII') in this case will return 'metier'.

This is how I build my criteria:

builder.like(builder.lower(join.get(myTable.lib)),"%" + search.toLowerCase() + "%"));

How can I solve this ?

Duplicate of [this question](https://stackoverflow.com/questions/3322152/is-there-a-way-to-get-rid-of-accents-and-convert-a-whole-string-to-regular-lette) — senape, Feb 19 '18 at 10:44
@senape can you please tell me why this question is duplicated ? how can I use `StringUtils.stripAccents` function on a JPA SingularAttribute ?? — Renaud is Not Bill Gates, Feb 19 '18 at 14:48

osama yaccoub · Answer 1 · 2018-02-19T15:50:52.947

1

Try this:

builder.like(builder.lower(builder.function("convert",String.class,join.get(myTable.lib),builder.literal("US7ASCII"))),"%" + search.toLowerCase() + "%"));

edited Feb 19 '18 at 15:50

answered Feb 19 '18 at 10:56

osama yaccoub

1,884
2
17
47

I'm getting this error : `The method function(String, Class, Expression>...) in the type CriteriaBuilder is not applicable for the arguments (String, Class, Path, String)` – Renaud is Not Bill Gates Feb 19 '18 at 14:50
what is `join` ? – osama yaccoub Feb 19 '18 at 15:28
The join variable is of type `Join` – Renaud is Not Bill Gates Feb 19 '18 at 15:34
The third param of the CriteriaBuilder's function method is an Arbitrary Number of Arguments of `Expression` so it's normal that the String param is not applicable for this method, the 4th param should be like this: `builder.parameter(String.class, "param")` and: `typedQuery.setParameter("param", "US7ASCII");` – Renaud is Not Bill Gates Feb 19 '18 at 15:34
by the way, the code here is using vendor specific function `convert` , thus it wont be portable to DBs not having the same function – osama yaccoub Feb 19 '18 at 16:05

senape · Answer 2 · 2018-02-19T17:16:58.610

-1

The method CONVERT(..., ...) is not ANSI SQL standard syntax, but it's ODBC, as well as US7ASCII character set Oracle's doc

The standard syntax is CONVERT(... USING ...)

The second problem is how database behaves with conversion.

If you run, on an Oracle DB the simple query

select CONVERT('àèìòù', 'US7ASCII') from DUAL;

you will receive

aeiou

The same query in MySQL can't be run, because of the missing US7ASCII character set. According to the Oracle's documentation, its standard counterpart is ASCII, but unfortunately the simple query

select CONVERT("àèìòù" USING ASCII)

returns null

Now, given the problems, as I stated before, using functions to search on data is a very bad idea, since it dramatically affects your queries performance. In my opinion, you should pick the right collation for your tables.

The collation is the way characters are ordered and compared. Using a european collation, or an utf8 collation for your tables (or columns or database) will ensure that accented characters are sorted and compared just as the non-accented ones and, above all, you won't need any additional function to your search query.

edited Feb 19 '18 at 17:16

answered Feb 19 '18 at 15:12

senape

342
2
12

So lets say that the `search` string is equal to `"mét"` and the value in the database is equal to `'métier'` in this case the `term` string is equal to `"met"` so this will not fetch any data since the value in the database contains an accent, please read my question, I said that I want to strip accents using JPA Criteria API and not in JAVA, I already did my google search before posting this question. – Renaud is Not Bill Gates Feb 19 '18 at 15:18
Sorry, I misunderstood. Let me think about it, I'll try to be more helpful :) – senape Feb 19 '18 at 15:21
What's the database you are using? – senape Feb 19 '18 at 15:24
It doesn't matter what database I use, since JPA is a Java specification, the code should work with any database and JPA implementation. – Renaud is Not Bill Gates Feb 19 '18 at 15:31
I was asking, since by default, MySQL ignores diacritics/accents in searches. So if your query is `select * from myTable where myCol like %met%` and you have `métier`, `mètier` or `metier`, it will return all of them. Anyway, we need to clarify the purpose of your question. If it's purely academical or it's for real life. Because in the latter case, I strongly discourage you from using functions within queries, as well as "% term %" like queries, since they badly affect db performance. Give us some more info about your problem I'll be happy to help. – senape Feb 19 '18 at 15:47
well, as I said, since I'm using JPA it means that whatever database I use the code should work, in fact, the application I'm working on can be configured to run with any database. – Renaud is Not Bill Gates Feb 19 '18 at 15:51
please, give a review to my answer and let me know what you think – senape Feb 19 '18 at 17:17

How to strip accents in JPA Criteria API

2 Answers2