custom sql queries in JPA Criteria Predicate, StringPath, Querydsl

Question

I have a Spring boot project with hibernate 5.4.12, Java 11 and Postgres.

I am trying to build a custom Sort/Filter mechanism using JPA and Querydsl, here is one blog for reference.

We have a gin index column which is used for full text search feature by postgres. In jpa repository, I can query the column easily as below

@Query(value = "select * from products where query_token @@ plainto_tsquery(:query)", nativeQuery = true)
Page<Product> findAllByTextSearch(@Param("query") String query, Pageable pageable);

I am aware that fts queries are not yet supported by JPA criteria or querydsl APIs (I may be wrong). Since normal filtering logic will go through criteria API, how do add fts capabilities in criteria API? Is there a way to add custom native query as predicate or StringPath or any other Qtype paths?

UPDATE

My SearchPredicate class

public class SearchPredicate<E extends Enum<E>> {

  private SearchCriteria<E> searchCriteria;

  public <T> BooleanExpression getPredicate(Class<T> entityClass, String entityName) {
    PathBuilder<T> entityPath = new PathBuilder<>(entityClass, entityName);

    switch (searchCriteria.getPathType()) {
      case String:
        StringPath stringPath = entityPath.getString(searchCriteria.getKey());
        return stringPath.eq(searchCriteria.getStringValue());

      case Enum:
        return entityPath.getEnum(searchCriteria.getKey(), searchCriteria.getEnumClass())
            .eq(Enum.valueOf(searchCriteria.getEnumClass(), searchCriteria.getStringValue()));

      case Float:
        NumberPath<Float> floatPath = entityPath.getNumber(searchCriteria.getKey(), Float.class);
        Float floatValue = Float.parseFloat(searchCriteria.getStringValue());
        return floatPath.eq(floatValue);

      case Integer:
        NumberPath<Integer> integerPath = entityPath.getNumber(searchCriteria.getKey(), Integer.class);
        Integer integerValue = Integer.parseInt(searchCriteria.getStringValue());
        return integerPath.eq(integerValue);
    }
    return null;
  }
}

My SearchCriteria class

public class SearchCriteria<E extends Enum<E>> {
  private String key;
  private Object value;
  private PathType pathType;
  private Class<E> enumClass;

  public String getStringValue() {
    return value.toString();
  }
}

And My PathType Enum

public enum PathType {
  String, Enum, Integer, Float;
}

On these same lines, I am assuming/expecting something for text search as well e.g.

case Search:
  FtsPath ftsPath = entityPath.getFtsPath("query_token");
  return ftsPath.search("some search string")

Jan-Willem Gmelig Meyling Jan-Willem Gmelig Meyling · Accepted Answer · 2020-06-17T08:08:00

You should first make the @@ operator available by registering a custom function for your ORM. Then you can do plainto_tsquery(query_token, :query) in your JPQL query. How to register a custom function depends on the ORM you use. Assuming you use Hibernate, you're probably best of using the MetadataContributor SPI because functions registered through the Dialect have less flexibility with regard to the underlying SQL rendering AFAIK.

Then, if you want to use this in QueryDSL, you'd have to create a custom Operator and register a Template for that Operator in a subclass of JPQLTemplates. Alternatively, you can bypass the Operation expressions using a simple TemplateExpression: Expressions.booleanTemplate("plainto_tsquery({0}, {1})", QProduct.product.queryToken, query), which returns a predicate.

custom sql queries in JPA Criteria Predicate, StringPath, Querydsl

1 Answers