Hibernate+boostrapTable分页查询(——后台)

前沿

很多系统都涉及到分页，分页分为前台分页（即：一次性把数据从后台全部取出来，分页在前台展示在页面，这种方法没有减轻后台服务器的压力，不建议使用），后台分页（根据分页查询部分取出来展示在页面，这种方法减轻了服务器的压力，目前都采用这种方法）

案例：

pojo层：

package com.pms.commons.pojo;

import java.util.List;

/**Bootstrap分页查询数据分装类
 * */
public class BootTablePageDto<T> {

    private Long total;//数据记录总数
    private List<T> rows;//对应页面的记录

    public Long getTotal() {
        return total;
    }
    public void setTotal(Long total) {
        this.total = total;
    }
    public List<T> getRows() {
        return rows;
    }
    public void setRows(List<T> rows) {
        this.rows = rows;
    }

}

package com.pms.commons.pojo;

/**
 * 所有模块返回信息通用实体类
 * @author user
 *
 */
public class ResponseEntity {

    private String status;//返回的状态码
    private Object data;//返回的数据
    private String errorMessage;//返回的错误提示信息
    public String getStatus() {
        return status;
    }


    public static ResponseEntity status(ResponseVoConstant code) {
        ResponseEntity responseEntity = new ResponseEntity();
        responseEntity.setStatus(code);
        return responseEntity;
    }

    public ResponseEntity data(Object value) {
        this.data = value;
        return this;
    }
    public ResponseEntity errorMessage(String message) {
        this.errorMessage = message;
        return this;
    }
    public void setStatus(ResponseVoConstant code) {    
        this.status =code.getCode() ;
    }
    public Object getData() {
        return data;
    }
    public void setData(Object data) {
        this.data = data;
    }
    public String getErrorMessage() {
        return errorMessage;
    }
    public void setErrorMessage(String errorMessage) {
        this.errorMessage = errorMessage;
    }
    @Override
    public String toString() {
        return "ReponseEntity [status=" + status + ", data=" + data + ", errorMessage=" + errorMessage + "]";
    }
    private ResponseEntity(){}
    private ResponseEntity(ResponseVoConstant code,Object data,String  errorMessage){

        this.status=code.getCode();
        this.data=data;
        this.errorMessage=errorMessage;
    }


}

package com.pms.projectsys.pojo;
import java.util.Date;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

import org.springframework.format.annotation.DateTimeFormat;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.pms.login.pojo.User;
/**
  *客户表
  */
@Entity
@Table(name="MAPS_MST_PJ_CUSTOMER")
@SequenceGenerator (name = "CUSTOMER_SEQ", sequenceName = "MAPS_MST_PJ_CUSTOMER_SEQ", allocationSize = 1) 
public class Customer {

    //客户ID
    @Id  
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CUSTOMER_SEQ") 
    @Column(name = "CUSTOMER_ID")  
    private Integer customerId;
    //客户名称
    @Column(name="CUSTOMER_NAME")
    private String customerName;
    //创建时间
    @Column(name="CREATION_TIME",updatable=false)
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date creationTime;
    //创建人ID
    @Column(name="CREATION_USER_ID",updatable=false)
    private Integer creationUserId;
    //修改时间
    @Column(name="MODIFIED_TIME")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date modifiedTime;
    //修改人ID
    @Column(name="MODIFIED_USER_ID")
    private Integer modifiedUserId;
    //一对一注解 关联
    @OneToOne(optional = true, cascade = CascadeType.ALL)  
    @JoinColumn(name = "creation_user_id",referencedColumnName="id",updatable=false,insertable=false) 
    private User creationUser;


    public User getCreationUser() {
        return creationUser;
    }

    public void setCreationUser(User creationUser) {
        this.creationUser = creationUser;
    }
    //一对一注解 关联
    @OneToOne(optional = true, cascade = CascadeType.ALL)  
    @JoinColumn(name = "modified_user_id",referencedColumnName="id",updatable=false,insertable=false) 
    private User modifiedUser;

    @javax.persistence.Transient
    private String creationUserName;

    public String getCreationUserName() {
        return creationUserName;
    }
    public void setCreationUserName(String creationUserName) {
        this.creationUserName = creationUserName;
    }
    @javax.persistence.Transient
    private String modifiedUserName;


    public String getModifiedUserName() {
        return modifiedUserName;
    }
    public void setModifiedUserName(String modifiedUserName) {
        this.modifiedUserName = modifiedUserName;
    }


    @Override
    public String toString() {
        return "Customer [customerId=" + customerId + ", customerName=" + customerName + ", creationTime="
                + creationTime + ", creationUserId=" + creationUserId + ", modifiedTime=" + modifiedTime
                + ", modifiedUserId=" + modifiedUserId + ", creationUser=" + creationUser + ", modifiedUser="
                + modifiedUser + ", creationUserName=" + creationUserName + ", modifiedUserName=" + modifiedUserName
                + "]";
    }

    public Customer(){

    }
    //通过构造器返回页面想要的数据
    public Customer(Integer customerId, String customerName, User creationUser,  Date creationTime,
            User modifiedUser,Date modifiedTime) {
        super();
        this.customerId = customerId;
        this.customerName = customerName;
        this.creationTime = creationTime;
        this.modifiedTime = modifiedTime;
        this.creationUserId=creationUser.getId();
        this.modifiedUserId=modifiedUser.getId();
        this.creationUserName=creationUser.getStaffName();
        this.modifiedUserName=modifiedUser.getStaffName();
    }


    public User getModifiedUser() {
        return modifiedUser;
    }

    public void setModifiedUser(User modifiedUser) {
        this.modifiedUser = modifiedUser;
    }

    public Integer getCustomerId() {
        return customerId;
    }

    public void setCustomerId(Integer customerId) {
        this.customerId = customerId;
    }

    public String getCustomerName() {
        return customerName;
    }

    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }

    public Date getCreationTime() {

        return creationTime;
    }

    public void setCreationTime(Date creationTime) {
        this.creationTime = creationTime;
    }


    public Integer getCreationUserId() {
        return creationUserId;
    }

    public void setCreationUserId(Integer creationUserId) {
        this.creationUserId = creationUserId;
    }

    public Date getModifiedTime() {
        return modifiedTime;
    }

    public void setModifiedTime(Date modifiedTime) {
        this.modifiedTime = modifiedTime;
    }

    public Integer getModifiedUserId() {
        return modifiedUserId;
    }

    public void setModifiedUserId(Integer modifiedUserId) {
        this.modifiedUserId = modifiedUserId;
    }





}

package com.pms.login.pojo;


import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import org.springframework.format.annotation.DateTimeFormat;

import com.fasterxml.jackson.annotation.JsonFormat;
/**
  *用户表
  */
@Entity
@Table(name="MAPS_MST_STAFF")
@SequenceGenerator (name = "SEQ", sequenceName = "MST_STAFF_SEQ", allocationSize = 1) 
public class User {

    @Id  
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ") 
    @Column(name = "id")  
    private Integer id;
    //员工编号
    @Column(name="STAFF_ID")
    private String staffId;
    //员工姓名
    @Column(name="STAFF_NAME")
    private String staffName;
    //登录名
    @Column(name="STAFF_LOGIN_NAME")
    private String staffLoginName;
    //员工角色
    @Column(name="STAFF_CHARACTOR")
    private String staffCharactor;
    //开始时间
    @Column(name="START_DT")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date startDate;
    //结束时间
    @Column(name="END_DT")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date endDate;
    //联系电话
    @Column(name="STAFF_TEL")
    private String staffTel;
    //员工性别
    @Column(name="STAFF_SEX")
    private String staffSex;
    //密码
    @Column(name="STAFF_PASS")
    private String staffPass;
    //密码有效期
    @Column(name="PASS_END_DT")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date passEndDate;
    //是否能登陆
    @Column(name="LOGIN_FLG")
    private String loginFlg;
    //部门编号
    @Column(name="DPET_ID")
    private String dpetId;

    public User(){

    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getStaffId() {
        return staffId;
    }

    public void setStaffId(String staffId) {
        this.staffId = staffId;
    }

    public String getStaffName() {
        return staffName;
    }

    public void setStaffName(String staffName) {
        this.staffName = staffName;
    }

    public String getStaffLoginName() {
        return staffLoginName;
    }

    public void setStaffLoginName(String staffLoginName) {
        this.staffLoginName = staffLoginName;
    }

    public String getStaffCharactor() {
        return staffCharactor;
    }

    public void setStaffCharactor(String staffCharactor) {
        this.staffCharactor = staffCharactor;
    }

    public Date getStartDate() {
        return startDate;
    }

    public void setStartDate(Date startDate) {
        this.startDate = startDate;
    }

    public Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    public String getStaffTel() {
        return staffTel;
    }

    public void setStaffTel(String staffTel) {
        this.staffTel = staffTel;
    }

    public String getStaffSex() {
        return staffSex;
    }

    public void setStaffSex(String staffSex) {
        this.staffSex = staffSex;
    }

    public String getStaffPass() {
        return staffPass;
    }

    public void setStaffPass(String staffPass) {
        this.staffPass = staffPass;
    }

    public Date getPassEndDate() {
        return passEndDate;
    }

    public void setPassEndDate(Date passEndDate) {
        this.passEndDate = passEndDate;
    }

    public String getLoginFlg() {
        return loginFlg;
    }

    public void setLoginFlg(String loginFlg) {
        this.loginFlg = loginFlg;
    }

    public String getDpetId() {
        return dpetId;
    }

    public void setDpetId(String dpetId) {
        this.dpetId = dpetId;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", staffId=" + staffId + ", staffName=" + staffName + ", staffLoginName="
                + staffLoginName + ", staffCharactor=" + staffCharactor + ", startDate=" + startDate + ", endDate="
                + endDate + ", staffTel=" + staffTel + ", staffSex=" + staffSex + ", staffPass=" + staffPass
                + ", passEndDate=" + passEndDate + ", loginFlg=" + loginFlg + ", dpetId=" + dpetId + "]";
    }

    public User(Integer id, String staffId, String staffName, String staffLoginName, String staffCharactor,
            Date startDate, Date endDate, String staffTel, String staffSex, String staffPass, Date passEndDate,
            String loginFlg, String dpetId) {
        super();
        this.id = id;
        this.staffId = staffId;
        this.staffName = staffName;
        this.staffLoginName = staffLoginName;
        this.staffCharactor = staffCharactor;
        this.startDate = startDate;
        this.endDate = endDate;
        this.staffTel = staffTel;
        this.staffSex = staffSex;
        this.staffPass = staffPass;
        this.passEndDate = passEndDate;
        this.loginFlg = loginFlg;
        this.dpetId = dpetId;
    }


}

controller层：

/**
     * 根据搜索条件分页查询数据。
     * @param request
     * @return
     * @throws Exception
     */
    @RequestMapping("findByPageAndParams")
    @ResponseBody
    @RequireLogin
    public ResponseEntity findByPageAndParams(HttpServletRequest request) throws Exception {
        BootTablePageDto<Customer> bt = new BootTablePageDto<Customer>();
        BufferedReader br=new BufferedReader(new InputStreamReader((ServletInputStream)request.getInputStream(),"utf-8"));
        StringBuffer sb=new StringBuffer("");
        String temp;
        while((temp=br.readLine())!=null){
          sb.append(temp);
         }
        br.close();
        JSONObject jsonData=JSONObject.fromObject(sb.toString());
        try{
        bt = customerService.findByPageAndParams(jsonData);//分页
        }catch (Exception e) {
            return ResponseEntity.status(ResponseVoConstant.BADREQUEST).
                    data(null).errorMessage(MessageUtil.getMessageValue("SELECT_FAIL"));
        }
        return ResponseEntity.status(ResponseVoConstant.OK).
                    data(bt).errorMessage(MessageUtil.getMessageValue("SELECT_SUCCESS"));
    }   

service层

/**根据搜索条件分页查询数据。
     * @param offset 偏移量，即记录索引位置
     * @param pageSize 每页记录数
     * @param jsonData 
     * @throws Exception 
     * */
    public BootTablePageDto<Customer> findByPageAndParams(JSONObject jsonData) throws Exception{
        BootTablePageDto<Customer> bt = new BootTablePageDto<Customer>();
        String CountHql="select   count(*)  from Customer customer "
                + " where customer.customerId like '%"+ jsonData.get("customerId")+"%' "
                + "and customer.customerName like '%"+ jsonData.get("customerName")+ "%'"
                + "and customer.creationUser.staffName like '%"+jsonData.get("creationUser")+ "%'"
                + "and customer.modifiedUser.staffName like '%"+jsonData.get("modifiedUser")+ "%'";

        String DataHql="select  new com.pms.projectsys.pojo.Customer("
                + "customer.customerId,"
                + "customer.customerName,"
                + "customer.creationUser, "
                + " customer.creationTime, "
                + "customer.modifiedUser, "
                + " customer.modifiedTime )"
                + "from Customer customer"
                + " where customer.customerId like '%"+ jsonData.get("customerId")+"%' "
                + "and customer.customerName like '%"+ jsonData.get("customerName")+ "%'"
                + "and customer.creationUser.staffName   like '%"+jsonData.get("creationUser")+ "%'"
                + "and customer.modifiedUser.staffName   like '%"+jsonData.get("modifiedUser")+ "%'";

        System.out.println(CountHql);
        System.out.println(DataHql);
        Long total = customerDao.getAllCount(CountHql);//获取总条数
        bt.setTotal(total);
        bt.setRows(customerDao.findByPageAndParam(DataHql,jsonData.getInt("offset"),jsonData.getInt("pageSize")));
        return bt;
    }

dao层

    @Autowired
    private SessionFactory sessionFactory;
    protected Class<T> persistentClass; //实体类类型
    protected String persistentName;    //实体类名

    /**获取上下文关联的Session*/
    protected Session getCurrentSession(){
        return sessionFactory.getCurrentSession();
    }
/**
     * 返回所有条数
     * @param hql
     * @return
     */
    public long getAllCount(String hql){
        Query query = getCurrentSession().createQuery(hql);

        return ((Long) query.iterate().next()).intValue();
    }

/**
    * 使用QBC 进行分页查询操作
    * @param hql   执行的sql语句
    * @param offset 偏移量，即记录索引位置
    * @param pageSize 每页记录数
    * @return 当前页的所有记录
    */
    public List<T> findByPageAndParam(String hql, int offset, int pageSize) throws Exception
    {

        Query query = sessionFactory.getCurrentSession().createQuery(hql);  
        query.setFirstResult(offset);
        query.setMaxResults(pageSize);
        // 执行分页，并返回查询结果
        return query
                .list();

    }

这样后台的分页就做完了，接下来测试：
请求参数：

返回结果：

这样后台分页就写完了