/***************************************************** MySQL分页存储过程 *****************************************************/ DROP PROCEDURE IF EXISTS pr_pager; CREATE PROCEDURE pr_pager( IN p_table_name VARCHAR(1024), /*表名*/ IN p_fields VARCHAR(1024), /*查询字段*/ IN p_page_size INT, /*每页记录数*/ IN p_page_now INT, /*当前页*/ IN p_order_string VARCHAR(128), /*排序条件(包含ORDER关键字,可为空)*/ IN p_where_string VARCHAR(1024), /*WHERE条件(包含WHERE关键字,可为空)*/ OUT p_out_rows INT /*输出记录总数*/ ) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '分页存储过程' BEGIN /*定义变量*/ DECLARE m_begin_row INT DEFAULT 0; DECLARE m_limit_string CHAR(64); /*构造语句*/ SET m_begin_row = (p_page_now - 1) * p_page_size; SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size); SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string); SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string); /*预处理*/ PREPARE count_stmt FROM @COUNT_STRING; EXECUTE count_stmt; DEALLOCATE PREPARE count_stmt; SET p_out_rows = @ROWS_TOTAL; PREPARE main_stmt FROM @MAIN_STRING; EXECUTE main_stmt; DEALLOCATE PREPARE main_stmt; END; @Override public ArrayList<Customerinfo> customerChangePage(WebPage webPage, String status) { if((webPage.getTotalRecord()==null)||("".equals(webPage.getTotalRecord()))){ initCardPage(webPage); } Session session = getBasicSession(); /*String hql = "from Customerinfo c where c.status='"+status+"'"; System.out.println("//"); System.out.println("hql = "+hql); System.out.println("//"); Query q = session.createQuery(hql); //q.setString(0, status); q.setFirstResult(webPage.getRecordNo()); q.setMaxResults(Integer.parseInt(webPage.getPageSize())); ArrayList<Customerinfo> list = (ArrayList<Customerinfo>)q.list();*/ SQLQuery query = session.createSQLQuery("{Call pr_pager(?,?,?,?,?,?,@xx)}"); query.setString(0, "customerinfo"); query.setString(1, "*"); query.setInteger(2, Integer.parseInt(webPage.getPageSize())); query.setInteger(3, Integer.parseInt(webPage.getPageNo())); query.setString(4, "ORDER by registTime desc"); query.setString(5, " WHERE status='"+status+"'"); //query.setString(6, "@xx"); query.addEntity(Customerinfo.class); ArrayList<Customerinfo> list = (ArrayList<Customerinfo>)query.list(); clearSession(session); return list; } @Override public WebPage initCustomerPage(WebPage p, String status) { if(p.getPageNo()==null){ p.setPageNo("1"); } Session session = getBasicSession(); String sql = "select count(*) c from Customerinfo where status=?"; SQLQuery q = session.createSQLQuery(sql); q.setString(0, status); String totalRecord = (q.list().get(0).toString()); p.setTotalRecord(totalRecord); clearSession(session); return p; }