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

Maven multi-module projects with Spring Roo and Eclipse/STS

Last week, we started a new project that really fits Maven's multi-module approach. We also decide to give Spring Roo a try in a (quite) large project. We've succesfully used Spring Roo for quick prototyping and small projects, and we found it is a really helpfull and flexible tool.

The hell began when we started our new Maven multi-module using Roo and Eclipse. We used STS support to create a new Roo project and then create some Maven modules... and Eclipse just didn't like the way we did it: Spring Roo shell in STS just stopped working and Roo didn't update, delete or create the AspectJ ITDs.

We googled for a while and found several threads at Stack Overflow and a really nice post explaining some issues and their workarounds.

In short, the working recipe is as follows:

1. Create your parent project using the command-line Roo Shell (remember to open the Roo shell in an newly created project directory).
2. Create your modules using command-line Roo Shell.
3. Create at least one Java class for each Java project before importing to Eclipse/STS.
4. Copy the project to your workspace folder and import projects to Eclipse/STS using m2e plugin features: "Import->Maven->Existing Maven Projects" from your file system or check-in your project into your SCM and use "Import->Maven->Check out Maven Project from SCM". Be sure to import the parent folder, all the modules should be detected and the Roo Shell for the parent project should automatically open. We never check-in our Eclipse project config files into SCM.
5. If you want to work with the STS embedded Roo Shell, use only the parent project shell and Roo's module commands to deal with modules. Just remember to keep the project and the shell open if you want Roo to be working with you :)
6. Remember to use Eclipse's resource filter and working set features to easilly manage all your projects.

If you follow this recipe, you should have no problems at all.

Here, you can download a Roo script sample to create a multi-module project that you can later import into Eclipse/STS.

This demo project consists of a parent demo project and two modules demo-core and demo-web, and uses JPA, Hibernate, PostgreSQL and JSF2/MyFaces/PrimeFaces. Update your JPA, database and/or web layer to fit your requirements.

This demo has been tested on Spring Roo 1.2.1, STS 2.9.1, Maven 3.0.3

Download multi-module_demo.roo

project --projectName demo-parent --topLevelPackage es.abacor.demo --packaging POM --java 6

module create --moduleName demo-core --topLevelPackage es.abacor.demo --java 6 --parent es.abacor.demo:demo-parent:0.1.0.BUILD-SNAPSHOT
jpa setup --provider HIBERNATE --database POSTGRES --hostName localhost --databaseName demo --userName demo --password demo
entity jpa --class es.abacor.demo.foo.Foo

module focus --moduleName ~

module create --moduleName demo-web --topLevelPackage es.abacor.demo.web --java 6 --parent es.abacor.demo:demo-parent:0.1.0.BUILD-SNAPSHOT
web jsf setup --implementation APACHE_MYFACES --library PRIMEFACES
web jsf all --package es.abacor.demo.jsf
module focus --moduleName ~