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

chinie2940

于 2019-05-10 17:08:00 发布

1957

文章标签： java 数据库

原文链接：https://my.oschina.net/junruiwang/blog/3048101

版权

一、背景及所用的技术

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