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)));
}

JPA setup: entities in JAR file

In this post, we'll see how to package our entities in a JAR file that will be used as a dependency for a WAR webapp file.

The setup is really simple, but a bit tricky too. The key is to place persistence.xml file in the META-INF folder of the JAR file, along with our entities. We should end up with the following directory tree:

my-core.jar:
META-INF/persistence.xml
bar/foo/MyEntity.class
//other core files

my-webapp.war:
WEB-INF/lib/my-core.jar
WEB-INF/web.xml
//other webapp files

That's it. We can now easily package our webapps as multi-module projects (eg Maven).