MySql 分页

torlee

package com.yous365.dao;   
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"));   

}   

}  
package com.yous365.dao;  
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"));

}

}



package com.yous365.util;  
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;   
}   

}