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)