Java代码执行SQL脚本和SQL查询

1、Java 代码执行SQL脚本和SQL查询

package com.xxxxx.security.controller;


import com.xxxxx.security.vo.ExecScriptRunnerVo;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.ibatis.jdbc.SqlRunner;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author HanKeQi
 * @Date 2020/4/28 2:28 上午
 * @Version 1.0
 **/
@RestController
@RequestMapping(value = "/api/xxxxx")
public class ExecScriptRunnerController {

	//执行文件
    @PostMapping("/execFile")
    public String execFile(@RequestParam("file") MultipartFile multipartFile, ExecScriptRunnerVo execScriptRunnerVo) {
        ScriptRunner runner = null;
        Connection conn = null;
        try {
            InputStream inputStream = multipartFile.getInputStream();
            Class.forName(execScriptRunnerVo.getDriver());
            conn = DriverManager.getConnection(execScriptRunnerVo.getUrl(), execScriptRunnerVo.getUsername(), execScriptRunnerVo.getPassword());
            runner = new ScriptRunner(conn);
            runner.setFullLineDelimiter(false);
            runner.setSendFullScript(false);
            runner.setStopOnError(true);
            runner.runScript(new InputStreamReader(inputStream,"utf-8"));
            runner.setAutoCommit(true);//自动提交
            //这个设置为false是为了能够识别plsql的代码块，否则无法执行pl/sql的脚本，只能执行正常的sql语句。
            runner.setEscapeProcessing(false);
            runner.setSendFullScript(true); //这个是为了读取脚本所有语句，否则会按行读取进行执行，导致脚本解析错误，无法正常执行分行的begin end代码块。
        }catch (Exception e){
            if (conn != null){
                try {
                    conn.rollback();
                }catch (Exception e1){
                   e.printStackTrace();
                }
            }else {
                e.printStackTrace();
            }

        }finally {
            if (runner != null){
                runner.closeConnection();
            }
            if (conn != null){
                try {
                    conn.close();
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
        }
        return "success";
    }

	//执行单条SQL
    @PostMapping("/execSql")
    public List<Map<String, Object>> execSql(@RequestBody ExecScriptRunnerVo execScriptRunnerVo) {
        List<Map<String, Object>> maps = new ArrayList<>();
        Connection conn = null;
        SqlRunner runner = null;
        int sqlInt = -1;
        try {
            String selectSql = execScriptRunnerVo.getSelectSql();

            if (StringUtils.isEmpty(selectSql)){
                return ResponseData.<List<Map<String, Object>>>builder().error(Constants.ERROR_CODE).msg("selectSql 不能为空").build();
            }
            selectSql = selectSql.trim();
            Object[] args = {};
            Class.forName(execScriptRunnerVo.getDriver());
            conn = DriverManager.getConnection(execScriptRunnerVo.getUrl(), execScriptRunnerVo.getUsername(), execScriptRunnerVo.getPassword());
            runner = new SqlRunner(conn);
            if (selectSql.matches("^S.*") ||  selectSql.matches("^s.*"))
                maps = runner.selectAll(selectSql, args);
            else if (selectSql.matches("^U.*") ||  selectSql.matches("^u.*"))
                sqlInt = runner.update(selectSql, args);
            else if (selectSql.matches("^D.*") ||  selectSql.matches("^d.*"))
                sqlInt = runner.delete(selectSql, args);
            else
                return ResponseData.<List<Map<String, Object>>>builder().error(Constants.ERROR_CODE).msg("请检查SQL").build();
            if (sqlInt != -1){
                Map<String, Object> map = new HashMap<>();
                map.put("count", sqlInt);
                maps.add(map);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (runner != null){
                runner.closeConnection();
            }
            if (conn != null){
                try {
                    conn.close();
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }

        }
        return maps;
    }

对象类

package com.xxxxx.security.vo;

/**
 * @Author HanKeQi
 * @Date 2020/4/28 2:42 上午
 * @Version 1.0
 **/
public class ExecScriptRunnerVo {
	//默认MySQL
    private String driver = "com.mysql.jdbc.Driver";

    private String url;

    private String username;

    private String password;

    private String selectSql;

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSelectSql() {
        return selectSql;
    }

    public void setSelectSql(String selectSql) {
        this.selectSql = selectSql;
    }
}