spring boot 执行sql文件

た入场券 2022-12-27 14:02 371阅读 0赞

现有个需求需要动态连接到不同数据库,执行sql文件,类似于navicat这种,可以执行sql

1.导入依赖

<dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.3</version>
    </dependency>
    
    <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.25</version>
    </dependency>

2.在启动类中加入

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)

防止spring 项目启动的时候自动注入数据库连接信息(如果项目中有依赖数据库跳过该步骤)

package cn.ucmed.licensemanager.client.utils;
    
    
    import java.io.*;
    import java.nio.charset.Charset;
    import java.sql.Connection;
    import java.sql.DriverManager;
    
    import cn.ucmed.licensemanager.client.model.ExecuteResult;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.jdbc.ScriptRunner;
    
    /**
     * @Author: li
     * @Date: 2020/11/5/005 19:58
     * @describe sql
     */
    @Slf4j
    public class SqlExecutor {
    
        public static ExecuteResult executeSql(String taskStepId, String url, String userName, String userPassword, String sql) {
            ExecuteResult executeResult = new ExecuteResult();
            executeResult.setTaskStepId(taskStepId);
    
            //sql执行输出流
            StringWriter succeedWriter = new StringWriter();
            PrintWriter succeedOut = new PrintWriter(succeedWriter);
            StringWriter errorWriter = new StringWriter();
            PrintWriter errorOut = new PrintWriter(errorWriter);
            Connection conn = null;
            ScriptRunner runner;
    
            try {
                conn = getMySqlConnection(url, userName, userPassword);
            } catch (Exception e) {
                executeResult.setStatus("数据库连接错误");
                executeResult.setLog(e.getStackTrace().toString());
                try {
                    conn.close();
                } catch (Exception ee) {
                    log.error(url + "关闭连接错误!");
                }
                return executeResult;
            }
            runner = new ScriptRunner(conn);
            //设置字符集,不然中文乱码插入错误
            Resources.setCharset(Charset.forName("UTF-8"));
            runner.setAutoCommit(false);
            runner.setSendFullScript(true);
            //设置日志
            runner.setLogWriter(succeedOut);
            runner.setErrorLogWriter(errorOut);
            //遇到错误停止
            runner.setStopOnError(true);
            // 绝对路径读取
    //            Reader read = new FileReader(new File("C:\\Users\\Ucmed\\Documents\\1.sql"));
            // 从class目录下直接读取
    //            Reader read = Resources.getResourceAsReader("test.sql");
            Reader read = new StringReader(sql);
            try {
                runner.runScript(read);
            } catch (Exception e) {
                executeResult.setStatus("sql脚本执行发生异常");
                executeResult.setLog(errorWriter.toString());
                return executeResult;
            } finally {
                try {
                    runner.closeConnection();
                    conn.close();
                } catch (Exception e) {
                    log.error(url + "关闭连接错误!");
                }
            }
            executeResult.setSucceed(true);
            executeResult.setLog(succeedWriter.toString());
            executeResult.setStatus("执行成功");
            return executeResult;
        }
    
        /**
         * @return
         * @throws Exception
         * @功能描述： 获取数据库连接
         */
        public static Connection getMySqlConnection(String url, String userName, String userPassword) throws Exception {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(url, userName, userPassword);
        }
    
        public static void main(String[] args) {
            ExecuteResult executeResult = SqlExecutor.executeSql("123", "jdbc:mysql://192.168.2.42:30306/manager?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai", "root", "123456", "select * from project;");
            System.out.println(executeResult);
        }
    }

package cn.ucmed.licensemanager.client.model;
    
    /**
     * @Author: li
     * @Date: 2020/12/11/011 13:48
     * @describe 接口返回封装类
     */
    public class ExecuteResult {
    
        private String taskStepId;
    
        private boolean isSucceed = false;
    
        private String status;
    
        private String logs;
    
        public String getTaskStepId() {
            return taskStepId;
        }
    
        public void setTaskStepId(String taskStepId) {
            this.taskStepId = taskStepId;
        }
    
        public boolean isSucceed() {
            return isSucceed;
        }
    
        public void setSucceed(boolean succeed) {
            isSucceed = succeed;
        }
    
        public String getStatus() {
            return status;
        }
    
        public void setStatus(String status) {
            this.status = status;
        }
    
        public String getLog() {
            return logs;
        }
    
        public void setLog(String log) {
            this.logs = log;
        }
    
        @Override
        public String toString() {
            return "ExecuteResult{" +
                    "taskStepId='" + taskStepId + '\'' +
                    ", isSucceed=" + isSucceed +
                    ", status='" + status + '\'' +
                    ", logs='" + logs + '\'' +
                    '}';
        }
    }