前端代码
略,比较简单的文件上传,这里就不写了
Java代码
由于要结合Mybatis使用所以就需要创建Mapper文件了
RunSqlMapper.java
和你的其他Mapper文件放一起就行了,要被扫描到
public interface RunSqlMapper extends BaseMapper<T> {
//执行sql语句
public void runSqlBySqlStr(String sqlStr);
}
RunSqlMapper.xml
我这里执行的sql语句是新增语句,如果是查询语句的话情况略微不同,需要加上resultMap返回查询的格式
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="RunSqlMapper的位置">
//执行sql语句
<select id="runSqlBySqlStr" parameterType="java.lang.String">
<![CDATA[${sqlStr}]]>
</select>
</mapper>
Controller文件
/**
* 导入sql数据
*
* @param request
* @param response
* @return
*/
@RequestMapping(value = "/importSql", method = RequestMethod.POST)
public Result<?> importSql(HttpServletRequest request, HttpServletResponse response) {
return 调用service的方法(request, response);
}
ServiceImpl文件
@Resource
private RunSqlMapper runSqlMapper;
/**
* 导入sql文件
*
* @param request
* @param response
* @return
*/
protected Result<?> importSql(HttpServletRequest request, HttpServletResponse response) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
MultipartFile file = entity.getValue();// 获取上传文件对象
List<String> sqlList = new ArrayList<String>();
InputStream sqlFileIn = null;
try {
sqlFileIn = file.getInputStream();
int available = sqlFileIn.available();
if (available == 0){
throw new RuntimeException("文件内容为空,请检查后再进行导入");
}
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
}
// Windows 下换行是 \r\n, Linux 下是 \n
String[] sqlArr = sqlSb.toString()
.split("(;\\s*\\r\\n)|(;\\s*\\n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
for (String sql:sqlList){
Boolean b = sql != null && (sql.contains("insert") || sql.contains("INSERT"));
if (b){
runSqlMapper.runSqlBySqlStr(sql);
}else {
throw new JeecgBootException(sql + " sql语句错误");
}
}
return Result.ok("文件导入成功!数据行数:" + sqlList.size());
} catch (Exception e) {
log.error(e.getMessage(), e);
return Result.error("文件导入失败:" + e.getMessage());
} finally {
try {
sqlFileIn.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return Result.error("文件导入失败!");
}