Pagination, Filter and Sorting implementation at repository layer - Rest/GraphQL API

[DevShivmohan]

Implementation Pagination, Filter and Sorting at DB layer

Service layer implementation

import org.springframework.data.domain.*;

@Override
    public ResponseEntity<?> fetchRecordsWithPaginationFilterSorting(final PaginationRequestDto paginationRequestDto, final NGram filterDto) {
        final Sort.Direction direction = (paginationRequestDto.getSortOrder() == null || paginationRequestDto.getSortOrder() == SortOrder.ASC) ? Sort.Direction.ASC : Sort.Direction.DESC;
        final PaginationResponseDto paginationResponseDto = new PaginationResponseDto();
        final PageRequest pageRequest = PageRequest.of(paginationRequestDto.getPageNumber(), paginationRequestDto.getPageSize(), direction, paginationRequestDto.getSortColumn());
        if (filterDto == null) {
            final Page<NGram> page = nGramRepository.findAll(pageRequest);
            mapPageToEntity(page, paginationResponseDto);
            return ResponseEntity.status(HttpStatus.OK).body(paginationResponseDto);
        }
        final ExampleMatcher customExampleMatcher = ExampleMatcher.matchingAny();
        final Example<NGram> example = Example.of(filterDto, customExampleMatcher);
        final Page<NGram> page = nGramRepository.findAll(example, pageRequest);
        mapPageToEntity(page, paginationResponseDto);
        return ResponseEntity.status(HttpStatus.OK).body(paginationResponseDto);
    }

private void mapPageToEntity(final Page<NGram> page, final PaginationResponseDto paginationResponseDto) {
        paginationResponseDto.setContent(page.getContent());
        paginationResponseDto.setPageNumber(page.getNumber());
        paginationResponseDto.setPageSize(page.getSize());
        paginationResponseDto.setTotalPages(page.getTotalPages());
        paginationResponseDto.setTotalElements(page.getNumberOfElements());
        paginationResponseDto.setLastPage(page.isLast());
    }

API layer implementation

@GetMapping
    public ResponseEntity<?> findAllRecordsWithPaginationFilterSorting(
            @RequestParam(value = "pageNumber",defaultValue = "0",required = false) Integer pageNumber,
            @RequestParam(value = "pageSize",defaultValue = "10",required = false) Integer pageSize,
            @RequestParam(value = "sortOrder",defaultValue = "ASC",required = false) SortOrder sortOrder,
            @RequestParam(value = "sortColumn",defaultValue = "name",required = false) String sortColumn,
            @RequestParam(value = "name",required = false) String name,
            @RequestParam(value = "bio",required = false) String bio
    ){
        final PaginationRequestDto paginationRequestDto=new PaginationRequestDto();
        paginationRequestDto.setPageNumber(pageNumber);
        paginationRequestDto.setPageSize(pageSize);
        paginationRequestDto.setSortOrder(sortOrder);
        paginationRequestDto.setSortColumn(sortColumn);

        final NGram filter=(name==null || bio==null) ? null : new NGram();
        if(filter!=null){
            filter.setName(name);
            filter.setBio(bio);
        }
        return nGramService.fetchRecordsWithPaginationFilterSorting(paginationRequestDto,filter);
    }

Models

@Data
public class PaginationRequestDto {
    private int pageNumber;
    private int pageSize;
    private SortOrder sortOrder;
    private String sortColumn;
}

@Data
public class PaginationResponseDto {
    private int pageNumber;
    private long pageSize;
    private int totalElements;
    private int totalPages;
    private boolean lastPage;
    private List<NGram> content;
}

public enum SortOrder {
    ASC,DESC
}

Entity

@Entity
@Table(name = "n_gram")
@Data
public class NGram {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String name;
    private String bio;
}

[DevShivmohan]

API request payload

API response

{
    "pageNumber": 0,
    "pageSize": 20,
    "totalElements": 20,
    "totalPages": 4,
    "lastPage": false,
    "content": [
        {
            "id": 31,
            "name": "Xenophilius Lovegood",
            "bio": "Father of Luna and editor of The Quibbler."
        },
        {
            "id": 5,
            "name": "Vincent Crabbe Sr.",
            "bio": "Father of Crabbe and death-eater who escaped Azkaban."
        },
        {
            "id": 6,
            "name": "Vincent Crabbe",
            "bio": "Slytherin student who was best friends with Goyle and followed Draco."
        },
        {
            "id": 25,
            "name": "Viktor Krum",
            "bio": "Participant in the Triwizard tournament. Dated Hermione."
        },
        {
            "id": 15,
            "name": "Vernon Dursley",
            "bio": "Harry's muggle uncle."
        },
        {
            "id": 44,
            "name": "Tom Riddle Sr.",
            "bio": "Muggle father of Voldemort who was killed by him."
        },
        {
            "id": 2,
            "name": "Sirius Black",
            "bio": "Best friend of James Potter and godfather of Harry."
        },
        {
            "id": 48,
            "name": "Severus Snape",
            "bio": "Head of the Slytherin house and saved Harry in many occasions."
        },
        {
            "id": 17,
            "name": "Seamus Finnigan",
            "bio": "Harry's friend and member of Dumbledore's army."
        },
        {
            "id": 23,
            "name": "Rubeus Hagrid",
            "bio": "Half-giant who loves Harry. He was the keeper of Keys and Grounds at Hogwards."
        },
        {
            "id": 59,
            "name": "Ron Weasley",
            "bio": "Harry's best friend. Marries Hermione."
        },
        {
            "id": 47,
            "name": "Rita Skeeter",
            "bio": "Reporter at the Daily Prophet."
        },
        {
            "id": 32,
            "name": "Remus Lupin",
            "bio": "Friend of James Potter and werewolf. He married Nymphadora."
        },
        {
            "id": 1,
            "name": "Regulus Arcturus Black",
            "bio": "Brother of Sirius. Used to be a Death Eater but defected."
        },
        {
            "id": 43,
            "name": "Quirinus Quirrell",
            "bio": "Possessed by Voldemort. Defence against the Dark Arts professor."
        },
        {
            "id": 14,
            "name": "Petunia Dursley",
            "bio": "Harry's aunt and sister of Lily."
        },
        {
            "id": 39,
            "name": "Peter Pettigrew",
            "bio": "Betrays James and Lily Potter. Follower of Voldemort."
        },
        {
            "id": 58,
            "name": "Percy Weasley",
            "bio": "Third son of Arthur and Molly. He is a Gryffindor prefect."
        },
        {
            "id": 36,
            "name": "Olympe Maxime",
            "bio": "Half-giantess and headmistress of Beauxbatons."
        },
        {
            "id": 49,
            "name": "Nymphadora Tonks",
            "bio": "Married Remus Lupin and was killed by Bellatrix."
        }
    ]
}

https://github.com/DevShivmohan/Learning-everything/assets/72655528/f1d6ae81-8917-409e-a3a2-46a582730d6b

[DevShivmohan]

Pagination with custom native query at JPA level

code block

@Repository
public interface NGramRepository extends JpaRepository<NGram,Integer> {
    @Query(nativeQuery = true,value = ApiConstant.N_GRAM_SEARCH_QUERY)
    List<NGram> findByNgram(@Param("keyword") String keyword);

    @Query(value = "SELECT *, MATCH(name, bio) AGAINST(:keyword) AS score FROM n_gram WHERE MATCH(name, bio) AGAINST(:keyword) ORDER BY score DESC",
           countQuery = "SELECT COUNT(*) FROM n_gram WHERE MATCH(name, bio) AGAINST(:keyword)",
           nativeQuery = true)
    Page<NGram> findAllWithPagination(@Param("keyword") String keyword, Pageable pageable);
}