mysql分页

基础类
public class PageOper {
 private int currentPage;//当前页面 private int pageSize=2; //页面显示的条数 private int countPage; //页面的总数 private int count; //总体数据 private List<SpotInfo> datas; 
 public int getCurrentPage() {  return currentPage; }
 public void setCurrentPage(int currentPage) {  this.currentPage = currentPage; }
 public int getPageSize() {  return pageSize; }
 public void setPageSize(int pageSize) {  this.pageSize = pageSize; }
 public int getCountPage() {  return countPage; }
 public void setCountPage(int countPage) {  this.countPage = countPage; }
 public int getCount() {  return count; }
 public void setCount(int count) {  this.count = count; }
 public List<SpotInfo> getDatas() {  return datas; }
 public void setDatas(List<SpotInfo> datas) {  this.datas = datas; }}
action层
public class PageOperAction {

        public PageOper pagOper = new PageOper();
        public PageOperService pageService = new PageOperService ();
            public PageOper getPagOper() {
  String tempCurrentPage = request.getParameter("currentPage"); //从前台传来的当前页码;
  if(tempCurrentPage == null){
   this.currentPage = "1";
  }else{
     this.currentPage = tempCurrentPage;
  }
     this.pagOper = pageService.getPageSpotList(Integer.parseInt(this.currentPage),pageService.getCounts("表名"));
                    //注：如果用ssh实现 均可将this.pagOper 设置到session  然后从jsp页面中读取
     return this.pagOper;
     }
}
service层
public class PageOperService {
private String sql = null;
private Connection con = null;
private Statement st = null;
private ResultSet rs = null;

public PageOper getPageSpotList(int currentPage,int count) {
  PageOper page = new PageOper();
  int  countPage=0;
  int tempCurrent = 0;
  countPage = (count / page.getPageSize()) + (count % page.getPageSize() == 0 ? 0 : 1);
  if (currentPage <= 1) {tempCurrent = 1;}
   else if (currentPage > 1 && currentPage < countPage) {tempCurrent = currentPage;}
   else if (currentPage >= countPage) {tempCurrent = countPage;}
  StringBuffer sb = new StringBuffer();
  sb.append(" select * from  表名 as st  ");
  if (tempCurrent > 1) {
   sb.append("where  st.spotId > (" +
      " select max(s_t.spotId) from (" +
      " select 表ID from 表名  limit "+((tempCurrent - 1) * page.getPageSize())+") as s_t )");
  }
  sb.append(" limit " + page.getPageSize());
  sb.append(" select * from  tbl_spotinfo ");
  sb.append("limit "+(tempCurrent - 1) * page.getPageSize()+","+page.getPageSize());
  try {
   List<SpotInfo> spotInfoList = new ArrayList<SpotInfo>();
   con = DbConnection.getConnection();
   st = con.createStatement();
   rs = st.executeQuery(sb.toString());
   while (rs.next()) {
     SpotInfo spotInfo = new SpotInfo();
    spotInfo.setSpotId(rs.getInt("spotId"));
    spotInfo.setProduct(rs.getString("product"));
    spotInfo.setRegion(rs.getString("region"));
      spotInfoList.add(spotInfo);
   }
    page.setDatas(spotInfoList);
    page.setCountPage(countPage);
                  page.setCurrentPage(tempCurrent);
                  page.setDatas(spotInfoList);
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   DbConnection.close(con, st, rs);
  }
  return page;
}
}

service层的 获取总数据的方法
public int getCounts(String tableName) {
  int counts = 0;
  String sql = " SELECT COUNT(*) FROM " + tableName;
  try {
   con = DbConnection.getConnection();
   st = con.createStatement();
   rs = st.executeQuery(sql.toString());
   if (rs.next()) {
    counts = rs.getInt(1);
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   DbConnection.close(con, st, rs);
  }
  return counts;
}