private int checkSituationRecordInDb(MobisTrafficSituationRecord sitRec, Connection con) {
try {
String selectString =
"SELECT * FROM public.mobissituationrecord WHERE providerid='"
+ sitRec.getProviderId()
+ "' AND provider='"
+ sitRec.getProvider()
+ "' AND version="
+ sitRec.getSituationRecordVersion()
+ ";";
PreparedStatement stmt = con.prepareStatement(selectString);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
return 0;
} else {
selectString =
"SELECT * FROM public.mobissituationrecord WHERE providerid='"
+ sitRec.getProviderId()
+ "' AND provider='"
+ sitRec.getProvider()
+ "';";
stmt = con.prepareStatement(selectString);
rs = stmt.executeQuery();
if (rs.next()) {
return 1;
} else return 2;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return -1;
}
}
public void endElement(String uri, String localName, String qName) throws SAXException {
String sqlInsert = "", publTime = "";
Connection con = null;
PreparedStatement insertStmt = null;
Object[] dateTime;
Date startDate = null, endDate = null, publicationDate = null, versionDate = null;
Time startTime = null, endTime = null, publicationTime = null, versionTime = null;
ResultSet rs;
long sitId = 0;
if (qName.equalsIgnoreCase("situationrecord")) {
if (!sitRec.getStartTime().equals("")) {
if (sitRec.getStartTime().contains("."))
sitRec.setStartTime(
sitRec
.getStartTime()
.replace("T", " ")
.substring(0, sitRec.getStartTime().lastIndexOf(".")));
else if (sitRec.getStartTime().contains("+"))
sitRec.setStartTime(
sitRec
.getStartTime()
.replace("T", " ")
.substring(0, sitRec.getStartTime().lastIndexOf("+")));
else sitRec.setStartTime(sitRec.getStartTime().replace("T", " "));
}
if (!sitRec.getEndTime().equals("")) {
if (sitRec.getEndTime().contains("."))
sitRec.setEndTime(
sitRec
.getEndTime()
.replace("T", " ")
.substring(0, sitRec.getEndTime().lastIndexOf(".")));
else if (sitRec.getEndTime().contains("+"))
sitRec.setEndTime(
sitRec
.getEndTime()
.replace("T", " ")
.substring(0, sitRec.getEndTime().lastIndexOf("+")));
else sitRec.setEndTime(sitRec.getEndTime().replace("T", " "));
}
if (!sitRec.getVersionTime().equals("")) {
if (sitRec.getVersionTime().contains("."))
sitRec.setVersionTime(
sitRec
.getVersionTime()
.replace("T", " ")
.substring(0, sitRec.getVersionTime().lastIndexOf(".")));
else if (sitRec.getVersionTime().contains("+"))
sitRec.setVersionTime(
sitRec
.getVersionTime()
.replace("T", " ")
.substring(0, sitRec.getVersionTime().lastIndexOf("+")));
else sitRec.setVersionTime(sitRec.getVersionTime().replace("T", " "));
}
records.add(sitRec);
}
if (qName.equalsIgnoreCase("situation")) {
if (sit.getPublicationTime().contains("."))
publTime =
sit.getPublicationTime()
.replace("T", " ")
.substring(0, sit.getPublicationTime().lastIndexOf("."));
else if (sit.getPublicationTime().contains("+"))
publTime =
sit.getPublicationTime()
.replace("T", " ")
.substring(0, sit.getPublicationTime().lastIndexOf("+"));
else publTime = sit.getPublicationTime().replace("T", " ");
if (!publTime.equals(lastPublTime)) {
try {
con =
dbUtils.startConnection(
"/MobisPGSQLConfig.xml",
StringConstants.MOBIS_TRAFFIC,
StringConstants.JDBC_POSTGRES);
if (sit.getVersionTime().contains("."))
sit.setVersionTime(
sit.getVersionTime()
.replace("T", " ")
.substring(0, sit.getVersionTime().lastIndexOf(".")));
else if (sit.getVersionTime().contains("+"))
sit.setVersionTime(
sit.getVersionTime()
.replace("T", " ")
.substring(0, sit.getVersionTime().lastIndexOf("+")));
else sit.setVersionTime(sit.getVersionTime().replace("T", " "));
sit.setPublicationTime(publTime);
sit.setRecords(records);
if (sit.getPublicationTime() != null && !sit.getPublicationTime().equals("")) {
dateTime = dbUtils.readStringToDateTime(sit.getPublicationTime());
publicationDate = (Date) dateTime[0];
publicationTime = (Time) dateTime[1];
}
if (sit.getVersionTime() != null && !sit.getVersionTime().equals("")) {
dateTime = dbUtils.readStringToDateTime(sit.getVersionTime());
versionDate = (Date) dateTime[0];
versionTime = (Time) dateTime[1];
}
flag = this.checkSituationInDb(sit, con);
if (flag == 2) {
sqlInsert =
"INSERT INTO public.mobissituation (providerid, provider, version, overallseverity, country, publicationtime, versiontime, publicationdate, versiondate) VALUES ('"
+ sit.getProviderId()
+ "','"
+ sit.getProvider()
+ "',"
+ sit.getVersion()
+ ",'"
+ sit.getOverallSeverity()
+ "','"
+ sit.getCountry()
+ "',"
+ (publicationTime != null ? "'" + publicationTime + "'" : null)
+ ","
+ (versionTime != null ? "'" + versionTime + "'" : null)
+ ","
+ (publicationDate != null ? "'" + publicationDate + "'" : null)
+ ","
+ (versionDate != null ? "'" + versionDate + "'" : null)
+ ") RETURNING situationid;";
} else if (flag == 1) {
sqlInsert =
"UPDATE public.mobissituation SET providerid='"
+ sit.getProviderId()
+ "',provider='"
+ sit.getProvider()
+ "',version="
+ sit.getVersion()
+ ",publicationtime="
+ (publicationTime != null ? "'" + publicationTime + "'" : null)
+ ",versiontime="
+ (versionTime != null ? "'" + versionTime + "'" : null)
+ ",overallseverity='"
+ sit.getOverallSeverity()
+ "',publicationdate="
+ (publicationDate != null ? "'" + publicationDate + "'" : null)
+ ",versiondate="
+ (versionDate != null ? "'" + versionDate + "'" : null)
+ " WHERE providerid='"
+ sit.getProviderId()
+ "' AND provider='"
+ sit.getProvider()
+ "' RETURNING situationid;";
}
if (flag == 1 || flag == 2) {
insertStmt = con.prepareStatement(sqlInsert);
rs = insertStmt.executeQuery();
if (rs != null && rs.next()) {
sitId = rs.getLong("situationid");
}
for (int i = 0; i < records.size(); i++) {
sitRec = records.get(i);
flag = this.checkSituationRecordInDb(sitRec, con);
if (sitRec.getStartTime() != null && !sitRec.getStartTime().equals("")) {
dateTime = dbUtils.readStringToDateTime(sitRec.getStartTime());
startDate = (Date) dateTime[0];
startTime = (Time) dateTime[1];
}
if (sitRec.getEndTime() != null && !sitRec.getEndTime().equals("")) {
dateTime = dbUtils.readStringToDateTime(sitRec.getEndTime());
endDate = (Date) dateTime[0];
endTime = (Time) dateTime[1];
}
if (sitRec.getVersionTime() != null && !sitRec.getVersionTime().equals("")) {
dateTime = dbUtils.readStringToDateTime(sitRec.getVersionTime());
versionDate = (Date) dateTime[0];
versionTime = (Time) dateTime[1];
}
if (flag == 2) {
sqlInsert =
"INSERT INTO public.mobissituationrecord (providerid, provider, version, starttime, endtime, affectedways, lat, lng, text, locationdescriptor, description, lengthaffected, severity, roadnumber, recordtype, versiontime, situationid, startdate, enddate, versiondate) VALUES "
+ "('"
+ sitRec.getProviderId()
+ "','"
+ sitRec.getProvider()
+ "',"
+ sitRec.getSituationRecordVersion()
+ ","
+ (startTime != null ? "'" + startTime + "'" : null)
+ ","
+ (endTime != null ? "'" + endTime + "'" : null)
+ ",'"
+ sitRec.getAffectedWays()
+ "',"
+ sitRec.getLat()
+ ","
+ sitRec.getLng()
+ ",'"
+ sitRec.getText()
+ "','"
+ sitRec.getLocationDescriptor()
+ "','"
+ sitRec.getDescription()
+ "',"
+ sitRec.getLengthAffected()
+ ",'"
+ sitRec.getSeverity()
+ "','"
+ sitRec.getRoadNumber()
+ "','"
+ sitRec.getSituationRecordType()
+ "','"
+ sitRec.getVersionTime()
+ "',"
+ sitId
+ ","
+ (startDate != null ? "'" + startDate + "'" : null)
+ ","
+ (endDate != null ? "'" + endDate + "'" : null)
+ ","
+ (versionDate != null ? "'" + versionDate + "'" : null)
+ ")";
} else if (flag == 1) {
sqlInsert =
"UPDATE public.mobissituationrecord SET providerid='"
+ sitRec.getProviderId()
+ "',provider='"
+ sitRec.getProvider()
+ "',version="
+ sitRec.getSituationRecordVersion()
+ ",starttime="
+ (startTime != null ? "'" + startTime + "'" : null)
+ ",endtime="
+ (endTime != null ? "'" + endTime + "'" : null)
+ ",affectedways='"
+ sitRec.getAffectedWays()
+ "',lat="
+ sitRec.getLat()
+ ",lng="
+ sitRec.getLng()
+ ",text='"
+ sitRec.getText()
+ "',locationdescriptor='"
+ sitRec.getLocationDescriptor()
+ "',description='"
+ sitRec.getDescription()
+ "',lengthaffected="
+ sitRec.getLengthAffected()
+ ",severity='"
+ sitRec.getSeverity()
+ "',roadnumber='"
+ sitRec.getRoadNumber()
+ "',recordtype='"
+ sitRec.getSituationRecordType()
+ "',versiontime="
+ (versionTime != null ? "'" + versionTime + "'" : null)
+ ",situationid="
+ sitId
+ ",startdate="
+ (startDate != null ? "'" + startDate + "'" : null)
+ ",enddate="
+ (endDate != null ? "'" + endDate + "'" : null)
+ ",versiondate="
+ (versionDate != null ? "'" + versionDate + "'" : null)
+ " WHERE providerid='"
+ sitRec.getProviderId()
+ "' AND provider='"
+ sitRec.getProvider()
+ "';";
}
if (flag == 1 || flag == 2) {
insertStmt = con.prepareStatement(sqlInsert);
insertStmt.executeUpdate();
}
}
insertStmt.close();
}
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
if (qName.equalsIgnoreCase("payloadPublication")) {
lastPublTime = sit.getPublicationTime();
}
}