package com.highdatas.mdm.controller;
|
|
import com.alibaba.fastjson.JSONObject;
|
import com.highdatas.mdm.entity.*;
|
import com.highdatas.mdm.mapper.SysViewMapper;
|
import com.highdatas.mdm.mapper.TableInfoMapper;
|
import com.highdatas.mdm.pojo.CodeMsg;
|
import com.highdatas.mdm.pojo.Result;
|
import com.highdatas.mdm.pojo.Segment;
|
import com.highdatas.mdm.pojo.kettle.UnBigDataDataSourceInfo;
|
import com.highdatas.mdm.service.*;
|
import com.highdatas.mdm.util.*;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.ibatis.session.ExecutorType;
|
import org.apache.ibatis.session.SqlSession;
|
import org.mybatis.spring.SqlSessionTemplate;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.web.bind.annotation.*;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.http.HttpServletRequest;
|
import java.util.ArrayList;
|
import java.util.Date;
|
import java.util.List;
|
import java.util.concurrent.atomic.AtomicInteger;
|
|
/**
|
* @author kimi
|
* @description 简单上传文件接口
|
* @date 2019-12-18 10:22
|
*/
|
|
@RestController
|
@RequestMapping("/file")
|
public class FileController {
|
@Autowired
|
ISysAssembleDbService dbService;
|
@Autowired
|
ISysAssembleService assembleService;
|
@Autowired
|
ISysAssembleParamsService paramsService;
|
@Autowired
|
RedisClient redisClient;
|
@Autowired
|
ISysViewService viewService;
|
@Autowired
|
IMenuMappingService menuMappingService;
|
@Autowired
|
TableInfoMapper tableInfoMapper;
|
@Autowired
|
private SqlSessionTemplate sqlSessionTemplate;
|
@Autowired
|
UnBigDataDataSourceInfo unBigDataDataSourceInfo;
|
|
/**
|
*
|
* @description: 汇集 上传由源表组合的sql
|
* @param file 文件
|
* @param dbId 数据源
|
* @return: 是否添加成功
|
*
|
*/
|
@RequestMapping(value = "/loadSqlTable", method = RequestMethod.POST)
|
@ResponseBody
|
public Result loadSqlTable(@RequestParam("file")MultipartFile file,@RequestParam String dbId, HttpServletRequest request) {
|
try {
|
byte[] bytes = file.getBytes();
|
|
String sql = new String (bytes);
|
//去除空格防治sql因为空格报错
|
sql = DbUtils.replaceEscape(sql);
|
String[] split = sql.split(Constant.SEMICOLON);
|
//校验sql语句条数
|
if (split.length > 1) {
|
return Result.error(new CodeMsg(6001 , "sql脚本上传错误,仅能支持一条语句"));
|
}
|
String sqlId = DbUtils.getUUID();
|
//添加进缓存内，后续使用
|
boolean b = redisClient.putRedisVal(sqlId, sql);
|
if (!b){
|
return Result.error(new CodeMsg(6002, "sql保存失败"));
|
}
|
// 暂不校验是不是select语句了 后续需要再校验
|
//通过sql查询字段
|
List<String> fieldsBySql = dbService.getFieldsBySql(dbId, sql);
|
JSONObject object = new JSONObject();
|
object.fluentPut("fields", fieldsBySql);
|
object.fluentPut("sqlId",sqlId);
|
|
return Result.success(object);
|
}catch (Exception e) {
|
e.printStackTrace();
|
return Result.error(new CodeMsg(6002, e.getMessage()));
|
}
|
|
}
|
/**
|
*
|
* @description: 汇集 上传清洗sql
|
* @param file 清洗文件
|
* @param id 汇集id
|
* @return: 是否添加成功
|
*
|
*/
|
@RequestMapping(value = "/loadPurgeSql", method = RequestMethod.POST)
|
@ResponseBody
|
public Result loadPurgeSql(@RequestParam("file")MultipartFile file,@RequestParam String id, HttpServletRequest request) {
|
|
//通过id获取汇集任务信息
|
SysAssemble assemble = assembleService.selectById(id);
|
if (assemble == null){
|
return Result.error(CodeMsg.ERROR_PARAMS_NOT_MATHED);
|
}
|
try {
|
byte[] bytes = file.getBytes();
|
|
String sql = new String (bytes);
|
//去除空格防治sql因为空格报错
|
sql = DbUtils.replaceEscape(sql);
|
// 暂不校验是不是select语句了 后续需要再校验
|
//TODO 校验sql 暂不进行
|
assemble.setPurgeSql(sql);
|
boolean update = assemble.updateById();
|
if (update) {
|
return Result.success(assemble);
|
}else {
|
return Result.error(CodeMsg.UPDATE_ERROR);
|
}
|
|
}catch (Exception e) {
|
e.printStackTrace();
|
return Result.error(new CodeMsg(6002, e.getMessage()));
|
}
|
|
}
|
/**
|
*
|
* @description: 汇集 上传参数更新sql
|
* @param file 清洗文件
|
* @param id 汇集id
|
* @return: 是否添加成功
|
*
|
*/
|
@RequestMapping(value = "/loadParamsUpdateSql", method = RequestMethod.POST)
|
@ResponseBody
|
public Result loadParamsUpdateSql(@RequestParam("file")MultipartFile file,@RequestParam String id, HttpServletRequest request) {
|
// 暂不校验是不是select语句了 后续需要再校验
|
SysAssembleParams sysAssembleParams = paramsService.selectById(id);
|
if (sysAssembleParams == null){
|
return Result.error(CodeMsg.ERROR_PARAMS_NOT_MATHED);
|
}
|
try {
|
byte[] bytes = file.getBytes();
|
|
String sql = new String (bytes);
|
//去除空格防治sql因为空格报错
|
sql = DbUtils.replaceEscape(sql);
|
//TODO 校验sql 暂不进行
|
sysAssembleParams.setUpdateSql(sql).setUpdateTime(new Date()).updateById();
|
|
return Result.success(sysAssembleParams);
|
}catch (Exception e) {
|
e.printStackTrace();
|
return Result.error(new CodeMsg(6002, e.getMessage()));
|
}
|
|
}
|
|
/**
|
*
|
* @description: 视图，值对值转换
|
* @param file 清洗文件
|
* @param id 视图id
|
* @param menuId 视图对应数据来源的主题id
|
* @return: 是否添加成功
|
*
|
*/
|
@RequestMapping(value = "/loadMappingFile", method = RequestMethod.POST)
|
@ResponseBody
|
public Result loadMappingFile(@RequestParam("file")MultipartFile file,@RequestParam String id, @RequestParam String menuId, @RequestParam String field, HttpServletRequest request) {
|
//获取视图信息
|
SysView sysView = viewService.selectById(id);
|
if (sysView == null){
|
return Result.error(CodeMsg.ERROR_PARAMS_NOT_MATHED);
|
}
|
//获取视图对应的mapping表
|
String mappingTable = sysView.getMappingTable();
|
if (StringUtils.isEmpty(mappingTable)){
|
return Result.error(CodeMsg.ERROR_PARAMS_NOT_MATHED);
|
}
|
//检验是否存在
|
boolean exists = unBigDataDataSourceInfo.checkTableExists(mappingTable);
|
if (!exists) {
|
//不存在则创建mapping表
|
ArrayList<String> fields = new ArrayList<>();
|
fields.add(Constant.ID);
|
fields.add(Constant.Code);
|
fields.add("pre");
|
fields.add("fix");
|
boolean created = unBigDataDataSourceInfo.createTable(mappingTable, fields);
|
if (!created) {
|
return Result.error(CodeMsg.CREATE_ERROR);
|
}
|
}
|
|
SqlSession session = null;
|
try {
|
String name = file.getOriginalFilename();
|
//通过文件获取转换mapping列表
|
List<ViewMappingItem> viewMappingByExcel = ExcelUtil.getViewMappingByExcel(file.getInputStream(), name);
|
if (viewMappingByExcel.isEmpty()) {
|
return Result.error(CodeMsg.EMPTY_ERROR);
|
}
|
//获取视图对应主题的版本信息
|
Maintain maintainByMenu = viewService.getMaintainByMenu(sysView, menuId);
|
if (maintainByMenu == null) {
|
return Result.error(CodeMsg.ERROR_PARAMS_NOT_MATHED);
|
}
|
//获取转换后的视图实际使用的字段
|
String changeFieldName = viewService.changeFieldName(maintainByMenu.getTableName(), field);
|
Segment segment = new Segment(Constant.Code, changeFieldName);
|
//By 字段删除原来的mapping
|
tableInfoMapper.delete(mappingTable, segment.toString());
|
session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//关闭session的自动提交
|
|
SysViewMapper mapper = session.getMapper(SysViewMapper.class);
|
AtomicInteger idx = new AtomicInteger(0);
|
ContentBuilder builder = new ContentBuilder(Constant.COMMA);
|
//批量执行插入语句
|
for (ViewMappingItem viewMappingItem : viewMappingByExcel) {
|
builder.clear();
|
builder.append(DbUtils.quotedStr(changeFieldName));
|
builder.append(DbUtils.quotedStr(viewMappingItem.getPre())).append(DbUtils.quotedStr(viewMappingItem.getFix()));
|
mapper.insertViewMapping(mappingTable, builder.toString());
|
int i = idx.get();
|
if (i % 1000 == 0 || i == viewMappingByExcel.size()-1) {
|
//手动每1000个一提交，提交后无法回滚
|
session.commit();
|
session.clearCache();//注意，如果没有这个动作，可能会导致内存崩溃。
|
}
|
idx.getAndIncrement();
|
}
|
|
}catch (Exception e) {
|
e.printStackTrace();
|
if (session != null) {
|
session.rollback();
|
}
|
|
return Result.error(new CodeMsg(6002, e.getMessage()));
|
}finally {
|
if (session != null) {
|
session.close();
|
}
|
}
|
return Result.success(null);
|
}
|
}
|
