Exemplo n.º 1
0
  /**
   *
   *
   * <pre>
   * 사용자의 service_Infra_id 값을 읽음
   * SELECT service_Infra_id FROM user WHERE userid=?
   * </pre>
   */
  @Override
  public String readUserServiceInfra(String userId) throws Exception {

    String serviceInfra = null;

    String sql = "SELECT service_Infra_id FROM user WHERE userid=?";
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, userId);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      if (rs.next()) {
        serviceInfra = rs.getString(1);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }
    return serviceInfra;
  }
  @Override
  public boolean linkExists(LinkCrawled linkCrawled) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {linkCrawled.getLink()};

      preparedStatement =
          DAOUtil.prepareStatement(connection, this.SQL_CHECK_EXISTS, false, values);

      resultSet = preparedStatement.executeQuery();

      if (resultSet.next()) {
        int numVal = resultSet.getInt("count");

        if (numVal > 0) {
          return true;
        } else {
          return false;
        }
      }

      return false;
    } catch (final SQLException e) {
      LOG.error("Update link_crawled_table fails, " + e.getMessage());

      return false;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 3
0
  /**
   *
   *
   * <pre>
   * 주어진 사용자명(string)에 대한 id 값을 구함
   * SELECT id FROM user WHERE userid=?
   * </pre>
   */
  @Override
  public int readUserId(String userId) throws Exception {
    int id = -1;

    // Connection conn = DAOUtil.getConnection();
    String sql = "SELECT id FROM user WHERE userid=?";
    // String sql = "SELECT id FROM user WHERE name=?";
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, userId);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      if (rs.next()) {
        id = Integer.parseInt(rs.getString(1));
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
      // DAOUtil.closeJDBCConnection(conn);
    }
    return id;
  }
  @Override
  public boolean deActivate(String locationUrl, int maxLinkAge) throws SQLException {
    if (locationUrl == null) {
      return false;
    }

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      String matchLocationLinkString = locationUrl + "%";
      String pastDate = Helper.getPastDate(maxLinkAge);
      final Object[] values = {matchLocationLinkString, pastDate};

      preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_DEACTIVATE, false, values);

      Globals.crawlerLogManager.writeLog(preparedStatement.toString());

      preparedStatement.executeUpdate();

      return true;
    } catch (final SQLException e) {
      Globals.crawlerLogManager.writeLog(
          "Fails to deactivate link " + locationUrl + " in posting_location");
      Globals.crawlerLogManager.writeLog(e.getMessage());

      return false;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
  @Override
  public List<PostingLocation> getActive(int active) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {active};

      preparedStatement =
          DAOUtil.prepareStatement(connection, this.SQL_SELECT_BY_ACTIVE, false, values);
      resultSet = preparedStatement.executeQuery();

      final List<PostingLocation> locations = new ArrayList<PostingLocation>();
      while (resultSet.next()) {
        final PostingLocation location = this.constructPostingLocationObject(resultSet);
        locations.add(location);
      }

      return locations;
    } catch (final SQLException e) {
      Globals.crawlerLogManager.writeLog(
          "Get posting_location where active = " + active + " fails");
      Globals.crawlerLogManager.writeLog(e.getMessage());

      return null;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 6
0
  /**
   *
   *
   * <pre>
   * 사용자의 KeepAgentNO 값을 구함
   * SELECT keepAgentNO FROM user WHERE userid=?
   * </pre>
   */
  @Override
  public int readUserKeepAgentNO(String userId) throws Exception {
    int keepAgentNO = -1;

    String sql = "SELECT keepAgentNO FROM user WHERE userid=?";
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, userId);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      if (rs.next()) {
        keepAgentNO = rs.getInt(1);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }
    return keepAgentNO;
  }
Exemplo n.º 7
0
  /**
   *
   *
   * <pre>
   * 사용자아이디가 같은 레코드이 개수를 카운트
   * SELECT COUNT(*) AS cn FROM user WHERE userid=?
   * </pre>
   */
  @Override
  public boolean checkUser(String userId) throws Exception {

    String sql = "SELECT COUNT(*) AS cn FROM user WHERE userid=?";
    ResultSet rs = null;
    PreparedStatement stmt = null;
    int a = -1;

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, userId);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      if (rs.next()) {
        a = rs.getInt(1);
      }
      if (a > 0) {
        return true;
      } else {
        return false;
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }
  }
Exemplo n.º 8
0
  /**
   *
   *
   * <pre>
   * </pre>
   */
  @Override
  public void updateUserInfo(User usr) throws Exception {
    String sql = "UPDATE user set passwd=? , dn=? , service_Infra_id=? , name =? WHERE userid = ? ";
    ResultSet rs = null;
    PreparedStatement stmt = null;

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, usr.getPw());
      stmt.setString(2, usr.getDN());
      stmt.setString(3, usr.getServiceInfraID());
      stmt.setString(4, usr.getName());
      stmt.setString(5, usr.getUserID());
      // int rows = stmt.executeUpdate();
      int rows = _update(stmt);

      if (rows != 1) {
        throw new SQLException();
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }
  }
  @Override
  public List<LinkCrawled> get(int domainId) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();
      preparedStatement =
          DAOUtil.prepareStatement(connection, this.SQL_SELECT_BY_DOMAINID, false, domainId);
      resultSet = preparedStatement.executeQuery();

      final List<LinkCrawled> linksCrawled = new ArrayList<LinkCrawled>();
      while (resultSet.next()) {
        final LinkCrawled linkCrawled = this.constructLinkCrawledObject(resultSet);
        linksCrawled.add(linkCrawled);
      }

      return linksCrawled;
    } catch (final SQLException e) {
      LOG.error("Get link_crawled_table fails, " + e.getMessage());

      return null;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 10
0
  @Override
  public boolean update(LinkCrawled linkCrawled) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    final ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {
        linkCrawled.getLink(), linkCrawled.getPriority(), linkCrawled.getId()
      };

      preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_UPDATE, false, values);

      preparedStatement.executeUpdate();

      return true;
    } catch (final SQLException e) {
      LOG.error("Update link_crawled_table fails, " + e.getMessage());

      return false;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 11
0
  /**
   *
   *
   * <pre>
   * </pre>
   */
  @Override
  public List<String> getUserList() throws Exception {

    List<String> usrList = new ArrayList<String>();

    String sql = "SELECT userid FROM user";

    ResultSet rs = null;
    PreparedStatement stmt = null;

    try {
      stmt = conn.prepareStatement(sql);
      // rs = stmt.executeQuery();
      rs = _query(stmt);

      while (rs.next()) {
        usrList.add(rs.getString(1));
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }

    return usrList;
  }
Exemplo n.º 12
0
  /**
   *
   *
   * <pre>
   * </pre>
   */
  @Override
  public void setUserPassword(String userId, String pw) throws Exception {

    String sql = "UPDATE user set passwd = ? WHERE userid = ? ";
    ResultSet rs = null;
    PreparedStatement stmt = null;

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, pw);
      stmt.setString(2, userId);
      // int rows = stmt.executeUpdate();
      int rows = _update(stmt);

      if (rows != 1) {
        throw new SQLException();
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }
  }
  @Override
  public boolean activate(String url) throws SQLException {
    if (url == null) {
      return false;
    }

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {url};

      preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_ACTIVATE, false, values);

      Globals.crawlerLogManager.writeLog(preparedStatement.toString());

      preparedStatement.executeUpdate();

      return true;
    } catch (final SQLException e) {
      Globals.crawlerLogManager.writeLog("Fails to activate link " + url + " in posting_location");
      Globals.crawlerLogManager.writeLog(e.getMessage());

      return false;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 14
0
 public static void main(String arg[]) {
   Connection conn = DAOUtil.getConnection();
   try {
     UserDAOImpl userDAO = new UserDAOImpl(conn);
     List<User> aa = userDAO.getUserObjectList();
     for (User user : aa) {
       System.out.println(user.toString());
     }
     DAOUtil.doCommit(conn);
   } catch (Exception e) {
     e.printStackTrace();
     DAOUtil.doRollback(conn);
   }
 }
Exemplo n.º 15
0
  @Override
  public int create(LinkCrawled linkCrawled) throws SQLException {
    if (linkCrawled.getLink() == null) {
      return -1;
    }

    // If the time crawled is not specified, use the current time
    if (linkCrawled.getTimeCrawled() == null || linkCrawled.getDateCrawled() == null) {
      linkCrawled.setTimeCrawled(Helper.getCurrentTime());
      linkCrawled.setDateCrawled(Helper.getCurrentDate());
    }

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {
        linkCrawled.getLink(), linkCrawled.get_originalLink(),
        linkCrawled.getPriority(), linkCrawled.getDomainTableId1(),
        linkCrawled.get_downloadDuration(), linkCrawled.get_extractedTime(),
        linkCrawled.get_httpStatusCode(), linkCrawled.get_relevance(),
        linkCrawled.get_distanceFromRelevantPage(), linkCrawled.get_freshness(),
        linkCrawled.getTimeCrawled(), linkCrawled.getDateCrawled()
      };

      preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_INSERT, true, values);

      preparedStatement.executeUpdate();

      // Get the generated key (id)
      resultSet = preparedStatement.getGeneratedKeys();
      int generatedKey = -1;

      if (resultSet.next()) {
        generatedKey = resultSet.getInt(1);
      }

      return generatedKey;
    } catch (final SQLException e) {
      LOG.error("Insert into link_crawled_table fails, " + e.getMessage());

      return -1;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
  @Override
  public boolean create(PostingLocation location) throws SQLException {
    if (!location.isValid()) {
      return false;
    }

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {
        location.getState(),
        location.getCity(),
        location.getLatitude(),
        location.getLongitude(),
        location.getLocation_fk(),
        location.getLocation_link_fk(),
        location.getDatePosted(),
        location.getTimePosted(),
        location.getPosting_body(),
        location.getTitle(),
        location.getUrl(),
        location.getActive(),
        location.getEmail()
      };

      preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_INSERT, false, values);

      Globals.crawlerLogManager.writeLog(preparedStatement.toString());

      preparedStatement.executeUpdate();

      return true;
    } catch (final SQLException e) {
      Globals.crawlerLogManager.writeLog("Insert into table posting_location fails");
      Globals.crawlerLogManager.writeLog(e.getMessage());

      return false;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 17
0
  /**
   *
   *
   * <pre>
   * 사용자를 추가함 (dn, name, userid, passwd 등의 정보를 넣는다.)
   * INSERT INTO user (dn, name, userid, passwd, service_Infra_id) VALUES (?, ?, ?, ?, ?)
   * </pre>
   *
   * @return 입력된 사용자의 id 값
   */
  @Override
  public int createUser(String dn, String name, String userId, String passwd, String infraMetric)
      throws Exception {

    String sql =
        "INSERT INTO user (dn, name, userid, passwd, service_Infra_id) VALUES (?, ?, ?, ?, ?)";
    ResultSet rs = null;
    PreparedStatement stmt = null;
    int id = -1;

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, dn);
      stmt.setString(2, name);
      stmt.setString(3, userId);
      stmt.setString(4, passwd);
      stmt.setString(5, infraMetric);
      // int rows = stmt.executeUpdate();
      int rows = _update(stmt);
      if (rows != 1) {
        throw new SQLException();
      }

      sql = "SELECT LAST_INSERT_ID()";
      stmt = conn.prepareStatement(sql);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      if (rs.next()) {
        id = rs.getInt(1);
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }

    return id;
  }
Exemplo n.º 18
0
  public int execute() {
    int id = -1;
    String sql =
        this.sql.toString().replace("${_where_}", this.condition.toString()).replace("'?'", "?");
    DataSource ds = DataSourceWrapCache.get(dsName);
    try {
      int rs = 0;
      if (args != null && args.size() > 0) {
        rs =
            (Integer)
                JdbcUtil.updateWithArgs(ds.getConnection(), sql, args.toArray(new Object[] {}));
      } else {
        rs = (Integer) JdbcUtil.update(ds.getConnection(), sql);
      }

      if (rs > 0 && sql.contains("INSERT INTO")) {
        if (Map.class.isAssignableFrom(clazz)) {
          if (map == null) {
            map = new HashMap<String, Object>();
            map.put("idColumn", "id");
            map.put("table", this.table);
          } else if (map.get("idColumn") == null) {
            map.put("idColumn", "id");
          }

          id = (Integer) DAOUtil.selectMaxId(map, ds.getConnection(), dbType);
        } else {
          id = (Integer) DAOUtil.selectMaxId(clazz, ds.getConnection(), dbType);
        }
      }

    } catch (SQLException e) {
      log.error("sql-->" + sql + "exception:" + StringUtil.getExceptionString(e));
      throw new DAOException(sql + " execute exception", e);
    }

    // this.clear();
    return id;
  }
Exemplo n.º 19
0
  /**
   *
   *
   * <pre>
   * 주어진 id의 사용자를 삭제
   * DELETE FROM user WHERE id=?
   * </pre>
   */
  @Override
  public void deleteUser(int id) throws Exception {

    String sql = "DELETE FROM user WHERE id=?";
    ResultSet rs = null;
    PreparedStatement stmt = null;

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setInt(1, id);
      // int rows = stmt.executeUpdate();
      int rows = _update(stmt);
      if (rows != 1) {
        throw new SQLException();
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }
  }
Exemplo n.º 20
0
  /**
   *
   *
   * <pre>
   * </pre>
   */
  @Override
  public List<User> getUserObjectList() throws Exception {

    List<User> usrList = new ArrayList<User>();
    User usr = null;

    String sql = "SELECT * FROM user";
    PreparedStatement stmt = null;
    ResultSet rs = null;

    try {
      stmt = conn.prepareStatement(sql);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      while (rs.next()) {
        usr = new User();
        usr.setId(rs.getInt("id"));
        usr.setDN(rs.getString("dn"));
        usr.setUserID(rs.getString("userid"));
        usr.setPw(rs.getString("passwd"));
        usr.setName(rs.getString("name"));
        usr.setServiceInfraID(rs.getString("service_Infra_id"));
        usr.setUsergroupId(rs.getInt("ug_id"));
        usrList.add(usr);
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }

    return usrList;
  }
Exemplo n.º 21
0
  /**
   *
   *
   * <pre>
   * </pre>
   */
  @Override
  public User getUserInfo(String userId) throws Exception {

    String sql = "SELECT * FROM user WHERE userid=?";
    ResultSet rs = null;
    PreparedStatement stmt = null;
    User usr = new User();

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setString(1, userId);

      // rs = stmt.executeQuery();
      rs = _query(stmt);

      if (rs.next()) {
        usr.setId(rs.getInt("id"));
        usr.setUserID(rs.getString("userid"));
        usr.setName(rs.getString("name"));
        usr.setServiceInfraID(rs.getString("service_Infra_id"));
        usr.setDN(rs.getString("dn"));
        usr.setPw(rs.getString("passwd"));
        usr.setKeepAgentNo(rs.getInt("KeepAgentNO"));
        usr.setUsergroupId(rs.getInt("ug_id"));
        usr.setOtpflag(rs.getInt("otp_flag"));
        usr.setShared(rs.getBoolean("shared"));
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
      DAOUtil.closeResultSet(rs);
    }

    return usr;
  }
  @Override
  public boolean update(PostingLocation postingLocation) throws SQLException {
    if (postingLocation == null) {
      return false;
    }

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {
        postingLocation.getEmail(), postingLocation.getActive(), postingLocation.getLocation_fk()
      };

      preparedStatement = DAOUtil.prepareStatement(connection, this.SQL_UPDATE, false, values);

      Globals.crawlerLogManager.writeLog(preparedStatement.toString());

      preparedStatement.executeUpdate();

      return true;
    } catch (final SQLException e) {
      Globals.crawlerLogManager.writeLog(
          "Fails to update email "
              + postingLocation.getEmail()
              + " in posting_location for id "
              + postingLocation.getLocation_fk());
      Globals.crawlerLogManager.writeLog(e.getMessage());

      return false;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
  @Override
  public PostingLocation get(int locationId) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
      connection = this.daoFactory.getConnection();

      final Object[] values = {locationId};

      preparedStatement =
          DAOUtil.prepareStatement(connection, this.SQL_SELECT_BY_ID, false, values);
      resultSet = preparedStatement.executeQuery();

      PostingLocation locations = null;
      if (resultSet.next()) {
        locations = new PostingLocation();
        locations = this.constructPostingLocationObject(resultSet);
      }

      if (resultSet.next()) {
        Globals.crawlerLogManager.writeLog(
            "There are two locations with the same id " + locationId);
        throw new Exception("There are two locations with the same id");
      }

      return locations;
    } catch (final SQLException e) {
      Globals.crawlerLogManager.writeLog("Get posting_location fails");
      Globals.crawlerLogManager.writeLog(e.getMessage());

      return null;
    } finally {
      DAOUtil.close(connection, preparedStatement, resultSet);
    }
  }
Exemplo n.º 24
0
  /**
   *
   *
   * <pre>
   * </pre>
   */
  @Override
  public void updateUserOtpFlag(String userId, int otp_flag) throws Exception {

    String sql = "UPDATE user set otp_flag = ? WHERE userid = ? ";
    PreparedStatement stmt = null;

    try {
      stmt = conn.prepareStatement(sql);
      stmt.setInt(1, otp_flag);
      stmt.setString(2, userId);
      // int rows = stmt.executeUpdate();
      int rows = _update(stmt);

      if (rows != 1) {
        throw new SQLException();
      }

    } catch (SQLException e) {
      throw e;
    } finally {
      DAOUtil.closeStatement(stmt);
    }
  }