Spring Data分页和排序

18-12-04 banq
    

当我们执行批量操作时,比如从数据库中查找“Person”的所有实例或者根据国家查找每个人,我们经常进行分页,以便我们可以向最终用户提供一个小数据块,并在下一个请求中,我们获取下一个数据块。

Spring Data为分页提供支持。它创建了实现分页的所有逻辑,例如所有页面的行计数等等。

在Spring Data中实现分页非常简单。我们只需要按照以下步骤操作:

  • 在自定义存储库中,扩展  PagingAndSortingRepository。
  • 创建PageRequest对象,该对象是Pageable接口的实现。  此PageRequest对象获取页码,页面大小以及排序方向和排序字段。
  • 通过传递请求的页码和页面限制,您可以获取此页面的数据。如果您传递错误的页码,Spring Data将负责处理并且不返回任何数据。

1.创建扩展PagingAndSortingRepository的存储库。

@Repository
public interface PersonRepositary extends PagingAndSortingRepository<Person, Long>,QueryDslPredicateExecutor<Person> {

    @Query("select p from Person p where p.country like ?1 order by country")
    List<Person> findByCountryContains(String country);

    List<Person> findPersonByHobbyName(String name);

    @Query("select p from Person p where p.id = ?1 and  country='America'")
    Person findOne(Long id);
}

2.  创建域对象。

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;
    private String country;
    private String gender;
@OneToMany(mappedBy="person",targetEntity=Hobby.class,
       fetch=FetchType.EAGER,cascade=CascadeType.ALL)
        List<Hobby> hobby;
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getCountry() {
    return country;
}
public void setCountry(String country) {
    this.country = country;
}
public String getGender() {
    return gender;
}
public void setGender(String gender) {
    this.gender = gender;
}
public Long getId() {
    return id;
}
public void setId(Long id) {
    this.id = id;
}
public List<Hobby> getHobby() {
    return hobby;
}
public void setHobby(List<Hobby> hobby) {
    this.hobby = hobby;
}
public void addHobby(Hobby ihobby)
{
    if(hobby == null)
    {
        hobby = new ArrayList<Hobby>();
    }
    hobby.add(ihobby);
}
    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", country=" + country + ", gender=" + gender + "]";
    }
}

3.获取所有人员。创建一个限制为1的PageRequest对象并请求第一页。

@SpringBootApplication
@EnableJpaRepositories("com.example.repo")
public class PersonApplication {
    @Autowired
    HobbyRepository hRepo;

    private static final Logger log = LoggerFactory.getLogger(PersonApplication.class);

    @Bean
    public CommandLineRunner demo(PersonRepositary repository) {
        findAll(repository);
        return null;
    }

    private PageRequest gotoPage(int page)
    {
        PageRequest request = new PageRequest(page,1)
        return request;
    }

    private void findAll(PersonRepositary repository)
    {
        Iterable<Person> pList = repository.findAll(gotoPage(0));
        for(Person p : pList)
          log.info("Person " + p);
    }

    public static void main(String[] args) {
        SpringApplication.run(PersonApplication.class, args);
    }
}

运行时SQL输出:

Hibernate: 
    select
        count(person0_.id) as col_0_0_ 
    from
        person person0_
Hibernate: 
    select
        person0_.id as id1_1_,
        person0_.country as country2_1_,
        person0_.gender as gender3_1_,
        person0_.name as name4_1_ 
    from
        person person0_ limit ?
Person Person [id=13, name=Samir mitra, country=America, gender=male]

分页和排序代码实现

要进行排序,我们必须传递排序方向和排序字段以及页码和限制。假设我们想按国家名称按升序排序 - 我们修改  goto 方法如下:

private PageRequest gotoPage(int page)
{
    PageRequest request = new PageRequest(page,1,Sort.Direction.ASC,"country");
    return request;
}

SQL输出:

select
        count(person0_.id) as col_0_0_ 
    from
        person person0_
Hibernate: 
    select
        person0_.id as id1_1_,
        person0_.country as country2_1_,
        person0_.gender as gender3_1_,
        person0_.name as name4_1_ 
    from
        person person0_ 
    order by
        person0_.country asc limit ?