Spring Boot JPA Full Text Search

JPA-FULL-TEXT-SEARCH.md

This is a small guide on how to get database-specific full text search working on Spring JPA. Before you proceed, make sure you actually need full text search. Full text search is about matching words against words, not partial words against words, so depending on your use case, a simple like %data% operator can be a better user experience.

As an example, if your dataset looks like this:

id | name
------------------
0  | reimu hakurei
1  | suwako moriya

If you search using FTS for hakurei then you will find one result, however if you search for haku instead (a partial word), then your query will return no results.

# select count(*) from touhou where fts(name, 'hakurei');
1
# select count(*) from touhou where fts(name, 'haku');
0

If however, you need word search on your dataset, this solution is usually easier and uses less resources than duplicating your entire dataset into Lucene/ElasticSearch/MeiliSearch.

This solution is also not database agnostic, so you will need a different configuration if you are running postgres/mariadb/etc..

MARIADB.md

MariaDB Implementation

On MariaDB, full text search works using the match and against operators. To enable full text search on Hibernate JPA, you need to create a MetadataBuilderContributor that implements the full text operation.

MariaDB Contributor:

public class MariaDBFullTextContributor implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("fts",
                new SQLFunctionTemplate(DoubleType.INSTANCE,
                        "match(?1) against (?2)"));
    }

}

You then need to register it on your application's configuration:

spring.jpa.properties.hibernate.metadata_builder_contributor: com.example.MariaDBFullTextContributor

Indexes

On MariaDB, a full text index is REQUIRED.

ALTER TABLE users ADD FULLTEXT INDEX idx_fts_name(name);

JPA Specification

If you want to use full text search with JPA specification, all you need to do is implement the specification builder, below is an example that is generic for doing FTS on any text field.

private Specification<T> fts(String fieldName, String search, double score) {
    return (root, query, builder) -> {
        Path<String> path = this.getPath(fieldName, root);
        return builder.greaterThanOrEqualTo(
                builder.function("fts", Double.class, path, builder.literal(search)), score);
    };
}

Usage

Now you can use it on your JPA queries like this:

@Query("SELECT u FROM Users u WHERE fts(name, :name) => score")
List<User> search(@Param("name") String name, double score);

Which will result in the following sql query:

SELECT u FROM Users u WHERE match(name) against (?) >= 1.0

POSTGRES.md

PostgreSQL Implementation

On PostgreSQL, full text search works using the to_tsvector and plainto_tsquery operators. To enable full text search on Hibernate JPA, you need to create a MetadataBuilderContributor that implements the full text operation.

PostgreSQL Contributor:

public class PostgresFullTextContributor implements MetadataBuilderContributor {

    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("fts",
                new SQLFunctionTemplate(BooleanType.INSTANCE,
                        "to_tsvector('english', ?1) @@ plainto_tsquery(?2)"));
    }

}

You then need to register it on your application's configuration:

spring.jpa.properties.hibernate.metadata_builder_contributor: com.example.PostgresFullTextContributor

Indexes

On PostgreSQL, a full text index is OPTIONAL, however it's good for performnace on larger datasets.

CREATE INDEX idx_fts_name ON users USING GIN (to_tsvector('english', users.name));

JPA Specification

If you want to use full text search with JPA specification, all you need to do is implement the specification builder, below is an example that is generic for doing FTS on any text field.

private Specification<T> fts(String fieldName, String search) {
    return (root, query, builder) -> {
        Path<String> path = this.getPath(fieldName, root);
        return builder.equal(
                builder.function("fts", Boolean.class, path, builder.literal(search)), true);
    };
}

Usage

Now you can use it on your JPA queries like this:

@Query("SELECT u FROM Users u WHERE fts(name, :name) = true")
List<User> search(@Param("name") String name);

Which will result in the following sql query:

SELECT u FROM Users u WHERE to_tsvector('english', name) @@ plainto_tsquery(?) = true