import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
/**
* mysql数据库执行sql语句
**/
@Component("MYSQL_CALLER")
public class MysqlCaller extends AbstractDbCaller{
/**
* 执行sql语句,返回日志
* @param chkUpdateScript
* @param chkDatabase
* @return List<String>
*/
@Override
protected ExecuteSqlVO executeSql(ChkUpdateScript chkUpdateScript, ChkDatabase chkDatabase) throws Exception {
//获取用户名
String username =chkDatabase.getUsername();
//获取密码
String password = EncryptUtil.getInstance().DESdecode(chkDatabase.getPassword());
//数据库url地址
String dataSourceUrl = chkDatabase.getDataSourceUrl();
//获取驱动类
String databaseTypeDriver = chkDatabase.getDriver();
//获取sql语句
String sql = chkUpdateScript.getScriptContent();
//将sql脚本文件按分号分割开来
String[] sqlStatement = sql.split(";");
//获取数据库名称
String databaseName = chkDatabase.getDatabaseName();
//uuid
String uuid = "uuid";
//日志集合
List<String> logs= new ArrayList<>();
//执行sql语句
for (int i = 0; i<sqlStatement.length; i++){
Boolean aBoolean = JDBCUtils.getInstance().executeSql(databaseTypeDriver, dataSourceUrl, username, password, sqlStatement[i], uuid);
while (!aBoolean == true){
ExecuteSqlVO executeSqlVO = new ExecuteSqlVO();
String msg = sql + "SQL语句执行失败,执行数据库为" + databaseName;
List<String> logList = new ArrayList<>();
logList.add(sql);
executeSqlVO.setLogs(logList);
executeSqlVO.setCode(HttpStatus.ERROR);
return executeSqlVO;
}
String log = sqlStatement[i] + "执行成功";
logs.add(log);
}
ExecuteSqlVO executeSqlVO = new ExecuteSqlVO();
executeSqlVO.setCode(HttpStatus.SUCCESS);
executeSqlVO.setLogs(logs);
return executeSqlVO;
}
/**
* 数据库连接测试
* @param chkDatabase
* @return
* @throws Exception
*/
@Override
protected AjaxResult testData(ChkDatabase chkDatabase) throws Exception {
//获取用户名
String username =chkDatabase.getUsername();
//获取密码
String password =chkDatabase.getPassword();
//数据库url地址
String dataSourceUrl = chkDatabase.getDataSourceUrl();
//获取驱动类
String databaseTypeDriver = chkDatabase.getDriver();
//获取数据库名称
String databaseName = chkDatabase.getDatabaseName();
//测试数据库连接
Connection connection = JDBCUtils.getInstance().testDatabase(databaseTypeDriver, dataSourceUrl, username, password);
if (connection == null){
String msg = databaseName + "数据库连接测试失败";
return AjaxResult.success(msg,false);
}
if (!connection.isClosed()) {
String msg = databaseName + "数据库连接测试成功";
return AjaxResult.success(msg);
}
return AjaxResult.success("数据连接已关闭");
}
}
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.sql.*;
/**
* JDBC工具类
* @DATE 2022-07-13
*/
@Component
public class JDBCUtils {
private static int queryTimeOut;
//获取sql查询超时时间
@Value("${dq.sql.queryTimeOut}")
public void setQueryTimeOut(int queryTimeOut){
JDBCUtils.queryTimeOut = queryTimeOut;
}
public static volatile JDBCUtils instance;
private JDBCUtils() {}
public static JDBCUtils getInstance(){
if(instance == null){
synchronized (JDBCUtils.class){
if (instance == null){
instance = new JDBCUtils();
}
}
}
return instance;
}
/**
* 执行sql语句(非查询sql语句)
* @param driver
* @param url
* @param user
* @param password
* @param sql
* @param uuid
* @return Boolean
* @throws Exception
*/
public Boolean executeSql(String driver, String url, String user, String password, String sql, String uuid) throws Exception{
Connection conn = null;
Statement stmt = null;
Exception exception = null;
Boolean rs = null;
try{
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
//存储数据库连接vo
//设置数据库执行sql语句超时时间
stmt.setQueryTimeout(queryTimeOut);
rs = stmt.execute(sql);
}catch (ClassNotFoundException | SQLException e){
exception = e;
}finally {
try{
if (null != stmt){
stmt.close();
}
if (null != conn){
conn.close();
}
}catch (SQLException e){
e.printStackTrace();
throw e;
}
if (null != exception){
throw exception;
}
}
return !rs;
}
/**
* 数据库连接测试
* @param driver
* @param url
* @param user
* @param password
* @return
* @throws Exception
*/
public Connection testDatabase(String driver, String url, String user, String password) throws Exception{
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
return conn;
}catch (Exception e){
e.printStackTrace();
return null;
}
}
}
用法:1.执行sql语句,2.测试连接
logs = executorDatabase(chkDatabase, chkUpdateScript);
result = dbCaller.testDatabase(chkDatabase);
实体类参数:
@Data
public class ChkDatabase implements Serializable {
/**主键**/
private Integer id_;
/**删除标识**/
private String isdeleted;
/**创建人**/
private Integer oper;
/**创建时间**/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date optime;
/**更新人**/
private Integer updator;
/**更新时间**/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updatetime;
/**租户id**/
private Integer tenantid;
/**数据库名称**/
private String databaseName;
/**数据库类型**/
private String databaseType;
/**主机名称**/
private String host;
/**端口号**/
private Integer port;
/**用户名**/
private String username;
/**密码**/
private String password;
/**数据源连接url**/
private String dataSourceUrl;
/**数据库连接驱动**/
private String driver;
/**数据源名称**/
private String name;
/**启动状态‘0’启动,‘1’未启动**/
private String start;
}
public class ChkUpdateScript implements Serializable {
/**主键**/
private Integer id_;
/**删除标识**/
private String isdeleted;
/**创建人**/
private Integer oper;
/**创建时间**/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date optime;
/**更新人**/
private Integer updator;
/**更新时间**/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date updatetime;
/**租户id**/
private Integer tenantid;
/**脚本内容**/
private String scriptContent;
/**执行状态 :'1'已执行,'0'未执行**/
private char scriptStatus;
/**数据库连接信息id**/
private Integer databaseId;
/**服务版本信息id**/
private Integer serviceVersionId;
}