I'm using Spring Boot and Thymeleaf to set up a support ticketing system - currently working on the search functionality to search for existing tickets. Per business decision, certain business logic will be on the database, so we're using SQL stored procedures to populate our models rather than connecting directly to a table. I got the search functionality to work - now I'm working on paging the results.

Here is what I have:

Controller

@Autowired
private SearchResultTestService searchResultTestService;

@GetMapping("/searchresultstest")
public String searchResultsTest(Model model) {  
    int pageSize = 5;
    
    Page<SearchResult> page = searchResultTestService.findPaginated(1, pageSize, "requestnumber", "asc");
    List<SearchResult> listSearchResult = page.getContent();
    
    model.addAttribute("listSearchResult", listSearchResult);
    
    return "searchresultstest";
}

SearchResultTestService

    @Service
public interface SearchResultTestService {
    
    Page<SearchResult> findPaginated(int pageNo, int pageSize, String sortField, String sortDirection);
}

SearchResultTestServiceImpl

        @Service
    public class SearchResultTestServiceImpl implements SearchResultTestService{
        
        @Autowired
        private SearchResultTestRepository searchResultTestRepo;
        
        @Override
        public Page<SearchResult> findPaginated(int pageNo, int pageSize, String sortField, String sortDirection) {
            Sort sort = sortDirection.equalsIgnoreCase(Sort.Direction.ASC.name()) ? Sort.by(sortField).ascending() :
                Sort.by(sortField).descending();
                    
            Pageable pageable = PageRequest.of(pageNo - 1, pageSize, sort);
            return this.searchResultTestRepo.findAll(pageable);
        } 
    
    }

SearchResultTestRepository

@Repository
public interface SearchResultTestRepository extends JpaRepository<SearchResult, Long>{

}

SearchResult (model)

@Entity
//Map the columns returned from the stored procedure to the constructor
@SqlResultSetMapping(name = "SearchResult",
    classes = @ConstructorResult(targetClass = SearchResult.class,
        columns = {@ColumnResult(name = "requestnumber", type = Integer.class),
            @ColumnResult(name = "status", type = String.class),
            @ColumnResult(name = "assignedto", type = String.class),
            @ColumnResult(name = "opendate", type = String.class),
            @ColumnResult(name = "customer", type = String.class),
            @ColumnResult(name = "description", type = String.class)
        })
    )   
public class SearchResult {
    @Id
    private int requestnumber;
    private String status;
    private String assignedto;
    private String opendate;
    private String customer;
    private String description;
    
    //Constructor (must be in same order as mapping above)
    public SearchResult(int requestnumber, String status, String assignedto, String opendate, String customer,
            String description) {
        this.requestnumber = requestnumber;
        this.status = status;
        this.assignedto = assignedto;
        this.opendate = opendate;
        this.customer = customer;
        this.description = description;
    }

    /* getters */
}

And here is the actual chunk of code that calls the stored procedure:

PersistenceContext
private EntityManager entityManager;

@SuppressWarnings("unchecked")
public List<SearchResult> getSearchResults(){
    
    
    //Create list to hold all of the individual values (rows) returned by the query
    List<SearchResult> searchResults = new ArrayList<>();
                    
    //Set up a call to the stored procedure
    StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sc_app_test", "SearchResult");
                    
    try {
        //Execute the query and store the results
        query.execute();
        searchResults = query.getResultList();
    } finally {
        try {
            //Cleanup
            query.unwrap(ProcedureOutputs.class).release();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }       

    //Return the List of options
    return searchResults;
}

I am struggling to figure out where this last chunk of code is supposed to go in order to best get the pagination to work. Does this go in the service or in a repository? How does the "findAll()" method work when the model isn't tied to directly to a table?

Solution

The solution ended up being converting the list into a page as suggested below. This is the final "findPaginated" function:

@Service
public class SearchResultServiceImpl implements SearchResultService{
    
    @Autowired
    private SearchResultRepository searchResultRepo;
    
    @Override
    public PagedListHolder<SearchResult> findPaginated(SearchParameters searchParameters, int pageNo, int pageSize, String sortField, boolean ascending) {      
        //Get the results of the search from the repo
        List<SearchResult> searchResults = searchResultRepo.getSearchResults(searchParameters); 
        
        //Convert list into a list of pages
        PagedListHolder<SearchResult> page = new PagedListHolder<SearchResult>(searchResults);
        
        //Set our sort, page size, and page number
        page.setSort(new MutableSortDefinition(sortField, true, ascending));
        page.setPageSize(pageSize);
        page.setPage(pageNo-1); 
        
        //Return just the results for the page
        return page;
    } 

}

And the modification to the Controller:

PagedListHolder<SearchResult> page = searchResultService.findPaginated(searchParameters, pageNo,
                    Util.MAX_SEARCH_RESULTS_PER_PAGE, sortColumn, (sortDir.equals("desc") ? false : true));
    
 model.addAttribute("searchResultList", page.getPageList());

The code to get the actual search results was moved into the repo.

CodePudding user response：

You can convert your List to a Page by using the PageImpl constructor(s). If you want an unpaged Page all you have to do is the following:

Page<SearchResult> searchResultsPage = new PageImpl<>(searchResults);

If you want an actual paged result you'll also need a pageable and total records count. The conversion would then look like this:

Page<SearchResult> searchResultsPage = new PageImpl<>(searchResults, pageable, count);

A pageable can be created by using PageRequest:

var pageable = PageRequest.of(pageNumber, pageSize, sort); 

CodePudding user response：

If you are using jpa and spring, why not use the PaginationRepository from Spring?

https://www.baeldung.com/spring-data-jpa-pagination-sorting