Setup Spring and Postgres for Full Text Search
Tags: java, tutorial, postgres, database
Published: 2020-07-07 12:31:30

# 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: 
{% codesandbox fabiothirokireact-pokedex-textsearch-w06r3 %}

Backend code is on Github:
{% github fabiothiroki/spring-pokemon-textsearch no-readme %}

Credits for frontend design goes to [Florin Pop](https://codepen.io/FlorinPop17).

# Setup
Go to [Spring Initializr](https://start.spring.io/) 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

![Spring configuration](https://dev-to-uploads.s3.amazonaws.com/i/s7oizafhht10nyzt1m2b.png)

# 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:
```sql
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:
```sql
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.

```sql
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.
```sql
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:
```java
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:
```java
@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:
```java
@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](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.