There is a requirement for a user interface with a search function:
The search process is as follows:
This requirement involves two entities:
- “Rating, Username” data
Useris related to entities - “Create date (create date), number of views (number of views), title (title), body (body)” are
Storyrelated to entities
Features that need to be supported include Userfrequent modifications of ratings in entities and the following search functions:
- Range search by User rating
- Range search by Story creation date
- Range search by Story views
- Full text search by Story title
- Full text search by Story text
Contents
Create table structures and indexes in Postgres
Create userstables and storiesindexes related to corresponding search requirements, including:
- Use btree index to support search by User rating
- Use btree index to support search by Story creation date and number of views
- Use gin index to support full-text search content (also create full-text search columns
fulltext, type usedtsvectorto support full-text search)
The specific creation script is as follows:
--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)
;
Create a Spring Boot application
- Project dependencies (built using Gradle here):
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.yamlConfigure database connection information in
spring:
datasource:
url: jdbc:postgresql://localhost:5432/postgres
username: postgres
password: postgres
- data model
Define the various data models that need to be used:
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) {
}
This is implemented using the new feature record introduced in Java 16 , so the code is very concise. If you don’t understand it yet, you can go to Programmer DD’s Java New Features column to supplement your knowledge.
- Data access (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 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);
}
}
summary
This article introduces how to implement the full-text search function in Spring Boot combined with the Postgres database. This method is more lightweight than using Elasticsearch and is very suitable for some small project scenarios. Hope this article is helpful to you. What if you encounter difficulties while studying? You can join our super-high-quality Spring technology exchange group to participate in exchanges and discussions for better learning and progress! For more Spring Boot tutorials, click here! , welcome to collect and forward for support!