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