CODEWITHSUNDEEP
javaoracle-databasehibernatejpaquerydsl
Pierre Henry
Pierre Henry

Reputation: 17497

How to convert strings to ASCII in QueryDSL?

I Have an Oracle DB that uses UTF-8. I am issuing some search queries, with likepredicates to filter by some name column.

Since the data in the table are French and German names, they might contain accentuated characters, such as àäèüéöetc...

A like predicate that is written in JPQL looks like this (I simplified to keep just the relevant part):

...where lower(convert(item.value, 'US7ASCII')) like lower(convert(CONCAT('%',:query,'%'), 'US7ASCII'))

That yields the following SQL once converted to the Oracle dialect :

lower(convert(items4_.SSI_ITEM, 'US7ASCII')) like lower(convert('%'||?||'%', 'US7ASCII'))

Both sides of the likeoperator are converted to lower case and to the ?US7ASCII` character set, which removes accents. Note that both sides are converted by the database itself, using the same function. It makes the search "accent-insensitive", so to say.

Now, this works fine and is used for a relatively simple query, that returns names for the autocomplete of the field.

However, the main search query is way more complex and dynamic (lots of joins, dynamic criteria), and I am using QueryDSL to build it.

Is it possible to reproduce the above character conversion using QueryDSL over JPA ?

Upvotes: 1

Views: 1441

Answers (1)

asm0dey
asm0dey

Reputation: 2931

Sorry to say that, but convert function is not JPA-compatible. It works for you just because hibernate interprets your JPQL as HQL. But QueryDSL supports only JPA standard (or you should use querydsl over hibernate). You have several options, as I see:

  1. Use hibernate instead of JPA
  2. Create additional column, where you will store diacritics-free data (btw, your indexes will work better there too as I understand)
  3. Do not use querydsl for this query but generate it by hand…

Looks like 2nd option is most easy to implement - just one pretty simple SQL migration, where you create additional column, insert normalized data there and create trigger for newly-added data to be automatically converted.

Upvotes: 2

Related Questions