Tag: template

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))
Categories
Java

calculate date difference in JPA / JPQL query using PostgreSQL and QueryDSL

Unfortunately JPA or JQL currently did not support a date difference function. So you have to use native database functions. In PostgreSQL you would use date_part like:

SELECT DATE_PART('day', date2 - date1) FROM mytable

JPA only supports to call functions with simple parameters, but not the calculation of two dates. So you have the problem to subtract one date or timestamp from another. I used a custom function to solve this problem:

CREATE OR REPLACE FUNCTION datediff(a TIMESTAMP, b TIMESTAMP) RETURNS integer AS $$
BEGIN
    RETURN DATE_PART('day', a - b);
END;
$$ LANGUAGE plpgsql

So you can use this function with two simple parameters.

But how to use this function in QueryDSL? This is quite easy. You just have to define a template, especially a number template in this example. You can use the static methods in class Expressions to create such templates. This template is then easily usable in the next query.

QMyTable myTable = QMyTable.myTable;
final NumberTemplate<Integer> template = Expressions.numberTemplate(Integer.class, "function('datediff', {0}, {1})", myTable.date1, myTable.date2);
JPAQuery<MyTable> query = new JPAQuery<>(getEntityManager()).select(myTable).from(myTable).where(template.goe(5));
return query.fetch();