private int checkSituationInDb(MobisTrafficSituation sit, Connection con) {
   try {
     String selectString =
         "SELECT * FROM public.mobissituation WHERE providerid='"
             + sit.getProviderId()
             + "' AND provider='"
             + sit.getProvider()
             + "' AND version="
             + sit.getVersion()
             + ";";
     PreparedStatement stmt = con.prepareStatement(selectString);
     ResultSet rs = stmt.executeQuery();
     if (rs.next()) {
       return 0;
     } else {
       selectString =
           "SELECT * FROM public.mobissituation WHERE providerid='"
               + sit.getProviderId()
               + "' AND provider='"
               + sit.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();
    }
  }