How to provide custom Expression or Predicate with JPA QueryBuilder

Question

I am using JPA2 (EclipseLink implementation) with Spring Data and using Specification pattern to provide filter specification.

How can I introduce custom Expression or Predicate that will map to custom SQL operator? I have particularly in mind using PostgreSQL FTS extension and add conditions like this:

SELECT ... WHERE ... AND column @@ 'ts query'

to my Specification instance.

JPQL supports generalised SQL functions using "FUNCTION", maybe that — Neil Stockton, Feb 23 '15 at 13:27
@Neil Stockton: I know that, but is it possible to mix JPQL with Specification object? If yes - that would be OK... — redguy, Feb 23 '15 at 13:53
Since "Specification object" (no idea what that is) is not part of JPA then the answer is clearly no, see the JPA spec — Neil Stockton, Feb 23 '15 at 13:56
"Specification" object is part of Spring Data and is used to build criteria — redguy, Feb 23 '15 at 14:35

score 2 · Accepted Answer · edited Sep 08 '20 at 12:32

There are few ways to do that:

If switching to Hibernate is an option, there is a way to do that using Hibernate infrastructure:

For HQL in Hibernate
http://java-talks.blogspot.co.uk/2014/04/use-postgresql-full-text-search-with-hql.html

And for integrating Hibernate implementation with JPQL
http://metabroadcast.com/blog/hibernate-and-postgres-fts

Basically, what you need to do is 

-  Override your PostgreSQL Dialect
-  Register functions with appropriate interpreters
-  Call functions, when you need to use FTS

If you can't go with Hibernate, you still can do it, by basically using the same approach, as described above, but with EclipseLink specifics

Here is a link of how you can do it, using EclipseLink https://wiki.eclipse.org/Introduction_to_EclipseLink_Expressions_%28ELUG%29

Basically you'll need to do the same staff, but EclipseLink style

Based on Documentation, it can look something like this:

public class MyDatabasePlatform extends DatabasePlatform {

   final private int FTS_ID = Interger.MAX_VALUE;
        
   protected void initializePlatformOperators() {
      super.initializePlatformOperators();
      // Create user-defined function

      ExpressionOperator fts = new ExpressionOperator();
      fts.setSelector(FTS_ID);
      List args = new ArrayList();
      args.addElement(" @@ to_tsquery(");
      args.addElement(")");
      fts.printAs(args);
      fts.bePrefix();
      fts.setNodeClass(FunctionExpression.class);

      // Make it available to this platform only
      ExpressionOperator.registerOperator(FTS_ID, "fts");
      addOperator(fts);
   }

}

Although I'm not sure about that code, you'll need to test this.

I tried to follow 2) suggestion and I am able to prepare criteria but only using eclipselink native classes... is there a way I could use this using: javax.persistence.criteria.CriteriaBuilder, javax.persistence.criteria.CriteriaQuery, javax.persistence.criteria.Predicate, etc? — redguy, Apr 03 '15 at 11:34
I managed to work it out. Extending DatabasePlatfor is still handy because you can use new operator in jpql like: SELECT t FROM entity t WHERE OPERATOR('fts', COLUMN('fts_column_in_table', t), 'fts & query & string'); but to prepare JPA criteria you need to do something different: http://stackoverflow.com/questions/29509261/eclipselink-jpa-criteria-reference-unmapped-column — redguy, Apr 10 '15 at 09:43
There is a description, of how to add it to CriteriaBuilder in the hibernate article. — mavarazy, Apr 10 '15 at 10:01

How to provide custom Expression or Predicate with JPA QueryBuilder

1 Answers1