Search Service Layer

Watch Code

In this lesson, we'll implement search functionality for published events using PostgreSQL's text search capabilities in the service layer. This will allow users to find events by searching terms that match event names and venues.

PostgreSQL Text Search Query

Let's look at the SQL query that powers our search functionality:

@Query(value = "SELECT * FROM events WHERE " +
    "status = 'PUBLISHED' AND " +
    "to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(venue, '')) " +
    "@@ plainto_tsquery('english', :searchTerm)",
    countQuery = "SELECT count(*) FROM events WHERE " +
        "status = 'PUBLISHED' AND " +
        "to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(venue, '')) " +
        "@@ plainto_tsquery('english', :searchTerm)",
    nativeQuery = true)
Page<Event> searchEvents(@Param("searchTerm") String searchTerm, Pageable pageable);

This query:

Uses PostgreSQL's to_tsvector to create a searchable text vector from the name and venue fields
Applies plainto_tsquery to convert the search term into a format PostgreSQL can use
Only returns events with PUBLISHED status
Supports pagination through Spring Data JPA's Pageable parameter

Service Layer Implementation

The service layer implementation connects the repository query to our application:

@Override
public Page<Event> searchPublishedEvents(String query, Pageable pageable) {
    return eventRepository.searchEvents(query, pageable);
}

Summary

Added the searchEvents custom query to the EventRepository interface
Added the searchPublishedEvents method to the EventService interface
Implemented the searchPublishedEvents method in the EventServiceImpl class