MySQL存儲(chǔ)過(guò)程分頁(yè)兼返回總頁(yè)數(shù)

因?yàn)镸ySQL目前不支持動(dòng)態(tài)參數(shù)分頁(yè)所以存儲(chǔ)過(guò)程中要么傳sql語(yǔ)句過(guò)去要么自己寫(xiě)個(gè)字符串連接方法

下為第二種方法

把每個(gè)需要分割的地方單寫(xiě)一行，清除

1.BEGIN 
-
 
1.SELECT CEIL(COUNT(*)/pageNum) INTO pageCount FROM mytestdb.mttest WHERE tt_id = arg_mt_id; 
1.SET @sqlStr = CONCAT('SELECT `mt_id`,`tt_id`,`tt_name` FROM `mytestdb`.`mttest` WHERE `tt_id` = '); 
1.SET @sqlStr = CONCAT(@sqlStr,arg_mt_id); 
1.SET @sqlStr = CONCAT(@sqlStr,' LIMIT ');SET @sqlStr = CONCAT(@sqlStr,((pageNum-1)*pageSize)); 
1.SET @sqlStr = CONCAT(@sqlStr,','); 
1.SET @sqlStr = CONCAT(@sqlStr,pageSize); 
1.SET @sqlStr = CONCAT(@sqlStr,';'); 
1.PREPARE s1 FROM @sqlStr; 
1.EXECUTE s1; 
1.DEALLOCATE PREPARE s1; 
1.END 
或者寫(xiě)成一行，效率高但是更容易寫(xiě)錯(cuò)

1.BEGIN 
1.SELECT CEIL(COUNT(*)/pageNum) INTO pageCount FROM mytestdb.mttest WHERE tt_id = arg_mt_id; 
1.SET @sqlStr = CONCAT('SELECT `mt_id`,`tt_id`,`tt_name` FROM `mytestdb`.`mttest` WHERE `tt_id` = ',arg_mt_id,' LIMIT ',
1.((pageNum-1)*pageSize),',',pageSize';'); 
1.PREPARE s1 FROM @sqlStr;EXECUTE s1;DEALLOCATE PREPARE s1;END 
java端

1.try {Connection conn = db.openDB(getServletContext());String sql = "{call `myPagingTest`(?,?,?,?)}";CallableStatement cs = conn.prepareCall(sql);cs.setInt(1, 1);cs.setInt(2, 1);cs.setInt(3, 5);cs.registerOutParameter(4, Types.INTEGER);rs = cs.executeQuery();System.out.println(cs.getInt(4));while(rs.next()){out.println(rs.getInt(1));              out.println(rs.getInt(2));              out.println(rs.getString(3) + "<br>");    }} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{          try {if(rs != null){rs.close();}if(db!=null){db.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}      } 

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]