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 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 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 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); } }