Categories
Java

JPA / JQL + QueryDSL string replace

Sometimes you need a string replace in SQL query, for example to compare multiple values each other which need to manipulate before comparing.

Unfortunately JPA did not support any replace function, so you have to use database specific functions. You should read the documentation of your database server how you can replace strings via SQL. For PostgreSQL you can use the replace function.

To use custom functions with QueryDSL, you have to use templates as described in a previous blog post about difference of dates in JPA.

final StringTemplate convertedField1 = Expressions.stringTemplate("function('replace', {0}, '/', '-')", model.field1);

Then you can use this template in your query (for example compare replaced field1 with field2 of your model):

new JPAQuery<>()
…
.where(convertedField1.eq(model.field2))