HOWTO: invoke database functions in JPA Criteria Queries

In this post we'll see how to invoke database functions in a JPA Criteria Query. The CriteriaBuilder interface has a function() method:

 /**
* Create an expression for the execution of a database
* function.
*
* @param name function name
* @param type expected result type
* @param args function arguments
*
* @return expression
*/
 Expression function(String name, Class type, Expression... args);

This method will return a plain criteria Expression that we can use as usual in our criteria query.

Let's see an example to perform accent-insensitive search using PostgreSQL's translate function:

public abstract class CriteriaBuilderHelper {

private static final String PG_TRANSLATE  = "translate";
private static final String PG_TRANSLATE_FROM  = "'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ'";
private static final String PG_TRANSLATE_TO  = "'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcC'";

private static final String WILDCARD = "%";

/**
* @param cb the CriteriaBuilder to use
* @param value the value to translate
* @return Expression<String>
*/
public static Expression functionAccentInsensitivePostgres(CriteriaBuilder cb, String value) {
return cb.function(
PG_TRANSLATE,
String.class,
cb.lower(cb.literal(WILDCARD + value + WILDCARD)),
cb.literal(PG_TRANSLATE_FROM),
cb.literal(PG_TRANSLATE_TO)));
}