Exemplo n.º 1
0
  public TrafficCountRecord getTrafficCountRecordById(String id) throws Exception {
    TrafficCountRecord trafficCountRecord = new TrafficCountRecord();
    try {
      Connection connection = getConnection();
      Statement s = connection.createStatement();
      String sql =
          "select id, tag, countdate, daytype, starttime,"
              + "endtime, w2, w3, pc, tx, ldv, ldc, hdc, mdb, hdb from trafficcount"
              + "where id = '"
              + id
              + "' ORDER BY countdate";
      ResultSet r = s.executeQuery(sql);
      while (r.next()) {

        String tag = r.getString(2);
        Date date = r.getDate(3);
        String dayType = r.getString(4);
        Date startTime = r.getDate(5);
        Date endTime = r.getDate(6);
        int W2 = r.getInt(7);
        int W3 = r.getInt(8);
        int PC = r.getInt(9);
        int TX = r.getInt(10);
        int LDV = r.getInt(11);
        int LDC = r.getInt(12);
        int HDC = r.getInt(13);
        int MDB = r.getInt(14);
        int HDB = r.getInt(15);

        trafficCountRecord.setId(id);
        trafficCountRecord.setDate(date);
        trafficCountRecord.setDayType(dayType);
        trafficCountRecord.setEndTime(endTime);
        trafficCountRecord.setHDB(HDB);
        trafficCountRecord.setHDC(HDC);
        trafficCountRecord.setLDC(LDC);
        trafficCountRecord.setLDV(LDV);
        trafficCountRecord.setMDB(MDB);
        trafficCountRecord.setPC(PC);
        trafficCountRecord.setStartTime(startTime);
        trafficCountRecord.setTag(tag);
        trafficCountRecord.setTX(TX);
        trafficCountRecord.setW2(W2);
        trafficCountRecord.setW3(W3);
      }

      connection.close(); // returns connection to pool

    } catch (SQLException e) {
      logger.error(e.getMessage());
      throw new Exception("There was an error executing the SQL: " + e.getMessage());
    } catch (Exception e) {
      logger.error(e.getMessage());
      throw new Exception("Unknown exception: " + e.getMessage());
    }

    return trafficCountRecord;
  }
Exemplo n.º 2
0
  public ArrayList<TrafficCountRecord> getTrafficCountRecords(StudyRegion region) throws Exception {
    ArrayList<TrafficCountRecord> trafficCountRecordList = new ArrayList<TrafficCountRecord>();
    try {
      Connection connection = getConnection();
      Statement s = connection.createStatement();
      String sql =
          "SELECT "
              + "t1.id, "
              + "t2.name as tag, "
              + "t1.countdate, "
              + "t1.daytype, "
              + "t1.starttime,"
              + "t1.endtime, "
              + "t1.w2, "
              + "t1.w3, "
              + "t1.pc, "
              + "t1.tx, "
              + "t1.ldv, "
              + "t1.ldc, "
              + "t1.hdc, "
              + "t1.mdb, "
              + "t1.hdb "
              + "FROM "
              + "	trafficcount t1, "
              + "	tagdetails t2 "
              + "WHERE "
              + "	t1.regionid = '"
              + region.getId()
              + "' "
              + "AND "
              + "	t1.tagid = t2.id "
              + "ORDER BY t1.countdate ASC, t1.starttime ASC";
      logger.debug("SQL for getTrafficCountRecords: " + sql);
      ResultSet r = s.executeQuery(sql);
      while (r.next()) {

        String id = r.getString(1);
        String tag = r.getString(2);
        Date date = r.getDate(3);
        String dayType = r.getString(4);
        Date startTime = r.getTimestamp(5);
        Date endTime = r.getTimestamp(6);
        int W2 = r.getInt(7);
        int W3 = r.getInt(8);
        int PC = r.getInt(9);
        int TX = r.getInt(10);
        int LDV = r.getInt(11);
        int LDC = r.getInt(12);
        int HDC = r.getInt(13);
        int MDB = r.getInt(14);
        int HDB = r.getInt(15);

        TrafficCountRecord trafficCountRecord = new TrafficCountRecord();
        trafficCountRecord.setId(id);
        trafficCountRecord.setDate(date);
        trafficCountRecord.setDayType(dayType);
        trafficCountRecord.setEndTime(endTime);
        trafficCountRecord.setHDB(HDB);
        trafficCountRecord.setHDC(HDC);
        trafficCountRecord.setLDC(LDC);
        trafficCountRecord.setLDV(LDV);
        trafficCountRecord.setMDB(MDB);
        trafficCountRecord.setPC(PC);
        trafficCountRecord.setStartTime(startTime);
        trafficCountRecord.setTag(tag);
        trafficCountRecord.setTX(TX);
        trafficCountRecord.setW2(W2);
        trafficCountRecord.setW3(W3);

        trafficCountRecordList.add(trafficCountRecord);
      }

      connection.close(); // returns connection to the pool

    } catch (SQLException e) {
      logger.error(e.getMessage());
      throw new Exception("There was an error executing the SQL: " + e.getMessage());
    } catch (Exception e) {
      e.printStackTrace();
      logger.error(e.getMessage());
      throw new Exception("Unknown exception: " + e.getMessage());
    }

    return trafficCountRecordList;
  }