java 分页读取数据[通俗易懂]

Jetbrains全系列IDE稳定放心使用

    /**
     * 查询总条数
     * @return
     */
    public Long querySize() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = " select count(*) as num from compare; ";
        Long size = 0L;
        try {
            conn = DBHelper.getConnection();
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                size = rs.getLong("num");
            }
            return size;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @param pageNo   表示第几页
     * @param pageSize 表示每页的数量
     * @return
     */
    public List<String> query(int pageNo, int pageSize) {
        //计算起始值，比如假如每页条数为5，第一页是0 - 4，第二页是5 - 9;。。。。
        int pageStart = (pageNo - 1) * pageSize;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = " select * from compare limit ?,?; ";
        List<String> mapList = new ArrayList<String>();
        try {
            conn = DBHelper.getConnection();
            stmt = conn.prepareStatement(sql);
            //把参数设置到 ?号里面
            stmt.setInt(1, pageStart);
            stmt.setInt(2, pageSize);
            rs = stmt.executeQuery();
            while (rs.next()) {
                mapList.add(rs.getString("id"));
            }
            return mapList;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

import java.sql.Connection;
import java.sql.DriverManager;

public class DBHelper {
	
	private static final String driver = "com.mysql.jdbc.Driver";//数据库驱动
	private static final String url = "jdbc:mysql://localhost:3306/gumysql?useUnicon=true&characterEncoding=UTF-8";
	private static final String username = "root";
	private static final String password = "root";
	
	static
	{
		try 
		{
			Class.forName(driver);
		}
		catch (Exception e)
		{
			
			e.printStackTrace();
		}
	}
	
	private static Connection conn = null;
	//单例模式返回数据库连接
	public static Connection getConnection() throws Exception
	{
		if(conn == null)
		{
			conn = DriverManager.getConnection(url, username, password);
			return conn;
		}
		else
		{
			return conn;
		}
	}
}

import java.util.ArrayList;
import java.util.List;

public class Bootstrap {

    public static void main(String args[]){
        ReadDBPage read = new ReadDBPage();

        //查询数据库中总条数
        Long allSize = read.querySize();

        System.out.println("数据库中总数为："+ allSize +" 条");

        List<String> allList = new ArrayList<String>();

        int pageSize = 5;//定义每页的条数

        //计算循环次数，即总页数  Math.ceil为向上取整
        int pages = (int) Math.ceil(allSize/(pageSize*1.0));
        System.out.println("总页数为："+ pages +" 页");

        for(int i=1;i<=pages;i++){
            List<String> onePageList = read.query(i,pageSize);
            allList.addAll(onePageList);
            System.out.println("第"+ i +"页的数据为："+ onePageList);
        }

        System.out.println("总数据为："+ allList);
    }
}

推荐一个公众号

号主为一线大厂架构师，CSDN博客专家，博客访问量突破一千万。主要分享Java、golang架构，源码，分布式，高并发等技术，用大厂程序员的视角来探讨技术进阶、面试指南、职业规划等。15W技术人的选择！