Spring Boot整合Postgres实现轻量级全文搜索

有这样一个带有搜索功能的用户界面需求：

搜索流程如下所示：

这个需求涉及两个实体：

“评分（Rating）、用户名（Username）”数据与User实体相关
“创建日期（create date）、观看次数（number of views）、标题（title）、正文（body）”与Story实体相关

需要支持的功能对User实体中的评分（Rating）的频繁修改以及下列搜索功能：

按User评分进行范围搜索
按Story创建日期进行范围搜索
按Story浏览量进行范围搜索
按Story标题进行全文搜索
按Story正文进行全文搜索

# Postgres中创建表结构和索引

创建users表和stories表以及对应搜索需求相关的索引，包括：

使用 btree 索引来支持按User评分搜索
使用 btree 索引来支持按Story创建日期、查看次数的搜索
使用 gin 索引来支持全文搜索内容（同时创建全文搜索列fulltext，类型使用tsvector以支持全文搜索）

具体创建脚本如下：

--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)
TABLESPACE pg_default
;

--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)
;

# 创建Spring Boot应用

项目依赖关系（这里使用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()
}

application.yaml中配置数据库连接信息

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

定义需要用到的各种数据模型：

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) {
}

这里使用Java 16推出的新特性recordopen in new window实现，所以代码非常简洁。如果您还不了解的话，可以前往程序猿DD的Java新特性专栏open in new window补全一下知识点。

数据访问（Repository）

@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);

        var 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 filtersStream = search.ranges().stream()
                .flatMap(
                    range -> Stream.of((Object) range.min(), range.max())
                );

        var periodsStream = search.periods().stream()
                .flatMap(
                    range -> Stream.of((Object) Timestamp.valueOf(range.min()), Timestamp.valueOf(range.max()))
                );

        filtersStream = Stream.concat(filtersStream, periodsStream);

        if (!search.fullText().isBlank()) {
            filtersStream = Stream.concat(filtersStream, Stream.of(search.fullText()));
        }

        filtersStream = Stream.concat(filtersStream, Stream.of(search.offset(), search.limit()));

        return filtersStream.toArray();
    }

    private String buildDynamicFiltersText(Search search) {
        var rangesFilterString =
                Stream.concat(
                  search.ranges()
                        .stream()
                        .map(
                            range -> String.format(" and %s between ? and ? ", range.fieldName())
                        ),
                  search.periods()
                        .stream()
                        .map(
                            range -> String.format(" and %s between ? and ? ", range.fieldName())
                        )
                  )
                  .collect(Collectors.joining(" "));

        return rangesFilterString + buildFulltextFilterText(search.fullText());
    }

    private String buildFulltextFilterText(String fullText) {
        return fullText.isBlank() ? "" : " and fulltext @@ plainto_tsquery(?) ";
    }
}

Controller实现

@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);
    }
}

# 小结

本文介绍了如何在Spring Boot中结合Postgres数据库实现全文搜索的功能，该方法比起使用Elasticsearch更为轻量级，非常适合一些小项目场景使用。希望本文内容对您有所帮助。如果您学习过程中如遇困难？可以加入我们超高质量的Spring技术交流群open in new window，参与交流与讨论，更好的学习与进步！更多Spring Boot教程可以点击直达！open in new window，欢迎收藏与转发支持！

# Postgres中创建表结构和索引

# 创建Spring Boot应用

# 小结

# 参考资料

Recommend

新手刚拿上驾照,是租个共享汽车找个朋友带着练比较好，还是驾校陪练服务比较好

海康威视：公司专注感知智能，与Sora方向不同

China new year holiday spending surges past pre-pandemic levels

Mac apps to speed your work, and games to make you forget all that [Awesome Apps...

开工你们第一天都是怎样度过的？

苹果15耳机功能

苹果15会不会全系灵动岛

Awesomely Weird Alibaba Electric Vehicle of the Week: Box Truck on a Golf Cart C...

车企数据中台指标体系建设方法论

Cisco – re-imagining its identity and your identity-based trust

About Joyk