spring boot 執行sql檔案

現有個需求需要動態連線到不同資料庫,執行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 專案啟動的時候自動注入資料庫連線資訊(如果專案中有依賴資料庫跳過該步驟)

3.

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 + '\'' +
                '}';
    }
}