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 characters(char ch[], int start, int length) throws SAXException {
if (isVersionTime) {
sit.setVersionTime(new String(ch, start, length));
isVersionTime = false;
}
if (isPublicationTime) {
// sit.setPublicationTime(new String(ch, start, length)) ;
publTime = new String(ch, start, length);
isPublicationTime = false;
}
if (isRecordVersionTime) {
sitRec.setVersionTime(new String(ch, start, length));
isRecordVersionTime = false;
}
if (isSeverity) {
sitRec.setSeverity(new String(ch, start, length));
isSeverity = false;
}
if (isOverallSeverity) {
sit.setOverallSeverity(new String(ch, start, length));
isOverallSeverity = false;
}
if (isStartTime) {
sitRec.setStartTime(new String(ch, start, length));
isStartTime = false;
}
if (isEndTime) {
sitRec.setEndTime(new String(ch, start, length));
isEndTime = false;
}
if (isCommentValue) {
commentValue = new String(ch, start, length);
isCommentValue = false;
isComment = false;
}
if (isCommentType) {
commentType = new String(ch, start, length);
if (commentType.equals("description")) {
sitRec.setDescription(commentValue);
} else {
sitRec.setLocationDescriptor(commentValue);
}
isCommentType = false;
}
if (isLat) {
sitRec.setLat(Double.parseDouble(new String(ch, start, length)));
isLat = false;
}
if (isLng) {
sitRec.setLng(Double.parseDouble(new String(ch, start, length)));
isLng = false;
isLocationForDisplay = false;
}
if (isRoadNumber) {
sitRec.setRoadNumber(new String(ch, start, length));
isRoadNumber = false;
}
if (isCarriageway) {
sitRec.setAffectedWays(new String(ch, start, length));
isCarriageway = false;
}
if (isLengthAffected) {
sitRec.setLengthAffected(Integer.parseInt(new String(ch, start, length)));
isLengthAffected = false;
}
if (isText) {
sitRec.setText(new String(ch, start, length));
isText = false;
}
if (isSpeedLimit) {
sitRec.setText(sitRec.getText() + ": " + new String(ch, start, length));
isSpeedLimit = false;
}
}
public void startElement(String uri, String localName, String qName, Attributes attributes)
throws SAXException {
if (qName.equalsIgnoreCase("situation")) {
sit = new MobisTrafficSituation();
records = new ArrayList<>();
sit.setProviderId(attributes.getValue("id"));
sit.setProvider("TRAFIKVERKET_DATEX");
sit.setVersion(Integer.parseInt(attributes.getValue("version")));
sit.setPublicationTime(publTime);
sit.setCountry("Sweden");
}
if (qName.equalsIgnoreCase("situationrecord")) {
sitRec = new MobisTrafficSituationRecord();
sitRec.setProviderId(attributes.getValue("id"));
sitRec.setProvider("TRAFIKVERKET_DATEX");
sitRec.setSituationRecordVersion(Integer.parseInt(attributes.getValue("version")));
sitRec.setSituationRecordType(attributes.getValue("xsi:type"));
}
if (qName.equalsIgnoreCase("situationversiontime")) {
isVersionTime = true;
}
if (qName.equalsIgnoreCase("publicationtime")) {
isPublicationTime = true;
}
if (qName.equalsIgnoreCase("situationrecordversiontime")) {
isRecordVersionTime = true;
}
if (qName.equalsIgnoreCase("severity")) {
isSeverity = true;
}
if (qName.equalsIgnoreCase("overallseverity")) {
isOverallSeverity = true;
}
if (qName.equalsIgnoreCase("overallstarttime")) {
isStartTime = true;
}
if (qName.equalsIgnoreCase("overallendtime")) {
isEndTime = true;
}
if (qName.equalsIgnoreCase("generalpubliccomment")) {
isComment = true;
}
if (qName.equalsIgnoreCase("value") && isComment) {
isCommentValue = true;
}
if (qName.equalsIgnoreCase("commenttype")) {
isCommentType = true;
}
if (qName.equalsIgnoreCase("locationForDisplay")) {
isLocationForDisplay = true;
}
if (qName.equalsIgnoreCase("latitude") && isLocationForDisplay) {
isLat = true;
}
if (qName.equalsIgnoreCase("longitude") && isLocationForDisplay) {
isLng = true;
}
if (qName.equalsIgnoreCase("roadnumber")) {
isRoadNumber = true;
}
if (qName.equalsIgnoreCase("carriageway")) {
isCarriageway = true;
}
if (qName.equalsIgnoreCase("lengthAffected")) {
isLengthAffected = true;
}
if (qName.equalsIgnoreCase("speedManagementType")
|| qName.equalsIgnoreCase("roadMaintenanceType")
|| qName.equalsIgnoreCase("roadOrCarriagewayOrLaneManagementType")) {
isText = true;
}
if (qName.equalsIgnoreCase("temporarySpeedLimit")) {
isSpeedLimit = true;
}
}
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();
}
}