MySql分页

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import com.hcwy.basic.jdbc.DBConnection; import com.hcwy.basic.page.PageBean; public class ArticlesDAO { private static final Map HashMap = null; private PreparedStatement pstmt; private ResultSet rs; private Connection con; // private DBConnection conn; public Connection conn(){ try { Class.forName("com.mysql.jdbc.Driver"); try { con=DriverManager.getConnection("jdbc:mysql://localhost:3316/hcwy","root","root"); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } return con; } //查询SQL public ArrayList chaSQL(String sql){ ArrayList list=new ArrayList(); try { pstmt=this.conn().prepareStatement(sql); rs=pstmt.executeQuery(); ResultSetMetaData rsmd=rs.getMetaData(); int count=rsmd.getColumnCount(); while(rs.next()){ // System.out.println("名字是-->"+rsmd.getColumnName(i)+"/t 得到的object是-->"+rs.getObject(i)+" "+i); HashMap map=new HashMap(); for(int i=0;i<count;i++){ map.put(rsmd.getColumnName(i+1), rs.getObject(i+1)); } list.add(map); } } catch (SQLException e) { e.printStackTrace(); } return list; } //查询所总条数 public int count(String name){ String sql="select count(*) as aa from "+name; int i=0; try { pstmt=this.conn().prepareStatement(sql); rs=pstmt.executeQuery(); if(rs.next()){ i=rs.getInt("aa"); } } catch (SQLException e) { e.printStackTrace(); } return i; } //查询SQL带分页 public ArrayList chaSQL(String sql,String name,PageBean page){ ArrayList list=new ArrayList(); if(page!=null){ page.setTotalCount(this.count(name)); sql=sql+" limit "+page.getStart()+","+page.getPageSize(); } System.out.println(sql); try { pstmt=this.conn().prepareStatement(sql); rs=pstmt.executeQuery(); ResultSetMetaData rsmd=rs.getMetaData(); int count=rsmd.getColumnCount();//得到表里字段的总数 while(rs.next()){ // System.out.println("名字是-->"+rsmd.getColumnName(i)+"/t 得到的object是-->"+rs.getObject(i)+" "+i); HashMap map=new HashMap(); for(int i=0;i<count;i++){ map.put(rsmd.getColumnName(i+1), rs.getObject(i+1));//名字和值 } list.add(map); } } catch (SQLException e) { e.printStackTrace(); } return list; } public static void main(String[] args) { PageBean page=new PageBean(); ArticlesDAO dd=new ArticlesDAO(); ArrayList list=dd.chaSQL("select * from articles","articles",page);//如果这里不写page和articles的意思 就是说不要分页 //任何对象都能解析 for(int i=0;i<list.size();i++){ HashMap map=(HashMap)list.get(i); Iterator it=map.keySet().iterator(); while(it.hasNext()){ Object id=it.next(); System.out.println(""+map.get(id)); } System.out.println("/n"); } // ArticlesDAO dd=new ArticlesDAO(); // System.out.println(dd.count("articles")); } }

 

Bean:

package com.hcwy.basic.page; public class PageBean { private static final int DEFAULT_PAGE_SIZE = 20; private int pageSize = DEFAULT_PAGE_SIZE; // 每页的记录数 private int start=0; // 当前页第一条数据在List中的位置,从0开始 private int page=1; //当前页 private int totalPage=0; //总计有多少页 private int totalCount=0; // 总记录数 //////////////// // 构造函数 public PageBean() { } public PageBean(int page) { this.page=page; } ///////////////// public void setPage(int page) { if(page>0) { start=(page-1)*pageSize; this.page = page; } } public int getPage() { return page; } public int getPageSize() { return pageSize; } public PageBean setPageSize(int pageSize) { this.pageSize = pageSize; return this; } /** * @return the start */ public int getStart() { return start; } // 此位置根据计算得到 protected void setStart() { } /** * @return the totalCount */ public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount=totalCount; totalPage = (int) Math.ceil((totalCount + pageSize - 1) / pageSize); start=(page-1)*pageSize; } // 总页面数根据总数计算得到 protected void setTotalPage() { } public int getTotalPage() { return totalPage; } /////////////// //获取上一页页数 public int getLastPage() { if(hasLastPage()) { return page-1; } return page; } public int getNextPage() { if(hasNextPage()) { return page+1; } return page; } /** * 该页是否有下一页. */ public boolean hasNextPage() { return page < totalPage; } /** * 该页是否有上一页. */ public boolean hasLastPage() { return page > 1; } }