@PostMapping("/sql/execute")
@Transactional(rollbackFor = Exception.class)
public AjaxResult importData(@RequestParam("sqlFile") MultipartFile sqlFile,
@RequestParam("account") String account,
@RequestParam("password") String password,
@RequestParam("anhao") String anhao) throws Exception {
if (!Objects.equals(account, "111")) {
throw new ServiceException("账号不正确");
}
if (!Objects.equals(password, "2222")) {
throw new ServiceException("密码不正确");
}
if (!Objects.equals(anhao, "3333")) {
throw new ServiceException("执行失败");
}
List<String> lines = new ArrayList<>();
try (BufferedReader reader = new BufferedReader(new InputStreamReader(sqlFile.getInputStream()))) {
String line;
while (StringUtils.isNotEmpty(line = reader.readLine())) {
String subLine = line.substring(0, line.length() -1);
lines.add(subLine);
}
}
String[] sqlStatements = lines.toArray(new String[0]);
int[] resultArray = jdbcTemplate.batchUpdate(sqlStatements);
int[] evenIndices = IntStream.range(0, resultArray.length)
.filter(index -> resultArray[index] != 1)
.toArray();
if(evenIndices.length != 0) {
throw new ServiceException(Arrays.toString(evenIndices));
}
return AjaxResult.success();
}
需要注意的是,sql文件中若有多个insert或update、delete的操作,无需用;隔开