SQL百万数据高效插入框架

原生SQL高效插入框架（通用）

databaseUrl后面需要加上rewriteBatchedStatements=true，否则批量插入性能会收到很大的影响

业务代码

/**
     * 批量保存
     * @param list
     * @return {@link Boolean}
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static <T> Boolean saveBatch(List<T> list) throws SQLException {
        T info = list.get(0);
        List<Field> fieldList = Arrays.asList(info.getClass().getDeclaredFields());
        String prepareSql = generalPrepareSql(info, fieldList);
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName(driver);
            //连接数据库
            conn = DriverManager.getConnection(databaseUrl, databaseUser, databasePassword);
            conn.setAutoCommit(Boolean.FALSE);
            //预编译sql
            preparedStatement = conn.prepareStatement(prepareSql);
            //批量提交
            prepareExecuteBatch(list, fieldList, preparedStatement);
            conn.commit();
            return Boolean.TRUE;
        } catch (Exception e) {
            log.error("批量保存异常，数据：{}，异常信息：{}", JSON.toJSONString(list), ExceptionUtils.getStackTrace(e));
            throw new RuntimeException(e.getMessage());
        } finally {
            if (Objects.nonNull(conn)) {
                conn.close();
            }
            if (Objects.nonNull(preparedStatement)) {
                preparedStatement.close();
            }
        }
    }

    /**
     * 准备执行批处理
     *
     * @param list              列表
     * @param fieldList         字段列表
     * @param preparedStatement 事先准备好声明中
     * @throws SQLException           sqlexception异常
     * @throws IllegalAccessException 非法访问异常
     */
    private static <T> void prepareExecuteBatch(List<T> list, List<Field> fieldList, PreparedStatement preparedStatement) throws SQLException, IllegalAccessException {
        fieldList.forEach(ele -> ele.setAccessible(Boolean.TRUE));
        int count = 0;
        for (T fileInfo : list) {
            for (int i = 1; i <= fieldList.size(); i++) {
                Field declaredField = fieldList.get(i - 1);
                Object o = declaredField.get(fileInfo);
                setStatement(preparedStatement, i, declaredField, o);
            }
            preparedStatement.addBatch();
            ++count;
            if (count > batchSize) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
                count = 0;
            }
        }
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
    }

    /**
     * preparedStatement 设值，下面只对部分类型进行判断，如果有需要，可以自行增加
     * @param preparedStatement
     * @param i 参数序号
     * @param declaredField 字段
     * @param o 值
     * @throws SQLException
     */
    private static void setStatement(PreparedStatement preparedStatement, int i, Field declaredField, Object o) throws SQLException {
        if (declaredField.getType().equals(String.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setString(i, "");
                return;
            }
            preparedStatement.setString(i, (String) o);
            return;
        }
        if (declaredField.getType().equals(Integer.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setInt(i, 0);
                return;
            }
            preparedStatement.setInt(i, (Integer) o);
            return;
        }
        if (declaredField.getType().equals(Double.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setDouble(i, 0L);
                return;
            }
            preparedStatement.setDouble(i, (Double) o);
            return;
        }
        if (declaredField.getType().equals(Float.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setFloat(i, 0f);
                return;
            }
            preparedStatement.setFloat(i, (Float) o);
            return;
        }
        if (declaredField.getType().equals(BigDecimal.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setBigDecimal(i, BigDecimal.valueOf(0L));
                return;
            }
            preparedStatement.setBigDecimal(i, (BigDecimal) o);
            return;
        }
        if (declaredField.getType().equals(Byte.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setByte(i, (byte) 0);
                return;
            }
            preparedStatement.setByte(i, (Byte) o);
            return;
        }
        if (declaredField.getType().equals(Boolean.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setBoolean(i, Boolean.FALSE);
                return;
            }
            preparedStatement.setBoolean(i, (Boolean) o);
            return;
        }
        if (declaredField.getType().equals(Long.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setLong(i, 0);
                return;
            }
            preparedStatement.setLong(i, (long) o);
            return;
        }
        if (declaredField.getType().equals(Collection.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setString(i, "");
                return;
            }
            preparedStatement.setString(i, String.valueOf(o));
            return;
        }
        if (declaredField.getType().equals(java.util.Date.class) || declaredField.getType().equals(java.sql.Date.class)) {
            if (Objects.isNull(o)) {
                preparedStatement.setDate(i, new java.sql.Date(System.currentTimeMillis()));
                return;
            }
            Date date = (Date) o;
            preparedStatement.setTimestamp(i, new Timestamp(date.getTime()));
        }
    }

    /**
     * 生成预编译sql
     *
     * @param info      信息
     * @param fieldList 字段列表
     * @return {@link String}
     */
    private static <T> String generalPrepareSql(T info, List<Field> fieldList) {
        TableName annotation = info.getClass().getAnnotation(TableName.class);
        if (Objects.isNull(annotation)) {
            throw new IllegalArgumentException("相关类请使用注解@TableName修饰");
        }
        StringBuilder sqlBuilder = new StringBuilder();
        String tableName = annotation.value();
        sqlBuilder.append("insert into ");
        sqlBuilder.append(tableName);

        List<String> fieldNameList = fieldList.stream()
                .map(ele -> {
                    Column column = ele.getAnnotation(Column.class);
                    if (Objects.nonNull(column) && StringUtils.isNotBlank(column.value())) {
                        return column.value();
                    }
                    return camelToUnderscore(ele.getName());
                })
                .collect(Collectors.toList());
        String fieldName = Joiner.on(",").join(fieldNameList);
        sqlBuilder.append("(").append(fieldName).append(") ");
        sqlBuilder.append("values");
        int size = fieldList.size();
        List<String> markList = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            markList.add("?");
        }
        sqlBuilder.append("(").append(Joiner.on(",").join(markList)).append(")");
        return sqlBuilder.toString();
    }

    /**
     * 将驼峰格式的字符串转换为下划线格式
     *
     * @param camel 驼峰格式的字符串
     * @return 下划线格式的字符串
     */
    private static String camelToUnderscore(String camel) {
        // 使用正则表达式将小驼峰格式的字符串替换为下划线格式
        return camel.replaceAll("([A-Z])", "_$1").toLowerCase();
    }

自定义注解

@TableName 和 @Column 可以自己定义，可以使用现成的一些注解，改动注解名后，需要改动上面的代码，通过注解或者表名或者列名

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TableName {
    String value() default "";
}
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Column {
    String value() default "";
}