Close

Spring Data JPA - Web support, Sorting And Pagination

[Updated: Jul 4, 2018, Created: Jun 13, 2018]

In previous tutorials we have seen how to use Spring Data pagination web support. In this tutorial we will see how to use sorting support.

There are mainly two ways to apply sorting in a web based application:

Sorting by Pageable handler parameter

We have used Pageable in our previous examples, but without any sorting query parameters. Other than pagination information, a Pageable instance can also be set with sorting information (check out this and this). In this case we have to use query string similar to:

?page=1&size=10&sort=personName,desc

which is equivalent to:

  PageRequest.of(1,10, Sort.by("personName").descending())

Sorting by Sort handler parameter

We can also use Sort as our controller method parameter (check our Sort basic example). In this case we need to create query string similar to:

?sort=personName,desc

which is equivalent to:

  Sort.by("personName").descending()

@SortDefault annotation

This annotation defines the default Sort options to be used when injecting a Sort instance into a controller handler method.

SortHandlerMethodArgumentResolver

In both above cases, SortHandlerMethodArgumentResolver is used to automatically create Sort instances from request parameters or SortDefault annotations. This resolver is active when we use @EnableSpringDataWebSupport in our configuration class.

Example

Entity

@Entity
public class Employee {
  private @Id
  @GeneratedValue
  Long id;
  private String name;
  private String dept;
  private int salary;
    .............
}

Repository

public interface EmployeeRepository extends PagingAndSortingRepository<Employee, Long> {

  @Query("SELECT DISTINCT e.dept FROM Employee e")
  List<String> findAllDepartments(Sort sort);
}

We created a custom query method in above repository with Sort parameter so that we can use Sort parameter in our controller which will only produce department list for the view.

MVC Controller

In following controller we are going to use two handler methods, one is using Pageable parameter and other one is using Sort parameter:

@Controller
public class EmployeeController {

  @Autowired
  private EmployeeRepository repository;

  @GetMapping("/employees")
  public String getEmployees(@PageableDefault(size = 10, sort = "id") Pageable pageable,
                             Model model) {
      Page<Employee> page = repository.findAll(pageable);
      List<Sort.Order> sortOrders = page.getSort().stream().collect(Collectors.toList());
      if (sortOrders.size() > 0) {
          Sort.Order order = sortOrders.get(0);
          model.addAttribute("sortProperty", order.getProperty());
          model.addAttribute("sortDesc", order.getDirection() == Sort.Direction.DESC);
      }
      model.addAttribute("page", page);
      return "employee-page";
  }

  @GetMapping("departments")
  public String getDepartments(@SortDefault(sort="dept",direction = Sort.Direction.ASC)
                                           Sort sort, Model model) {
      List<String> depts = repository.findAllDepartments(sort);
      model.addAttribute("depts", depts);
      return "dept-page";
  }
}

Thymeleaf views

Following view implements both sorting and pagination together. We are doing single column sorting which is performed by clicking on table header. This is similar to Java Swing JTable sorting functionality (check out example here).

We are using JQuery to handle table header clicking and showing arrows; ▾ and ▴ at the right place for descending and ascending sorting respectively.

src/main/webapp/WEB-INF/views/employee-page.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<head>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script th:inline="javascript">
    //thymeleaf to javascript variables
    /*<![CDATA[*/
    var sortProperty = /*[[${sortProperty}]]*/ null;
    var sortDesc = /*[[${sortDesc}]]*/ null;
    var currentPage = /*[[${page.number}]]*/ 0;
    var pageSize =/*[[${page.size}]]*/ 0;
    /*]]>*/

    $(document).ready(function(){
     //show up/down arrows
     $("table#emp-table thead th").each(function(){
        var head = $(this);
        if(head.attr('data-sort-prop')==sortProperty){
            head.append(sortDesc?'▾':'▴');
        }
        });

     //set click action, reload page on clicking with all query params
     $("table#emp-table thead th").click(function() {
      var headerSortPropName = $(this).attr("data-sort-prop");
      if(headerSortPropName==sortProperty){
          window.location.href = window.location.pathname+
          '?page='+currentPage+'&size='+pageSize+'&sort='+ headerSortPropName+','+
          (sortDesc?'asc':'desc');
      }else{
           window.location.href = window.location.pathname+
          '?page='+currentPage+'&size='+pageSize+'&sort='+ headerSortPropName+',asc';
      }
      });
    });
  </script>
  <style>
     table{width:100%;}
     table td, table th { border: 1px solid grey;}
     table th { user-select: none; background: #eee;}
     table tr th:first-child{width:100px;}
     table tr th:nth-child(3){width:150px;}
     table tr th:nth-child(4){width:150px;}
     .pagination-div{user-select: none;}
     .pagination-div span{border-radius:3px;border:1px solid #999;
         padding:5px;margin:10px 0px 0px 10px;display:inline-block}
     span.selected{background:#ccf;}
  </style>
</head>
<body>
<h2>Employees</h2>
<table id="emp-table">
    <thead>
    <tr>
        <th data-sort-prop="id">Id</th>
        <th data-sort-prop="name">Name</th>
        <th data-sort-prop="dept">Department</th>
        <th data-sort-prop="salary">Salary</th>
    </tr>
    </thead>
    <tr th:each="employee : ${page.content}">
        <td th:text="${employee.id}"></td>
        <td th:text="${employee.name}"></td>
        <td th:text="${employee.dept}"></td>
        <td th:text="${employee.salary}"></td>
    </tr>
</table>
<!-- using th:with to declare a local variable for sorting query param -->
<div class="pagination-div" th:with="sortParam=${sortProperty+','+(sortDesc?'desc':'asc')}">
    <span th:if="${page.hasPrevious()}">
        <a th:href="@{/employees(page=${page.number-1},size=${page.size},sort=${sortParam})}">Previous</a>
    </span>
    <th:block th:each="i: ${#numbers.sequence(0, page.totalPages - 1)}">
        <span th:if="${page.number == i}" class="selected">[[${i}+1]]</span>
        <span th:unless="${page.number == i}">
             <a th:href="@{/employees(page=${i},size=${page.size},sort=${sortParam})}">[[${i}+1]]</a>
        </span>
    </th:block>
    <span th:if="${page.hasNext()}">
        <a th:href="@{/employees(page=${page.number+1},size=${page.size},sort=${sortParam})}">Next</a>
    </span>
</div>
</body>
</html>

Following view shows only department list. The corresponding handler method (as seen above) only uses Sort parameter.

src/main/webapp/WEB-INF/views/dept-page.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
<body>
<h2>Departments</h2>
    <p th:each="dept : ${depts}">
        <td th:text="${dept}"></td>
    </p>
<br>
<a th:href="@{/departments(sort='dept,asc')}">In ascending order</a> <br/>
<a th:href="@{/departments(sort='dept,desc')}">In descending order</a>
</body>
</html>

Running

To try examples, run embedded tomcat (configured in pom.xml of example project below):

mvn tomcat7:run-war

Output

localhost:8080/employees

As seen above, by default first page is selected and 'id' column is sorted in ascending order. Let's click on 'Name' column header:

Check out the query string in the address bar above where all needed query parameters are present. Clicking more time on the 'Name' header will change the sort direction:

Clicking on the pagination buttons preserves the last sorted column.

Let's access departments page via localhost:8080/departments

Clicking on 'In descending order' link at the bottom will show the departments in descending order:

Example Project

Dependencies and Technologies Used:

  • spring-data-jpa 2.0.7.RELEASE: Spring Data module for JPA repositories.
    Uses org.springframework:spring-context version 5.0.6.RELEASE
  • spring-webmvc 5.0.6.RELEASE: Spring Web MVC.
  • javax.servlet-api 3.0.1 Java Servlet API
  • hibernate-core 5.3.1.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • h2 1.4.197: H2 Database Engine.
  • thymeleaf-spring5 3.0.9.RELEASE: Modern server-side Java template engine for both web and standalone environments.
  • jackson-databind 2.9.5: General data-binding functionality for Jackson: works on core streaming API.
  • JDK 1.8
  • Maven 3.3.9

Sorting and pagination with Thymeleaf view Select All Download
  • spring-data-jpa-sort-method-arg-resolver
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • EmployeeController.java
          • resources
            • META-INF
          • webapp
            • WEB-INF
              • views

    See Also