コード例 #1
0
 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;
   }
 }
コード例 #2
0
  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();
    }
  }