Using Specification CriteriaBuilder with non-standard operators like % from pg_trgm

[MinnDevelopment]

Is there any way I can make a Predicate that adds a WHERE clause in the form of a % b, or any custom operators?

I'm using pg_trgm, which has this operator as a way to implement fuzzy search. I can't just make a function around it, since that disables index scanning.

I need something similar to CriteriaBuilder#function, but for infix operators to get a query like their example:

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word' -- this operator enables an index scan
  ORDER BY sml DESC, t;

Is there something like builder.operator("%", Boolean.class, fieldA, fieldB)? Or is the only way to do this a native query and give up on specifications?

[gavinking]

Definitely a perfect use case for a native SQL query. There's no way that query is ever going to work on any other database, so you lose very little by writing it in SQL.

[beikov]

You can define an immutable inline function in PostgreSQL for the operator and use that instead, which will make use of the index.

Alternatively, you can use the sql bultin function through CriteriaBuilder.function() which is supported by Hibernate ORM and EclipseLink. That allows you to specify a string representing a SQL template with placeholders as first argument followed by varargs that are filled into that template. Take a look into the Hibernate ORM or EclipseLink documentation for more details.

Lastly, you can also register custom functions in Hibernate ORM and EclipseLink that render whatever SQL that you want. You use them through CriteriaBuilder.function() as well.

[MinnDevelopment]

Thank you, I was able to solve it with cb.function("sql", Boolean.class, cb.literal("? % ?"), a, b).

[Spliterash]

If someone like me is looking for a way to make criteriaBuilder.literal("SearchValue") a bind parameter rather than embedding it directly in the query, this will help you out

((HibernateCriteriaBuilder)criteriaBuilder).value("SearchValue")