需求为:
需要动态连接到不同数据库,执行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.在启动类中加入
防止spring 项目启动的时候自动注入数据库连接信息(如果项目中有依赖数据库跳过该步骤)
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
(一般不需要添加)
3.共二个方法
其一为通过账号,密码,url与数据库获取连接
其二为执行sql语句
方法二中调用了方法一
方法一
/**
* @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);
}
方法二
//执行sql语句
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;
}
4.通过一个返回类接收
/**
* @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 + '\'' +
'}';
}
}
5.调用执行方法
ExecuteResult executeResult = 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);