有这样一个带有搜寻性能的用户界面需要：
搜寻流程如下所示：
这个需要波及两个实体：
- “评分（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 tableCREATE TABLE IF NOT EXISTS users( id bigserial NOT NULL, name character varying(100) NOT NULL,rating integer,PRIMARY KEY (id));CREATE INDEX usr_rating_idxON users USING btree(rating ASC NULLS LAST)TABLESPACE pg_default;--Create Stories tableCREATE 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 SIMPLEON UPDATE NO ACTIONON DELETE NO ACTIONNOT VALID);CREATE INDEX str_bt_idxON stories USING btree(create_date ASC NULLS LAST,num_views ASC NULLS LAST, user_id ASC NULLS LAST);CREATE INDEX fulltext_search_idxON 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推出的新个性record实现，所以代码十分简洁。如果您还不理解的话，能够返回程序猿DD的Java新个性专栏补全一下知识点。
- 数据拜访（Repository）
@Repositorypublic 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技术交换群，参加交换与探讨，更好的学习与提高！更多Spring Boot教程能够点击中转！，欢送珍藏与转发反对！
参考资料
- Postgres full-text search Spring boot integration
- Java 16新个性：record
欢送关注我的公众号：程序猿DD。第一工夫理解前沿行业音讯、分享深度技术干货、获取优质学习资源