JDBC的批量查询报告内存溢出解决方法

转载

mob604756ef1373 2016-08-11 15:44:00

文章标签 mysql java sql redis 数据 文章分类 代码人生

由于表中的数据过多(我的超过了50W+),查询select * from table ....报告内存溢出

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1621)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1398)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2816)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:467)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2510)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1746)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2135)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1383)
    at cn.cdv.redis.Mysql2Redis0.main(Mysql2Redis0.java:40)

解决方法,就是给jdbc的url添加2个属性即可useCursorFetch=true&defaultFetchSize=100

Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useCursorFetch=true&defaultFetchSize=100", "root", "root");
            statement = connection.createStatement();
            String sql = "select * from asset ;";