mysql hql分页查询_Hibernate实现分页查询

分页查询就是把数据库中某张表的记录数进行分页查询，在做分页查询时会有一个Page类，下面是一个Page类，我对其做了详细的注解：

1 package com.entity;

2 /**

3 * @author:秦林森

4 */

5

6 import javax.persistence.criteria.CriteriaBuilder;

7

8 public class Page {

9 /**

10 * 其中currentPage,perPageRows这两个参数是做分页查询必须具备的参数

11 * 原因是：hibernate中的Criteria或则是Query这两个接口：都有setFirstResult(Integer firstResult)

12 * 和setMaxResult(Integer maxResult),

13 * 这里的firstResult就是每页的开始的索引数：

14 * 每页开始的索引数的计算公式是：(currentPage-1)*perPageRows+1,(这是相对索引从1开始的)

15 * 但是Hibernate中的firstResult的索引是从0开始的，所以在hibernate中每页开始的索引数的计算公式是：

16 * (currentPage-1)*perPageRows+1-1=(currentPge-1)*perPageRows.

17 *

18 * maxResult就是每页能查询的最大记录数：也就是perPageRows.

19 *

20 * Math.ceil(totalRows/perPageRows)==totalPages;//这是根据总记录数和每页的记录数算出总页数的计算公式。

21 */

22 private Integer currentPage;//当前页

23 private Integer perPageRows;//每页的记录数

24 private Integer totalRows;//总记录数：

25 private Integer totalPages;//总页数：

26 public Integer getCurrentPage() {

27 return currentPage;

28 }

29

30 public void setCurrentPage(Integer currentPage) {

31 this.currentPage = currentPage;

32 }

33

34 public Integer getPerPageRows() {

35 return perPageRows;

36 }

37

38 public void setPerPageRows(Integer perPageRows) {

39 this.perPageRows = perPageRows;

40 }

41

42 public Integer getTotalRows() {

43 return totalRows;

44 }

45

46 public void setTotalRows(Integer totalRows) {

47 this.totalRows = totalRows;

48 }

49

50 public Integer getTotalPages() {

51 return totalPages;

52 }

53

54 public void setTotalPages(Integer totalPages) {

55 this.totalPages = totalPages;

56 }

57 }

下面用Hibernate的Criteira接口进行查询：

对应的实体类Employee的代码如下：

1 package com.entity;

2

3 import javax.persistence.*;

4

5 @Entity

6 @Table(name = "EMPLOYEE")

7 public class Employee {

8 @Id

9 @GeneratedValue(strategy = GenerationType.IDENTITY)

10 private int id;

11 @Column(name = "first_name")

12 private String firstName;

13 @Column(name = "last_name")

14 private String lastName;

15 @Column(name = "salary")

16 private int salary;

17 //a constructor with no arguments

18

19

20 public Employee() {

21 }

22

23 public int getId() {

24 return id;

25 }

26

27 public void setId(int id) {

28 this.id = id;

29 }

30

31 public String getFirstName() {

32 return firstName;

33 }

34

35 public void setFirstName(String firstName) {

36 this.firstName = firstName;

37 }

38

39 public String getLastName() {

40 return lastName;

41 }

42

43 public void setLastName(String lastName) {

44 this.lastName = lastName;

45 }

46

47 public int getSalary() {

48 return salary;

49 }

50

51 public void setSalary(int salary) {

52 this.salary = salary;

53 }

54 }

//创建EMPLOYEE表的sql语句是：

create table EMPLOYEE (

id INT NOT NULL auto_increment,

first_name VARCHAR(20) default NULL,

last_name VARCHAR(20) default NULL,

salary INT default NULL,

PRIMARY KEY (id)

);

首先在写一个配置文件：hibernate.cfg.xml用于连接数据库；

hibernate.cfg.xml的代码如下：

/p>

"-//Hibernate/Hibernate Configuration DTD//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-5.0.dtd">

com.mysql.jdbc.Driver

jdbc:mysql://localhost:3306/test

root

130850a,

10

true

org.hibernate.dialect.MySQLDialect

thread

在写一个用于启动Hibernate的util类：HibernateUtil的代码如下：

1 package com.util;

2 import org.hibernate.SessionFactory;

3 import org.hibernate.boot.Metadata;

4 import org.hibernate.boot.MetadataSources;

5 import org.hibernate.boot.registry.StandardServiceRegistry;

6 import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

7 import org.hibernate.cfg.Configuration;

8 import org.hibernate.service.ServiceRegistry;

9 public class HibernateUtil {

10 private static final SessionFactory sessionFactory;

11

12 private static ServiceRegistry serviceRegistry;

13

14 static {

15 try {

16 StandardServiceRegistry standardRegistry =

17 new StandardServiceRegistryBuilder().configure("hibernate.cfg.xml").build();

18 Metadata metaData =

19 new MetadataSources(standardRegistry).getMetadataBuilder().build();

20 sessionFactory = metaData.getSessionFactoryBuilder().build();

21 } catch (Throwable th) {

22

23 System.err.println("Enitial SessionFactory creation failed" + th);

24 throw new ExceptionInInitializerError(th);

25

26 }

27 }

28 public static SessionFactory getSessionFactory() {

29

30 return sessionFactory;

31

32 }

33 }

最后是分页查询的代码，代码如下：

packagecom.hibDemo;importcom.entity.Employee;importcom.entity.Page;importcom.util.HibernateUtil;importorg.hibernate.Criteria;importorg.hibernate.Session;importorg.hibernate.SessionFactory;importorg.hibernate.Transaction;importjava.util.List;public classPaginationQuery {public voidpaginationByCriteria(){

SessionFactory sessionFactory=HibernateUtil.getSessionFactory();

Session session=sessionFactory.getCurrentSession();

Transaction tx=null;try{//do some work

tx=session.beginTransaction();

Page page= newPage();/*** 假设现在查询的是第一页，每页查询的最大记录数是3.*/page.setCurrentPage(1);

page.setPerPageRows(3);

Criteria criteria= session.createCriteria(Employee.class);

Integer currentPage= page.getCurrentPage();//得到当前页

Integer perPageRows = page.getPerPageRows();//得到每页的记录数：

/*** 在Page类中我已说明了：每页开始的索引数在hibernate中的计算公式是：(currentPage-1)*perPageRows*/criteria.setFirstResult((currentPage-1)*perPageRows);

criteria.setMaxResults(perPageRows);

List employees =criteria.list();for(Employee employee:employees){

System.out.println("*********************");

System.out.println("id="+employee.getId()+" firstName="+employee.getFirstName()+" lastName="+employee.getLastName());

}

tx.commit();

}catch(Exception e) {if(tx!=null){

tx.rollback();

}

e.printStackTrace();

}finally{

session.close();//关闭流，一定要关闭，不然会影响运行速度。

}

}public static voidmain(String[] args) {

PaginationQuery paginationQuery= newPaginationQuery();

paginationQuery.paginationByCriteria();

}

}

总结：这个Page类为Hibernate中setFirstResult,和setMaxResult服务的，抓住这个就可以了。代码可以随便写。