Java implements the sql script compression package interface

table of Contents

Background overview

Precondition

Realization ideas and processes

Get a list of database library names

Unzip the zip file

Parsing stream processing method (jdbc)

Temporary file processing method (ScriptRunner in mybatis)


Background overview

If the project has been released to the production or uat environment, the sql script will be saved, and the sql script will be run on the server the next time the iterative version is released. This workload can be large or small. If there is a lot of data to be modified or inserted, it is conceivable to execute one file by file, and the service deployment on centos is not friendly enough to execute. In this context, an interface for executing SQL scripts is provided. Select the database name at the front end, upload the compressed sql package to execute, and return abnormal sql and rollback at the same time.

Precondition

Need to agree on the file naming of the compressed package, because generally run ddl first and then run dml.

Realization ideas and processes

1. Read the configuration file database information and return the database list information for the front end to select the database

2. The name of the front-end input database and File

3. Use ZipEntry to decompress the zip file

4. Check ddl

5. Check dml

6. Assemble the data in the configuration file and run sql with jdbc

Get a list of database library names

The url in the configuration file is jdbc:mysql://localhost:3306/test_user?useUnicode=true&characterEncoding=UTF-8, it can be concluded that the database library name is test_user, and all database library names can be obtained as follows:

select schema_name from information_schema.schemata;

Unzip the zip file

There are two ways to try here, one is to use temporary files (decompress to a temporary file, directly fetch from the temporary file when running sql, and delete the temporary file after running), the other is to directly parse the stream (summarize the file stream into one In the stream, it is parsed into text through base64), I prefer the way of parsing the stream.

Parsing stream processing method (jdbc)

Convert file to ZipInputStream, use zipInputStream.getNextEntry(); to traverse the file, write the file into ByteArrayOutputStream, and then convert it to String through toByteArray (you can get each sql), and bring sql into jdbc for execution.

  /**     * 解压文件     *     * @return     */    public Map<String, List<String>> unZip(FileParam param) {        MultipartFile file = param.getFile();        // 校验是否是zip文件        if (!file.getOriginalFilename().contains(".zip")) {            throw new BadRequestException("请上传zip文件");        }        String ddlSqlStr = null;        String dmlSqlStr = null;        try {            ZipInputStream zipInputStream = new ZipInputStream(file.getInputStream());            // 这里需要校验ddl和dml规范,如果不用校验可以只定义一个ByteArrayOutputStream            ByteArrayOutputStream ddlBos = new ByteArrayOutputStream();            ByteArrayOutputStream dmlBos = new ByteArrayOutputStream();            // 遍历每一个文件            ZipEntry zipEntry = zipInputStream.getNextEntry();            while (zipEntry != null) {                if (!zipEntry.isDirectory()) {                    int size = 0;                    byte[] buffer = new byte[1024];                    while (true) {                        size = zipInputStream.read(buffer, 0, buffer.length);                        if (size <= 0) {                            break;                        }                        if (zipEntry.getName().contains("ddl")) {                            ddlBos.write(buffer, 0, size);                        }                        if (zipEntry.getName().contains("dml")) {                            dmlBos.write(buffer, 0, size);                        }                    }                }                zipInputStream.closeEntry();                zipEntry = zipInputStream.getNextEntry();            }            // 将流转换为字符串 (解析sql文件)            ddlSqlStr = new String(ddlBos.toByteArray(), "utf-8");            dmlSqlStr = new String(dmlBos.toByteArray(), "utf-8");            // 释放            ddlBos.flush();            ddlBos.close();            dmlBos.flush();            dmlBos.close();             zipInputStream.close();            log.info(file.getName() + "解压成功");        } catch (Exception e) {            log.error("解压失败:" + e.getMessage());            throw new RenException(file.getName() + "解压失败: " + e.getMessage());        }         Map<String, List<String>> listMap = new HashMap<>();        // 处理ddl        if (StringUtils.isNotBlank(ddlSqlStr)) {            List<String> ddlSqlList = new ArrayList<>();            // 此方法进行数据清洗,将string中的sql按分号切割            Arrays.stream(ddlSqlStr.split(";")).forEach(sql -> {                // 切割完的sql要补上分号                ddlSqlList.add(sql + ";");            });            // 校验ddl            checkDdl(ddlSqlList);            listMap.put("ddl", ddlSqlList);        }        if (StringUtils.isNotBlank(dmlSqlStr)) {            List<String> dmlSqlList = new ArrayList<>();            Arrays.stream(dmlSqlStr.split(";")).forEach(sql -> {                dmlSqlList.add(sql + ";");            });            // 校验dml            checkDml(dmlSqlList);            listMap.put("dml", dmlSqlList);        }        return listMap;    }

Use jdbc to execute SQL core code:

        Exception error = null;        Connection conn = null;        Statement stmt = null;        try {            Class.forName(driverClassName);            conn = DriverManager.getConnection(url, username, password);            log.info("执行的数据库url: " + url.toString());            // 设置不自动提交            conn.setAutoCommit(false);            stmt = conn.createStatement();            // 先运行ddl            Statement ddlStmt = stmt;            Statement dmlStmt = stmt;            List<String> ddlSqlList = MapUtils.getObject(listMap, RunSqlConstant.DDL);            if (CollectionUtils.isNotEmpty(ddlSqlList)) {                ddlSqlList.forEach(sql -> {                    try {                        log.info("ddl: " + sql);                        ddlStmt.execute(sql);                    } catch (SQLException sqlException) {                        throw new RenException("SQL:" + sql + " 执行失败:" + sqlException.getMessage());                     }                });             }             // 再运行dml            List<String> dmlSqlList = MapUtils.getObject(listMap, RunSqlConstant.DML);            if (CollectionUtils.isNotEmpty(dmlSqlList)) {                dmlSqlList.forEach(sql -> {                    try {                        log.info("dml: " + sql);                        dmlStmt.execute(sql);                    } catch (SQLException sqlException) {                        throw new RenException("SQL:" + sql + " 执行失败:" + sqlException.getMessage());                    }                });            }             // 提交事务            conn.commit();            ddlStmt.close();            dmlStmt.close();        } catch (Exception e) {            try {                conn.rollback();                log.error("数据回滚成功");            } catch (SQLException e1) {                log.error("数据回滚失败: " + e1.getMessage());                throw new RenException("数据回滚失败: " + e1.getMessage());            }            error = e;        } finally {            try {                // 关闭                stmt.close();                conn.close();            } catch (SQLException e) {                e.printStackTrace();                error = e;            }        }        if (error != null) {            throw new RenException(error.getMessage());        } else {            log.info("SQL脚本执行完成");            return "SQL脚本执行完成";        }

This can be achieved! Look at the running effect of the front end

Temporary file processing method (ScriptRunner in mybatis)

Convert file to ZipInputStream, use zipInputStream.getNextEntry(); to traverse the file, first save it in a temporary folder, enter the method of running sql, use mybatis' ScriptRunner to execute the sql file.

    public void runFileSql(FileParam param) {        // 1、解压文件        unZip(param);        // 2、对临时位置进行读取,并运行        runSql();        // 3、删除临时文件        deleteFile(new File(DesDirectory_ONE));    }

The core code is as follows:

 private static final String DesDirectory_ONE = "E:" + File.separator + "testRunSql";     private static final String DesDirectory_TWO = "E:" + File.separator + "testRunSql" + File.separator + "sql";     /**     * 在使用zip格式压缩、解压缩时,     * 压缩可以使用ZipEntry类和ZipOutputStream类,     * 解压缩可以使用ZipEntry类和ZipInputStream类     *     * @param fileParam     */    @Override    public void unZip(FileParam fileParam) {         MultipartFile file = fileParam.getFile();        try {            ZipInputStream zipInputStream = new ZipInputStream(file.getInputStream());            // 遍历每一个文件            ZipEntry zipEntry = zipInputStream.getNextEntry();            while (zipEntry != null) {                // 创建文件夹                File fileInitOne = new File(DesDirectory_ONE);                if (!fileInitOne.exists()) {                    fileInitOne.mkdir();                }                File fileInitTwo = new File(DesDirectory_TWO);                if (!fileInitTwo.exists()) {                    fileInitTwo.mkdir();                }                if (zipEntry.isDirectory()) {                    // 文件夹                    String name = zipEntry.getName();                    name = name.substring(0, name.length() - 1).replace("/", "\\");                    String unzipFilePath = DesDirectory_ONE + File.separator + name;                    mkdir(unzipFilePath);                } else { // 文件                    String unzipFilePath = DesDirectory_ONE + File.separator + zipEntry.getName();                    File parentFile = new File(unzipFilePath);                    // 创建父目录                    mkdir(parentFile.getParentFile());                    // 写出文件流                    BufferedOutputStream bufferedOutputStream =                            new BufferedOutputStream(new FileOutputStream(unzipFilePath));                    byte[] bytes = new byte[1024];                    int readLen;                    while ((readLen = zipInputStream.read(bytes)) != -1) {                        bufferedOutputStream.write(bytes, 0, readLen);                    }                    bufferedOutputStream.close();                }                zipInputStream.closeEntry();                zipEntry = zipInputStream.getNextEntry();            }            zipInputStream.close();        } catch (Exception e) {            e.printStackTrace();        }    }

Use mybatis's ScriptRunner to execute sql files:

Exception error = null;        Connection conn = null;        try {             Class.forName(driver);            conn = DriverManager.getConnection(url, userName, password);            // 设置不自动提交            conn.setAutoCommit(false);            ScriptRunner runner = new ScriptRunner(conn);            // 设置不自动提交            runner.setAutoCommit(false);            /*             * setStopOnError参数作用:遇见错误是否停止;             * (1)false,遇见错误不会停止,会继续执行,会打印异常信息,并不会抛出异常,当前方法无法捕捉异常无法进行回滚操作,             * 无法保证在一个事务内执行; (2)true,遇见错误会停止执行,打印并抛出异常,捕捉异常,并进行回滚,保证在一个事务内执行;             */            runner.setStopOnError(true);            /*             * 按照那种方式执行 方式一:true则获取整个脚本并执行; 方式二:false则按照自定义的分隔符每行执行;             */            runner.setSendFullScript(false);             // 设置是否输出日志,null不输出日志,不设置自动将日志输出到控制台            runner.setLogWriter(null);            // 如果又多个sql文件,可以写多个runner.runScript(xxx),            Resources.setCharset(Charset.forName("UTF8"));            // 运行sql            runFile(runner, DesDirectory_TWO);         } catch (Exception e) {            try {                conn.rollback();                log.error("数据回滚成功");            } catch (SQLException e1) {                log.error("数据回滚失败,系统错误");            }            log.error("执行sql文件进行数据库创建失败....", e);            error = e;        } finally {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();                error = e;            }        }        if (error != null) {            try {                throw error;            } catch (Exception e) {                log.error(error.getLocalizedMessage());            }        } else {            log.info("SQL脚本执行完成");        }

runFile method

  /**     * 用mybatis ScriptRunner跑sql     *     * @param runner     * @param sqlStr     */    public static void runFile(ScriptRunner runner, String sqlStr) {        try {            File sqlFile = new File(sqlStr);            if (sqlFile.exists()) {                File[] sqlListFile = sqlFile.listFiles();                for (int i = 0; i < sqlListFile.length; i++) {                    if (!sqlListFile[i].isDirectory()) {                        // 定义命令间的分隔符                        runner.setDelimiter(";");                        runner.setFullLineDelimiter(false);                        InputStream inputStream = new FileInputStream(sqlListFile[i].toString());                        try {                            runner.runScript(new InputStreamReader(inputStream));                        } catch (Exception e) {                            log.info("执行sql脚本:" + sqlListFile[i].toString() + " 失败原因:" + e.getMessage());                        }                        // 临时文件 关闭文件流 file.delete()才能删除成功                        inputStream.close();                    } else {                        runFile(runner, sqlListFile[i].toString());                    }                }            }        } catch (Exception e) {            log.error(e.getLocalizedMessage());        }    }

Remember to delete temporary files in this way

  /**     * 先根遍历序递归删除文件夹     *     * @param dirFile 要被删除的文件或者目录     * @return 删除成功返回true, 否则返回false     */    public static boolean deleteFile(File dirFile) {        // 如果dir对应的文件不存在,则退出        if (!dirFile.exists()) {            return false;        }        if (dirFile.isFile()) {            log.info("删除文件:" + dirFile.isFile());            return dirFile.delete();        } else {            for (File file : dirFile.listFiles()) {                log.info("删除文件:" + file);                deleteFile(file);            }        }        return dirFile.delete();    }