Skip to main content

How to Implement PostgreSQL Full-Text Search with Spring Boot

In this article, we will explore how to implement full-text search with PostgreSQL in a Spring Boot application.

1) Set up PostgreSQL

Here's an example PostgreSQL query to create a PostgreSQL database named "postgres-full-text-search-db", create a table named "articles", and insert 10 rows of sample data into the "articles" table.

Now, let's say we want to perform a full-text search on the data in the "title" and "content" columns. We can add a new column to the table to store the preprocessed search document (i.e., the list of lexemes):

The above query is also helpful when we have an existing table filled with data and want to enable full-text search on that data for selected columns. Now, even if we add or update data in the table, the data will be automatically processed and added to the ts column for use in full-text search.

Now, if we query the table, we should see processed data in the ts column, as shown below:


We can then create a GIN index on the "ts" column:

Now we can query this like:

This SQL query performs a full-text search on the articles table and ranks the results based on their relevance to the search query.

"Ts_rank(ts, Phraseto_tsquery('english', 'Shawshank Redemption')) AS rank" calculates the relevance rank of each article based on the search query. Ts_rank returns a numeric rank representing how well the article matches the search query. The Phraseto_tsquery function creates a query that matches the exact phrase "Shawshank Redemption" in the specified language ('english').

"ts @@ phraseto_tsquery('english', 'Shawshank Redemption')" filters the rows to include only those where the ts column matches the search query. The @@ operator is used for full-text search matching.

"rank DESC" orders the results by the relevance rank in descending order, so the most relevant articles appear first.

"LIMIT 10" Limits the number of rows returned to 10.

"OFFSET 0" Skips the first 0 rows, effectively starting at the beginning of the result set.

2) Create a Spring Boot Project

Creating a Spring Boot project using Spring Initializr is straightforward. Go to Spring Initializr. Click on "Add Dependencies" and select the dependencies we need.

If everything went well, our final pom.xml should look something like this:

2.1) Create Entity

For full-text search, we'll use the "tsvector" column to store precomputed search vectors. In our entity class, we can mark fields that should be used for full-text search. For example:


2.2) Create Repository

To perform full-text search queries in PostgreSQL from Spring Boot, we can use the @Query annotation in our repository interface. Here’s an example:

2.3) Create Service

Let's add a service layer that converts the list of object arrays returned by the repository method into a list of Article objects.

2.4) Create Controller

Use the service in our controller to handle HTTP requests:

2.5) Configure PostgreSQL

To configure PostgreSQL we need to set up the appropriate properties in our application.yml file:

2.6) Run & Test

If you are using an IDE like IntelliJ IDEA or Eclipse, you can run your Spring Boot application directly. Locate the main application class (the one with @SpringBootApplication annotation). Right-click on the class and select "Run" or "Debug".

To test the API we created for full-text search, we can use various methods, including cURL, Postman, and unit tests. For now, let's test it in the browser:

Source Code: GitHub

Labels:

Comments

Post a Comment

Popular posts from this blog

Deploying Spring Boot microservices on Kubernetes Cluster

This article guides you through the deployment of two Spring Boot microservices, namely "order-service" and "inventory-service," on Kubernetes using "MiniKube" . We will establish communication between them, with "order-service" making calls to an endpoint in "inventory-service." Additionally, we will configure "order-service" to be accessible from the local machine's browser . 1) Create Spring Boot microservices The Spring Boot microservices, "order-service" and "inventory-service," have been developed and can be found in this GitHub repository. If you are interested in learning more about creating Spring Boot REST microservices, please refer to this or this (Reactive) link. 2) Build Docker Images The Docker images for both "order-service" and "inventory-service" have already been generated and deployed on DockerHub, as shown below. codeburps/order-service cod...
Post a Comment
Read more

Circuit Breaker Pattern with Resilience4J in a Spring Boot Application

Read Also: Spring Cloud Circuit Breaker + Resilience4j Resilience4j is a lightweight fault tolerance library that draws inspiration from Netflix Hystrix but is specifically crafted for functional programming. The library offers higher-order functions, known as decorators , designed to augment any functional interface, lambda expression, or method reference with features such as Circuit Breaker, Rate Limiter, Retry, or Bulkhead . These functionalities can be seamlessly integrated within a project, class, or even applied to a single method. It's possible to layer multiple decorators on any functional interface, lambda expression, or method reference, allowing for versatile and customizable fault tolerance. While numerous annotation-based implementations exist online, this article focuses solely on the reactive approach using router predicates and router functions . How Circuit Breaker Pattern works? In general, a circuit breaker functions as an automatic electrical s...
Post a Comment
Read more

How to create a basic Spring 6 project using Maven

Below is a step-by-step guide to creating a basic Spring project using Maven. 1) Create a Maven Project Use the following Maven command to create a new Maven project. mvn archetype:generate -DgroupId=com.tb -DartifactId=spring-demo -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false 2) Import in IntelliJ IDEA If you haven't already, open IntelliJ IDEA on your system. Go to "File" > "New" > "Project from Existing Sources..." . In the file dialog, navigate to the directory where your Maven project is located. Select the pom.xml file within the project directory and click "Open." 3) Update pom.xml In total, the application requires the below-mentioned dependencies: 4) Create Spring Configuration Create a Java configuration class that uses annotations to define your Spring beans and their dependencies. This class should be annotated with @Configuration . 5) Create the Main Application C...
Post a Comment
Read more