查个问题十分钟，走工单一小时。慢！ 连线上库一分钟，一小时就超时，累！ 自己写吧！
注意： 查询必须带上where/limit（强校验） 增删改提交工单生成工单地址——>拥有签名的员工可签名执行
DbWorkController
@Controller
@RequestMapping("/dbWork")
public class DbWorkController {
private final Logger logger = LoggerFactory.getLogger(getClass());
@Autowired
CacheHelper cacheHelper;
@Autowired
DbWorkBizMapper dbWorkMapper;
@RequestMapping
public String dbWorkIndex() {
return "dbWork";
}
@RequestMapping("/{uuid}")
public ModelAndView dbWork(@PathVariable("uuid")String uuid) {
ModelAndView modelAndView = new ModelAndView("dbWork");
if(Strings.isBlank(uuid)){
return modelAndView;
}
String sql = cacheHelper.get("WMS_ADMIN_DBWORK_TRANSACTION:" + uuid);
modelAndView.addObject("uuid",uuid);
modelAndView.addObject("sql",sql);
return modelAndView;
}
@PostMapping("/select")
@ResponseBody
public ResultDTO select(String sql){
String sql1 = sql.toLowerCase();
if(!sql1.contains("where") || !sql1.contains("limit")){
throw new BusinessException(400,"安全起见，请加上where和limit");
}
List<LinkedHashMap<String, Object>> list = dbWorkMapper.select(sql);
list.forEach(item->{
item.entrySet().forEach(entry->{
Object value = entry.getValue();
if(value instanceof Date){
entry.setValue(DateUtils.convertTimeToStr((Date) value));
}
});
});
return ResultDTO.success(list);
}
@PostMapping("/submit")
@ResponseBody
public ResultDTO submit(String sql){
String uuid = UUID.randomUUID().toString();
cacheHelper.put("WMS_ADMIN_DBWORK_TRANSACTION:"+uuid,3600*24,sql);
logger.info("工单{}已提交：\n{}",uuid,sql);
return ResultDTO.success(uuid);
}
@PostMapping("/excute/{uuid}")
@ResponseBody
public ResultDTO update(@PathVariable("uuid")String uuid, @RequestParam String sign) {
String sql = cacheHelper.get("WMS_ADMIN_DBWORK_TRANSACTION:" + uuid);
if(Strings.isBlank(sql)){
throw new BusinessException(400,"工单不存在或已执行！");
}
String operator = decryptSign(sign);
logger.info("工单{}已由【{}】执行：\n{}",uuid,operator,sql);
cacheHelper.del("WMS_ADMIN_DBWORK_TRANSACTION:" + uuid);
if(sql.startsWith("update")){
return ResultDTO.success(dbWorkMapper.update(sql));
}
if(sql.startsWith("insert")){
return ResultDTO.success(dbWorkMapper.insert(sql));
}
if(sql.startsWith("delete")){
return ResultDTO.success(dbWorkMapper.delete(sql));
}
throw new BusinessException(400,"不支持的sql类型");
}
private String decryptSign(String sign){
String keyt = toSHA(sign);
switch (keyt){
case "e49564765c06eb8fb0ea4d75d65b45c89b8e2e54a22c21fd966e8f303c512a3a":return "zengdm";
}
throw new BusinessException(403,"无效的签名");
}
public static String toSHA(String ePass) {
try {
MessageDigest messageDigest = MessageDigest.getInstance("SHA-256");
messageDigest.update(ePass.getBytes(StandardCharsets.UTF_8));
byte[] byteBuffer = messageDigest.digest();
StringBuffer strHexString = new StringBuffer();
for (byte b : byteBuffer) {
String hex = Integer.toHexString(0xff & b);
if (hex.length() == 1) {
strHexString.append('0');
}
strHexString.append(hex);
}
return strHexString.toString();
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
}
DbWorkBizMapper
@Repository
public interface DbWorkBizMapper {
@Select("${sql}")
List<LinkedHashMap<String,Object>> select(@Param("sql") String sql);
@Update("${sql}")
int update(@Param("sql") String sql);
@Insert("${sql}")
int insert(@Param("sql") String sql);
@Delete("${sql}")
int delete(@Param("sql") String sql);
}
dbWork.html
<!DOCTYPE html>
<html lang="en" xmlns:layout="http://www.thymeleaf.org" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>DB工具</title>
<meta name="viewport" content="width=device-width,initial-scale=1"/>
<link rel="stylesheet" href="/libs/layui/css/layui.css"/>
<script src="/libs/layui/layui.js"></script>
<script src="/javascripts/common.js"></script>
<script src="/javascripts/utils/ajaxhook.min.js"></script>
<link rel="stylesheet" href="/stylesheets/layui-theme.css"/>
<link rel="stylesheet" href="/stylesheets/app.css"/>
</head>
<body class="smallsize" style="padding: 0 5% 0 5%;">
<input id="sqlSubmit" th:value="${sql}" hidden />
<input id="uuid" th:value="${uuid}" hidden />
<textarea id="sql" placeholder="输入sql语句，查询语句需包含where和limit" class="layui-textarea"
style="width: 1000px;height: 200px;margin-top: 20px;"></textarea>
<button type="button" class="layui-btn" id="dbSelect"style="margin: 15px;">查询</button>
<button type="button" class="layui-btn" id="dbSubmit"style="margin: 15px;">提交工单</button>
<button type="button" class="layui-btn" id="dbExcute"style="margin: 15px;display:none;">执行工单</button>
<table class="layui-table-radius" id="myTable" lay-filter="myTable"></table>
</body>
<script th:inline="none">
layui.use(['form', 'laydate', 'laypage', 'layer', 'table', 'carousel', 'element', 'httpclient','upload'], function () {
var laypage = layui.laypage //分页
, table = layui.table //表格
, laydate = layui.laydate //日期
, httpclient = layui.httpclient
, upload = layui.upload
, form = layui.form;
const {$} = layui;
// 判断sql是查询还是提交工单
$("#sql").change(function () {
if($(this).val().startsWith("select") || $(this).val().startsWith("SELECT")){
$("#dbSelect").show();
$("#dbSubmit").hide();
}else{
$("#dbSelect").hide();
$("#dbSubmit").show();
}
});
if($("#sqlSubmit").val()){
$("#sql").val($("#sqlSubmit").val());
$("#sql").attr("readonly", true);
$("#dbSelect").hide();
$("#dbSubmit").hide();
$("#dbExcute").show();
}
$("#dbSelect").click(function () {
httpclient.post('/dbWork/select', {
'sql': $("#sql").val()
}, function(resp){
let list = resp.data;
if (list.length <= 0){
layer.msg('未查到数据');
return;
}
let item1 = list[0];
let cols = [[]]
Object.keys(item1).forEach(
key=>{
let width = key.length*8+32;
cols[0].push({field: key, title: key,width: width,align: 'center'})
}
)
table.render({
elem: '#myTable',
cols: cols,
data: list,
page: true,
limits: [10, 20, 50, 9999],
limit: 20
});
});
});
$("#dbSubmit").click(function () {
httpclient.post('/dbWork/submit', {
'sql': $("#sql").val()
}, function(resp){
let uuid = resp.data;
if (uuid){
layer.alert('工单已提交：http://'+window.location.host+'/dbWork/'+uuid);
}
});
});
$("#dbExcute").click(function () {
layer.prompt({
formType: 1,
value: '',
title: '请输入签名',
btn: ['确定','取消'],
btnAlign: 'c'
}, function(value,index){
httpclient.post('/dbWork/excute/'+$('#uuid').val(), {
'sign': value
}, function(resp){
let num = resp.data;
layer.alert('工单已执行，成功行数：'+num);
});
});
});
});
</script>
</html>
