예제 #1
0
  @Override
  public List<UserLocationInfo> list(int from, int to, String orderField, boolean orderAsc)
      throws DAOException {
    List<UserLocationInfo> list = new ArrayList<UserLocationInfo>();
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      if ("date".equalsIgnoreCase(orderField)) {
        orderField = TBL_USER_LOC.FLD_DATE;
      } else if ("name".equalsIgnoreCase(orderField)) {
        orderField = TBL_USER_LOC.FLD_NAME;
      } else if ("longitude".equalsIgnoreCase(orderField)) {
        orderField = TBL_USER_LOC.FLD_LONGITUDE;
      } else if ("latitude".equalsIgnoreCase(orderField)) {
        orderField = TBL_USER_LOC.FLD_LATITUDE;
      } else if ("tags".equalsIgnoreCase(orderField)) {
        orderField = TBL_USER_LOC.FLD_TAGS;
      } else if ("moreInfo".equalsIgnoreCase(orderField)) {
        orderField = TBL_USER_LOC.FLD_MORE_INFO;
      } else {
        orderField = TBL_USER_LOC.FLD_ID;
      }

      String selectSQL =
          "Select * from "
              + TBL_USER_LOC.NAME
              + " order by "
              + orderField
              + (orderAsc ? " asc " : " desc ")
              + " limit "
              + (to - from + 1)
              + " offset "
              + from;
      con = ServiceFactory.getResourceService().getConnection();
      ps = con.prepareStatement(selectSQL);

      rs = ps.executeQuery();
      while (rs.next()) {
        UserLocationInfo locInfo = new UserLocationInfo();

        locInfo.setId(rs.getInt(TBL_USER_LOC.FLD_ID));
        locInfo.setLatitude(rs.getString(TBL_USER_LOC.FLD_LATITUDE));
        locInfo.setLongitude(rs.getString(TBL_USER_LOC.FLD_LONGITUDE));
        locInfo.setName(rs.getString(TBL_USER_LOC.FLD_NAME));
        locInfo.setTags(mapTagsField(rs.getString(TBL_USER_LOC.FLD_TAGS)));
        locInfo.setMoreInfo(
            LocationMoreInfo.mapMoreInfoField(rs.getString(TBL_USER_LOC.FLD_MORE_INFO)));
        locInfo.setDate(rs.getTimestamp(TBL_USER_LOC.FLD_DATE));
        locInfo.setAccept(rs.getString(TBL_USER_LOC.FLD_ACCEPT));

        list.add(locInfo);
      }
    } catch (Throwable ex) {
      ex.printStackTrace();
      throw new DAOException(ex.getMessage(), ex);
    } finally {
      ResourceManagement.releaseResource(rs);
      ResourceManagement.releaseResource(ps);
      ResourceManagement.releaseResource(con);
    }
    return list;
  }
예제 #2
0
  @Override
  public List<LocationInfo> accept(List<Integer> ids) throws DAOException {
    List<LocationInfo> list = new ArrayList<LocationInfo>();
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {

      StringBuilder sbIds = new StringBuilder();
      sbIds.append(ids.get(0));
      for (int i = 1; i < ids.size(); i++) {
        sbIds.append("," + ids.get(i));
      }

      String selectSQL =
          "Select * from "
              + TBL_USER_LOC.NAME
              + " where "
              + TBL_USER_LOC.FLD_ID
              + " in ("
              + sbIds.toString()
              + ")";

      String updateSQL =
          "update "
              + TBL_USER_LOC.NAME
              + " set "
              + TBL_USER_LOC.FLD_ACCEPT
              + " ='Y' "
              + " where "
              + TBL_USER_LOC.FLD_ID
              + " in ("
              + sbIds.toString()
              + ")";

      con = ServiceFactory.getResourceService().getConnection();
      ps = con.prepareStatement(selectSQL);

      rs = ps.executeQuery();
      while (rs.next()) {
        UserLocationInfo locInfo = new UserLocationInfo();

        locInfo.setId(rs.getInt(TBL_USER_LOC.FLD_ID));
        locInfo.setLatitude(rs.getString(TBL_USER_LOC.FLD_LATITUDE));
        locInfo.setLongitude(rs.getString(TBL_USER_LOC.FLD_LONGITUDE));
        locInfo.setName(rs.getString(TBL_USER_LOC.FLD_NAME));
        locInfo.setTags(mapTagsField(rs.getString(TBL_USER_LOC.FLD_TAGS)));
        locInfo.setMoreInfo(
            LocationMoreInfo.mapMoreInfoField(rs.getString(TBL_USER_LOC.FLD_MORE_INFO)));
        locInfo.setDate(rs.getTimestamp(TBL_USER_LOC.FLD_DATE));

        list.add(locInfo);
      }

      con.createStatement().execute(updateSQL);

    } catch (Throwable ex) {
      ex.printStackTrace();
      throw new DAOException(ex.getMessage(), ex);
    } finally {
      ResourceManagement.releaseResource(rs);
      ResourceManagement.releaseResource(ps);
      ResourceManagement.releaseResource(con);
    }
    return list;
  }