java数据迁移，内存溢出

最新推荐文章于 2025-05-03 06:45:00 发布

java_xxxx

最新推荐文章于 2025-05-03 06:45:00 发布

阅读量659

点赞数

分类专栏： # 杂谈_java 文章标签：内存溢出数据迁移

本文链接：https://blog.csdn.net/java_xxxx/article/details/85094852

版权

杂谈_java 专栏收录该内容

16 篇文章

订阅专栏

最近要将一个老系统的数据迁移到新系统来，当然是将老数据库中的数据放到新数据库中来。
当查询数据的时候出现了jvm内存溢出，这里记录一下

原先的代码，这代码一看就有问题，按时间查询，查询出的东西是放在jvm内存里的，如果时间短，数据少那当然没啥问题，但是如果我一下子要迁移2年的数据，那数据量就非常大，内存就可能溢出了。

 ResultSet select = JdbcHelper.select(con, "SELECT * FROM jc_content_ext cex \n" +
                                "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                                "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                                "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                                "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                                "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                                "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                                "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2 LIMIT " + pageIndex +"," + pageSize
                        );
                        handleResult(select, columnMap, con);

优化：还是分页查询，先查询出时间段内总共的记录数，根据记录数的范围，然后分页查询。
优化后的代码。这里我按500/页来查询：
注意1：这里查询数据总数的时候，还遇见了一些问题。
如果直接count(*),ResultSet 是无法通过列名得到总的数据数。
这时得随便找一个列名，然后起个别名，之后，取总记录数的时候，用该别名才能得到。原因嘛，我们不是用mybatis，也不是在mysql直接查，它是将结果集放在了ResultsSet里，所以我们直接是拿不到数据的，
不起别名的话是这样在这里插入图片描述
。这样就不好拿到数据了。这时的结果集，只有这个，如果起过别名，这时我们就可以直接通过rs.getString(“a”)来得到列明为a的数据了。
当然也可以一次查询出所有数据，然后循环结果集rs。但是如果数据太大，还是会导致内存溢出。
注意2：
在用COUNT(cex.content_id) as a取出数据的时候要，先 rs.next();，再rs.getString(“a”)。才能取到总的记录数。否则会异常。


            ResultSet rs = JdbcHelper.select(con, "SELECT COUNT(cex.content_id) as a FROM jc_content_ext cex \n" +
                    "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                    "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                    "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                    "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                    "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                    "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                    "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2"
            );

            System.out.println("总条数++++++"+ rs.getRow());
            System.out.println("总条数++++++"+ rs);

            rs.next();
            System.out.println(rs.getString("a"));
            int count = Integer.parseInt(rs.getString("a"));
            System.out.println(count);


            if(count > 0 ){
                if(count <= 500 ){
                    System.out.println("小于500 ================== " + count);
                    ResultSet select = JdbcHelper.select(con, "SELECT * FROM jc_content_ext cex \n" +
                            "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                            "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                            "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                            "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                            "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                            "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                            "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2"
                    );
                    handleResult(select, columnMap, con);

                    con.close();
                }else{
               
                    int row = count;
                    int pageCount = row / 500 ;

                    for(int i =0; i<pageCount; i++){
                        int pageIndex = i * 500;
                        int pageSize  = 500 ;
                        ResultSet select = JdbcHelper.select(con, "SELECT * FROM jc_content_ext cex \n" +
                                "\t\tLEFT JOIN jc_content jcon ON jcon.`content_id` = cex.`content_id`\n" +
                                "                LEFT JOIN jc_content_txt ctx ON cex.`content_id` = ctx.`content_id` \n" +
                                "                LEFT JOIN jc_content_channel jcc ON cex.`content_id` = jcc.`content_id` \n" +
                                "                LEFT JOIN jc_channel jc ON jcc.`channel_id` = jc.`channel_id` \n" +
                                "                LEFT JOIN jc_channel_ext jct ON jct.`channel_id` = jcc.`channel_id`\n" +
                                "                LEFT JOIN jc_user_ext jue ON jue.`user_id` = jcon.`user_id`\n" +
                                "                WHERE 1 = 1 AND  cex.`release_date` BETWEEN '"+TAG_LAST_TIME+"' AND '"+TAG_THIS_TIME+"' AND jcon.`status` = 2 LIMIT " + pageIndex +"," + pageSize
                        );
                        handleResult(select, columnMap, con);
                    }
                    con.close();

                }
            }