How to Build a Full‑Text Search API with Spring Boot and PostgreSQL

The article describes a UI with search functionality that involves two entities: User (rating, username) and Story (create date, number of views, title, body). Required search features include range searches on rating, creation date, view count, and full‑text searches on title and body.

Create PostgreSQL tables and indexes

Two tables, users and stories, are created along with indexes to support the search requirements.

-- Create Users table
CREATE TABLE IF NOT EXISTS users (
  id bigserial NOT NULL,
  name character varying(100) NOT NULL,
  rating integer,
  PRIMARY KEY (id)
);

CREATE INDEX usr_rating_idx
ON users USING btree (rating ASC NULLS LAST);

-- Create Stories table
CREATE TABLE IF NOT EXISTS stories (
  id bigserial NOT NULL,
  create_date timestamp without time zone NOT NULL,
  num_views bigint NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  fulltext tsvector,
  user_id bigint,
  PRIMARY KEY (id),
  CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES users (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION NOT VALID
);

CREATE INDEX str_bt_idx
ON stories USING btree (create_date ASC NULLS LAST, num_views ASC NULLS LAST, user_id ASC NULLS LAST);

CREATE INDEX fulltext_search_idx
ON stories USING gin (fulltext);

Create Spring Boot application

Project dependencies (Gradle):

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.1.3'
    id 'io.spring.dependency-management' version '1.1.3'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'

java {
    sourceCompatibility = '17'
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    runtimeOnly 'org.postgresql:postgresql'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

tasks.named('test') {
    useJUnitPlatform()
}

Database connection configuration ( application.yaml ):

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/postgres
    username: postgres
    password: postgres

Data models using Java 16 records:

public record Period(String fieldName, LocalDateTime min, LocalDateTime max) {}
public record Range(String fieldName, long min, long max) {}
public record Search(List<Period> periods, List<Range> ranges, String fullText, long offset, long limit) {}
public record UserStory(Long id, LocalDateTime createDate, Long numberOfViews,
                         String title, String body, Long userRating,
                         String userName, Long userId) {}

Repository implementation:

@Repository
public class UserStoryRepository {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public UserStoryRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<UserStory> findByFilters(Search search) {
        return jdbcTemplate.query(
            """
                SELECT s.id id, create_date, num_views,
                       title, body, user_id, name user_name,
                       rating user_rating
                FROM stories s INNER JOIN users u
                  ON s.user_id = u.id
                WHERE true
            """ + buildDynamicFiltersText(search) +
            " order by create_date desc offset ? limit ?",
            (rs, rowNum) -> new UserStory(
                rs.getLong("id"),
                rs.getTimestamp("create_date").toLocalDateTime(),
                rs.getLong("num_views"),
                rs.getString("title"),
                rs.getString("body"),
                rs.getLong("user_rating"),
                rs.getString("user_name"),
                rs.getLong("user_id")
            ),
            buildDynamicFilters(search)
        );
    }

    public void save(UserStory userStory) {
        var keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(
                """
                    INSERT INTO stories (create_date, num_views, title, body, user_id)
                    VALUES (?, ?, ?, ?, ?)
                """,
                Statement.RETURN_GENERATED_KEYS
            );
            ps.setTimestamp(1, Timestamp.valueOf(userStory.createDate()));
            ps.setLong(2, userStory.numberOfViews());
            ps.setString(3, userStory.title());
            ps.setString(4, userStory.body());
            ps.setLong(5, userStory.userId());
            return ps;
        }, keyHolder);
        Long generatedId = (Long) keyHolder.getKeys().get("id");
        if (generatedId != null) {
            updateFullTextField(generatedId);
        }
    }

    private void updateFullTextField(Long generatedId) {
        jdbcTemplate.update(
            """
                UPDATE stories SET fulltext = to_tsvector(title || ' ' || body)
                WHERE id = ?
            """,
            generatedId
        );
    }

    private Object[] buildDynamicFilters(Search search) {
        var filters = Stream.concat(
            search.ranges().stream().flatMap(r -> Stream.of((Object) r.min(), r.max())),
            search.periods().stream().flatMap(p -> Stream.of((Object) Timestamp.valueOf(p.min()), Timestamp.valueOf(p.max())))
        );
        if (!search.fullText().isBlank()) {
            filters = Stream.concat(filters, Stream.of(search.fullText()));
        }
        filters = Stream.concat(filters, Stream.of(search.offset(), search.limit()));
        return filters.toArray();
    }

    private String buildDynamicFiltersText(Search search) {
        String rangesFilter = Stream.concat(
            search.ranges().stream().map(r -> String.format(" and %s between ? and ? ", r.fieldName())),
            search.periods().stream().map(p -> String.format(" and %s between ? and ? ", p.fieldName()))
        ).collect(Collectors.joining(" "));
        return rangesFilter + (search.fullText().isBlank() ? "" : " and fulltext @@ plainto_tsquery(?) ");
    }
}

Controller implementation:

@RestController
@RequestMapping("/user-stories")
public class UserStoryController {
    private final UserStoryRepository userStoryRepository;

    @Autowired
    public UserStoryController(UserStoryRepository userStoryRepository) {
        this.userStoryRepository = userStoryRepository;
    }

    @PostMapping
    public void save(@RequestBody UserStory userStory) {
        userStoryRepository.save(userStory);
    }

    @PostMapping("/search")
    public List<UserStory> search(@RequestBody Search search) {
        return userStoryRepository.findByFilters(search);
    }
}

Conclusion

The tutorial demonstrates a lightweight approach to adding full‑text search to a Spring Boot application using PostgreSQL’s native capabilities, avoiding the overhead of external search engines and making it suitable for small‑to‑medium projects.