java生成sql文件
2022/8/25 2:23:31
本文主要是介绍java生成sql文件,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
java生成sql文件
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; } } 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257
注:此处仅做日常记录方便后续用到时参考,不可转载
https://blog.csdn.net/csdn565973850/article/details/114872647这篇关于java生成sql文件的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-07面向对象编程基础:初学者指南
- 2024-09-07数据结构入门:了解基础概念与简单应用
- 2024-09-07面向对象编程入门:掌握基本概念与实践
- 2024-09-07数据结构入门:从基础概念到基本操作
- 2024-09-07从零开始学数据库:入门级指南与实操技巧
- 2024-09-07综合设备故障分析与预防:实例代码与优化策略
- 2024-09-07数据库入门:轻松掌握数据管理基础
- 2024-09-07面向对象入门:快速掌握对象编程基础
- 2024-09-07数据库教程:入门与实践指南
- 2024-09-07面向对象开发教程 - 从零基础到实践应用的进阶之路