java生成sql文件

旧城等待， 2022-11-10 03:53 157阅读 0赞

### java生成sql文件 ###

*   *  场景

## 场景 ##

用于清理数据库数据，生成sql语句脚本，方便DBA执行

@RequestMapping("/cleanQuestion")
        @ResponseBody
        public AjaxResult cleanQuestion(@ModelAttribute("common") Common common, HttpServletRequest request)
        {
            //sign校验
            AjaxResult ajaxResult = new AjaxResult();
            try{
    
                //获取请求参数
                String  detailPlanId = request.getParameter("detailPlanId");
    
                long start = System.currentTimeMillis();
                BufferedWriter out = null;
                Map objMap = new HashMap(8);
                try {
                    //路径
                    String filePath = "D:\\100\\export\\"+DateUtils.getFormatDate(DateUtils.YYYY_MM_DD,new Date())+"\\";
                    File pathFile = new File(filePath);
                    if (!pathFile.exists()) {
                        pathFile.mkdirs();
                    }
                    String relFilePath = filePath + "计划id-" + detailPlanId + ".sql";
                    File file = new File(relFilePath);
                    if (!file.exists()) {
                        file.createNewFile();
                    }
                    out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "UTF-8"));
                    objMap.put("file",relFilePath);
    
                    DetailPlanQuestion detailPlanQuestion = detailPlanQuestionService.selectDetailPlanQuestionByDetailPlanId(Long.parseLong(detailPlanId));
                    StringBuffer detailQuesSql = new StringBuffer();
                    //删除后台题信息
                    detailQuesSql.append("UPDATE qs_study_detail_plan_question SET is_valid=0,update_by='手动',update_time=NOW() WHERE id=");
                    detailQuesSql.append(detailPlanQuestion.getId());
                    detailQuesSql.append(";");
                    detailQuesSql.append("\r\n");
    
                    //获取到当前题所在课表题信息
                    UserDetailPlanQuestion udpq = new UserDetailPlanQuestion();
                    udpq.setDetailPlanQuestionId(detailPlanQuestion.getId());
                    List<UserDetailPlanQuestion> planQuestions = userDetailPlanQuestionService.selectUserDetailPlanQuestionList(udpq);
                    if (CollectionUtils.isNotEmpty(planQuestions)) {
    
                        StringBuffer quesSql = new StringBuffer();
                        StringBuffer planSql = new StringBuffer();
                        StringBuffer planCompSql = new StringBuffer();
                        StringBuffer kpUpSql = new StringBuffer();
                        StringBuffer kpInSql = new StringBuffer();
                        for (UserDetailPlanQuestion planQuestion:planQuestions) {
                            Long userExtendId = planQuestion.getUserExtendId();
                            //生成删除题信息sql UPDATE qs_study_user_detail_plan_question3 SET is_valid=0 WHERE id=56321;
                            quesSql.append("UPDATE qs_study_user_detail_plan_question");
                            quesSql.append(userExtendId%32);
                            quesSql.append(" SET is_valid=0,update_by='手动',update_time=NOW() WHERE id=");
                            quesSql.append(planQuestion.getId());
                            quesSql.append(";");
                            quesSql.append("\r\n");
    
                            if (Constants.EXECUTE_STATUS.NOT_LEARN.getValue().equals(planQuestion.getStatus()) ||
                                    Constants.EXECUTE_STATUS.LEARNING.getValue().equals(planQuestion.getStatus())) {
                                //未学习 学习中 获取课信息
                                List<UserDetailPlanListen> planListens = userDetailPlanListenService.selectUserDetailPlanListenByExtendId(userExtendId, planQuestion.getUserDetailPlanId());
                                if (CollectionUtils.isNotEmpty(planListens)) {
                                    //没有题了但是有课，根据课的状态决定
                                    UserDetailPlanListen planListen = planListens.get(0);
                                    if (Constants.EXECUTE_STATUS.FINISHED.getValue().equals(planListen.getStatus())) {
                                        //当前课已经学完了，需要更新计划为已完成
                                        UserDetailPlan userDetailPlan = userDetailPlanService.selectUserDetailPlanById(userExtendId, planQuestion.getUserDetailPlanId());
                                        //获取计划信息
                                        if (userDetailPlan != null) {
                                            planSql.append("UPDATE qs_study_user_detail_plan");
                                            planSql.append(userExtendId%32);
                                            planSql.append(" SET status=3,update_by='手动',update_time=NOW(),finish_date='");
                                            planSql.append(DateUtils.getFormatDate(null,planListen.getFinishDate()));
                                            planSql.append("' WHERE id=");
                                            planSql.append(planQuestion.getUserDetailPlanId());
                                            planSql.append(";");
                                            planSql.append("\r\n");
    
                                            //插入阶段完成计划
                                            UserDaySchedule daySchedule = userDayScheduleService.selectUserDayScheduleById(userExtendId, userDetailPlan.getUserDayScheduleId());
                                            UserPath userPath = userPathService.selectUserPathById(daySchedule.getUserPathId());
                                            //INSERT INTO qs_study_user_stage_plan_complete (`user_extend_id`, `stage_id`, `subject_id`, `plan_extend_id`, `long_short_path_id`, `plan_id`, `complete_method`, `is_valid`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `paper_id`) VALUES ('33', '11', '1', '13', '17', '1719', '1', '1', '', '2020-08-26 15:13:06', '', NULL, '', NULL);
                                            planCompSql.append("INSERT INTO qs_study_user_stage_plan_complete (`user_extend_id`, `stage_id`, `subject_id`, `plan_extend_id`, `long_short_path_id`, `plan_id`, `complete_method`, `is_valid`, `create_by`, `create_time`) VALUES (");
                                            planCompSql.append(userExtendId);
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getStageId());
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getSubjectId());
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getPlanExtendId());
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getLongShortPathId());
                                            planCompSql.append(",");
                                            planCompSql.append(userDetailPlan.getDetailPlanId());
                                            planCompSql.append(",2,1,'手动',NOW());");
                                            planCompSql.append("\r\n");
    
                                            List<Long> kpIdList = detailPlanKpService.getKpIdListByPlanId(userDetailPlan.getDetailPlanId());
                                            if(CollectionUtils.isNotEmpty(kpIdList)){
                                                for (Long aLong : kpIdList) {
                                                    UserStageKpComplete uskc = new UserStageKpComplete();
                                                    uskc.setUserExtendId(userExtendId);
                                                    uskc.setStageId(userPath.getStageId());
                                                    uskc.setSubjectId(userPath.getSubjectId());
                                                    uskc.setKpId(aLong);
                                                    List<UserStageKpComplete> uskcList = userStageKpCompleteService.selectUserStageKpCompleteList(uskc);
                                                    if(CollectionUtils.isNotEmpty(uskcList)){
                                                        uskc = uskcList.get(0);
                                                        //更新阶段完成知识点
                                                        kpUpSql.append("UPDATE qs_study_user_stage_kp_complete SET complete_method=2,update_by='手动',update_time=NOW() WHERE id=");
                                                        kpUpSql.append(uskc.getId());
                                                        kpUpSql.append(";");
                                                        kpUpSql.append("\r\n");
                                                    }else {
                                                        //插入阶段完成知识点
                                                        kpInSql.append("INSERT INTO qs_study_user_stage_kp_complete (`user_extend_id`, `stage_id`, `subject_id`, `kp_id`, `complete_method`, `is_valid`, `create_by`, `create_time`) VALUES (");
                                                        kpInSql.append(userExtendId);
                                                        kpInSql.append(",");
                                                        kpInSql.append(uskc.getStageId());
                                                        kpInSql.append(",");
                                                        kpInSql.append(uskc.getSubjectId());
                                                        kpInSql.append(",");
                                                        kpInSql.append(aLong);
                                                        kpInSql.append(",2,1,'手动',NOW());");
                                                        kpInSql.append("\r\n");
                                                    }
                                                }
                                            }
                                        }
    
    
    
                                    }else if (Constants.EXECUTE_STATUS.SKIPPED.getValue().equals(planListen.getStatus())) {
    
                                        UserDetailPlan userDetailPlan = userDetailPlanService.selectUserDetailPlanById(userExtendId, planQuestion.getUserDetailPlanId());
                                        if (userDetailPlan != null) {
                                            planSql.append("UPDATE qs_study_user_detail_plan");
                                            planSql.append(userExtendId%32);
                                            planSql.append(" SET status=4,update_by='手动',update_time=NOW(),finish_date='");
                                            planSql.append(DateUtils.getFormatDate(null,planListen.getFinishDate()));
                                            planSql.append("' WHERE id=");
                                            planSql.append(planQuestion.getUserDetailPlanId());
                                            planSql.append(";");
                                            planSql.append("\r\n");
    
                                            //插入阶段完成计划
                                            UserDaySchedule daySchedule = userDayScheduleService.selectUserDayScheduleById(userExtendId, userDetailPlan.getUserDayScheduleId());
                                            UserPath userPath = userPathService.selectUserPathById(daySchedule.getUserPathId());
                                            //INSERT INTO qs_study_user_stage_plan_complete (`user_extend_id`, `stage_id`, `subject_id`, `plan_extend_id`, `long_short_path_id`, `plan_id`, `complete_method`, `is_valid`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `paper_id`) VALUES ('33', '11', '1', '13', '17', '1719', '1', '1', '', '2020-08-26 15:13:06', '', NULL, '', NULL);
                                            planCompSql.append("INSERT INTO qs_study_user_stage_plan_complete (`user_extend_id`, `stage_id`, `subject_id`, `plan_extend_id`, `long_short_path_id`, `plan_id`, `complete_method`, `is_valid`, `create_by`, `create_time`) VALUES (");
                                            planCompSql.append(userExtendId);
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getStageId());
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getSubjectId());
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getPlanExtendId());
                                            planCompSql.append(",");
                                            planCompSql.append(userPath.getLongShortPathId());
                                            planCompSql.append(",");
                                            planCompSql.append(userDetailPlan.getDetailPlanId());
                                            planCompSql.append(",2,1,'手动',NOW());");
                                            planCompSql.append("\r\n");
    
                                            List<Long> kpIdList = detailPlanKpService.getKpIdListByPlanId(userDetailPlan.getDetailPlanId());
                                            if(CollectionUtils.isNotEmpty(kpIdList)){
                                                for (Long aLong : kpIdList) {
                                                    UserStageKpComplete uskc = new UserStageKpComplete();
                                                    uskc.setUserExtendId(userExtendId);
                                                    uskc.setStageId(userPath.getStageId());
                                                    uskc.setSubjectId(userPath.getSubjectId());
                                                    uskc.setKpId(aLong);
                                                    List<UserStageKpComplete> uskcList = userStageKpCompleteService.selectUserStageKpCompleteList(uskc);
                                                    if(CollectionUtils.isNotEmpty(uskcList)){
                                                        uskc = uskcList.get(0);
                                                        //更新阶段完成知识点
                                                        kpUpSql.append("UPDATE qs_study_user_stage_kp_complete SET complete_method=2,update_by='手动',update_time=NOW() WHERE id=");
                                                        kpUpSql.append(uskc.getId());
                                                        kpUpSql.append(";");
                                                        kpUpSql.append("\r\n");
                                                    }else {
                                                        //插入阶段完成知识点
                                                        kpInSql.append("INSERT INTO qs_study_user_stage_kp_complete (`user_extend_id`, `stage_id`, `subject_id`, `kp_id`, `complete_method`, `is_valid`, `create_by`, `create_time`) VALUES (");
                                                        kpInSql.append(userExtendId);
                                                        kpInSql.append(",");
                                                        kpInSql.append(uskc.getStageId());
                                                        kpInSql.append(",");
                                                        kpInSql.append(uskc.getSubjectId());
                                                        kpInSql.append(",");
                                                        kpInSql.append(aLong);
                                                        kpInSql.append(",2,1,'手动',NOW());");
                                                        kpInSql.append("\r\n");
                                                    }
                                                }
                                            }
                                        }
    
                                    }else {
    
    
                                    }
    
                                }else {
                                    //没有题了也没有课则删除当前计划
                                    planSql.append("UPDATE qs_study_user_detail_plan");
                                    planSql.append(userExtendId%32);
                                    planSql.append(" SET is_valid=0,update_by='手动',update_time=NOW() WHERE id=");
                                    planSql.append(planQuestion.getUserDetailPlanId());
                                    planSql.append(";");
                                    planSql.append("\r\n");
                                }
    
    
                            }
                        }
                        out.write(detailQuesSql.toString());
                        out.newLine();
                        out.write(quesSql.toString());
                        out.newLine();
                        out.write(planSql.toString());
                        out.newLine();
                        out.write(planCompSql.toString());
                        out.newLine();
                        out.write(kpUpSql.toString());
                        out.newLine();
                        out.write(kpInSql.toString());
    
                    }
                }catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    if (out != null) {
                        try {
                            out.flush();
                            out.close();
                        }catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
                long end = System.currentTimeMillis();
    
                objMap.put("time","耗时："+(end-start)/1000+"s");
    
                ajaxResult.put("code", Constants.CODE.SUCCESS.getValue());
                ajaxResult.put("msg",Constants.CODE.SUCCESS.getDescription());
                ajaxResult.put("obj",objMap);
                return ajaxResult;
            }catch (Exception e){
                e.printStackTrace();
                ajaxResult.put("code", Constants.CODE.SYSTEM_ERROE.getValue());
                ajaxResult.put("msg", Constants.CODE.SYSTEM_ERROE.getDescription());
                ajaxResult.put("obj", "");
                return ajaxResult;
            }
        }

注：此处仅做日常记录方便后续用到时参考，不可转载