PostgreSQL Full Text Search with Hibernate Query Language cannot execute query

Question

I am developing a full text search service. I use PostgreSQL's built-in FTS query syntax. As you know, I need to use @@ characters to use it. But, this characters are not recognized by HQL, you cannot directly write it like inside the sql. One option is to use nativeQuery, which I used to use. But, due to software's requirements, now I need to use HQL. For this purpose, I tried to implement this implementation.

In that implementation first you create a PostgreSQLFTSFunction class. For me it is:

public class BFTSFunction implements SQLFunction {
    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return false;
    }

    @Override
    public Type getReturnType(Type type, Mapping mapping) throws QueryException {
        return new BooleanType();
    }

    @Override
    public String render(Type type, List list, SessionFactoryImplementor sessionFactoryImplementor)
            throws QueryException {

        if (list.size() != 2) {
            throw new IllegalArgumentException("The function must be passed 3 args");
        }
        String field = (String) list.get(0);
        String value = (String) list.get(1);

        String fragment = null;
        fragment =  "( to_tsvector(coalesce(" + field + ",' ')) @@ " + "plainto_tsquery('%" +
                value + "%')) ";

        return fragment;

    }
}

Then need to create CustomPostgreSQLDialect:(I have, for sure, added dialect configuration onto application.yml file)

public class FTSPostgresDialect extends PostgisDialect {
    public FTSPostgresDialect() {
        super();
        registerFunction("fts", new BFTSFunction());
    }
}

Lastly I created HQL in my RepositoryImp

Query<Long> q = session.createQuery(
                "select b.id from B b where (fts(b.name,:searched) = true )",Long.class).setParameter("searched",searched);

List<Long> listResults = q.getResultList();

But the query created by hibernate cannot be executed. Full error will be given below, but I should say that, logged query in the console (created by Hibernate) can be executed in pgAdmin. Therefore, Query creation process is correct. Then, where can be the error is?

Full error is given:

rg.springframework.dao.DataIntegrityViolationException: could not execute query; SQL [select b0_.id as col_0_0_ from bs b0_ where ( b0_.deleted=false) and ( to_tsvector(coalesce(b0_.name,' ')) @@ plainto_tsquery('%?%')) =true]; nested exception is org.hibernate.exception.DataException: could not execute query
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:223) ~[spring-orm-5.0.9.RELEASE.jar:5.0.9.RELEASE]
...
Caused by: org.hibernate.exception.DataException: could not execute query
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:118) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
...
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128) ~[postgresql-42.2.5.jar:42.2.5]


2019-07-16 10:08:59.328 ERROR 8248 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute query; SQL [select b0_.id as col_0_0_ from bs b0_ where ( b0_.deleted=false) and ( to_tsvector(coalesce(b0_.name,' ')) @@ plainto_tsquery('%?%')) =true]; nested exception is org.hibernate.exception.DataException: could not execute query] with root cause

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:128) ~[postgresql-42.2.5.jar:42.2.5]
    at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:996) ~[postgresql-42.2.5.jar:42.2.5]

If additional info is needed, I will fastly send it. Thanks in advance.

Answer 1

Interestingly, as the error suggests, hibernate cannot bind the parameters onto sql. I couldn't make it happen. But in my situation, I could just concatenate as string, therefore problem is resolved by changing HQL from:

Query<Long> q = session.createQuery(
                "select b.id from B b where (fts(b.name,:searched) = true )",Long.class)
                .setParameter("searched",searched);

to:

Query<Long> q = session.createQuery(
                "select b.id from B b where (fts(b.name,"+searched+") = true )",Long.class);