ggx1abc
gu123xin
2018-08-10 07:55
采纳率: 95.2%
浏览 1.9k

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

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • qq_29777207
    张音乐 2018-08-10 08:37
    已采纳
     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);
    
        }
    }
    
    

    还拿你昨天的数据库举例
    图片说明

    运行结果为
    图片说明

    点赞 评论
  • u011869418
    liaowencong 2018-08-10 08:16

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

    点赞 评论
  • qq_29777207
    张音乐 2018-08-10 09:01

    考虑到你要循环查询修改了一下,望采纳 ,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);
        }
    }
    
    
    
    

    执行结果为
    图片说明

    点赞 评论
  • qq_31942447
    SkyForce_hw 2018-08-11 14:17

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

    点赞 评论

相关推荐