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();    }

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

返回结果：

这样后台分页就写完了