springboot集成spring data jpa使用JpaRepository中的api分页与排序（gradle）

版本环境

java:jdk-14.0.1 springboot:v2.3.4.RELEASE gradle:gradle-6.7-rc-4 IntelliJ IDEA:2020.1.2 spring-boot-starter-data-jpa:v2.3.4.RELEASE

实体类

@NoArgsConstructor @ApiModel(value = "用户实体", description = "用户实体类") @Data @Table(name = "user") @Entity public class User implements Serializable { private static final long serialVersionUID = 4728506793752030545L; @ApiModelProperty(value = "用户名") @Column(name = "name" ) private String name; @JsonIgnore @ApiModelProperty(value = "用户密码") @Column(name = "pass" ) private String pass; @ApiModelProperty(value = "用户年龄") @Column(name = "age" ) private Integer age; @Column(name = "email" ) @ApiModelProperty(value = "用户邮件") private String email; @ApiModelProperty(value = "用户id") @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id" ) private Integer id ; }

数据库

create table mytest.user ( id int auto_increment primary key, name varchar(30) not null, pass varchar(30) not null, age int null, email varchar(30) null, constraint user_name_uindex unique (name) );

Repository

@Repository public interface UserRepository extends JpaRepository<User, Integer> { }

分页查询

service

在UserService中添加下面的方法

Page<User> find(Integer page, Integer size);

并在UserServiceImpl中进行实现

@Autowired UserRepository userRepository; @Override public Page<User> find(Integer page, Integer size) { PageRequest pageable = PageRequest.of(page, size); Page<User> users = userRepository.findAll(pageable); return users; }

其中page是页面 从0开始，size是分页大小。建议在调用该方法时，page和size进行校验

调用测试

@Autowired UserService userService; @Test public void contextLoads() { // for (int i = 0; i < 50; i++) { // String name="lzc"+i; // userRepository.addUserByName(name, "123", 123, "aksjlaklsdf"); // } var list0=userService.find(0,5); list0.forEach(System.out::println); System.out.println(); var list1=userService.find(1,5); list1.forEach(System.out::println); System.out.println(); var list2=userService.find(2,5); list2.forEach(System.out::println); System.out.println(); var list3=userService.find(3,5); list3.forEach(System.out::println); }

输出结果

Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_, user0_.pass as pass5_0_ from user user0_ limit ? Hibernate: select count(user0_.id) as col_0_0_ from user user0_ User(name=lxj, pass=123, age=21, email=jksdf, id=2) User(name=dk, pass=123, age=22, email=asflh, id=3) User(name=tzy, pass=321, age=23, email=ksdja, id=4) User(name=jinx, pass=123, age=123, email=aksjlaklsdf, id=5) User(name=jinx1, pass=123, age=123, email=aksjlaklsdf, id=7) Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_, user0_.pass as pass5_0_ from user user0_ limit ?, ? Hibernate: select count(user0_.id) as col_0_0_ from user user0_ User(name=jinx2, pass=123, age=123, email=aksjlaklsdf, id=9) User(name=jinx3, pass=123, age=123, email=aksjlaklsdf, id=10) User(name=lzc, pass=960929, age=123, email=aksjlaklsdf, id=11) User(name=lzc0, pass=123, age=123, email=aksjlaklsdf, id=12) User(name=lzc1, pass=123, age=123, email=aksjlaklsdf, id=13) Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_, user0_.pass as pass5_0_ from user user0_ limit ?, ? Hibernate: select count(user0_.id) as col_0_0_ from user user0_ User(name=lzc2, pass=123, age=123, email=aksjlaklsdf, id=14) User(name=lzc3, pass=123, age=123, email=aksjlaklsdf, id=15) User(name=lzc4, pass=123, age=123, email=aksjlaklsdf, id=16) User(name=lzc5, pass=123, age=123, email=aksjlaklsdf, id=17) User(name=lzc6, pass=123, age=123, email=aksjlaklsdf, id=18) Hibernate: select user0_.id as id1_0_, user0_.age as age2_0_, user0_.email as email3_0_, user0_.name as name4_0_, user0_.pass as pass5_0_ from user user0_ limit ?, ? Hibernate: select count(user0_.id) as col_0_0_ from user user0_ User(name=lzc7, pass=123, age=123, email=aksjlaklsdf, id=19) User(name=lzc8, pass=123, age=123, email=aksjlaklsdf, id=20) User(name=lzc9, pass=123, age=123, email=aksjlaklsdf, id=21) User(name=lzc10, pass=123, age=123, email=aksjlaklsdf, id=22) User(name=lzc11, pass=123, age=123, email=aksjlaklsdf, id=23)

排序

JpaRepository中的api

/* * (non-Javadoc) * @see org.springframework.data.repository.PagingAndSortingRepository#findAll(org.springframework.data.domain.Sort) */ @Override List<T> findAll(Sort sort);

注：若findAll不填参数，可调用List findAll(); 返回结果为数据表中的所有数据

遇到的坑

在springboot2.2.1（含）以上的版本Sort已经不能再实例化了，构造方法已经是私有的了！

以按年龄降序查询所有用户信息

原调用写法为：

//先定义 Order规则 //Direction.DESC 表示倒序 //"age" 表示对id 进行倒序排序 Sort.Order order = new Sort.Order(Sort.Direction.DESC, "age"); Sort sort = new Sort(order);//本行Sort构造方法报错 var allUsersSort=userRepository.findAll(sort); allUsersSort.forEach(System.out::println);

新写法为：

var allUsersSort=userRepository.findAll(Sort.by(Sort.Direction.DESC,"age")); allUsersSort.forEach(System.out::println);

运行结果：

//结果上部省略，一下是返回结果的最后，可以看见输出顺序已按age降序排列 User(name=lzc39, pass=123, age=123, email=aksjlaklsdf, id=51) User(name=lzc40, pass=123, age=123, email=aksjlaklsdf, id=52) User(name=lzc41, pass=123, age=123, email=aksjlaklsdf, id=53) User(name=tzy, pass=321, age=23, email=ksdja, id=4) User(name=dk, pass=123, age=22, email=asflh, id=3) User(name=lxj, pass=123, age=21, email=jksdf, id=2) User(name=lzc19, pass=123, age=5, email=aksjlaklsdf, id=31) User(name=lzc17, pass=123, age=4, email=aksjlaklsdf, id=29) User(name=lzc10, pass=123, age=3, email=aksjlaklsdf, id=22) User(name=lzc, pass=960929, age=2, email=aksjlaklsdf, id=11) User(name=lzc2, pass=123, age=1, email=aksjlaklsdf, id=14)