package com.xtuone.friday.server.modules.curriculum.fetchData.insert;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.codec.digest.DigestUtils;
import com.mysql.jdbc.Statement;
import com.xtuone.friday.server.modules.curriculum.fetchData.bo.Emptyclassmessage;
import com.xtuone.friday.server.modules.curriculum.fetchData.bo.Emptyclassroom;
import com.xtuone.friday.server.modules.curriculum.fetchData.crawl.free.CompareSection4RoomMsg;
import com.xtuone.friday.server.modules.curriculum.fetchData.utils.ConnectionUtil;
public class InsertNew {
public static void RoomListInsertTemp(Map<String, Integer> roomMd5Map, List<Emptyclassroom> emptyclassroomList, Emptyclassmessage emptyclassmessage) {
RoomListInsert(roomMd5Map, emptyclassroomList, emptyclassmessage, "emptyclassmessagetemp");
}
public static void RoomListInsertNormal(Map<String, Integer> roomMd5Map, List<Emptyclassroom> emptyclassroomList, Emptyclassmessage emptyclassmessage) {
RoomListInsert(roomMd5Map, emptyclassroomList, emptyclassmessage, "emptyclassmessage_fromschool");
}
public static void MessageListInsertTemp(Map<String, Integer> roomMd5Map, Emptyclassroom emptyclassroom, List<Emptyclassmessage> emptyclassmessageList) {
MessageListInsert(roomMd5Map, emptyclassroom, emptyclassmessageList, "emptyclassmessagetemp");
}
public static void MessageListInsertNormal(Map<String, Integer> roomMd5Map, Emptyclassroom emptyclassroom, List<Emptyclassmessage> emptyclassmessageList) {
MessageListInsert(roomMd5Map, emptyclassroom, emptyclassmessageList, "emptyclassmessage_fromschool");
}
/**
* 教室和空闲信息（n:1）写入方法【注意各个方法的加密方式要统一】
*
* @param roomMd5Map
* @param emptyclassroomList
* @param emptyclassmessage
* @param emptyClassMsgTable
*/
public static void RoomListInsert(Map<String, Integer> roomMd5Map, List<Emptyclassroom> emptyclassroomList, Emptyclassmessage emptyclassmessage,
String emptyClassMsgTable) {
// if()
Connection conn = null;
conn = ConnectionUtil.getConnection();
int schoolId = emptyclassmessage.getSchoolId();
String ClASS_SEQUENCE_SEPARATOR = ">@<";
PreparedStatement pstm = null;
PreparedStatement pstmRoom = null;
PreparedStatement pstmMess = null;
try {
// 生成这间学校的所有教室信息的map
if (roomMd5Map.isEmpty()) { // 为空说明第一次进行操作
ResultSet rs = null;
String md5Sql = "SELECT id,sequence FROM emptyclassroom_fromschool WHERE schoolId = ?";
pstm = conn.prepareStatement(md5Sql);// 判断数据库中是否存在当前教室信息
pstm.setInt(1, schoolId);
rs = pstm.executeQuery();
// 把数据库当前学校的教室信息查询出来存入到md5Map中
while (rs.next()) { // 已经存在
int classId = rs.getInt(1);
String sequence = rs.getString(2);
roomMd5Map.put(sequence, classId);
}
rs.close();
}
// 保存教室空闲详情map
String dateStart = emptyclassmessage.getDateStart();
dateStart = dateStart == null ? "" : dateStart;
int day = emptyclassmessage.getDay();
Map<String, Integer> messMd5Map = new HashMap<String, Integer>();
ResultSet rs = null;
pstm = null;
String md5Sql = "SELECT sequence FROM " + emptyClassMsgTable + " WHERE schoolId = ? ";
if (!dateStart.equals("")) {
md5Sql = md5Sql + "AND dateStart = ?";
}
if (day != 0) {
md5Sql = md5Sql + " AND day = ?";
}
pstm = conn.prepareStatement(md5Sql);
pstm.setInt(1, schoolId);
if (!dateStart.equals("")) {
pstm.setString(2, dateStart);
}
if (day != 0) {
if (dateStart.equals("")) {
pstm.setInt(2, day);
} else {
pstm.setInt(3, day);
}
}
// System.out.println("pstm:"+pstm);
rs = pstm.executeQuery();
while (rs.next()) { // 已经存在
String sequence = rs.getString(1);
messMd5Map.put(sequence, 1);
}
rs.close();
// 新教室写入
String newClassRoomsql = "INSERT INTO emptyclassroom_fromschool(schoolId,campus,building,classroom,classroomType,emptyPlace,message,sequence) "
+ "VALUES (?,?,?,?,?,?,?,?)";
// String[] columnNames= {"id"} ;
pstmRoom = conn.prepareStatement(newClassRoomsql, Statement.RETURN_GENERATED_KEYS);
conn.setAutoCommit(false);
List<Integer> emptyclassroomIds = new ArrayList<Integer>();
List<String> needAddSequence = new ArrayList<String>();
// 循环教室列表
for (Emptyclassroom emptyclassroom : emptyclassroomList) {
int emptyclassroomId = 0;
String classroomContent = schoolId + ClASS_SEQUENCE_SEPARATOR;
// 写入班级---不可为空
String classroom = emptyclassroom.getClassroom();
classroom = classroom == null ? "" : classroom;
classroomContent += classroom + ClASS_SEQUENCE_SEPARATOR;
// ---可以为空
String campus = emptyclassroom.getCampus();
campus = campus == null ? "" : campus;
classroomContent += campus + ClASS_SEQUENCE_SEPARATOR;
String building = emptyclassroom.getBuilding();
building = building == null ? "" : building;
classroomContent += building + ClASS_SEQUENCE_SEPARATOR;
String classroomType = emptyclassroom.getClassroomType();
classroomType = classroomType == null ? "" : classroomType;
classroomContent += classroomType + ClASS_SEQUENCE_SEPARATOR;
int emptyPlace = emptyclassroom.getEmptyPlace();
classroomContent += emptyPlace + ClASS_SEQUENCE_SEPARATOR;
String message = emptyclassroom.getMessage();
message = message == null ? "" : message;
classroomContent += message + ClASS_SEQUENCE_SEPARATOR;
// 需要写入的教室信息，md5提取信息
String classroomSequence = DigestUtils.md5Hex(classroomContent);
// 判断要写入的教室是否存在
if (roomMd5Map.containsKey(classroomSequence)) {
emptyclassroomId = roomMd5Map.get(classroomSequence);
emptyclassroomIds.add(emptyclassroomId);
} else {
pstmRoom.setInt(1, schoolId);
pstmRoom.setString(2, campus);
pstmRoom.setString(3, building);
pstmRoom.setString(4, classroom);
pstmRoom.setString(5, classroomType);
pstmRoom.setInt(6, emptyPlace);
pstmRoom.setString(7, message);
pstmRoom.setString(8, classroomSequence);
pstmRoom.addBatch();
needAddSequence.add(classroomSequence);
}
}
// 获得批量写入后返回的key值列表
pstmRoom.executeBatch();
conn.commit();
ResultSet rsR = pstmRoom.getGeneratedKeys();
int i = 0;
while (rsR.next()) {
int id = rsR.getInt(1);
String seq = needAddSequence.get(i);
// System.out.println(id+"---------------------------------------------------------------"+
// seq);
emptyclassroomIds.add(id);
roomMd5Map.put(seq, id);// 添加新的到map中
i++;
}
// System.out.println("emptyclassroomIds:"+emptyclassroomIds);
rsR.close();
if (pstmRoom != null)
pstmRoom.close();
// 教室列表空闲详情相同部分初始化
// String insertMessSql = "INSERT INTO "+ emptyClassMsgTable
// +" (classid,schoolId,period,dateStart,dateEnd,day,sectionStart,sectionEnd,beginTime,endTime,smartPeriod,beginYear,term,sequence) "
// + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
String insertMessSql = "INSERT INTO " + emptyClassMsgTable
+ "(classid,schoolId,beginYear,term,period,smartPeriod,dateStart,dateEnd,day,sectionStart,sectionEnd,beginTime,endTime,sequence) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
pstmMess = conn.prepareStatement(insertMessSql);
// String classMessContent = emptyclassroomId +
// ClASS_SEQUENCE_SEPARATOR;
String classMessContent = ClASS_SEQUENCE_SEPARATOR;
String smartPeriod = emptyclassmessage.getSmartPeriod();
smartPeriod = smartPeriod == null ? "" : smartPeriod;
classMessContent += smartPeriod + ClASS_SEQUENCE_SEPARATOR;
String period = emptyclassmessage.getPeriod();
period = period == null ? "" : period;
classMessContent += period + ClASS_SEQUENCE_SEPARATOR;
classMessContent += dateStart + ClASS_SEQUENCE_SEPARATOR;
String dateEnd = emptyclassmessage.getDateEnd();
dateEnd = dateEnd == null ? "" : dateEnd;
classMessContent += dateEnd + ClASS_SEQUENCE_SEPARATOR;
int beginYear = emptyclassmessage.getBeginYear();
classMessContent += beginYear + ClASS_SEQUENCE_SEPARATOR;
int term = emptyclassmessage.getTerm();
classMessContent += term + ClASS_SEQUENCE_SEPARATOR;
classMessContent += day + ClASS_SEQUENCE_SEPARATOR;
int sectionStart = emptyclassmessage.getSectionStart();
classMessContent += sectionStart + ClASS_SEQUENCE_SEPARATOR;
int sectionEnd = emptyclassmessage.getSectionEnd();
classMessContent += sectionEnd + ClASS_SEQUENCE_SEPARATOR;
String beginTime = emptyclassmessage.getBeginTime();
beginTime = beginTime == null ? "" : beginTime;
classMessContent += beginTime + ClASS_SEQUENCE_SEPARATOR;
String endTime = emptyclassmessage.getEndTime();
endTime = endTime == null ? "" : endTime;
classMessContent += endTime + ClASS_SEQUENCE_SEPARATOR;
// classid,schoolId,beginYear,term,period,smartPeriod,dateStart,dateEnd,day,sectionStart,sectionEnd,beginTime,endTime,sequence
// pstmMess.setInt(1, emptyclassroomId);
pstmMess.setInt(2, schoolId);
pstmMess.setInt(3, beginYear);
pstmMess.setInt(4, term);
pstmMess.setString(5, period);
pstmMess.setString(6, smartPeriod);
pstmMess.setString(7, dateStart.equals("") ? null : dateStart);
pstmMess.setString(8, dateEnd.equals("") ? null : dateEnd);
pstmMess.setInt(9, day);
pstmMess.setInt(10, sectionStart);
pstmMess.setInt(11, sectionEnd);
pstmMess.setString(12, beginTime);
pstmMess.setString(13, endTime);
for (int emptyclassroomId : emptyclassroomIds) {
String classMessContent1 = emptyclassroomId + classMessContent;
// 需要写入的教室详细空闲信息，md5提取信息
String classmessSequence = DigestUtils.md5Hex(classMessContent1);
if (!messMd5Map.containsKey(classmessSequence)) {
pstmMess.setInt(1, emptyclassroomId);
pstmMess.setString(14, classmessSequence);
messMd5Map.put(classmessSequence, 1);
pstmMess.addBatch();
}
}
Date S = new Date();
pstmMess.executeBatch();
conn.commit();
if (pstmMess != null)
pstmMess.close();
Date E = new Date();
System.out.print("(" + (E.getTime() - S.getTime()) + ")");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
}
/**
* 教室和空闲信息（1:n）写入方法【注意各个方法的加密方式要统一】
*
* @param roomMd5Map
* @param emptyclassroom
* @param emptyclassmessageList
* @param emptyClassMsgTable
*/
public static void MessageListInsert(Map<String, Integer> roomMd5Map, Emptyclassroom emptyclassroom, List<Emptyclassmessage> emptyclassmessageList,
String emptyClassMsgTable) {
// 查询有没有这间空教室的信息
Connection conn = null;
conn = ConnectionUtil.getConnection();
int schoolId = emptyclassroom.getSchoolId();
int emptyclassroomId = 0;
// 当前传入教室的md5值（学校id+）
String ClASSROOM_SEQUENCE_SEPARATOR = ">@<";
String classroomContent = schoolId + ClASSROOM_SEQUENCE_SEPARATOR;
// 写入班级---不可为空
String classroom = emptyclassroom.getClassroom();
classroom = classroom == null ? "" : classroom;
classroomContent += classroom + ClASSROOM_SEQUENCE_SEPARATOR;
// ---可以为空
String campus = emptyclassroom.getCampus();
campus = campus == null ? "" : campus;
classroomContent += campus + ClASSROOM_SEQUENCE_SEPARATOR;
String building = emptyclassroom.getBuilding();
building = building == null ? "" : building;
classroomContent += building + ClASSROOM_SEQUENCE_SEPARATOR;
String classroomType = emptyclassroom.getClassroomType();
classroomType = classroomType == null ? "" : classroomType;
classroomContent += classroomType + ClASSROOM_SEQUENCE_SEPARATOR;
int emptyPlace = emptyclassroom.getEmptyPlace();
classroomContent += emptyPlace + ClASSROOM_SEQUENCE_SEPARATOR;
String message = emptyclassroom.getMessage();
message = message == null ? "" : message;
classroomContent += message + ClASSROOM_SEQUENCE_SEPARATOR;
// 需要写入的教室信息，md5提取信息
String classroomSequence = DigestUtils.md5Hex(classroomContent);
PreparedStatement pstm = null;
try {
if (roomMd5Map.isEmpty()) { // 为空说明第一次进行操作
ResultSet rs = null;
String md5Sql = "SELECT id,sequence FROM emptyclassroom_fromschool WHERE schoolId = ?";
pstm = conn.prepareStatement(md5Sql);// 判断数据库中是否存在当前教室信息
pstm.setInt(1, schoolId);
rs = pstm.executeQuery();
// 把数据库当前学校的教室信息查询出来存入到md5Map中
while (rs.next()) { // 已经存在
int classId = rs.getInt(1);
String sequence = rs.getString(2);
roomMd5Map.put(sequence, classId);
}
rs.close();
}
// 判断要写入的教室是否存在
if (roomMd5Map.containsKey(classroomSequence)) {
emptyclassroomId = roomMd5Map.get(classroomSequence);
} else {
ResultSet rs = null;
// 新教室写入
String newClassRoomsql = "INSERT INTO emptyclassroom_fromschool(schoolId,classroom,campus,building,classroomType,emptyPlace,message,sequence) "
+ "VALUES (?,?,?,?,?,?,?,?)";
pstm = conn.prepareStatement(newClassRoomsql, Statement.RETURN_GENERATED_KEYS);
pstm.setInt(1, schoolId);
pstm.setString(2, classroom);
pstm.setString(3, campus);
pstm.setString(4, building);
pstm.setString(5, classroomType);
pstm.setInt(6, emptyPlace);
pstm.setString(7, message);
pstm.setString(8, classroomSequence);
// System.out.println(pstm);
pstm.execute();
rs = pstm.getGeneratedKeys();
if (rs != null && rs.next()) {
emptyclassroomId = rs.getInt(1);
}
rs.close();
roomMd5Map.put(classroomSequence, emptyclassroomId);// 添加到map中
}
// 拼接insertMessage语句
if (emptyclassroomId != 0) {
PreparedStatement pstm1 = null;
Map<String, Integer> md5MapForMess = new HashMap<String, Integer>();
ResultSet rs = null;
// 本地缓存，方便查询
if (md5MapForMess.isEmpty()) { // 为空说明第一次写入当前教室的详细空课程信息
String md5Sql = "SELECT sequence FROM " + emptyClassMsgTable + " WHERE classid = ?";
pstm = conn.prepareStatement(md5Sql);// 判断数据库中是否存在当前教室详细的空闲信息
pstm.setInt(1, emptyclassroomId);
rs = pstm.executeQuery();
// 把数据库当前教室详细的空闲信息查询出来存入到md5Map中
while (rs.next()) { // 已经存在
String sequence = rs.getString(1);
md5MapForMess.put(sequence, 1);
}
rs.close();
// pstm.clearBatch();
// pstmt.clearParameters();
}
// 生成唯一序列值
String ClASSMESS_SEQUENCE_SEPARATOR = ">@<";
String insertMessSql = "INSERT INTO " + emptyClassMsgTable
+ "(classid,schoolId,period,smartPeriod,day,sectionStart,sectionEnd,beginTime,endTime,beginYear,term,sequence) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
conn.setAutoCommit(false);
pstm1 = conn.prepareStatement(insertMessSql);
for (Emptyclassmessage emptyclassmessage : emptyclassmessageList) {
String classMessContent = emptyclassroomId + ClASSMESS_SEQUENCE_SEPARATOR;
String smartPeriod = emptyclassmessage.getSmartPeriod();
smartPeriod = smartPeriod == null ? "" : smartPeriod;
classMessContent += smartPeriod + ClASSMESS_SEQUENCE_SEPARATOR;
String period = emptyclassmessage.getPeriod();
period = period == null ? "" : period;
classMessContent += period + ClASSMESS_SEQUENCE_SEPARATOR;
// String dateStart = emptyclassmessage.getdateStart();
// dateStart = dateStart == null ? "" : dateStart;
// String dateEnd = emptyclassmessage.getdateEnd();
// dateEnd = dateEnd == null ? "" : dateEnd;
classMessContent += "" + ClASSMESS_SEQUENCE_SEPARATOR;
classMessContent += "" + ClASSMESS_SEQUENCE_SEPARATOR;
int beginYear = emptyclassmessage.getBeginYear();
classMessContent += beginYear + ClASSMESS_SEQUENCE_SEPARATOR;
int term = emptyclassmessage.getTerm();
classMessContent += term + ClASSMESS_SEQUENCE_SEPARATOR;
int day = emptyclassmessage.getDay();
classMessContent += day + ClASSMESS_SEQUENCE_SEPARATOR;
int sectionStart = emptyclassmessage.getSectionStart();
classMessContent += sectionStart + ClASSMESS_SEQUENCE_SEPARATOR;
int sectionEnd = emptyclassmessage.getSectionEnd();
classMessContent += sectionEnd + ClASSMESS_SEQUENCE_SEPARATOR;
String beginTime = emptyclassmessage.getBeginTime();
beginTime = beginTime == null ? "" : beginTime;
classMessContent += beginTime + ClASSMESS_SEQUENCE_SEPARATOR;
String endTime = emptyclassmessage.getEndTime();
endTime = endTime == null ? "" : endTime;
classMessContent += endTime + ClASSMESS_SEQUENCE_SEPARATOR;
// 需要写入的教室详细空闲信息，md5提取信息
String classmessSequence = DigestUtils.md5Hex(classMessContent);
// 判断要写入的教室是否存在
if (!md5MapForMess.containsKey(classmessSequence)) {
// 拼接进要写入的insert中
// classid,schoolId,period,smartPeriod,day,sectionStart,sectionEnd,beginTime,endTime,beginYear,term
pstm1.setInt(1, emptyclassroomId);
pstm1.setInt(2, schoolId);
pstm1.setString(3, period);
pstm1.setString(4, smartPeriod);
pstm1.setInt(5, day);
pstm1.setInt(6, sectionStart);
pstm1.setInt(7, sectionEnd);
pstm1.setString(8, beginTime);
pstm1.setString(9, endTime);
pstm1.setInt(10, beginYear);
pstm1.setInt(11, term);
pstm1.setString(12, classmessSequence);
pstm1.addBatch();
// 并且存入map
md5MapForMess.put(classmessSequence, 1);
}
}
Date S = new Date();
pstm1.executeBatch();
conn.commit();
if (pstm1 != null)
pstm1.close();
Date E = new Date();
System.out.print("(" + (E.getTime() - S.getTime()) + ")");
} else {
// return "空教室添加失败，莫有外键哟";
}
} catch (Exception e) {
e.printStackTrace();
// return "空教室详情添加失败外：" + emptyclassmessageList;
} finally {
try {
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
}
/**
* 数据库Message临时表写入正式表（并且合并）
*
* @param maxDay
* @param schoolId
* @param beginYear
* @param term
* @param periodType
*/
public static void moveTempToNormal(int maxDay, int schoolId, int beginYear, int term, String periodType) {
Connection conn = null;
conn = ConnectionUtil.getConnection();
PreparedStatement pstm = null;
PreparedStatement pstm1 = null;
try {
List<Emptyclassmessage> emptyclassmessages = new ArrayList<Emptyclassmessage>();
Emptyclassmessage message = null;
for (int i = 1; i <= maxDay; i++) {
String sql = "select classid,"
+ periodType
+ ",`day`,sectionStart,sectionEnd from emptyclassmessagetemp where schoolId = ? and beginYear = ? AND term = ? AND day = ? order by classid asc,"
+ periodType + " + 0 asc;";
pstm = conn.prepareStatement(sql);
pstm.setInt(1, schoolId);
pstm.setInt(2, beginYear);
pstm.setInt(3, term);
pstm.setInt(4, i);
ResultSet rs = null;
rs = pstm.executeQuery();
while (rs.next()) {
message = new Emptyclassmessage();
message.setClassid(rs.getInt(1));
message.setSchoolId(schoolId);
message.setSmartPeriod(rs.getString(2));
message.setDay(rs.getInt(3));
message.setSectionStart(rs.getInt(4));
message.setSectionEnd(rs.getInt(5));
message.setBeginYear(beginYear);
message.setTerm(term);
emptyclassmessages.add(message);
}
rs.close();
mergeEmptyRoomMsgs(emptyclassmessages); // 合并该校当天课程
// 写入正式message数据库
System.out.println("Save to db.........");
Map<String, Integer> md5MapForMess = new HashMap<String, Integer>();
if (md5MapForMess.isEmpty()) { // 为空说明第一次进行操作
rs = null;
String md5Sql = "SELECT sequence FROM emptyclassmessage_fromschool WHERE schoolId = ?";
pstm = conn.prepareStatement(md5Sql);// 判断数据库中是否存在当前教室信息
pstm.setInt(1, schoolId);
rs = pstm.executeQuery();
// 把数据库当前学校的教室信息查询出来存入到md5Map中
while (rs.next()) { // 已经存在
String sequence = rs.getString(1);
md5MapForMess.put(sequence, 1);
}
rs.close();
}
String ClASSMESS_SEQUENCE_SEPARATOR = ">@<";
String insertMessSql = "INSERT INTO emptyclassmessage_fromschool (classid,schoolId,period,smartPeriod,day,sectionStart,sectionEnd,beginTime,endTime,beginYear,term,sequence) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
conn.setAutoCommit(false);
pstm1 = conn.prepareStatement(insertMessSql);
for (Emptyclassmessage emptyclassmessage : emptyclassmessages) {
int emptyclassroomId = emptyclassmessage.getClassid();
String classMessContent = emptyclassroomId + ClASSMESS_SEQUENCE_SEPARATOR;
String smartPeriod = emptyclassmessage.getSmartPeriod();
smartPeriod = smartPeriod == null ? "" : smartPeriod;
classMessContent += smartPeriod + ClASSMESS_SEQUENCE_SEPARATOR;
String period = emptyclassmessage.getPeriod();
period = period == null ? "" : period;
classMessContent += period + ClASSMESS_SEQUENCE_SEPARATOR;
// String dateStart = emptyclassmessage.getdateStart();
// dateStart = dateStart == null ? "" : dateStart;
// String dateEnd = emptyclassmessage.getdateEnd();
// dateEnd = dateEnd == null ? "" : dateEnd;
classMessContent += "" + ClASSMESS_SEQUENCE_SEPARATOR;
classMessContent += "" + ClASSMESS_SEQUENCE_SEPARATOR;
classMessContent += beginYear + ClASSMESS_SEQUENCE_SEPARATOR;
classMessContent += term + ClASSMESS_SEQUENCE_SEPARATOR;
classMessContent += i + ClASSMESS_SEQUENCE_SEPARATOR;
int sectionStart = emptyclassmessage.getSectionStart();
classMessContent += sectionStart + ClASSMESS_SEQUENCE_SEPARATOR;
int sectionEnd = emptyclassmessage.getSectionEnd();
classMessContent += sectionEnd + ClASSMESS_SEQUENCE_SEPARATOR;
String beginTime = emptyclassmessage.getBeginTime();
classMessContent += (beginTime == null ? "" : beginTime) + ClASSMESS_SEQUENCE_SEPARATOR;
String endTime = emptyclassmessage.getEndTime();
classMessContent += (endTime == null ? "" : endTime) + ClASSMESS_SEQUENCE_SEPARATOR;
// 需要写入的教室详细空闲信息，md5提取信息
String classmessSequence = DigestUtils.md5Hex(classMessContent);
// 判断要写入的教室是否存在
if (!md5MapForMess.containsKey(classmessSequence)) {
// 拼接进要写入的insert中
// classid,schoolId,period,smartPeriod,day,sectionStart,sectionEnd,beginTime,endTime,beginYear,term
pstm1.setInt(1, emptyclassroomId);
pstm1.setInt(2, schoolId);
pstm1.setString(3, period);
pstm1.setString(4, smartPeriod);
pstm1.setInt(5, i);
pstm1.setInt(6, sectionStart);
pstm1.setInt(7, sectionEnd);
pstm1.setString(8, beginTime);
pstm1.setString(9, endTime);
pstm1.setInt(10, beginYear);
pstm1.setInt(11, term);
pstm1.setString(12, classmessSequence);
System.out.println(smartPeriod);
pstm1.addBatch();
// 并且存入map
md5MapForMess.put(classmessSequence, 1);
} else {
System.out.println("----------------------存在了S--------------------");
System.out.println(emptyclassmessage);
System.out.println(classmessSequence);
System.out.println("----------------------存在了E--------------------");
}
}
Date S = new Date();
pstm1.executeBatch();
conn.commit();
if (pstm1 != null)
pstm1.close();
Date E = new Date();
System.out.println("(" + (E.getTime() - S.getTime()) + ")");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
}
/**
* 数据库Message临时表写入正式表（并且合并）
*
* @param maxDay
* @param schoolId
* @param beginYear
* @param term
* @param periodType
*/
public static void moveTempToNormal2( int schoolId, int beginYear, int term) {
Connection conn = null;
conn = ConnectionUtil.getConnection();
PreparedStatement pstm = null;
try {
List<Emptyclassmessage> emptyclassmessages = new ArrayList<Emptyclassmessage>();
Emptyclassmessage message = null;
// for (int i = 1; i <= maxDay; i++) {
String sql = "SELECT classid,dateStart,dateEnd,sectionStart,sectionEnd FROM emptyclassmessagetemp WHERE schoolId = ? and beginYear = ? AND term = ? ORDER BY classid ,dateStart, sectionStart,sectionEnd ";
pstm = conn.prepareStatement(sql);
pstm.setInt(1, schoolId);
pstm.setInt(2, beginYear);
pstm.setInt(3, term);
ResultSet rs = null;
rs = pstm.executeQuery();
while (rs.next()) {
message = new Emptyclassmessage();
message.setClassid(rs.getInt(1));
message.setSchoolId(schoolId);
// message.setSmartPeriod(rs.getString(2));
// message.setDay(rs.getInt(3));
message.setDateStart(rs.getString(2));
message.setDateEnd(rs.getString(3));
message.setSectionStart(rs.getInt(4));
message.setSectionEnd(rs.getInt(5));
message.setBeginYear(beginYear);
message.setTerm(term);
emptyclassmessages.add(message);
}
// System.out.println(emptyclassmessages);
//System.out.println(emptyclassmessages.size());
rs.close();
emptyclassmessages = splitListToClassIdOneDayAndCombine(emptyclassmessages); // 合并该校当天课程
// 写入正式message数据库
System.out.println("Save to db.........");
Map<String, Integer> md5MapForMess = new HashMap<String, Integer>();
if (md5MapForMess.isEmpty()) { // 为空说明第一次进行操作
rs = null;
String md5Sql = "SELECT sequence FROM emptyclassmessage_fromschool WHERE schoolId = ?";
pstm = conn.prepareStatement(md5Sql);// 判断数据库中是否存在当前教室信息
pstm.setInt(1, schoolId);
rs = pstm.executeQuery();
// 把数据库当前学校的教室信息查询出来存入到md5Map中
while (rs.next()) { // 已经存在
String sequence = rs.getString(1);
md5MapForMess.put(sequence, 1);
}
rs.close();
}
PreparedStatement pstm1 = null;
String insertMessSql = "INSERT INTO emptyclassmessage_fromschool"
+ "(classid,schoolId,beginYear,term,period,smartPeriod,dateStart,dateEnd,sectionStart,sectionEnd,beginTime,endTime,sequence) "
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
conn.setAutoCommit(false);
pstm1 = conn.prepareStatement(insertMessSql);
String ClASS_SEQUENCE_SEPARATOR = ">@<";
for (Emptyclassmessage emptyclassmessage : emptyclassmessages) {
// System.out.println(emptyclassmessage);
// String classMessContent = emptyclassroomId +
// ClASS_SEQUENCE_SEPARATOR;
int classId = emptyclassmessage.getClassid();
String classMessContent = classId + ClASS_SEQUENCE_SEPARATOR;
String smartPeriod = emptyclassmessage.getSmartPeriod();
smartPeriod = smartPeriod == null ? "" : smartPeriod;
classMessContent += smartPeriod + ClASS_SEQUENCE_SEPARATOR;
String period = emptyclassmessage.getPeriod();
period = period == null ? "" : period;
classMessContent += period + ClASS_SEQUENCE_SEPARATOR;
String dateStart = emptyclassmessage.getDateStart();
dateStart = (dateStart == null) ? "" : dateStart;
classMessContent += dateStart + ClASS_SEQUENCE_SEPARATOR;
String dateEnd = emptyclassmessage.getDateEnd();
dateEnd = (dateEnd == null)? "" : dateEnd;
classMessContent += dateEnd + ClASS_SEQUENCE_SEPARATOR;
classMessContent += beginYear + ClASS_SEQUENCE_SEPARATOR;
classMessContent += term + ClASS_SEQUENCE_SEPARATOR;
classMessContent += 0 + ClASS_SEQUENCE_SEPARATOR;//day
int sectionStart = emptyclassmessage.getSectionStart();
classMessContent += sectionStart + ClASS_SEQUENCE_SEPARATOR;
int sectionEnd = emptyclassmessage.getSectionEnd();
classMessContent += sectionEnd + ClASS_SEQUENCE_SEPARATOR;
String beginTime = emptyclassmessage.getBeginTime();
beginTime = beginTime == null ? "" : beginTime;
classMessContent += beginTime + ClASS_SEQUENCE_SEPARATOR;
String endTime = emptyclassmessage.getEndTime();
endTime = endTime == null ? "" : endTime;
classMessContent += endTime + ClASS_SEQUENCE_SEPARATOR;
// classid,schoolId,beginYear,term,period,smartPeriod,dateStart,dateEnd,day,sectionStart,sectionEnd,beginTime,endTime,sequence
// pstmMess.setInt(1, emptyclassroomId);
String classmessSequence = DigestUtils.md5Hex(classMessContent);
// 判断要写入的教室是否存在
if (!md5MapForMess.containsKey(classmessSequence)) {
// 拼接进要写入的insert中
// classid,schoolId,period,smartPeriod,day,sectionStart,sectionEnd,beginTime,endTime,beginYear,term
pstm1.setInt(1, classId);
pstm1.setInt(2, schoolId);
pstm1.setInt(3, beginYear);
pstm1.setInt(4, term);
pstm1.setString(5, period);
pstm1.setString(6, smartPeriod);
pstm1.setString(7, dateStart.equals("") ? null : dateStart);
pstm1.setString(8, dateEnd.equals("") ? null : dateEnd);
pstm1.setInt(9, sectionStart);
pstm1.setInt(10, sectionEnd);
pstm1.setString(11, beginTime);
pstm1.setString(12, endTime);
pstm1.setString(13, classmessSequence);
pstm1.addBatch();
md5MapForMess.put(classmessSequence, 1);
} else {
System.out.println("----------------------存在了S--------------------");
System.out.println(emptyclassmessage);
System.out.println(classmessSequence);
System.out.println("----------------------存在了E--------------------");
}
}
Date S = new Date();
//System.out.println(pstm1);
pstm1.executeBatch();
conn.commit();
if (pstm1 != null)
pstm1.close();
Date E = new Date();
System.out.println("(" + (E.getTime() - S.getTime()) + ")");
// }
// }
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
}
/**
* 数据库正式表写入临时表（不合并）
*
* @param maxDay
* @param schoolId
* @param beginYear
* @param term
* @param periodType
*/
// public static void moveNormalToTemp(int maxDay,int schoolId,int
// beginYear,int term,String periodType){
// Connection conn = null;
// conn = ConnectionUtil.getConnection();
//
// PreparedStatement pstm = null;
// PreparedStatement pstm1 = null;
//
// try {
//
// List<Emptyclassmessage> emptyclassmessages = new
// ArrayList<Emptyclassmessage>();
// Emptyclassmessage message = null;
//
// for (int i = 7; i <= maxDay; i++) {
// String sql =
// "select classid,"+periodType+",`day`,sectionStart,sectionEnd,sequence from emptyclassmessage_fromschool where schoolId = ? and beginYear = ? AND term = ? AND day = ? order by classid asc,"+periodType+" + 0 asc;";
// pstm = conn.prepareStatement(sql);
// pstm.setInt(1, schoolId);
// pstm.setInt(2, beginYear);
// pstm.setInt(3, term);
// pstm.setInt(4, i);
// ResultSet rs = null;
// rs = pstm.executeQuery();
// while (rs.next()) {
//
// message = new Emptyclassmessage();
// message.setClassid(rs.getInt(1));
// message.setSchoolId(schoolId);
// message.setSmartPeriod(rs.getString(2));
// message.setDay(rs.getInt(3));
// message.setSectionStart(rs.getInt(4));
// message.setSectionEnd(rs.getInt(5));
// message.setBeginYear(beginYear);
// message.setTerm(term);
// message.setSequence(rs.getString(6));
// emptyclassmessages.add(message);
//
// }
// rs.close();
// // mergeEmptyRoomMsgs(emptyclassmessages); //合并该校当天课程
//
// //写入正式message数据库
// System.out.println("Save to db........."+i);
//
// Map<String, Integer> md5MapForMess= new HashMap<String, Integer>();
// if(md5MapForMess.isEmpty()){ //为空说明第一次进行操作
// rs = null;
// String md5Sql =
// "SELECT sequence FROM emptyclassmessage_fromschool WHERE schoolId = ? and day = ?";
// pstm = conn.prepareStatement(md5Sql);//判断数据库中是否存在当前教室信息
// pstm.setInt(1, schoolId);
// pstm.setInt(2, i);
// rs = pstm.executeQuery();
//
// //把数据库当前学校的教室信息查询出来存入到md5Map中
// while (rs.next()) { // 已经存在
// String sequence = rs.getString(1);
// md5MapForMess.put(sequence, 1);
// }
// rs.close();
// }
//
// String insertMessSql =
// "INSERT INTO emptyclassmessagetemp (classid,schoolId,"+periodType+",day,sectionStart,sectionEnd,beginYear,term,sequence) "
// + "VALUES (?,?,?,?,?,?,?,?,?)";
// conn.setAutoCommit(false);
// pstm1 = conn.prepareStatement(insertMessSql);
//
// for (Emptyclassmessage emptyclassmessage : emptyclassmessages) {
// int emptyclassroomId= emptyclassmessage.getClassid();
//
// String smartPeriod = emptyclassmessage.getSmartPeriod();
// if(periodType.equals("period")){
// smartPeriod = emptyclassmessage.getPeriod();
// }
//
// // String dateStart = emptyclassmessage.getdateStart();
// // dateStart = dateStart == null ? "" : dateStart;
// // String dateEnd = emptyclassmessage.getdateEnd();
// // dateEnd = dateEnd == null ? "" : dateEnd;
//
// int sectionStart = emptyclassmessage.getSectionStart();
// int sectionEnd = emptyclassmessage.getSectionEnd();
//
// String classmessSequence = emptyclassmessage.getSequence();
// // 需要写入的教室详细空闲信息，md5提取信息
// //判断要写入的教室是否存在
// if(!md5MapForMess.containsKey(classmessSequence)){
// //拼接进要写入的insert中
// //classid,schoolId,period,smartPeriod,day,sectionStart,sectionEnd,beginTime,endTime,beginYear,term
// pstm1.setInt(1, emptyclassroomId);
// pstm1.setInt(2, schoolId);
// pstm1.setString(3, smartPeriod);
// pstm1.setInt(4, i);
// pstm1.setInt(5, sectionStart);
// pstm1.setInt(6, sectionEnd);
// pstm1.setInt(7, beginYear);
// pstm1.setInt(8, term);
// pstm1.setString(9, classmessSequence);
//
// pstm1.addBatch();
// //并且存入map
// md5MapForMess.put(classmessSequence, 1);
// }else{
// // System.out.println("----------------------存在了S--------------------");
// // System.out.println(emptyclassmessage);
// // System.out.println(classmessSequence);
// // System.out.println("----------------------存在了E--------------------");
// }
// }
// pstm1.executeBatch();
// conn.commit();
// if(pstm1!=null) pstm1.close();
// }
// } catch (Exception e) {
// e.printStackTrace();
// } finally{
// try {
// if(pstm!=null) pstm.close();
// if(conn!=null) conn.close();
// } catch (SQLException e) {}
// }
//
// }
// 合并临时表中课程的方法
public static List<Emptyclassmessage> mergeEmptyRoomMsgs(List<Emptyclassmessage> unmergeEmptyRoomMsgs) throws Exception {
if (unmergeEmptyRoomMsgs == null) {
return unmergeEmptyRoomMsgs;
}
if (unmergeEmptyRoomMsgs.size() == 0) {
return unmergeEmptyRoomMsgs;
}
List<Emptyclassmessage> mergerEmptyRoomMsgs = new ArrayList<Emptyclassmessage>();
Emptyclassmessage msg1 = null;
Emptyclassmessage msg2 = null;
int size = unmergeEmptyRoomMsgs.size();
for (int i = 0; i < size; i++) {
msg1 = unmergeEmptyRoomMsgs.get(i);
msg1.setSmartPeriod(msg1.getSmartPeriod());
for (int j = i + 1; j < size; j++) {
msg2 = unmergeEmptyRoomMsgs.get(j);
if (msg1.getClassid() == msg2.getClassid()) {
if (msg1.getSectionStart() == msg2.getSectionStart() && msg1.getSectionEnd() == msg2.getSectionEnd()) {
msg1.setSmartPeriod(msg1.getSmartPeriod() + " " + msg2.getSmartPeriod());
unmergeEmptyRoomMsgs.remove(j);
j--;
size--;
}
} else {
msg1.setPeriod(null);
mergerEmptyRoomMsgs.add(msg1);
break;
}
}
if (i + 1 == size) {
msg1.setPeriod(null);
mergerEmptyRoomMsgs.add(msg1);
}
}
Collections.sort(mergerEmptyRoomMsgs, new CompareSection4RoomMsg());
unmergeEmptyRoomMsgs.clear();
unmergeEmptyRoomMsgs.addAll(mergerEmptyRoomMsgs);
mergerEmptyRoomMsgs.clear();
size = unmergeEmptyRoomMsgs.size();
for (int i = 0; i < size; i++) {
msg1 = unmergeEmptyRoomMsgs.get(i);
for (int j = i + 1; j < size; j++) {
msg2 = unmergeEmptyRoomMsgs.get(j);
if (msg1.getClassid() == msg2.getClassid()) {
if (msg1.getSectionEnd() + 1 == msg2.getSectionStart()) {
msg1.setSectionEnd(msg2.getSectionEnd());
unmergeEmptyRoomMsgs.remove(j);
j--;
size--;
}
} else {
mergerEmptyRoomMsgs.add(msg1);
break;
}
}
if (i + 1 == size) {
msg1.setPeriod(null);
mergerEmptyRoomMsgs.add(msg1);
}
}
return mergerEmptyRoomMsgs;
}
/**
* 针对日起型 2014-11-11 日 的节次合并 1-2.3-4 合并成 1-4 同一天的节次合并
*
* @param
* @param
* @param
* @param
* @param periodType
* @creattime 2014年4月1日11:05:33
* @author zb
* */
public static List<Emptyclassmessage> combineInsertNormalDBforDate(List<Emptyclassmessage> roomMessagelist) {
// System.out.println("-----合并前list大小：" + roomMessagelist.size());
List<Emptyclassmessage> roomMessagelistNew = new ArrayList<Emptyclassmessage>();
if (roomMessagelist.size() > 0) {
int size = roomMessagelist.size();
for (int m = 0; m < size; m++) {
for (int n = m + 1; n < size; n++) {
Emptyclassmessage emptyclassmessage1 = roomMessagelist.get(m);
Emptyclassmessage emptyclassmessage2 = roomMessagelist.get(n);
int sectionStart1 = emptyclassmessage1.getSectionStart();
int sectionEnd1 = emptyclassmessage1.getSectionEnd();
int sectionStart2 = emptyclassmessage2.getSectionStart();
int sectionEnd2 = emptyclassmessage2.getSectionEnd();
if (sectionEnd1 + 1 == sectionStart2) {
// 合并
emptyclassmessage1.setSectionStart(sectionStart1);
emptyclassmessage1.setSectionEnd(sectionEnd2);
// 删除m
roomMessagelist.remove(n);
size--;
n--;
}
}
}
roomMessagelistNew.addAll(roomMessagelist);
// System.out.println("-----合并后list大小：" + roomMessagelistNew.size());
}
return roomMessagelistNew;
}
/**
* 拆分 list 同一教室，同一天 并且合并
* @param roomMessagelist 临时表中某一学校的所有记录
* @param periodType
* @return List<Emptyclassmessage> 合并后的list
* @creattime 2014年4月2日11:05:33
* @author zb
* */
public static List<Emptyclassmessage> splitListToClassIdOneDayAndCombine(List<Emptyclassmessage> roomMessagelist) {
List<Emptyclassmessage> messagelistALL = new ArrayList<Emptyclassmessage>();
System.out.println("接收到合并前的记录数为："+roomMessagelist.size());
List<List<Emptyclassmessage>> messagelistByRoomIdAndDate = new ArrayList<List<Emptyclassmessage>>();
if (roomMessagelist.size() > 0) {
int size = roomMessagelist.size();
for (int i = 0; i < size; i++) {
List<Emptyclassmessage> EmptyclassmessageListByIDAndOneDay = new ArrayList<Emptyclassmessage>();
Emptyclassmessage emptyclassmessage1 = roomMessagelist.get(i);
//System.out.println(emptyclassmessage1);
EmptyclassmessageListByIDAndOneDay.add(emptyclassmessage1);
for (int j = i + 1; j < size; j++) {
int schoolID1 = emptyclassmessage1.getSchoolId();
int classID1 = emptyclassmessage1.getClassid();
String dateStart1 = emptyclassmessage1.getDateStart();
String dateEnd1 = emptyclassmessage1.getDateEnd();
Emptyclassmessage emptyclassmessage2 = roomMessagelist.get(j);
//System.out.println(emptyclassmessage2);
int schoolID2 = emptyclassmessage2.getSchoolId();
int classID2 = emptyclassmessage2.getClassid();
String dateStart2 = emptyclassmessage2.getDateStart();
String dateEnd2 = emptyclassmessage2.getDateEnd();
if (schoolID1 == schoolID2 && classID1 == classID2 && dateStart1.equals(dateStart2) && dateEnd1.equals(dateEnd2) ) {
// 匹配成功，添加第二列的值
EmptyclassmessageListByIDAndOneDay.add(emptyclassmessage2);
roomMessagelist.remove(emptyclassmessage2);
j--;
size--;
}
// 添加到一个list中
}
messagelistByRoomIdAndDate.add(EmptyclassmessageListByIDAndOneDay);
}
// System.out.println(messagelistByRoomIdAndDate.size());
// System.out.println(messagelistByRoomIdAndDate);
// 遍历list
// System.out.println("messagelistByRoomIdAndDate.size()="+messagelistByRoomIdAndDate.size());
for (int i = 0; i < messagelistByRoomIdAndDate.size(); i++) {
List<Emptyclassmessage> emptyclassmessageList = messagelistByRoomIdAndDate.get(i);
// System.out.println(emptyclassmessageList);
List<Emptyclassmessage> onedayList = combineInsertNormalDBforDate(emptyclassmessageList);
messagelistALL.addAll(onedayList);
}
}
System.out.println("返回合并后准备插入数据库的记录条数大小为："+messagelistALL.size());
return messagelistALL;
}
public static void main(String[] args) {
List<Emptyclassmessage> emptyclassmessageList = new ArrayList<Emptyclassmessage>();
Emptyclassmessage emptyclassmessage4 = new Emptyclassmessage();
emptyclassmessage4.setSchoolId(1);
emptyclassmessage4.setClassid(2);
emptyclassmessage4.setDateStart("2014-10-10");
emptyclassmessage4.setDateEnd("2014-10-10");
emptyclassmessage4.setSectionStart(1);
emptyclassmessage4.setSectionEnd(1);
emptyclassmessageList.add(emptyclassmessage4);
Emptyclassmessage emptyclassmessage3 = new Emptyclassmessage();
emptyclassmessage3.setSchoolId(1);
emptyclassmessage3.setClassid(2);
emptyclassmessage3.setDateStart("2014-10-10");
emptyclassmessage3.setDateEnd("2014-10-10");
emptyclassmessage3.setSectionStart(2);
emptyclassmessage3.setSectionEnd(2);
emptyclassmessageList.add(emptyclassmessage3);
for (int i = 7; i < 13; i ++) {
Emptyclassmessage emptyclassmessage = new Emptyclassmessage();
emptyclassmessage.setSchoolId(1);
emptyclassmessage.setClassid(2);
emptyclassmessage.setDateStart("2014-10-10");
emptyclassmessage.setDateEnd("2014-10-10");
emptyclassmessage.setSectionStart(i);
emptyclassmessage.setSectionEnd(i);
emptyclassmessageList.add(emptyclassmessage);
}
//
// for (int i = 0; i < 5; i = i + 2) {
//
// Emptyclassmessage emptyclassmessage = new Emptyclassmessage();
// emptyclassmessage.setSchoolId(1);
// emptyclassmessage.setClassid(3);
// emptyclassmessage.setDateStart("2014-10-11");
// emptyclassmessage.setDateEnd("2014-10-11");
// emptyclassmessage.setSectionStart(i + 1);
// emptyclassmessage.setSectionEnd(i + 2);
// emptyclassmessageList.add(emptyclassmessage);
//
// }
//
// Emptyclassmessage emptyclassmessage3 = new Emptyclassmessage();
// emptyclassmessage3.setSchoolId(1);
// emptyclassmessage3.setClassid(3);
// emptyclassmessage3.setDateStart("2014-10-11");
// emptyclassmessage3.setDateEnd("2014-10-11");
// emptyclassmessage3.setSectionStart(9);
// emptyclassmessage3.setSectionEnd(10);
// emptyclassmessageList.add(emptyclassmessage3);
//
// for (int i = 0; i < 1; i++) {
//
// Emptyclassmessage emptyclassmessage = new Emptyclassmessage();
// emptyclassmessage.setSchoolId(1);
// emptyclassmessage.setClassid(4);
// emptyclassmessage.setDateStart("2014-10-13");
// emptyclassmessage.setDateEnd("2014-10-13");
// emptyclassmessage.setSectionStart(i + 1);
// emptyclassmessage.setSectionEnd(i + 2);
// emptyclassmessageList.add(emptyclassmessage);
// }
// System.out.println(emptyclassmessageList);
// System.out.println(splitListToClassIdOneDayAndCombine(emptyclassmessageList));
}
}
扫一扫
765
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
