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

淡淡的烟草味﹌ 2024-04-25 08:51 10阅读 0赞

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

![fd89bd09b89545c2b6f104903bda331d.png][]

搜索流程如下所示：

![4c714491044e455982732c0b28265bc6.png][]

这个需求涉及两个实体：

*  “评分（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应用 ###

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

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

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

1.  数据模型

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

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推出的新特性record][Java 16_record]实现，所以代码非常简洁。如果您还不了解的话，可以前往[程序猿DD的Java新特性专栏][Java 16_record]补全一下知识点。

1.  数据访问（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(?) ";
        }
    }

1.  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技术交流群][Spring]，参与交流与讨论，更好的学习与进步！更多[Spring Boot教程可以点击直达！][Spring Boot]，欢迎收藏与转发支持！

### 参考资料 ###

*  [Postgres full-text search Spring boot integration][]
 *  [Java 16新特性：record][Java 16_record]

> 欢迎关注我的公众号：程序猿DD。前沿技术早知道，弯道超车有希望！积累超车资本，从关注DD开始！

[fd89bd09b89545c2b6f104903bda331d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/3c59ec84a2364b40ae8b9695cd53ca99.png
[4c714491044e455982732c0b28265bc6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/9f8406fac1c3492f93c6fc6bb27ddbff.png
[Java 16_record]: https://www.didispace.com/java-features/java16/jep395-records.html
[Spring]: https://www.didispace.com/jiaqun.html
[Spring Boot]: https://www.didispace.com/spring-boot-2/
[Postgres full-text search Spring boot integration]: https://medium.com/@svosh2/postgres-full-text-search-spring-boot-integration-13cbeb7af570