Setup Spring and Postgres for Full Text Search

Introduction

In this article we will build a Pokemon API capable of full text search. That means we will be able to search Pokemons by its text description with the power of Spring Boot and Postresql!

The greatest advantage of this setup is the use of JPA (Java Persistence API) for database interaction, instead of using native SQL queries directly. In another words, when we query database we will get Java objects (Entities) automatically.

You can try the frontend demo on CodeSandbox:

Backend code is on Github:

fabiothiroki / spring-pokemon-textsearch

A spring boot application implementing full-text search using Postgresql

Credits for frontend design goes to Florin Pop.

Setup

Go to Spring Initializr to select the basic dependencies and download the boilerplate of our project. At the time I am writing this article, I've selected:

• Gradle Project
• Java 8 Language
• Spring Boot 2.3.1
• Spring Web
• Spring Data JPA

Full text search

Before we start effectively coding, let's take a step back and talk about the use case for full text search on our API. Supposing I query the API for word growing, I want at least the following results:

• Bulbasaur: For some time after its birth, it grows by gaining nourishment from the seed on its back.
• Nidorina: When it senses danger, it raises all the barbs on its body. These barbs grow slower than Nidorinos.

We could achieve this by using the common SQL LIKE operator, but we would still need to know the stem (or the root form) of growing word. So the query would be:

SELECT * FROM Pokemon WHERE description LIKE 'grow%'

But now for every other English word we would like to search, we would need to manually map the word to its corresponding stem. That's where full text search helps us so we can query by any word.

The same search above using the full text search operators from Postgresql is:

SELECT * FROM Pokemon 
WHERE to_tsvector(description) @@ plainto_tsquery('growing')

As you can notice, we can query directly by word growing and get the same results. In the next section I will explain what to_tsvector and plainto_tsquery means.

to_tsvector

Basically it receives a string as input and returns a list of lexemes (a minimal meaningful unit of language). Let's check what are the lexemes of Bulbasaur description:

For some time after its birth, it grows by gaining nourishment from the seed on its back.

SELECT to_tsvector(description) FROM pokemon where id=1
---
'back':17 'birth':6 'gain':10 'grow':8 'nourish':11 'seed':14 'time':3

As you can notice the grows word is reduced to its lexeme grow.

plainto_tsquery

This operator transforms a string to a tsquery, that is a list of tokens and Boolean operators describing the terms we would like to search.

SELECT plainto_tsquery('growing')
---
'grow'

In short words, we apply to_tsvector on the database column containing the text we would like to match, and we apply plainto_tsquery on search input.

Custom operators on JPQL

So how do we use these cool text operators on Spring Boot without having to write native SQL queries directly?

First we have to declare a MetadataBuilderContributor class to register a new SQL function:

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("fts",
                new SQLFunctionTemplate(BooleanType.INSTANCE,
                        "to_tsvector(description) @@ plainto_tsquery(?1)"));
    }
}

Then on application.properties configuration file, we just need to add a reference to this class:

spring.jpa.properties.hibernate.metadata_builder_contributor=com.example.demo.fts.SqlFunctionsMetadataBuilderContributor

Here we're registering an fts function that receives one parameter and tries to match the parameter with description column from Pokemon table.

Repository

Now on repository class, we can use the fts operator directly on JPQL:

@Repository
public interface PokemonRepository extends CrudRepository<Pokemon, Long> {

    @Query("SELECT p FROM Pokemon p WHERE fts(:description) = true")
    List<Pokemon> search(@Param("description") String description);

}

Controller

Finally we implement our API that receives a search parameter on URL and call the respective method from its repository:

@RestController()
@RequestMapping("/pokemon")
public class PokemonController {

  @Autowired
  private PokemonRepository repository;

  @GetMapping()
  public List<Pokemon> findByDescription(@RequestParam String search) {
      return repository.search(search);
  }
}

You can test the endpoint by accessing http://localhost:8080/pokemon/?search=growing on you browser.

Conclusion

We learned how we can use JPA to power up full text search search queries. It can be very helpful on catching errors on compilation time while taking a lot of performance advantage on execution time.

Comments

[Michał Brzeziński]

it was working for few minutes and then

function plainto_tsquery(bytea) does not exist
Wskazówka: No function matches the given name and argument types. You might need to add explicit type casts.

do not understand why

[Matheus Henrique Raymundo]

Simple and very useful!