MySql　分页

最新推荐文章于 2023-08-04 09:12:31 发布
liuqzan
最新推荐文章于 2023-08-04 09:12:31 发布
阅读量133
点赞数
分类专栏： DB 文章标签： MySQL Java SQL JDBC DAO
本文链接：https://blog.youkuaiyun.com/liuqzan/article/details/83724899
版权
DB 专栏收录该内容
8 篇文章
订阅专栏
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;   
    }   


}
MySql 分页

MySql　分页
