JAVA JDBCTemplate 批量插入数据优化实践

一、背景及所用的技术

  1.千万级数据库,每张表大概一千万的数据量,每个小时数据20w+的数据。

  2.JDBCTemplate,java,mysql

二、问题描述

  用jdbcTemplate.batchUpdate(String[] sqls)方法批量1000条执行一次,当数据量太大到达10w级别的时候,执行时间太长,无法满足10分钟内完成插入数据库的需求。

三、检查问题

  使用jdbcTemplate.batchUpdate(String[] sqls)方法会去编译加载每一条sql,浪费了很多时间。

四、查询资料

  可以使用jdbcTemplate.batchUpdate(String sql, final BatchPreparedStatementSetter pss)方法预编译sql,大大缩短批量更新的执行时间,还可以防止sql注入。

  准备10w数据用于测试批量更新的执行效率,结果为执行时间小于10s,结果符合预期,可以尝试用于项目。

五、优化尝试

  根据之前的测试结果尝试进行代码优化,使用jdbcTemplate.batchUpdate(String sql, final BatchPreparedStatementSetter pss)来进行批量插入,一次插入10w数据。

jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        //设置具体的参数
        ps.setString(1, "1");
        ps.setString(2, "2");
        ps.setInt(3, 1);
        ...
    }
    @Override
    public int getBatchSize() {
        //批量插入的数据(10w一批)
        return 100000;
    }
}

  但是实际情况并不能指定具体的参数的类型是Int,Long,Date,String或者其他类型。而且需要动态计算批量的batchSize大小,不满10w时修改batchSize为具体的值。

五、优化实践

  利用ps.setObject()中自动判断传入类型并设置具体的参数的功能。

public void setObject(int parameterIndex, Object parameterObj) throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        if (parameterObj == null) {
            setNull(parameterIndex, java.sql.Types.OTHER);
        } else {
            if (parameterObj instanceof Byte) {
                setInt(parameterIndex, ((Byte) parameterObj).intValue());
            } else if (parameterObj instanceof String) {
                setString(parameterIndex, (String) parameterObj);
            } else if (parameterObj instanceof BigDecimal) {
                setBigDecimal(parameterIndex, (BigDecimal) parameterObj);
            } else if (parameterObj instanceof Short) {
                setShort(parameterIndex, ((Short) parameterObj).shortValue());
            } else if (parameterObj instanceof Integer) {
                setInt(parameterIndex, ((Integer) parameterObj).intValue());
            } else if (parameterObj instanceof Long) {
                setLong(parameterIndex, ((Long) parameterObj).longValue());
            } else if (parameterObj instanceof Float) {
                setFloat(parameterIndex, ((Float) parameterObj).floatValue());
            } else if (parameterObj instanceof Double) {
                setDouble(parameterIndex, ((Double) parameterObj).doubleValue());
            } else if (parameterObj instanceof byte[]) {
                setBytes(parameterIndex, (byte[]) parameterObj);
            } else if (parameterObj instanceof java.sql.Date) {
                setDate(parameterIndex, (java.sql.Date) parameterObj);
            } else if (parameterObj instanceof Time) {
                setTime(parameterIndex, (Time) parameterObj);
            } else if (parameterObj instanceof Timestamp) {
                setTimestamp(parameterIndex, (Timestamp) parameterObj);
            } else if (parameterObj instanceof Boolean) {
                setBoolean(parameterIndex, ((Boolean) parameterObj).booleanValue());
            } else if (parameterObj instanceof InputStream) {
                setBinaryStream(parameterIndex, (InputStream) parameterObj, -1);
            } else if (parameterObj instanceof java.sql.Blob) {
                setBlob(parameterIndex, (java.sql.Blob) parameterObj);
            } else if (parameterObj instanceof java.sql.Clob) {
                setClob(parameterIndex, (java.sql.Clob) parameterObj);
            } else if (this.connection.getTreatUtilDateAsTimestamp() && parameterObj instanceof java.util.Date) {
                setTimestamp(parameterIndex, new Timestamp(((java.util.Date) parameterObj).getTime()));
            } else if (parameterObj instanceof BigInteger) {
                setString(parameterIndex, parameterObj.toString());
            } else {
                setSerializableObject(parameterIndex, parameterObj);
            }
        }
    }
}

  外部判断batchSize的方式来进行批量更新。

public static void batchSizeUpdate(List datas, String sqlTemplate, JdbcTemplate jdbcTemplate){
    if (datas.size() <= 0){
        //没有数据就跳出
        return;
    }
    int batchSize = 100000;
    int start = 0;
    int size = batchSize;

    do {
        if(datas.size() - start < size){
            //不足100000数量,重新计算size
            size = datas.size()-start;
        }
        batchUpdate(datas, sqlTemplate, start, size, jdbcTemplate);
        start += size;
    } while (start < datas.size());
}

private static void batchUpdate(final List datas, String sql, final int start, final int size, JdbcTemplate jdbcTemplate){
    try {
        int[] value = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map data = datas.get(start+i);
                //设置具体的参数
                ps.setObject(1, data.get(field1));
                ps.setObject(2, data.get(field2));
                ...
            }
            @Override
            public int getBatchSize() {
                return size;
            }
        });
    } catch (Exception e) {
        slf4jLogger.error("batchUpdate() error:", e);
    }
}

  注:但是这种方式如果其中有一条数据有错误(产生类型越界等问题),会导致当前一批10w数据全部丢失。这个问题暂时只能用提前检查数据是否安全的方式来解决。

七、参考文档

转载于:https://my.oschina.net/junruiwang/blog/3048101

相关推荐

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值