How I created a dynamic search and sort query with Spring Boot
This is a summary of some updates to this website this past month.
Today, I added a blog search and sort system. It allows users to search for substrings in a blog's title, description, or body and order ascending or descending by date, likes, title, or views. This is done with some SQL queries and Spring Boot, as shown below:
In the BlogService, we are creating a Pageable object with the desired Sort based on the BlogSearchForm and the desired page size and number based on the PaginationForm. In the BlogRepository, we create a SQL query that selects blogs if ":search" is a substring of the title, description, or body, case-insensitive. Spring creates the function that returns the desired page in the background. This is the final product:
Smaller updates also include previous/next buttons in blogs, caching for improved load times, annotations for RateLimitService and AdminAuthorization, and icons for skills. As always, the repository for the site's backend can be found here.
In the future, I might create a blog on how to self-host a dynamic website hosted on Ubuntu or Debian-based computers at no cost, secured with a Cloudflare tunnel, since I've had some people ask me how this site is set up.
Today, I added a blog search and sort system. It allows users to search for substrings in a blog's title, description, or body and order ascending or descending by date, likes, title, or views. This is done with some SQL queries and Spring Boot, as shown below:
...
...
public Page<Blog> getBlogs(HttpServletRequest request, BlogSearchForm blogSearchForm, PaginationForm paginationForm) {
Direction direction = (blogSearchForm.getAscending()) ? Sort.Direction.ASC : Sort.Direction.DESC;
Sort sort = Sort.by(direction, blogSearchForm.getOrderBy());
Pageable pageable = PageRequest.of(paginationForm.getPageNum(), paginationForm.getPageSize(), sort);
Page<Blog> page = (blogSearchForm.getLiked()) ?
blogRepository.findAllLikedWithPagination(pageable, UserService.getUserAddress(request), blogSearchForm.getSearch()) :
blogRepository.findAllWithPagination(pageable, blogSearchForm.getSearch());
...
...
@Repository
public interface BlogRepository extends JpaRepository<Blog, Long> {
@Query(value = "SELECT * FROM blogs b "
+ "WHERE UPPER(CONCAT(b.title, ' ', b.description, ' ', b.body)) LIKE CONCAT('%',UPPER(:search),'%')", nativeQuery = true)
public Page<Blog> findAllWithPagination(Pageable pageable, @Param("search") String search);
@Query(value = "SELECT b.id, b.body, b.date, b.like_count, b.title, b.description, b.unix_time, b.view_count FROM blogs b "
+ "LEFT JOIN (blog_user b_u JOIN users u on u.id=b_u.user_id) ON b.id=b_u.blog_id "
+ "WHERE u.address=:address AND UPPER(CONCAT(b.title, ' ', b.description, ' ', b.body)) LIKE CONCAT('%',UPPER(:search),'%')", nativeQuery = true)
public Page<Blog> findAllLikedWithPagination(Pageable pageable, @Param("address") String address, @Param("search") String search);
...
...
In the BlogService, we are creating a Pageable object with the desired Sort based on the BlogSearchForm and the desired page size and number based on the PaginationForm. In the BlogRepository, we create a SQL query that selects blogs if ":search" is a substring of the title, description, or body, case-insensitive. Spring creates the function that returns the desired page in the background. This is the final product:
Smaller updates also include previous/next buttons in blogs, caching for improved load times, annotations for RateLimitService and AdminAuthorization, and icons for skills. As always, the repository for the site's backend can be found here.
In the future, I might create a blog on how to self-host a dynamic website hosted on Ubuntu or Debian-based computers at no cost, secured with a Cloudflare tunnel, since I've had some people ask me how this site is set up.