技术框架

• Spring Boot
• Spring JPA
• Maven

Maven 依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Controller

@RequestMapping("/list")
public String list(int parentid, int page, int size){
    return categoryService.getList(parentid, page, size);
}

Service

public String getList(int parentid, int page, int size){
    StringBuilder sb = new StringBuilder();
    Sort sort = new Sort(Direction.DESC, "cid");//指定排序字段
    Page<Category> pp = categoryRepository.findByParentid(parentid, new PageRequest(page, size, sort));
    List<Category list = pp.getContent();
    for(Category item : list) {
        //logic process
    }
    return sb.toString();
}

Reponsitory (重点)

最简洁的写法

public interface CategoryRepository extends JpaRepository<Category, Integer> {
    Page<Category> findByParentid(int parentid, Pageable pageable);
}

手动写SQL的写法

public interface CategoryRepository extends JpaRepository<Category, Integer> {

    @Query(value="select * from Category where parentid =?1 order by cid desc /* #pageable# */ ",countQuery="select count(*) from Category where parentid = ?1",nativeQuery = true)
    Page<Category> findByParentid(int parentid, Pageable pageable);

}

总结

本文只贴出了主要部分代码，提供了实现的思路。
作者使用的是H2的数据库，很奇怪的一点就是在select语句后面需要添加一小段注释/* #pageable# */
stackoverflow上有一篇文章对这点进行了讨论，上面有人提到mysql的处理方案
mysql的代码示例

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY ?#{#pageable}",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

使用JPA实现数据访问，代码非常简洁，没有那么多复杂的DAO代码，非常优雅。