Esempio n. 1
0
  /**
   * FindById Searches the database for a Group object by the object id.
   *
   * @param connection The jdbc connection
   * @param id The id to select by
   */
  public static Post FindByID(Long post_ID) throws SQLException {
    Post post = null;
    PreparedStatement findPostId = null;
    Connection conn = null;

    String sql = String.format("SELECT * from %s where postid = ?", Post.dquote(POST), post_ID);

    if (post_ID != null) {
      try {
        conn = DB.getConnection();
        findPostId = conn.prepareStatement(sql);
        findPostId.setLong(1, post_ID);

        ResultSet rs = findPostId.executeQuery();

        if (rs.next()) {
          post =
              new Post(
                  rs.getString(Post.CONTENT),
                  rs.getString(Post.TYPE),
                  rs.getTimestamp(Post.TIMESTAMP),
                  rs.getLong(Post.USER_ID),
                  rs.getLong(Post.WALL_ID),
                  rs.getLong(Post.POST_ID));
        }

        findPostId.close();
        conn.close();
        return post;
      } catch (SQLException e) {
        Logger.debug("Error retrieving post.", e);
      }
    }
    return post;
  }
Esempio n. 2
0
  public static void deletePost(Long post_ID) throws SQLException {
    Post post = null;

    String deletePostStr =
        String.format("DELETE from %s where postid = ?", Post.dquote(POST), post_ID);

    Connection conn = null;
    PreparedStatement deleteP = null;

    if (post_ID != null) {
      try {
        conn = DB.getConnection();
        deleteP = conn.prepareStatement(deletePostStr);
        ResultSet rs = deleteP.executeQuery();

        if (rs.next()) {
          Logger.debug("Failed to delete the Post.");
        }
        deleteP.close();
        conn.close();
      } catch (SQLException e) {
        Logger.debug("Failed while trying to delete the post.");
      }
    } else {
      Logger.debug("Post id is null.");
    }
  }
Esempio n. 3
0
  public static ResultSet getAllNewVolume(EnumDataSet workingSet) {
    Connection connection2 = DB.getConnection();
    ResultSet rs = null;
    PreparedStatement preparedStatement = null;

    try {
      if (workingSet.equals(EnumDataSet.TWEETS)) {
        preparedStatement =
            connection2.prepareStatement(
                "SELECT \"UniqueTweets\" FROM \"FinalProject\".\"TweetsInInterval\" WHERE \"DateTimeStart\" > '2013-06-23 19:18:25.025+01' ORDER BY \"DateTimeFinish\" ASC");
      } else if (workingSet.equals(EnumDataSet.RETWEETS)) {
        preparedStatement =
            connection2.prepareStatement(
                "SELECT \"UniqueReTweets\" FROM \"FinalProject\".\"ReTweetsInInterval\" WHERE \"DateTimeStart\" > '2013-06-23 19:18:25.025+01' ORDER BY \"DateTimeFinish\" ASC");
      }

      rs = preparedStatement.executeQuery();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }

    try {
      connection2.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return rs;
  }
 public static void contents(Integer convID, Integer fromMsgID) {
   if (fromMsgID == null) {
     fromMsgID = 0;
   }
   Connection c = DB.getConnection();
   try {
     PreparedStatement stmt =
         c.prepareStatement(
             "select M.id, UR.name, U.id, M.contents, M.timeSent from `Person` as U, `Conversations` as C, `Messages` as M, `UserRoles` as UR "
                 + "where UR.id = U.role_id and C.id=M.conversation_id and U.id=M.user_id and C.id = ? and M.id>? "
                 + "ORDER BY M.id");
     stmt.setInt(1, convID);
     stmt.setInt(2, fromMsgID);
     ResultSet rs = stmt.executeQuery();
     rs.beforeFirst();
     ArrayList<ChatMessage> msgdata = new ArrayList<ChatMessage>();
     Integer maxID = fromMsgID;
     while (rs.next()) {
       ChatMessage msg =
           new ChatMessage(
               rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
       if (msg.name.equals(Security.connected())) {
         msg.name = "You";
         msg.role = "";
       }
       maxID = Math.max(maxID, msg.id);
       msgdata.add(msg);
     }
     render(msgdata, maxID);
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }
Esempio n. 5
0
  public static ResultSet getTopTweetedUsers(String topTweetorAvg) {
    Connection connection2 = DB.getConnection();
    ResultSet rs = null;
    PreparedStatement preparedStatement;

    try {
      if (topTweetorAvg.equals("TOP")) {
        preparedStatement =
            connection2.prepareStatement(
                "SELECT \"User\", \"UserName\", \"Total No. Of Tweets\", \"Total Retweets\", \"AverageRetweets\", \"Followers\", \"Followees\", \"Location\", \"Timezone\"  FROM \"FinalProject\".\"total_retweets_for_user\" ORDER BY \"Total Retweets\" DESC LIMIT 60");
      } else {
        preparedStatement =
            connection2.prepareStatement(
                "SELECT \"User\", \"UserName\", \"Followers\", \"Followees\", \"Location\", \"Timezone\"  FROM \"FinalProject\".\"total_retweets_for_user\" ORDER BY \"Average Retweets\" DESC LIMIT 60");
      }
      rs = preparedStatement.executeQuery();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    try {
      connection2.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return rs;
  }
Esempio n. 6
0
  public static ResultSet getLatestTweets() {
    Connection connection2 = DB.getConnection();
    ResultSet rs = null;

    try {
      PreparedStatement preparedStatement =
          connection2.prepareStatement(
              "("
                  + "SELECT \"UserName\", \"DateTime\", \"Blog\" "
                  + "FROM \"FinalProject\".\"ReTweets\" "
                  + "ORDER BY \"DateTime\" DESC "
                  + "LIMIT 20"
                  + ")"
                  + "UNION"
                  + "("
                  + "SELECT \"UserName\", \"DateTime\", \"Blog\" "
                  + "FROM \"FinalProject\".\"Tweets\" "
                  + "ORDER BY \"DateTime\" DESC "
                  + "LIMIT 20"
                  + ")"
                  + "ORDER BY \"DateTime\" DESC "
                  + "LIMIT 10"
                  + ";");
      rs = preparedStatement.executeQuery();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    try {
      connection2.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return rs;
  }
Esempio n. 7
0
 public static ResultSet executeList(String sql, List<Object> params) throws SQLException {
   PreparedStatement pst =
       DB.getConnection()
           .prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
   int index = 0;
   for (Object param : params) {
     pst.setObject(++index, param);
   }
   return pst.executeQuery();
 }
Esempio n. 8
0
  private static Result publishPrivateTweet(String owner_id, String content, String device) {
    TweetResult tweetResult;

    content = TextContentUtil.processTweetContent(content);

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    String insertTweet =
        "INSERT INTO t_private_tweet (owner_id, create_at, sysversion, content, device) VALUES( '%s', now(), 'mac', '%s', '%s')";
    String queryTweet = "SELECT * FROM t_private_tweet where id = ";
    String queryLastRowId = "SELECT LAST_INSERT_ID()";
    content = content.replaceAll("'", "''");
    device = device.replaceAll("'", "''");
    insertTweet = String.format(insertTweet, owner_id, content, device);
    long rowId;
    try {
      connection = DB.getConnection();
      statement = connection.createStatement();
      statement.executeUpdate(insertTweet);

      resultSet = statement.executeQuery(queryLastRowId);
      resultSet.next();
      rowId = resultSet.getLong("LAST_INSERT_ID()");

      resultSet = statement.executeQuery(queryTweet + rowId);
      resultSet.next(); // attention
      Maopao maopao = new Maopao(resultSet);
      tweetResult = new TweetResult(0, maopao);

    } catch (SQLException e) {
      e.printStackTrace();
      tweetResult = new TweetResult(-1, null);

    } finally {
      try {
        if (resultSet != null) {
          resultSet.close();
        }
        if (statement != null) {
          statement.close();
        }
        if (connection != null) {
          connection.close();
        }
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    return ok(Json.toJson(tweetResult));
  }
Esempio n. 9
0
  public static boolean isAlreadyPresent(String givenUrl) throws SQLException {
    //		System.out.println("checking given url  : " + givenUrl);
    Connection connection = DB.getConnection();
    String query = "select fbPageId from FBPage where givenUrl = ?";
    PreparedStatement statement = connection.prepareStatement(query);
    statement.setString(1, givenUrl);
    ResultSet rs = statement.executeQuery();

    boolean present = false;
    if (rs.first()) {
      present = true;
    }

    rs.close();
    connection.close();
    return present;
  }
Esempio n. 10
0
  public static ResultSet getAvgRetweetsForUser() {
    Connection connection2 = DB.getConnection();
    ResultSet rs = null;

    try {
      PreparedStatement preparedStatement =
          connection2.prepareStatement(
              "SELECT \"AverageRetweets\"  FROM \"FinalProject\".\"total_retweets_for_user\" ORDER BY \"AverageRetweets\" ASC");
      rs = preparedStatement.executeQuery();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    try {
      connection2.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return rs;
  }
Esempio n. 11
0
  /** Persist Saves the post to the database, this function will create a new database connection */
  public void Persist() {

    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      conn = DB.getConnection();
      String sql = "UPDATE " + User.dquote(Post.POST);
      sql += " set " + Post.CONTENT + " = ?, ";
      sql += Post.TYPE + " = ?, ";
      sql += Post.TIMESTAMP + " = ?, ";
      sql += Post.USER_ID + " = ?, ";
      sql += Post.WALL_ID + " = ?, ";
      sql += "where " + Post.POST_ID + " = ?";

      Logger.debug("Generated update: [%s]", sql);
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, this.content);
      pstmt.setString(2, this.type);
      pstmt.setTimestamp(3, this.time_stamp);
      pstmt.setLong(4, this.user_ID);
      pstmt.setLong(5, this.wall_ID);
      pstmt.setLong(6, this.post_ID);

      pstmt.executeUpdate();

      pstmt.close();
      conn.close();
    } catch (SQLException e) {
      // Attempt to close the connection
      Logger.debug("Error while persisting Post");
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception x) {
          Logger.debug("Error while closing connection during exception", x);
        }
      }
    }
  }
Esempio n. 12
0
  /**
   * Busca un usuario por su documento
   *
   * @param documento String documento el usuario
   * @return objeto de tipo Usuario encontrado o null en caso de que no se encuentre un usuario con
   *     ese documento.
   */
  public static Usuario findByDocumento(String documento) {
    Connection con = DB.getConnection();
    Usuario usuario = null;
    PreparedStatement p;
    try {
      p = con.prepareStatement(consultaBuscarPorDocumento);

      p.setString(1, documento);

      ResultSet rs = p.executeQuery();
      if (rs.next()) {
        usuario =
            new Usuario(rs.getString("nombre"), rs.getString("documento"), rs.getString("id_rol"));
      }
      con.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.out.println("Excepcion : " + e.getMessage());
      System.out.println(e.getLocalizedMessage());
    }
    return usuario;
  }
Esempio n. 13
0
  /**
   * Método que devuelve una lista de Usuarios realizando una búsqueda por el nombre o parte del
   * nombre. Se utiliza para la función de autocompletar en la interfaz.
   *
   * @param termino String con el nombre o parte el nombre del usuario a buscar
   * @return Lista de usuarios que cumplen el criterio de búsqueda.
   */
  public static List<Usuario> findAllByNombre(String termino) {
    Connection con = DB.getConnection();
    List<Usuario> usuarios = new ArrayList<Usuario>();
    PreparedStatement p;
    try {
      p = con.prepareStatement(consultaUsuarios);

      p.setString(1, "%%" + termino + "%%");

      ResultSet rs = p.executeQuery();
      while (rs.next()) {
        usuarios.add(new Usuario("", rs.getString("nombres"), "", rs.getString("documento"), ""));
      }
      con.close();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.out.println("Excepcion : " + e.getMessage());
      System.out.println(e.getLocalizedMessage());
    }

    return usuarios;
  }
Esempio n. 14
0
  public static Result privateTweets(long last_id, String sort, int limit) {
    String result = "";
    List<Maopao> maopaos = null;
    Maopao maopao;
    MaopaoList maopaoList = new MaopaoList();

    String uid = session("id");

    if (uid == null || uid.length() == 0) {
      maopaoList.setCode(1000);
      return ok(Json.toJson(maopaoList));
    }

    String orderBy = "id";
    if ("hot".equals(sort)) {
      orderBy = "comment_count";
    }

    if (limit <= 0) {
      limit = 30;
    }

    Connection connection = null;
    Statement maopaoStatement = null;
    Statement commentStatement = null;
    Statement likeUserStatement = null;
    Statement ownerStatement = null;
    ResultSet maopaoResultSet = null;
    ResultSet commentResultSet = null;
    ResultSet likeUsersResultSet = null;
    ResultSet ownerResultSet = null;
    long tweetId;
    long maxTweetId;
    String where = " owner_id = " + uid + " ";
    String tableComment = "t_comment";
    String tableMaopao = "t_private_tweet";
    try {
      connection = DB.getConnection();
      maopaoStatement = connection.createStatement();
      commentStatement = connection.createStatement();
      likeUserStatement = connection.createStatement();
      ownerStatement = connection.createStatement();

      maxTweetId = DBUtil.queryMaxId(maopaoStatement, tableMaopao);

      if (last_id > maxTweetId) {
        maopaoResultSet =
            DBUtil.queryLastRecord(maopaoStatement, tableMaopao, where, orderBy, limit);
      } else {
        maopaoResultSet =
            DBUtil.queryLessLastRecord(
                maopaoStatement, tableMaopao, where, orderBy, "" + last_id, limit);
      }

      maopaos = new ArrayList<Maopao>();
      while (maopaoResultSet.next()) {
        maopao = new Maopao(maopaoResultSet);
        tweetId = Long.parseLong(maopao.id);

        String userSql = "SELECT * FROM t_user WHERE id = " + maopao.owner_id;
        ownerResultSet = ownerStatement.executeQuery(userSql);
        ownerResultSet.next();
        maopao.owner = new UserObject(ownerResultSet);

        if (maopao.likes > 0) {
          maopao.like_users = new ArrayList<UserObject>();

          String sql =
              "SELECT * FROM t_user WHERE id IN (SELECT owner_id FROM t_like_tweet WHERE tweet_id = '%s')";
          sql = String.format(sql, tweetId);
          likeUsersResultSet = likeUserStatement.executeQuery(sql);

          UserObject userObject;
          while (likeUsersResultSet.next()) {
            userObject = new UserObject(likeUsersResultSet);
            maopao.like_users.add(userObject);
            if (userObject.id.equals(session("id"))) {
              maopao.liked = true;
            }
          }
        }
        if (maopao.comments > 0) {
          maopao.comment_list = new ArrayList<BaseComment>();
          String sql =
              "SELECT t_user.id, t_user.name, t_user.head_url, t_comment.id, t_comment.content, t_comment.create_at, t_user.created_at "
                  + "FROM t_user INNER JOIN t_comment ON t_user.id = t_comment.owner_id WHERE tweet_id = %s";
          sql = String.format(sql, tweetId);
          BaseComment comment;
          commentResultSet = commentStatement.executeQuery(sql);
          while (commentResultSet.next()) {
            // BaseComment(String id, String owner_id, String tweet_id, String content, String
            // created_at)
            comment =
                new BaseComment(
                    commentResultSet.getString(4),
                    commentResultSet.getString(1),
                    tweetId + "",
                    commentResultSet.getString(5),
                    commentResultSet.getTimestamp(6).getTime());
            // UserObject(long id, String name, String headImgUrl, long created_at){
            comment.owner =
                new UserObject(
                    commentResultSet.getLong(1),
                    commentResultSet.getString(2),
                    commentResultSet.getString(3),
                    commentResultSet.getLong(7));
            maopao.comment_list.add(comment);
          }
        }
        maopaos.add(maopao);
      }

      maopaoList.setCode(0);
      maopaoList.setData(maopaos);
    } catch (SQLException e) {
      e.printStackTrace();
      maopaoList.setCode(-1);
      maopaoList.setData(null);
    } finally {
      try {
        if (maopaoResultSet != null) {
          maopaoResultSet.close();
        }
        if (commentResultSet != null) {
          commentResultSet.close();
        }

        if (maopaoStatement != null) {
          maopaoStatement.close();
        }
        if (commentStatement != null) {
          commentStatement.close();
        }

        if (likeUsersResultSet != null) {
          likeUsersResultSet.close();
        }
        if (likeUserStatement != null) {
          likeUserStatement.close();
        }

        if (connection != null) {
          connection.close();
        }

      } catch (SQLException e) {

      }
    }

    return ok(Json.toJson(maopaoList));
  }
Esempio n. 15
0
 public static Connection getConnection() {
   Connection connection = DB.getConnection();
   createURLTable(connection);
   return connection;
 }
Esempio n. 16
0
 public QueryDB() {
   connection = DB.getConnection();
 }