技术框架
- 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代码，非常优雅。
