使用mybatis批量插入大量sql防止OOM

请把小熊还给我&

已于 2023-03-09 21:27:07 修改

阅读量780

点赞数 2

分类专栏： JAVA 文章标签： sql java 数据库

于 2023-03-07 19:05:19 首次发布

本文链接：https://blog.csdn.net/qq_42058998/article/details/129388727

版权

JAVA 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

mybatis提供了ScriptRunner程序,打开这个类可以看到官方是这样说的,如果想要增强功能满足自己的需求,请你自己修改源码,而不告诉我让我来帮你做。

官方的ScriptRunner存在以下几个缺点

1.执行完整的sql文件会导致OOM

2.开启手动提交事务后，如果出现Sql语法错误会导致停止，无法执后面的sql也就无法一次性知道该Sql文件中存在多少语法错误的sql,难道我每次错误都要停下来修改吗,不能一次性告诉我所有错误?

基于上面两点我的修改了ScriptRunner源码,满足的自己需求,使用规则

1.用户在controller指定sql文件地址以及名称

2.每次访问controller将会采用批处理的方式进行插入数据,如果中途批处理抛出异常则会自动转换为逐行检索错误的SQL最终事务回滚打印出所有错误的sql语句,修改后再次执行即可

3.注意数据源url要加allowMultiQueries=true参数,不然无法支持批处理!如下参考

url: jdbc:mysql://localhost:3306/test2?characterEncoding=utf-8&serverTimezone=UTC&allowMultiQueries=true

4.只支持insert into table (id,name) values(1,'张三');

5.不支持多值插入insert into table (id,name) values(1.'张三'),(2,'李四');

6.注释可能会导致执行失败,尽可能的删除所有注释

类文件

package com.yujie.utils;


import org.apache.ibatis.jdbc.RuntimeSqlException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL批处理执行器
 */
public class MyScriptRunner {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    private static final String LINE_SEPARATOR = System.lineSeparator();

    private static final String DEFAULT_DELIMITER = ";";

    private static final Pattern DELIMITER_PATTERN = Pattern.compile("^\\s*((--)|(//))?\\s*(//)?\\s*@DELIMITER\\s+([^\\s]+)", Pattern.CASE_INSENSITIVE);

    private final Connection connection;

    private boolean stopOnError;
    private boolean throwWarning;
    private boolean autoCommit;
    private boolean sendFullScript;
    private boolean removeCRs;
    private boolean escapeProcessing = true;
    private StringWriter msg;


    private PrintWriter logWriter = new PrintWriter(System.out);
    private PrintWriter errorLogWriter = new PrintWriter(System.err);

    private String delimiter = DEFAULT_DELIMITER;
    private boolean fullLineDelimiter;

    public MyScriptRunner(Connection connection) {
        this.connection = connection;
    }

    public void setStopOnError(boolean stopOnError) {
        this.stopOnError = stopOnError;
    }

    public void setThrowWarning(boolean throwWarning) {
        this.throwWarning = throwWarning;
    }

    public void setAutoCommit(boolean autoCommit) {
        this.autoCommit = autoCommit;
    }

    public StringWriter getMsg() {
        return msg;
    }

    public void setMsg(StringWriter msg) {
        this.msg = msg;
    }

    public void setSendFullScript(boolean sendFullScript) {
        this.sendFullScript = sendFullScript;
    }

    public void setRemoveCRs(boolean removeCRs) {
        this.removeCRs = removeCRs;
    }

    /**
     * Sets the escape processing.
     *
     * @param escapeProcessing
     *          the new escape processing
     * @since 3.1.1
     */
    public void setEscapeProcessing(boolean escapeProcessing) {
        this.escapeProcessing = escapeProcessing;
    }

    public void setLogWriter(PrintWriter logWriter) {
        this.logWriter = logWriter;
    }

    public void setErrorLogWriter(PrintWriter errorLogWriter) {
        this.errorLogWriter = errorLogWriter;
    }

    public void setDelimiter(String delimiter) {
        this.delimiter = delimiter;
    }

    public void setFullLineDelimiter(boolean fullLineDelimiter) {
        this.fullLineDelimiter = fullLineDelimiter;
    }

    public void runScript(Reader reader) {
        setAutoCommit();

        try {
            if (sendFullScript) {
                executeFullScript(reader);
            } else {
                executeLineByLine(reader);
            }
        } finally {
            rollbackConnection();
        }
    }

    private void executeFullScript(Reader reader) {
        StringBuilder script = new StringBuilder();
        try {
            BufferedReader lineReader = new BufferedReader(reader);
            String line;
            int count=0;
            String command="";
            while ((line = lineReader.readLine()) != null) {
                script.append(line);
                script.append(LINE_SEPARATOR);
                count++;
                //注意处理量不要设置大于mysql的max_allowed_packet
                if(count%1000 == 0){
                    command=script.toString();
                    println(command);
                    executeStatement(command);
                    script.setLength(0);
                }
            }
            //兜底执行
            command=script.toString();
            if(command.length() != 0 ){
                println(command);
                executeStatement(command);
                script.setLength(0);
            }
            logger.info("批处理务提交中,请耐心等待...");
            commitConnection();

        } catch (Exception e) {
            logger.error("批处理事务回滚中请耐心等待...");
            String message = "Error executing: " + script + ".  Cause: " + e;
            printlnError(message);
            throw new RuntimeSqlException(message, e);
        }
    }
    private void executeLineByLine(Reader reader) {
        StringBuilder command = new StringBuilder();
        try {
            BufferedReader lineReader = new BufferedReader(reader);
            String line;
            while ((line = lineReader.readLine()) != null) {
                handleLine(command, line);
            }
            if(msg.toString().length() == 0){
                logger.info("逐行事务提交中,请耐心等待...");
                commitConnection();
            }else {
                logger.info("逐行事务回滚中,请耐心等待...");
            }
           checkForMissingLineTerminator(command);
        } catch (Exception e) {
            String message = "Error executing: " + command + ".  Cause: " + e;
            printlnError(message);
            throw new RuntimeSqlException(message, e);
        }
    }

    /**
     * @deprecated Since 3.5.4, this method is deprecated. Please close the {@link Connection} outside of this class.
     */
    @Deprecated
    public void closeConnection() {
        try {
            connection.close();
        } catch (Exception e) {
            // ignore
        }
    }

    private void setAutoCommit() {
        try {
            if (autoCommit != connection.getAutoCommit()) {
                connection.setAutoCommit(autoCommit);
            }
        } catch (Throwable t) {
            throw new RuntimeSqlException("Could not set AutoCommit to " + autoCommit + ". Cause: " + t, t);
        }
    }

    private void commitConnection() {
        try {
            if (!connection.getAutoCommit()) {
                connection.commit();
            }
        } catch (Throwable t) {
            throw new RuntimeSqlException("Could not commit transaction. Cause: " + t, t);
        }
    }

    private void rollbackConnection() {
        try {
            if (!connection.getAutoCommit()) {
                connection.rollback();
            }
        } catch (Throwable t) {
            // ignore
        }
    }

    private void checkForMissingLineTerminator(StringBuilder command) {
        if (command != null && command.toString().trim().length() > 0) {
            throw new RuntimeSqlException("Line missing end-of-line terminator (" + delimiter + ") => " + command);
        }
    }

    private void handleLine(StringBuilder command, String line) throws SQLException {
        String trimmedLine = line.trim();
        if (lineIsComment(trimmedLine)) {//当前行有注释追加
            Matcher matcher = DELIMITER_PATTERN.matcher(trimmedLine);
            if (matcher.find()) {
                delimiter = matcher.group(5);
            }
            println(trimmedLine);
        } else if (commandReadyToExecute(trimmedLine)) {//当前行是否有结束符,停止追加
            command.append(line, 0, line.lastIndexOf(delimiter));
            command.append(LINE_SEPARATOR);
            println(command);
            executeStatement(command.toString());

            command.setLength(0);
        } else if (trimmedLine.length() > 0) {//没有碰到结束符一直追加
            command.append(line);
            command.append(LINE_SEPARATOR);
        }
    }

    private boolean lineIsComment(String trimmedLine) {
        return trimmedLine.startsWith("//") || trimmedLine.startsWith("--");
    }

    private boolean commandReadyToExecute(String trimmedLine) {
        // issue #561 remove anything after the delimiter
        return !fullLineDelimiter && trimmedLine.contains(delimiter) || fullLineDelimiter && trimmedLine.equals(delimiter);
    }

    private void executeStatement(String command) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.setEscapeProcessing(escapeProcessing);
            String sql = command;
            if (removeCRs) {
                sql = sql.replace("\r\n", "\n");
            }
            try {
                boolean hasResults = statement.execute(sql);
                while (!(!hasResults && statement.getUpdateCount() == -1)) {
                    checkWarnings(statement);
                    printResults(statement, hasResults);
                    hasResults = statement.getMoreResults();
                }
            } catch (SQLWarning e) {
                throw e;
            } catch (SQLException e) {
                if (stopOnError) {
                    throw e;
                } else {
                    String message = "Error executing: " + command + ".  Cause: " + e;
                    printlnError(message);
                }
            }
        }
    }

    private void checkWarnings(Statement statement) throws SQLException {
        if (!throwWarning) {
            return;
        }
        // In Oracle, CREATE PROCEDURE, FUNCTION, etc. returns warning
        // instead of throwing exception if there is compilation error.
        SQLWarning warning = statement.getWarnings();
        if (warning != null) {
            throw warning;
        }
    }

    private void printResults(Statement statement, boolean hasResults) {
        if (!hasResults) {
            return;
        }
        try (ResultSet rs = statement.getResultSet()) {
            ResultSetMetaData md = rs.getMetaData();
            int cols = md.getColumnCount();
            for (int i = 0; i < cols; i++) {
                String name = md.getColumnLabel(i + 1);
                print(name + "\t");
            }
            println("");
            while (rs.next()) {
                for (int i = 0; i < cols; i++) {
                    String value = rs.getString(i + 1);
                    print(value + "\t");
                }
                println("");
            }
        } catch (SQLException e) {
            printlnError("Error printing results: " + e.getMessage());
        }
    }

    private void print(Object o) {
        if (logWriter != null) {
            logWriter.print(o);
            logWriter.flush();
        }
    }

    private void println(Object o) {
        if (logWriter != null) {
            logWriter.println(o);
            logWriter.flush();
        }
    }

    private void printlnError(Object o) {
        if (errorLogWriter != null) {
            errorLogWriter.println();
            errorLogWriter.println(o);
            errorLogWriter.flush();
        }
    }

}

1.Controller

    @RequestMapping("/doExecuteSql")
    public String doExecuteSql( ){
        System.out.println("脚本执行中");
        ExecutionTimeUtil timeUtil = new ExecutionTimeUtil(ExecutionTimeUtil.TIMEUNIT.SECOND);
        timeUtil.start();
        String[] paths= new String[]{"D:\\testsql2.sql"};
        executeSqlService.doExecuteSql(paths);
        long end = timeUtil.end();
        System.out.println("执行耗时:"+end+"秒");
        return "任务已在运行";
    }

2.Service

package com.yujie.service.impl;

import com.yujie.utils.ExecutionTimeUtil;
import com.yujie.utils.MyScriptRunner;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.lucene.util.RamUsageEstimator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.*;

/**
 * mybatis执行SQL脚本
 */
@Component
public class ExecuteSqlServiceImpl {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private DataSource dataSource;

    /**
     * 使用MyScriptRunner执行SQL脚本
     * 1.第一次执行采用批处理,批处理执行失败将会自动转为逐行执行检索错误的sql打印进日志
     */
    public void doExecuteSql(String[] sqlPath) {
        //通过数据源获取数据库链接
        Connection connection = DataSourceUtils.getConnection(dataSource);
        String[] filePaths = sqlPath;
        //创建脚本执行器
        MyScriptRunner scriptRunner = new MyScriptRunner(connection);
        //关闭Info日志
        scriptRunner.setLogWriter(null);
        //打印错误的日志信息
        scriptRunner.setErrorLogWriter(null);
        //报错停止运行
        scriptRunner.setStopOnError(true);
        //设置手动提交事务
        scriptRunner.setAutoCommit(false);
        //开启批处理模式
        scriptRunner.setSendFullScript(true);

        logger.info("批处理执行中");
        boolean b = batchSql(filePaths, scriptRunner,0);
        //true 批处理出现异常,转为逐行执行
        if(b){
            logger.info("逐行检索SQL启动");            ;
            //打印错误的日志信息
            StringWriter errorSql = new StringWriter();
            scriptRunner.setMsg(errorSql);
            PrintWriter print = new PrintWriter(errorSql);
            scriptRunner.setErrorLogWriter(print);
            //报错不要停止运行
            scriptRunner.setStopOnError(false);
            //设置手动提交事务
            scriptRunner.setAutoCommit(false);
            //关闭批处理
            scriptRunner.setSendFullScript(false);

            batchSql(filePaths, scriptRunner,1);
            String errorMsg = errorSql.toString();
            //逐行执行所有SQL,打印所有错误的SQL
            if(errorMsg.length() != 0){
                logger.error("--------------请修改以下错误sql再次执行脚本--------------");            ;
                logger.error("sql错误:【{}】",errorMsg);
            }else{
                //处理量设置大于mysql的max_allowed_packet将转为逐行执行
                logger.info("逐行插入成功!");
            }
        }else {
            logger.info("批处理成功!");
        }

    }

    private boolean batchSql(String[] filePaths,MyScriptRunner scriptRunner,int mark){
        for (String path : filePaths) {
            try ( FileInputStream fileInputStream=new FileInputStream(path)){
                InputStreamReader inputStreamReader=new InputStreamReader(fileInputStream,"UTF-8");
                BufferedReader bufferedReader= new BufferedReader(inputStreamReader);
                try {
                    scriptRunner.runScript(bufferedReader);
                } catch (Exception e) {
                    if(mark == 0){
                        logger.error("批处理执行失败");            ;
                        return true;
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

}

logback-spring.xml日志配置信息

<?xml version="1.0" encoding="UTF-8" ?>
<configuration scan="true">
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n
            </pattern>
        </encoder>
    </appender>
    <!-- 将日志写入日志文件 -->
    <appender name="FILE" class="ch.qos.logback.core.FileAppender">
        <file>./logs/springSecurity.log</file>
        <append>true</append><!-- 日志追加 -->
        <encoder>
            <pattern>%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n
            </pattern>
        </encoder>
    </appender>

    <root level="INFO">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="FILE"/>
    </root>
</configuration>

3.运行结果

2023-03-09 01:11:28 [http-nio-8080-exec-1] INFO  c.y.s.impl.ExecuteSqlServiceImpl - 批处理执行中
2023-03-09 01:11:28 [http-nio-8080-exec-1] ERROR com.yujie.utils.MyScriptRunner - 事务回滚中请耐心等待...
2023-03-09 01:11:28 [http-nio-8080-exec-1] ERROR c.y.s.impl.ExecuteSqlServiceImpl - 批处理执行失败
2023-03-09 01:11:28 [http-nio-8080-exec-1] INFO  c.y.s.impl.ExecuteSqlServiceImpl - 逐行检索SQL启动
2023-03-09 01:11:28 [http-nio-8080-exec-1] ERROR com.yujie.utils.MyScriptRunner - 事务回滚中请耐心等待...
2023-03-09 01:11:28 [http-nio-8080-exec-1] ERROR c.y.s.impl.ExecuteSqlServiceImpl - --------------请修改以下错误sql再次执行脚本--------------
2023-03-09 01:11:28 [http-nio-8080-exec-1] ERROR c.y.s.impl.ExecuteSqlServiceImpl - sql错误:【
Error executing: insert into zhsdb(id,userId,email,gender,qq,username,realname,mobile,avatar,source,code,majorName,schoolName,enrollmentYear) values(null,167222403,"null",1,"null","b774b137ef6847ab8bb5e34246671a40","啊","05692361196","https://image.zhihuishu.com/zhs/ablecommons/demo/201804/5e314c660d31448d94fc201d434ca736.jpg","school","1610124007","null","喵喵大学",2016)
.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '167222403' for key 'userId'

Error executing: insert into zhsdb(id,userId,email,gender,qq,username,realname,mobile,avatar,source,code,majorName,schoolName,enrollmentYear) values(null,167211713,"null",1,"null","8895fcb70ffc4b95a655c42f29a78f52","李四","03613611181","https://image.zhihuishu.com/zhs/ablecommons/demo/201804/b4fdcbe066fe4d199915320d9179ce16.jpg","null","2016091001","音乐学);1601","哔哔学院",2016制造错误的语法)
.  Cause: java.sql.SQLSyntaxErrorException: Unknown column '2016制造错误的语法' in 'field list'
】
执行耗时:1秒

可以看到sql文件中有4条sql主要是为了测试方便,有2条是正确的,有两条是错误的,那么最终的执行结果会打印这两条错误的sql到控制台上和日志文件中,数据库的条数为0因为回滚了,要想执行成功按错误提示把sql改正确即可

性能消耗参考

i7处理器,16g内存,固态,jvm内存设置200M 单线程批量插入700M sql文件(约200万条数据) 大概需要6分钟,如果出现批量插入失败将会自动转换为逐行检索,需要3倍的时间