@Override
  public List<Comment> listComment(Pagination page) {
    String sql =
        "SELECT CM.*, V.videoname, U.username, U.userimageurl "
            + "FROM TBLCOMMENT CM "
            + "INNER JOIN TBLVIDEO V ON CM.videoid=V.videoid "
            + "INNER JOIN TBLUSER U ON CM.userid=U.userid "
            + "ORDER BY commentdate DESC OFFSET ? LIMIT ?";

    List<Comment> list = new ArrayList<Comment>();
    Comment comment = null;
    try (Connection cnn = dataSource.getConnection();
        PreparedStatement ps = cnn.prepareStatement(sql); ) {
      ps.setInt(1, page.offset());
      ps.setInt(2, page.getItem());
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        comment = new Comment();
        comment.setCommentId(Encryption.encode(rs.getString("commentid")));
        comment.setCommentDate(rs.getDate("commentdate"));
        comment.setCommentText(rs.getString("commenttext"));
        comment.setVideoId(Encryption.encode(rs.getString("videoid")));
        comment.setUserId(Encryption.encode(rs.getString("userid")));
        comment.setVideoName(rs.getString("videoname"));
        comment.setUsername(rs.getString("username"));
        comment.setUserImageUrl(rs.getString("userimageurl"));
        comment.setReplyId(Encryption.encode(rs.getString("replycomid")));
        list.add(comment);
      }
      return list;
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return null;
  }
  @Override
  public Comment getComment(String commentId) {
    String sql =
        "SELECT CM.*, V.videoname, U.username, U.userimageurl "
            + "FROM TBLCOMMENT CM "
            + "INNER JOIN TBLVIDEO V ON CM.videoid=V.videoid "
            + "INNER JOIN TBLUSER U ON CM.userid=U.userid "
            + "WHERE CM.commentid=?";

    Comment comment = null;
    try (Connection cnn = dataSource.getConnection();
        PreparedStatement ps = cnn.prepareStatement(sql); ) {
      ps.setInt(1, Integer.parseInt(Encryption.decode(commentId)));
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        comment = new Comment();
        comment.setCommentId(Encryption.encode(rs.getString("commentid")));
        comment.setCommentDate(rs.getDate("commentdate"));
        comment.setCommentText(rs.getString("commenttext"));
        comment.setVideoId(Encryption.encode(rs.getString("videoid")));
        comment.setUserId(Encryption.encode(rs.getString("userid")));
        comment.setVideoName(rs.getString("videoname"));
        comment.setUsername(rs.getString("username"));
        comment.setUserImageUrl(rs.getString("userimageurl"));
        comment.setReplyId(Encryption.encode(rs.getString("replycomid")));
      }
      return comment;
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (NumberFormatException e1) {
      System.out.println("Error");
      return null;
    }
    return null;
  }
  @Override
  public List<Comment> listReplyCommentOnVideo(String videoId) {
    String sql =
        "SELECT CM.*, V.videoname, U.username, U.userimageurl "
            + "FROM TBLCOMMENT CM "
            + "INNER JOIN TBLVIDEO V ON CM.videoid=V.videoid "
            + "INNER JOIN TBLUSER U ON CM.userid=U.userid "
            + "WHERE CM.videoid=? AND CM.replycomid is not null AND CM.replycomid <> 0 "
            + "ORDER BY commentdate DESC";

    List<Comment> list = new ArrayList<Comment>();
    Comment comment = null;
    try (Connection cnn = dataSource.getConnection();
        PreparedStatement ps = cnn.prepareStatement(sql); ) {
      ps.setInt(1, Integer.parseInt(Encryption.decode(videoId)));
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        comment = new Comment();
        comment.setCommentId(Encryption.encode(rs.getString("commentid")));
        comment.setCommentDate(rs.getDate("commentdate"));
        comment.setCommentText(rs.getString("commenttext"));
        comment.setVideoId(Encryption.encode(rs.getString("videoid")));
        comment.setUserId(Encryption.encode(rs.getString("userid")));
        comment.setUsername(rs.getString("username"));
        comment.setUserImageUrl(rs.getString("userimageurl"));
        comment.setReplyId(Encryption.encode(rs.getString("replycomid")));
        list.add(comment);
      }
      return list;
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (NumberFormatException e1) {
      System.out.println("ERROR covert");
      return null;
    }
    return null;
  }
Ejemplo n.º 4
0
 @RequestMapping(
     method = RequestMethod.POST,
     value = "/comment/reply/returnid",
     headers = "Accept=application/json")
 public ResponseEntity<Map<String, Object>> replyCommentReturnId(@RequestBody Comment comment) {
   Map<String, Object> map = new HashMap<String, Object>();
   try {
     int commentid = commentService.replyReturnId(comment);
     if (commentid > 0) {
       map.put("STATUS", true);
       map.put("COMMENTID", Encryption.encode(commentid + ""));
       map.put("MESSAGE", "RECORD HAS BEEN REPLYED");
     } else {
       map.put("STATUS", false);
       map.put("MESSAGE", "RECORD HAS NOT BEEN REPLYED");
     }
   } catch (Exception e) {
     map.put("MESSAGE", "OPERATION FAIL");
     map.put("STATUS", false);
   }
   return new ResponseEntity<Map<String, Object>>(map, HttpStatus.OK);
 }