java - 使用 Hibernate 查询语言的 PostgreSQL 全文搜索无法执行查询

Question

我正在开发全文搜索服务。我使用 PostgreSQL 的内置 FTS 查询 syntax .如您所知，我需要使用 @@ 字符才能使用它。但是，这个字符不被HQL识别，你不能像在sql里面那样直接写。一种选择是使用我过去使用的 nativeQuery。但是，由于软件的要求，现在我需要使用HQL。为此，我尝试实现 this执行。

在该实现中，首先创建一个 PostgreSQLFTSFunction 类。对我来说是:

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;

    }
}

然后需要创建CustomPostgreSQLDialect:(我肯定已经在 application.yml 文件中添加了方言配置)

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

最后我在我的 RepositoryImp 中创建了 HQL

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();

但是无法执行hibernate创建的查询。完整的错误将在下面给出，但我应该说，控制台中记录的查询(由 Hibernate 创建)可以在 pgAdmin 中执行。因此，Query 创建过程是正确的。那么，错误会在哪里呢？

给出了完整的错误:

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]

如果需要更多信息，我会尽快发送。提前致谢。

Answer 1

有趣的是，正如错误提示的那样，hibernate 无法将参数绑定(bind)到sql 上。我做不到。但在我的情况下，我只能连接为字符串，因此通过将 HQL 更改为:

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

到:

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