QueryDSL and SQL Function from Postgres

Question

I would like to use Postgres FullText search with QueryDsl JPA.

Generated in SQL:

select * 
from film 
where to_tsquery ('tarzan') @@ to_tsvector('french',film.title) = true

to get all film containing tarzan in their title.

In JPA I define a custom function 'ftsMatch' which I could use like that:

String jpql = "select film from Film film where ftsMatch(:queryString, film.titre) = true";

I QueryDSL I would like to have an opportunity to define a predicate on String type :

QFilm.film.titre.ftsMatch('tarzan')

I haven't found any solution

JPQL allows use of "FUNCTION(funcName, args)". You can't just dump in random SQL functions into JPQL, and must use FUNCTION — Neil Stockton
– Neil Stockton, Commented Dec 2, 2016 at 6:25
@Neil : i already solved the pb with JPQL using a custom Dialect and implementing the function in a class imlementing org.hibernate.dialect.function.SQLFunction. The question is How to solve the same problem with QueryDSL ? — Gauthier Peel
– Gauthier Peel, Commented Dec 2, 2016 at 15:11
As already said, there is no need for JPA proprietary solutions (custom Dialect), since "FUNCTION" is JPA 2.1 standard. And since QueryDSL is supposedly creating JPQL internally then it ought to have a method for FUNCTION — user3973283
– user3973283, Commented Dec 2, 2016 at 18:35

Gauthier Peel · Accepted Answer · 2016-12-04 18:36:35Z

What I would like to do is to extends the com.querydsl.core.types.dsl.StringExpression.class and add a custom function fullTextMatch() which could be used like :

BooleanBuilder booleanBuilder = new BooleanBuilder(QFilm.film.titre.fullTextMatch(_titre, "french"));

it would turn into SQL :

select film0_.id as id1_2_ .. from film film0_ 
where  to_tsquery (?) @@   to_tsvector('pg_catalog.french',film0_.titre)=true

I haven't found how to get the QueryDSL syntax above, but found the following solution:

1/ define Custom Dialect for Postgres and register the Customm function on this dialect :

public class CustomFullTextPostgresDialect extends PostgreSQL94Dialect {

    public CustomFullTextPostgresDialect() {
        registerFunction("ftsMatch", new PostgreSQLFullTextSearchFunction());
    }

}

2/ Code the custom function PostgreSQLFullTextSearchFunction implementing org.hibernate.dialect.function.SQLFunction This function 'ftsMacth' will generate the SQL :

String fragment = " to_tsquery (" + value + ") @@   to_tsvector(" + ftsConfig + "," + field + ")";

This step give me access to Posgres FullText in JPA :

String jpql = "select film from Film film "
        + "where FUNCTION( 'ftsMatch', :titre,'pg_catalog.french', film.titre) = true";
TypedQuery<Film> typedQuery = em.createQuery(jpql, Film.class);
typedQuery.setParameter("titre", _titre);
List<Film> list = typedQuery.getResultList();

3/Use QueryDsl to relay to the custom function defined on the extended postgres Dialect :

BooleanTemplate predicate = Expressions
        .booleanTemplate("FUNCTION('ftsMatch', {0},'pg_catalog.french', film.titre) = true ", _titre);

Page<Film> page = filmRepository.findAll(predicate, _pageable);

But with this QueryDSL solution, I still need the Hibernate customization. And the syntax is no longer DSL oriented

Collectives™ on Stack Overflow

QueryDSL and SQL Function from Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related