java循环读取mysql并存入java集合里

如果数据库数据过大，用java读取数据库，分页存入集合，每分页一次集合就被调用一次，这个java实现的方式是怎么样的？直接
直接在sql语句上面加个for循环吗？for(){
String sql = "SELECT ID,name, url From websites limit " + (i * 2) + ",2;";......}

 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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ReadDBPage {

    /**
     * @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.util.List;

public class Bootstrap {

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

        //查询第1页的数据，每页数据为5条
        List<String> strList = read.query(1,5);

        //查询第2页的数据，每页数据为5条
        List<String> strList2 = read.query(2,5);

        //查询第2页的数据，每页数据为4条
        List<String> strList3 = read.query(2,4);

        System.out.println(strList);

        System.out.println(strList2);

        System.out.println(strList3);

    }
}

还拿你昨天的数据库举例

运行结果为

SELECT ID,name, url From websites limit start ,len 分别在代码层计算好开始行跟分页的条目数,例如第一页数据start=0,len=20获取20条,获取第二页数据则start=20,len=20.前端只要传入一个页码然后乘20即可得到开始行

考虑到你要循环查询修改了一下，望采纳 ，DBHelper还用之前的。

 import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ReadDBPage {

    /**
     * 查询总条数
     * @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.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);
    }
}

执行结果为

不是很明白你说的循环查找是什么意思，但是个人建议，还是能少查数据库一次就少查一次，然后在程序中对数据进行封装，因为牵扯到性能和效率的问题，还有数据库连接超时的配置。