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.

Answer 1

There are few ways to do that:

1. 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

2. 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.