Pagination and sorting in Spring MVC 4

Question

I'm building a web app using spring mvc 4, thymeleaf and mysql (jdbc is used not hibernate or JPA) .. and I'm trying to do pagination and sorting, but I think I have a problem understanding it.

When I search for pagination using spring mvc all I can find is PagedListHolder and MutableSortDefinition, but I don't think it's the right way to go since it will load a list of all the data in server memory for each request, is that right? and if so, then what is the best way to implement pagination and sorting given that there are hundreds of thousands of records (advertisements for real-estate over a month, with almost 2500 ads a day)

So, could someone put a real-world example of how to implement pagination and sorting in a way that can be used with large data apps?

Answer 1

Try this way :

• findAll method with argument "PageRequest()" provides the server side pagination

• there are two methods

  PageRequest(int page, int size)

  PageRequest(int page, int size, Direction direction, String... properties)

View :

<table class="table">
            <thead style="background-color: #eee">
                <tr>
                    <td>Dispature</td>
                    <td>Service</td>
                    <td>Host</td>
                    <td>Value</td>
                </tr>
            </thead>
            <tbody>
                <tr ng-repeat="x in app.metricsList">
                    <td>{{x.dispature}}</td>
                    <td>{{x.service}}</td>
                    <td>{{x.host}}</td>
                    <td>{{x.value}}</td>
                </tr>
            </tbody>
        </table>
        <div align="center">
            <uib-pagination items-per-page="app.itemPerPage" num-pages="numPages"
                total-items="app.totalItems" boundary-link-numbers="true"
                ng-model="app.currentPage" rotate="false" max-size="app.maxSize"
                class="pagination-sm" boundary-links="true"
                ng-click="app.getPagableRecords()"></uib-pagination>        

            <div style="float: right; margin: 15px">
                <pre>Page: {{app.currentPage}} / {{numPages}}</pre>
            </div>          
        </div>

Js Controller :

app.controller('AllEntryCtrl',['$scope','$http','$timeout','$rootScope', function($scope,$http,$timeout,$rootScope){

    var app = this;
    app.currentPage = 1;
    app.maxSize = 5;
    app.itemPerPage = 5;
    app.totalItems = 0;

    app.countRecords = function() {
        $http.get("countRecord")
        .success(function(data,status,headers,config){
            app.totalItems = data;
        })
        .error(function(data,status,header,config){
            console.log(data);
        });
    };

    app.getPagableRecords = function() {
        var param = {
                page : app.currentPage,
                size : app.itemPerPage  
        };
        $http.get("allRecordPagination",{params : param})
        .success(function(data,status,headers,config){
            app.metricsList = data.content;
        })
        .error(function(data,status,header,config){
            console.log(data);
        });
    };

    app.countRecords();
    app.getPagableRecords();

}]);

Controller :

@RestController
public class HomeController {

@Autowired
private HomeRepo repo;

  @RequestMapping(value = "allRecordPagination", method = RequestMethod.GET)
    public Page<Metrics> getAllRecordPagination(@RequestParam("page") int page, @RequestParam("size") int size){
        return repo.findAll(new PageRequest(page-1, size));
    }
}

Repository :

@Repository
    public interface HomeRepo extends JpaRepository<Table, String>{
}

Answer 2

In order to expand my comments I want to share some code snippets to show how easy it is to implement Dandelion Datatables with Thymeleaf. So, on the client side I have two files: html for table render

....    
<table id="dTable" class="display dataTable" dt:table="true">
    <thead>
        <tr>
           <th th:text="#{requestId}"></th>
           <th th:text="#{clientTime}"></th>
           <th th:text="#{requestDate}"></th>
           <th th:text="#{receiver}"></th>
        </tr>
    </thead>
</table>
....

and js for table initialization

$(document).ready(function() {
    $('#dTable').DataTable( {
        ajax: { url: "/bhost/dtable_list"},
        processing: true,
        serverSide: true,
        bFilter: false,
        columns: [
            { data: "requestId" },
            { data: "clientTime" },
            { data: "requestDate" },
            { data: "receiver", orderable: false },
        ],
       lengthMenu: [50, 100, 200, 500],
       language: {
         thousands: " "
      }
    } );
} );

Even though it's possible to configure dandelion datatables only in html I prefer to use JQuery way to do it, since it's more flexible.

On the server side we used our own created database access layer (not quite useful to share it) and dandelion DatatablesCriterias class to get current state of table (current page index, page length, selected sorted column etc.)

    Controller
....
        @RequestMapping(value = "/dtable_list")
        @ResponseBody
        public DatatablesResponse<DataDTO> getTableData(HttpServletRequest request) {
            HttpSession session = request.getSession();
            DataModel model = (DaatModel) session.getAttribute(MODEL_NAME);
            DatatablesCriterias criterias = DatatablesCriterias.getFromRequest(request);
            List<DataDTO> list = finder.getForCriterias(model, timeZoneOffset, criterias);
            Long totalCount = model.getCount();
            return DatatablesResponse.build(new DataSet<>(list, totalCount, totalCount), criterias);
        }
....

The key feature here is DatatablesCriterias, since it contains all necessary data to retrieve entries that correlate with user selection. And that's pretty much it (Except configuration part, I guess)

Answer 3

this is an example of pagination with SpringBoot and Thymeleaf templates, try it!! clone it and run it