Hibernate and Postgres FTS

04 Feb 2015

Hello! We have a Postgres database that we'd like to search over. After much debate, we decided that Postgres's full text search is good enough. Our initial thought was to use our existing ElasticSearch infrastructure and code — however as we are using Postgres — the built in searching 'comes for free'.

The above article will give you more than enough information to use Postgres FTS, what I wish to demonstrate is getting Hibernate, your friendly neighbourhood ORM, to play nicely with it.

The methodology used here was inspired/learned from this article.

.put("hibernate.dialect", PgFullTextDialect.class.getCanonicalName())

So obviously Hibernate requires you to assert a dialect of SQL that is compatible with the database it must talk to. Here I am declaring it as PgFullTextDialect, this is a custom dialect that I have defined as...

public class  PgFullTextDialect extends PostgreSQLDialect {

public PgFullTextDialect() {
    registerFunction("fts", new PgFullTextFunction());
    registerFunction("ts_rank", new StandardSQLFunction("ts_rank", DoubleType.INSTANCE);
    registerFunction("to_tsquery", new StandardSQLFunction("to_tsquery", ObjectType.INSTANCE));
}

}

This is where we register additional functions on top of those defined in PostgreSQLDialect, which doesn't contain the full text functions. If you've used PG FTS before, you'll know of ts_rank which is used to get a relevance rank for a search result. You'll also know to_tsquery which turns a string of text (your query) into an object Postgres can use to search.

The one you probably don't recognise is fts, which is infact not a PG function at all but the name I have assigned to the special Postgres syntax of @@, used to compare a text vector to a query vector. Hibernate needs to be made aware of this syntax and requires us to declare a new function type, rather than just the name and return type like we did for the previous two. This is defined as...

public class PgFullTextFunction implements SQLFunction {

/* Column name of TSVECTOR field in PgSQL table */
public static final String FTS_VECTOR_FIELD = "search_text";

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

@Override
public boolean hasArguments() {
    return true;
}

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

@SuppressWarnings("rawtypes")
@Override
public String render(Type type, List args, SessionFactoryImplementor factory) throws QueryException {
    String searchString = (String) args.get(0);
    return FTS_VECTOR_FIELD + " @@ to_tsquery(" + searchString + ")";
}

}

Here we have implemented the interface SQLFunction. Like before we declare the return type of the function, we also implement two methods that indicate how Hibernate should handle the method. Crucially however we implement a method that is capable of rendering the function, that is, generating a valid SQL statement given some input. This is where the special syntax @@ is made available to Hibernate.

Finally, using the JPA CriteriaBuilder, we can make an FTS query by asserting that the function fts returns true given a searchString. Note that in this example the field to be search against is hardcoded and therefore is not specified here.

@Override public Predicate apply(Root<?> root, CriteriaBuilder cb, String searchString) {
        return cb.isTrue(
                cb.function(
                        "fts",
                        Boolean.class,
                        cb.literal(searchString)
                )
        );
    }

Hopefully this has been helpful if you are using JPA and Hibernate with Postgres for the purposes of full text searching. Thanks for reading!

Originally posted on MetaBroadcast's blog.