OOM专题 - 如何使用JdbcTemplate查询百万行超大结果,以及其他JdbcTemplate 其他冷门实用技能

博客针对一次性查询百万行数据返回前端导致OOM的问题展开。分析原因是JDBC一次性封装大量不可回收对象致JVM满、GC频繁。提出手动控制ResultSet生成,将大对象变小并保存到磁盘,释放内存,还给出核心示例代码及MySQL设置建议。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

问题:当一次性查询 百万行的数据,返回给前端的时候,很容易就会导致OOM。全网搜索,没有满意的解决方案,因此自己研究了 几番 JdbcTemplate 源码,手动编码解决。

原因:JDBC 一次性封装成了 百万个 对象,而且都不能被回收。如此大规模的快速生成对象,会立刻把JVM 盛满,而且都是不可回收对象,导致GC频繁,触发FullGC, 但是没什么实际效果。机器变得卡顿,OOM随之发生。

思路:自己手动控制 ResultSet 对象的生成过程,及时 将大对象 改变成小对象,临时保存为 磁盘的File ,立刻释放内存的占用,让GC及时回收垃圾。最终将文件流封装进 response 的输出流中,返回给前端。

          核心思想:此时的 ResultSet 仅仅是 数据库的一个游标指针(cursor),并不是真实的全部数据,所以并不会占据内存空间。理解这一点非常重要!!!我当年研究了 很多资料才得到这一看似简单的真知灼见。

          避坑指南:千万不能使用 SqlRowSet,因为 该对象是 真实的全部数据,并不是游标指针,所以会占据很多的内存空间。请看:

// The results will be mapped to an SqlRowSet which holds the data in a disconnected fashion 意思就是 这是离线版的ResultSet ,而不是指针 ResultSet
SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException;

protected SqlRowSet createSqlRowSet(ResultSet rs) throws SQLException {
		CachedRowSet rowSet = newCachedRowSet();
		rowSet.populate(rs); // 这段代码的意思,就是 将 ResultSet 指针指向的数据,全部倒腾出来,缓存到JVM 本地
		return new ResultSetWrappingSqlRowSet(rowSet);
	}

解决以上问题的思路,最核心的示例代码如下:

每隔5w行,写一次磁盘,并且清空一次当前的大对象,让 GC 来 work ,收走 garbage 

File file = new File("临时文件全路径")
Object query = getJdbcTemplate().query(sql.toString(), new ResultSetExtractor<Object>() {
    @Override
    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
          while (rs.next()) {
               processRow(columnCount, rs, sb);
               if(行数 >= 50000){
                   IOUtils.write(sb, new FileOutputStream(file,true), "UTF-8"); //及时写1次数磁盘
                   sb.delete(0, sb.length());// GC 清理垃圾,重置 sb 大对象
                }
            }
        return file;
    }
});

 完整代码如下: 可以直接CP到你的项目中使用 

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.JdbcUtils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

   /**
     * 自定义的 ResultSetExtractor, 直接操作 数据库的指针 ResultSet ,并且返回最终想要的 CSV 文件
     *
     * 在其中做 逻辑分步处理,可以避免 OOM . 比如,
     *      直接返回 CSV 文件,避免封装成 List  或者Map 对象
     *      及时处理大对象,让GC 释放临时空间,避免撑爆 JVM 内存, 也可以解决 OOM 问题
     */
    public File queryBigResult(CharSequence sql,Object... o){
        // 一般只有超过 5万行 的 结果集,才需要 返回csv 格式,所以容量最好是 5w行的两倍(以每行估计 100个字符来算)
        final StringBuilder sb = new StringBuilder(5000000 * 2);
        final File file = new File(SystemUtils.getJavaIoTmpDir() + File.separator + "临时大文件存储,可以删除".concat(System.currentTimeMillis() + "").concat(".csv"));

        Object query = getJdbcTemplate().query(sql.toString(), new ResultSetExtractor<Object>() {
            @Override
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                ResultSetMetaInfo info = new ResultSetMetaInfo(rs); //缓存基础信息,避免在while循环中反复调用,提高效率
                String[] columnNames = info.getColumnNames();
                int columnCount = info.getColumnCount(), line = 0;

                // first line : coLNames
                line++;
                for (int i = 0; i < columnCount; i++) {
                    sb.append(columnNames[i]).append(",");
                }
                sb.deleteCharAt(sb.length() - 1).append("\n");

                while (rs.next()) {
                    processRow(columnCount, rs, sb);
                    line++;

                    // 数据及时落盘
                    if (line >= 50000) {
                        try {
                            IOUtils.write(sb, new FileOutputStream(file,true), "UTF-8");
                        } catch (IOException e) {
                            log.error("正在写盘 [{}] 行时出错:{}", line, Utils.logMessage(e));
                        }
                        // reset line & result
                        line = 0;
                        sb.delete(0, sb.length());
                    }
                }

                try {
                    IOUtils.write(sb, new FileOutputStream(file,true), "UTF-8");
                    return file;
                } catch (IOException e) {
                    log.error("最后写盘 [{}] 行时出错:{}", line, Utils.logMessage(e));
                }

                sb.delete(0, sb.length());
                return file;
            }
        },o);

        return file;
    }

    /**
     * 大文件:处理单行
     */
    private static void processRow(int columnCount, ResultSet rs, StringBuilder sb) throws SQLException {
        for (int i = 1; i <= columnCount; i++) {
            Object value = JdbcUtils.getResultSetValue(rs, i);  // 参考牛掰spring的工具类方法,真汗颜,当初我自研究了个把月,手写了Jdbc框架工具,后来才发现spring的完美写法
            sb.append(value).append(",");
        }
        sb.deleteCharAt(result.length() - 1).append("\n");
    }

上文中用到的 对象:

ResultSetMetaInfo: 为了缓存 ResultSet 指针的元数据信息,避免 在while 循环中 反复get 调用导致 的效率降低。
import lombok.Builder;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import org.springframework.jdbc.support.JdbcUtils;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

/**
 * @author stormfeng
 * @date 2020-11-25  14:39
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ResultSetMetaInfo {
    private int columnCount;
    private int[] columnTypes;
    private String[] columnNames;

    /**
     * @see  org.springframework.jdbc.core.RowCountCallbackHandler#processRow(java.sql.ResultSet) 
    */
    @SneakyThrows
    public ResultSetMetaInfo(ResultSet rs) {
        ResultSetMetaData rsmd = rs.getMetaData();
        this.columnCount = rsmd.getColumnCount();
        this.columnTypes = new int[columnCount];
        this.columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            columnTypes[i] = rsmd.getColumnType(i + 1);
            columnNames[i] = JdbcUtils.lookupColumnName(rsmd, i + 1);
        }
    }

}

如果是 mysql ,请注意 ,

官网MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.4 JDBC API Implementation Notes

进行如下设置 ,共有1种可用设置,推荐第2种设置 fetchSize =100 。 

如果按照设置1 ,那么 resultset 每次取回的就是 1行 数据;单条取回,速度肯定较慢

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

如果按照设置2 ,那么 resultset 每次取回的就是 fetchsize的那几行 数据;比第1种肯定快很多,推荐

conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
stmt = conn.createStatement();
stmt.setFetchSize(100);
rs = stmt.executeQuery("SELECT * FROM your_table_here");

此时,虽然避免了 由于mysql数据库本身的 resultset 过大而导致的 oom ,但是,依然需要按照本文一开始的代码那样,不要一次性生成百万或者千万java对象,而是要及时将内存落地为写磁盘,避免超大java对象成的 oom 。

JdbcTemplate-API大全参考

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值