package com.wgcloud.controller;
|
|
import cn.hutool.json.JSONObject;
|
import cn.hutool.json.JSONUtil;
|
import com.github.pagehelper.PageInfo;
|
import com.wgcloud.config.CommonConfig;
|
import com.wgcloud.entity.DbInfo;
|
import com.wgcloud.entity.DbTable;
|
import com.wgcloud.entity.DbTableCount;
|
import com.wgcloud.service.*;
|
import com.wgcloud.util.*;
|
import com.wgcloud.util.jdbc.RDSConnection;
|
import com.wgcloud.util.staticvar.StaticKeys;
|
import org.apache.commons.lang3.StringUtils;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Controller;
|
import org.springframework.ui.Model;
|
import org.springframework.web.bind.annotation.RequestBody;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.ResponseBody;
|
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
|
|
import javax.annotation.Resource;
|
import javax.servlet.http.HttpServletRequest;
|
import javax.servlet.http.HttpServletResponse;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @version v3.3
|
* @ClassName:DbTableController.java
|
* @author: http://www.wgstart.com
|
* @date: 2021年1月16日
|
* @Description: 数据表管理
|
* @Copyright: 2019-2021 wgcloud. All rights reserved.
|
*/
|
@Controller
|
@RequestMapping("/dbTable")
|
public class DbTableController {
|
|
|
private static final Logger logger = LoggerFactory.getLogger(DbTableController.class);
|
|
@Resource
|
private DbInfoService dbInfoService;
|
@Resource
|
private DbTableService dbTableService;
|
@Resource
|
private DbTableCountService dbTableCountService;
|
@Resource
|
private LogInfoService logInfoService;
|
@Autowired
|
private TokenUtils tokenUtils;
|
@Resource
|
private ExcelExportService excelExportService;
|
@Resource
|
private DashboardService dashboardService;
|
@Autowired
|
CommonConfig commonConfig;
|
|
|
/**
|
* agent查询监控数据表列表
|
*
|
* @param paramBean
|
* @return
|
*/
|
@ResponseBody
|
@RequestMapping(value = "agentList")
|
public String agentList(@RequestBody String paramBean) {
|
JSONObject agentJsonObject = (JSONObject) JSONUtil.parse(paramBean);
|
if (!tokenUtils.checkAgentToken(agentJsonObject)) {
|
logger.error(StaticKeys.TOKEN_ERROR);
|
return ResDataUtils.resetErrorJson(StaticKeys.TOKEN_ERROR);
|
}
|
Map<String, Object> params = new HashMap<String, Object>();
|
try {
|
params.put("active", StaticKeys.ON_STATE);
|
if (null != agentJsonObject.get("dbInfoIds") && !StringUtils.isEmpty(agentJsonObject.get("dbInfoIds").toString())) {
|
params.put("dbInfoIds", agentJsonObject.get("dbInfoIds").toString().split(","));
|
}
|
List<DbTable> dbTableList = dbTableService.selectAllByParams(params);
|
List<DbTable> dbTableListResult = new ArrayList<>();
|
//sql信息加密处理 begin
|
for (DbTable dbTable : dbTableList) {
|
dbTable.setWhereVal(DESUtil.encryption(dbTable.getWhereVal()));
|
String sqlinkey = FormatUtil.haveSqlDanger(dbTable.getWhereVal(), commonConfig.getSqlInKeys());
|
if (!StringUtils.isEmpty(sqlinkey)) {
|
logger.error("统计SQL语句含有sql敏感字符" + sqlinkey);
|
continue;
|
}
|
dbTableListResult.add(dbTable);
|
}
|
//sql信息加密处理 end
|
return ResDataUtils.resetSuccessJson(dbTableListResult);
|
} catch (Exception e) {
|
logger.error("agent获取监控数据表信息错误", e);
|
logInfoService.save("agent获取监控数据表信息错误", e.toString(), StaticKeys.LOG_XTCZ);
|
return ResDataUtils.resetErrorJson(e.toString());
|
}
|
}
|
|
/**
|
* 根据条件查询列表
|
*
|
* @param model
|
* @param request
|
* @return
|
*/
|
@RequestMapping(value = "list")
|
public String dbTableList(DbTable DbTable, Model model, HttpServletRequest request) {
|
Map<String, Object> params = new HashMap<String, Object>();
|
try {
|
|
StringBuffer url = new StringBuffer();
|
String dbInfoId = null;
|
if (!StringUtils.isEmpty(DbTable.getDbInfoId())) {
|
dbInfoId = DbTable.getDbInfoId();
|
params.put("dbInfoId", dbInfoId.trim());
|
url.append("&dbInfoId=").append(dbInfoId);
|
}
|
if (!StringUtils.isEmpty(DbTable.getAccount())) {
|
params.put("account", DbTable.getAccount());
|
url.append("&account=").append(DbTable.getAccount());
|
}
|
if (!StringUtils.isEmpty(DbTable.getRemark())) {
|
params.put("remark", DbTable.getRemark());
|
url.append("&remark=").append(DbTable.getRemark());
|
}
|
if (!StringUtils.isEmpty(DbTable.getOrderBy())) {
|
params.put("orderBy", DbTable.getOrderBy());
|
params.put("orderType", DbTable.getOrderType());
|
url.append("&orderBy=").append(DbTable.getOrderBy());
|
url.append("&orderType=").append(DbTable.getOrderType());
|
}
|
|
//校验是否需要添加过滤用户查询条件
|
HostUtil.addAccountquery(request, params);
|
|
|
PageInfo<DbTable> pageInfo = dbTableService.selectByParams(params, DbTable.getPage(), DbTable.getPageSize());
|
PageUtil.initPageNumber(pageInfo, model);
|
params.clear();
|
|
//设置数据表的一些额外附加值 begin
|
List<DbInfo> dbInfoList = dbInfoService.selectAllByParams(params);
|
//设置数据库logo
|
dbInfoService.dbAddLogo(dbInfoList);
|
for (DbTable dbTable : pageInfo.getList()) {
|
for (DbInfo dbInfo : dbInfoList) {
|
if (dbInfo.getId().equals(dbTable.getDbInfoId())) {
|
//暂时借用取值的列名，来存贮下数据源名称，显示用
|
dbTable.setTableName(dbInfo.getAliasName());
|
//设置数据源logo
|
dbTable.setImage(dbInfo.getImage());
|
|
//设置所属用户账号 begin
|
if (StaticKeys.TRUE_VAL.equals(commonConfig.getUserInfoManage())) {
|
dbTable.setAccount(dbInfo.getAccount());
|
}
|
//设置所属用户账号 begin
|
}
|
}
|
}
|
//设置数据表的一些额外附加值 end
|
|
//设置用户列表
|
HostUtil.addAccountListModel(model);
|
|
model.addAttribute("pageUrl", "/dbTable/list?1=1" + url.toString());
|
model.addAttribute("page", pageInfo);
|
model.addAttribute("dbTable", DbTable);
|
model.addAttribute("dbInfoList", dbInfoList);
|
} catch (Exception e) {
|
logger.error("查询数据表信息错误", e);
|
logInfoService.save("查询数据表信息错误", e.toString(), StaticKeys.LOG_XTCZ);
|
|
}
|
return "mysql/tableList";
|
}
|
|
|
/**
|
* 保存数据源表信息
|
*
|
* @param DbTable
|
* @param model
|
* @param request
|
* @return
|
*/
|
@RequestMapping(value = "save")
|
public String saveDbTable(DbTable DbTable, Model model, HttpServletRequest request) {
|
try {
|
|
//检测有无sql敏感字 begin
|
String sqlinkey = FormatUtil.haveSqlDanger(DbTable.getWhereVal(), commonConfig.getSqlInKeys());
|
if (!StringUtils.isEmpty(sqlinkey)) {
|
model.addAttribute("dbTable", DbTable);
|
List<DbInfo> dbInfoList = dbInfoService.selectAllByParams(new HashMap<>());
|
model.addAttribute("dbInfoList", dbInfoList);
|
model.addAttribute("sqlInKeys", commonConfig.getSqlInKeys());
|
model.addAttribute("msg", "统计SQL语句含有sql敏感字符" + sqlinkey + "，请检查");
|
return "mysql/addTable";
|
}
|
//检测有无sql敏感字 end
|
|
if (StringUtils.isEmpty(DbTable.getId())) {
|
dbTableService.save(DbTable);
|
dbTableService.saveLog(request, StaticKeys.LOG_ADD, DbTable);
|
} else {
|
dbTableService.updateById(DbTable);
|
dbTableService.saveLog(request, StaticKeys.LOG_UPDATE, DbTable);
|
}
|
} catch (Exception e) {
|
logger.error("保存数据表错误", e);
|
logInfoService.save("保存数据表错误", e.toString(), StaticKeys.LOG_XTCZ);
|
}
|
return "redirect:/dbTable/list";
|
}
|
|
|
/**
|
* 查看数据源表信息
|
*
|
* @param DbTable
|
* @param model
|
* @param request
|
* @return
|
*/
|
@RequestMapping(value = "edit")
|
public String editDbTable(DbTable DbTable, Model model, HttpServletRequest request) {
|
try {
|
String id = request.getParameter("id");
|
DbTable dbTableInfo = new DbTable();
|
if (!StringUtils.isEmpty(id)) {
|
dbTableInfo = dbTableService.selectById(id);
|
}
|
|
//校验是否需要添加过滤用户查询条件
|
Map<String, Object> params = new HashMap<String, Object>();
|
HostUtil.addAccountquery(request, params);
|
|
List<DbInfo> dbInfoList = dbInfoService.selectAllByParams(params);
|
|
// redis和mongodb不支持数据表 begin
|
List<DbInfo> dbInfoListResult = new ArrayList<>();
|
for (DbInfo dbInfo : dbInfoList) {
|
if (RDSConnection.KEY_REDIS.equals(dbInfo.getDbType()) || RDSConnection.KEY_MONGODB.equals(dbInfo.getDbType())) {
|
continue;
|
}
|
dbInfoListResult.add(dbInfo);
|
}
|
// redis和mongodb不支持数据表 end
|
|
model.addAttribute("dbInfoList", dbInfoListResult);
|
model.addAttribute("dbTable", dbTableInfo);
|
model.addAttribute("sqlInKeys", commonConfig.getSqlInKeys());
|
} catch (Exception e) {
|
logger.error("查看数据表错误", e);
|
logInfoService.save("查看数据表错误", e.toString(), StaticKeys.LOG_XTCZ);
|
}
|
return "mysql/addTable";
|
}
|
|
/**
|
* 查看数据源表图表统计信息
|
*
|
* @param DbTable
|
* @param model
|
* @param request
|
* @return
|
*/
|
@RequestMapping(value = "viewChart")
|
public String viewChartDbTable(DbTable DbTable, Model model, HttpServletRequest request) {
|
try {
|
String id = request.getParameter("id");
|
String startTime = request.getParameter(StaticKeys.SEARCH_START_TIME);
|
String endTime = request.getParameter(StaticKeys.SEARCH_END_TIME);
|
String am = request.getParameter("am");
|
if (!StringUtils.isEmpty(id)) {
|
DbTable dbTableInfo = dbTableService.selectById(id);
|
Map<String, Object> params = new HashMap<String, Object>();
|
dashboardService.setDateParam(am, startTime, endTime, params, model);
|
model.addAttribute("amList", dashboardService.getAmList());
|
params.put("dbTableId", id);
|
List<DbTableCount> dbTableCounts = dbTableCountService.selectAllByParams(params);
|
model.addAttribute("dbTableCounts", JSONUtil.parseArray(dbTableCounts));
|
// 设置图表的副标题，数据量的最高、平均、最低值
|
dbTableCountService.setSubtitle(model, dbTableCounts);
|
model.addAttribute("dbTable", dbTableInfo);
|
}
|
} catch (Exception e) {
|
logger.error("查看数据表图表统计错误", e);
|
logInfoService.save("查看数据表图表统计错误", e.toString(), StaticKeys.LOG_XTCZ);
|
}
|
return "mysql/tableView";
|
}
|
|
/**
|
* 查看该数据表统计图导出excel
|
*
|
* @param model
|
* @param request
|
* @return
|
*/
|
@RequestMapping(value = "chartExcel")
|
public void chartExcel(Model model, HttpServletRequest request, HttpServletResponse response) {
|
String errorMsg = "数据表统计图导出excel错误";
|
String id = request.getParameter("id");
|
String startTime = request.getParameter(StaticKeys.SEARCH_START_TIME);
|
String endTime = request.getParameter(StaticKeys.SEARCH_END_TIME);
|
String am = request.getParameter("am");
|
try {
|
if (StringUtils.isEmpty(id)) {
|
response.setContentType("text/html;charset=UTF-8");
|
response.getOutputStream().write(StaticKeys.EXCEL_PARAM_ERROR.getBytes());
|
return;
|
}
|
Map<String, Object> params = new HashMap<String, Object>();
|
dashboardService.setDateParam(am, startTime, endTime, params, model);
|
params.put("dbTableId", id);
|
excelExportService.exportDbTableExcel(params, response);
|
} catch (Exception e) {
|
logger.error(errorMsg, e);
|
logInfoService.save(errorMsg, e.toString(), StaticKeys.LOG_XTCZ);
|
}
|
}
|
|
|
/**
|
* 删除数据源表
|
*
|
* @param model
|
* @param request
|
* @param redirectAttributes
|
* @return
|
*/
|
@RequestMapping(value = "del")
|
public String delete(Model model, HttpServletRequest request, RedirectAttributes redirectAttributes) {
|
String errorMsg = "删除数据表信息错误";
|
try {
|
if (!StringUtils.isEmpty(request.getParameter("id"))) {
|
String[] ids = request.getParameter("id").split(",");
|
for (String id : ids) {
|
DbTable dbTable = dbTableService.selectById(id);
|
dbTableService.saveLog(request, StaticKeys.LOG_DEL, dbTable);
|
}
|
dbTableService.deleteById(ids);
|
}
|
} catch (Exception e) {
|
logger.error(errorMsg, e);
|
logInfoService.save(errorMsg, e.toString(), StaticKeys.LOG_XTCZ);
|
}
|
|
return "redirect:/dbTable/list";
|
}
|
|
}
|
