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;
}
}