1. 云图网首页
  2. 技术专区
  3. Java

java生成sql文件

• Java


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





    

  • 1
    • 

  • 2
    • 

  • 3
    • 

  • 4
    • 

  • 5
    • 

  • 6
    • 

  • 7
    • 

  • 8
    • 

  • 9
    • 

  • 10
    • 

  • 11
    • 

  • 12
    • 

  • 13
    • 

  • 14
    • 

  • 15
    • 

  • 16
    • 

  • 17
    • 

  • 18
    • 

  • 19
    • 

  • 20
    • 

  • 21
    • 

  • 22
    • 

  • 23
    • 

  • 24
    • 

  • 25
    • 

  • 26
    • 

  • 27
    • 

  • 28
    • 

  • 29
    • 

  • 30
    • 

  • 31
    • 

  • 32
    • 

  • 33
    • 

  • 34
    • 

  • 35
    • 

  • 36
    • 

  • 37
    • 

  • 38
    • 

  • 39
    • 

  • 40
    • 

  • 41
    • 

  • 42
    • 

  • 43
    • 

  • 44
    • 

  • 45
    • 

  • 46
    • 

  • 47
    • 

  • 48
    • 

  • 49
    • 

  • 50
    • 

  • 51
    • 

  • 52
    • 

  • 53
    • 

  • 54
    • 

  • 55
    • 

  • 56
    • 

  • 57
    • 

  • 58
    • 

  • 59
    • 

  • 60
    • 

  • 61
    • 

  • 62
    • 

  • 63
    • 

  • 64
    • 

  • 65
    • 

  • 66
    • 

  • 67
    • 

  • 68
    • 

  • 69
    • 

  • 70
    • 

  • 71
    • 

  • 72
    • 

  • 73
    • 

  • 74
    • 

  • 75
    • 

  • 76
    • 

  • 77
    • 

  • 78
    • 

  • 79
    • 

  • 80
    • 

  • 81
    • 

  • 82
    • 

  • 83
    • 

  • 84
    • 

  • 85
    • 

  • 86
    • 

  • 87
    • 

  • 88
    • 

  • 89
    • 

  • 90
    • 

  • 91
    • 

  • 92
    • 

  • 93
    • 

  • 94
    • 

  • 95
    • 

  • 96
    • 

  • 97
    • 

  • 98
    • 

  • 99
    • 

  • 100
    • 

  • 101
    • 

  • 102
    • 

  • 103
    • 

  • 104
    • 

  • 105
    • 

  • 106
    • 

  • 107
    • 

  • 108
    • 

  • 109
    • 

  • 110
    • 

  • 111
    • 

  • 112
    • 

  • 113
    • 

  • 114
    • 

  • 115
    • 

  • 116
    • 

  • 117
    • 

  • 118
    • 

  • 119
    • 

  • 120
    • 

  • 121
    • 

  • 122
    • 

  • 123
    • 

  • 124
    • 

  • 125
    • 

  • 126
    • 

  • 127
    • 

  • 128
    • 

  • 129
    • 

  • 130
    • 

  • 131
    • 

  • 132
    • 

  • 133
    • 

  • 134
    • 

  • 135
    • 

  • 136
    • 

  • 137
    • 

  • 138
    • 

  • 139
    • 

  • 140
    • 

  • 141
    • 

  • 142
    • 

  • 143
    • 

  • 144
    • 

  • 145
    • 

  • 146
    • 

  • 147
    • 

  • 148
    • 

  • 149
    • 

  • 150
    • 

  • 151
    • 

  • 152
    • 

  • 153
    • 

  • 154
    • 

  • 155
    • 

  • 156
    • 

  • 157
    • 

  • 158
    • 

  • 159
    • 

  • 160
    • 

  • 161
    • 

  • 162
    • 

  • 163
    • 

  • 164
    • 

  • 165
    • 

  • 166
    • 

  • 167
    • 

  • 168
    • 

  • 169
    • 

  • 170
    • 

  • 171
    • 

  • 172
    • 

  • 173
    • 

  • 174
    • 

  • 175
    • 

  • 176
    • 

  • 177
    • 

  • 178
    • 

  • 179
    • 

  • 180
    • 

  • 181
    • 

  • 182
    • 

  • 183
    • 

  • 184
    • 

  • 185
    • 

  • 186
    • 

  • 187
    • 

  • 188
    • 

  • 189
    • 

  • 190
    • 

  • 191
    • 

  • 192
    • 

  • 193
    • 

  • 194
    • 

  • 195
    • 

  • 196
    • 

  • 197
    • 

  • 198
    • 

  • 199
    • 

  • 200
    • 

  • 201
    • 

  • 202
    • 

  • 203
    • 

  • 204
    • 

  • 205
    • 

  • 206
    • 

  • 207
    • 

  • 208
    • 

  • 209
    • 

  • 210
    • 

  • 211
    • 

  • 212
    • 

  • 213
    • 

  • 214
    • 

  • 215
    • 

  • 216
    • 

  • 217
    • 

  • 218
    • 

  • 219
    • 

  • 220
    • 

  • 221
    • 

  • 222
    • 

  • 223
    • 

  • 224
    • 

  • 225
    • 

  • 226
    • 

  • 227
    • 

  • 228
    • 

  • 229
    • 

  • 230
    • 

  • 231
    • 

  • 232
    • 

  • 233
    • 

  • 234
    • 

  • 235
    • 

  • 236
    • 

  • 237
    • 

  • 238
    • 

  • 239
    • 

  • 240
    • 

  • 241
    • 

  • 242
    • 

  • 243
    • 

  • 244
    • 

  • 245
    • 

  • 246
    • 

  • 247
    • 

  • 248
    • 

  • 249
    • 

  • 250
    • 

  • 251
    • 

  • 252
    • 

  • 253
    • 

  • 254
    • 

  • 255
    • 

  • 256
    • 

  • 257
    • 



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


https://blog.csdn.net/csdn565973850/article/details/114872647


原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/281964.html
赞 (0)
0
上一篇 2022年8月24日
下一篇 2022年8月24日

相关推荐

发表回复

登录后才能评论