目录
背景概述
前置条件
实现思路及流程
获取数据库库名列表
解压zip文件
解析流处理方式（jdbc）
临时文件处理方式（mybatis中的ScriptRunner）
背景概述
如果项目已经发布过生产或者uat环境后，便会保存sql脚本，在下一次发迭代版本的时候，会将sql脚本放在服务器上运行一遍。这个工作量可大可小，如果修改或者插入的数据多，一个文件一个文件去执行可想而知，并且服务部署在centos上执行起来也不够友好。在此背景下，提供一个专门执行sql脚本的接口，在前端选择数据库名，上传sql的压缩包即可执行，同时返回异常的sql和回滚。
前置条件
需要约定好压缩包的文件命名，因为一般是先运行ddl再运行dml。
实现思路及流程
1、读取配置文件数据库信息，返回数据库列表信息，供前端选择数据库
2、前端入参数据库名以及File
3、运用ZipEntry解压zip文件
4、校验ddl
5、校验dml
6、拼装配置文件中的数据，用jdbc运行sql
获取数据库库名列表
配置文件中的url是jdbc:mysql://localhost:3306/test_user?useUnicode=true&characterEncoding=UTF-8，可以得出这个数据库库名是test_user，可以通过如下方式获得所有数据库库名：
select schema_name from information_schema.schemata;解压zip文件
这里尝试了两种方式，一种是用临时文件（解压到临时文件，运行sql的时候直接从临时文件取，运行了需删除临时文件），一种是直接解析流（将文件流汇总到一个流里面，在通过base64解析成文字），本人更倾向解析流的方式。
解析流处理方式（jdbc）
将file转成ZipInputStream ，利用zipInputStream.getNextEntry();遍历文件，将文件写入ByteArrayOutputStream ，再通过toByteArray转成String（即可获得每一条sql），将sql带入jdbc执行。
/**
* 解压文件
*
* @return
*/
public Map<String, List<String>> unZip(FileParam param) {
MultipartFile file = param.getFile();
// 校验是否是zip文件
if (!file.getOriginalFilename().contains(".zip")) {
throw new BadRequestException("请上传zip文件");
}
String ddlSqlStr = null;
String dmlSqlStr = null;
try {
ZipInputStream zipInputStream = new ZipInputStream(file.getInputStream());
// 这里需要校验ddl和dml规范，如果不用校验可以只定义一个ByteArrayOutputStream
ByteArrayOutputStream ddlBos = new ByteArrayOutputStream();
ByteArrayOutputStream dmlBos = new ByteArrayOutputStream();
// 遍历每一个文件
ZipEntry zipEntry = zipInputStream.getNextEntry();
while (zipEntry != null) {
if (!zipEntry.isDirectory()) {
int size = 0;
byte[] buffer = new byte[1024];
while (true) {
size = zipInputStream.read(buffer, 0, buffer.length);
if (size <= 0) {
break;
}
if (zipEntry.getName().contains("ddl")) {
ddlBos.write(buffer, 0, size);
}
if (zipEntry.getName().contains("dml")) {
dmlBos.write(buffer, 0, size);
}
}
}
zipInputStream.closeEntry();
zipEntry = zipInputStream.getNextEntry();
}
// 将流转换为字符串 （解析sql文件）
ddlSqlStr = new String(ddlBos.toByteArray(), "utf-8");
dmlSqlStr = new String(dmlBos.toByteArray(), "utf-8");
// 释放
ddlBos.flush();
ddlBos.close();
dmlBos.flush();
dmlBos.close();
zipInputStream.close();
log.info(file.getName() + "解压成功");
} catch (Exception e) {
log.error("解压失败：" + e.getMessage());
throw new RenException(file.getName() + "解压失败: " + e.getMessage());
}
Map<String, List<String>> listMap = new HashMap<>();
// 处理ddl
if (StringUtils.isNotBlank(ddlSqlStr)) {
List<String> ddlSqlList = new ArrayList<>();
// 此方法进行数据清洗，将string中的sql按分号切割
Arrays.stream(ddlSqlStr.split(";")).forEach(sql -> {
// 切割完的sql要补上分号
ddlSqlList.add(sql + ";");
});
// 校验ddl
checkDdl(ddlSqlList);
listMap.put("ddl", ddlSqlList);
}
if (StringUtils.isNotBlank(dmlSqlStr)) {
List<String> dmlSqlList = new ArrayList<>();
Arrays.stream(dmlSqlStr.split(";")).forEach(sql -> {
dmlSqlList.add(sql + ";");
});
// 校验dml
checkDml(dmlSqlList);
listMap.put("dml", dmlSqlList);
}
return listMap;
}运用jdbc执行sql核心代码:
Exception error = null;
Connection conn = null;
Statement stmt = null;
try {
Class.forName(driverClassName);
conn = DriverManager.getConnection(url, username, password);
log.info("执行的数据库url: " + url.toString());
// 设置不自动提交
conn.setAutoCommit(false);
stmt = conn.createStatement();
// 先运行ddl
Statement ddlStmt = stmt;
Statement dmlStmt = stmt;
List<String> ddlSqlList = MapUtils.getObject(listMap, RunSqlConstant.DDL);
if (CollectionUtils.isNotEmpty(ddlSqlList)) {
ddlSqlList.forEach(sql -> {
try {
log.info("ddl: " + sql);
ddlStmt.execute(sql);
} catch (SQLException sqlException) {
throw new RenException("SQL：" + sql + " 执行失败：" + sqlException.getMessage());
}
});
}
// 再运行dml
List<String> dmlSqlList = MapUtils.getObject(listMap, RunSqlConstant.DML);
if (CollectionUtils.isNotEmpty(dmlSqlList)) {
dmlSqlList.forEach(sql -> {
try {
log.info("dml: " + sql);
dmlStmt.execute(sql);
} catch (SQLException sqlException) {
throw new RenException("SQL：" + sql + " 执行失败：" + sqlException.getMessage());
}
});
}
// 提交事务
conn.commit();
ddlStmt.close();
dmlStmt.close();
} catch (Exception e) {
try {
conn.rollback();
log.error("数据回滚成功");
} catch (SQLException e1) {
log.error("数据回滚失败: " + e1.getMessage());
throw new RenException("数据回滚失败: " + e1.getMessage());
}
error = e;
} finally {
try {
// 关闭
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
error = e;
}
}
if (error != null) {
throw new RenException(error.getMessage());
} else {
log.info("SQL脚本执行完成");
return "SQL脚本执行完成";
}这样就可以实现啦！看看前端的运行效果
临时文件处理方式（mybatis中的ScriptRunner）
将file转成ZipInputStream ，利用zipInputStream.getNextEntry();遍历文件，先存入临时文件夹，在进入运行sql的方法，运用mybatis的ScriptRunner执行sql文件.
public void runFileSql(FileParam param) {
// 1、解压文件
unZip(param);
// 2、对临时位置进行读取,并运行
runSql();
// 3、删除临时文件
deleteFile(new File(DesDirectory_ONE));
}核心代码如下:
private static final String DesDirectory_ONE = "E:" + File.separator + "testRunSql";
private static final String DesDirectory_TWO = "E:" + File.separator + "testRunSql" + File.separator + "sql";
/**
* 在使用zip格式压缩、解压缩时，
* 压缩可以使用ZipEntry类和ZipOutputStream类，
* 解压缩可以使用ZipEntry类和ZipInputStream类
*
* @param fileParam
*/
@Override
public void unZip(FileParam fileParam) {
MultipartFile file = fileParam.getFile();
try {
ZipInputStream zipInputStream = new ZipInputStream(file.getInputStream());
// 遍历每一个文件
ZipEntry zipEntry = zipInputStream.getNextEntry();
while (zipEntry != null) {
// 创建文件夹
File fileInitOne = new File(DesDirectory_ONE);
if (!fileInitOne.exists()) {
fileInitOne.mkdir();
}
File fileInitTwo = new File(DesDirectory_TWO);
if (!fileInitTwo.exists()) {
fileInitTwo.mkdir();
}
if (zipEntry.isDirectory()) {
// 文件夹
String name = zipEntry.getName();
name = name.substring(0, name.length() - 1).replace("/", "\\");
String unzipFilePath = DesDirectory_ONE + File.separator + name;
mkdir(unzipFilePath);
} else { // 文件
String unzipFilePath = DesDirectory_ONE + File.separator + zipEntry.getName();
File parentFile = new File(unzipFilePath);
// 创建父目录
mkdir(parentFile.getParentFile());
// 写出文件流
BufferedOutputStream bufferedOutputStream =
new BufferedOutputStream(new FileOutputStream(unzipFilePath));
byte[] bytes = new byte[1024];
int readLen;
while ((readLen = zipInputStream.read(bytes)) != -1) {
bufferedOutputStream.write(bytes, 0, readLen);
}
bufferedOutputStream.close();
}
zipInputStream.closeEntry();
zipEntry = zipInputStream.getNextEntry();
}
zipInputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}运用mybatis的ScriptRunner执行sql文件:
Exception error = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userName, password);
// 设置不自动提交
conn.setAutoCommit(false);
ScriptRunner runner = new ScriptRunner(conn);
// 设置不自动提交
runner.setAutoCommit(false);
/*
* setStopOnError参数作用：遇见错误是否停止；
* （1）false，遇见错误不会停止，会继续执行，会打印异常信息，并不会抛出异常，当前方法无法捕捉异常无法进行回滚操作，
* 无法保证在一个事务内执行； （2）true，遇见错误会停止执行，打印并抛出异常，捕捉异常，并进行回滚，保证在一个事务内执行；
*/
runner.setStopOnError(true);
/*
* 按照那种方式执行 方式一：true则获取整个脚本并执行； 方式二：false则按照自定义的分隔符每行执行；
*/
runner.setSendFullScript(false);
// 设置是否输出日志，null不输出日志，不设置自动将日志输出到控制台
runner.setLogWriter(null);
// 如果又多个sql文件，可以写多个runner.runScript(xxx),
Resources.setCharset(Charset.forName("UTF8"));
// 运行sql
runFile(runner, DesDirectory_TWO);
} catch (Exception e) {
try {
conn.rollback();
log.error("数据回滚成功");
} catch (SQLException e1) {
log.error("数据回滚失败，系统错误");
}
log.error("执行sql文件进行数据库创建失败....", e);
error = e;
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
error = e;
}
}
if (error != null) {
try {
throw error;
} catch (Exception e) {
log.error(error.getLocalizedMessage());
}
} else {
log.info("SQL脚本执行完成");
}runFile方法
/**
* 用mybatis ScriptRunner跑sql
*
* @param runner
* @param sqlStr
*/
public static void runFile(ScriptRunner runner, String sqlStr) {
try {
File sqlFile = new File(sqlStr);
if (sqlFile.exists()) {
File[] sqlListFile = sqlFile.listFiles();
for (int i = 0; i < sqlListFile.length; i++) {
if (!sqlListFile[i].isDirectory()) {
// 定义命令间的分隔符
runner.setDelimiter(";");
runner.setFullLineDelimiter(false);
InputStream inputStream = new FileInputStream(sqlListFile[i].toString());
try {
runner.runScript(new InputStreamReader(inputStream));
} catch (Exception e) {
log.info("执行sql脚本：" + sqlListFile[i].toString() + " 失败原因：" + e.getMessage());
}
// 临时文件 关闭文件流 file.delete()才能删除成功
inputStream.close();
} else {
runFile(runner, sqlListFile[i].toString());
}
}
}
} catch (Exception e) {
log.error(e.getLocalizedMessage());
}
}运用这种方式记得删除临时文件
/**
* 先根遍历序递归删除文件夹
*
* @param dirFile 要被删除的文件或者目录
* @return 删除成功返回true, 否则返回false
*/
public static boolean deleteFile(File dirFile) {
// 如果dir对应的文件不存在，则退出
if (!dirFile.exists()) {
return false;
}
if (dirFile.isFile()) {
log.info("删除文件：" + dirFile.isFile());
return dirFile.delete();
} else {
for (File file : dirFile.listFiles()) {
log.info("删除文件：" + file);
deleteFile(file);
}
}
return dirFile.delete();
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
