コード例 #1
0
  @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;
  }
コード例 #2
0
  @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;
  }
コード例 #3
0
 @Override
 public boolean insert(Comment comment) {
   String sql = "INSERT INTO TBLCOMMENT VALUES(nextval('seq_comment'), NOW(), ?, ?, ?,0)";
   try (Connection cnn = dataSource.getConnection();
       PreparedStatement ps = cnn.prepareStatement(sql); ) {
     ps.setString(1, comment.getCommentText());
     ps.setInt(2, Integer.parseInt(Encryption.decode(comment.getVideoId())));
     ps.setInt(3, Integer.parseInt(Encryption.decode(comment.getUserId())));
     if (ps.executeUpdate() > 0) {
       return true;
     }
   } catch (SQLException e) {
     e.printStackTrace();
   } catch (NumberFormatException e1) {
     System.out.println(e1.getMessage());
     return false;
   }
   return false;
 }
コード例 #4
0
 @Override
 public int insertReturnId(Comment comment) {
   String sql = "INSERT INTO TBLCOMMENT VALUES(nextval('seq_comment'), NOW(), ?, ?, ?,0)";
   try (Connection cnn = dataSource.getConnection();
       PreparedStatement ps =
           cnn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS); ) {
     ps.setString(1, comment.getCommentText());
     ps.setInt(2, Integer.parseInt(Encryption.decode(comment.getVideoId())));
     ps.setInt(3, Integer.parseInt(Encryption.decode(comment.getUserId())));
     if (ps.executeUpdate() > 0) {
       ResultSet rs = ps.getGeneratedKeys();
       rs.next();
       return rs.getInt(1);
     }
   } catch (SQLException e) {
     e.printStackTrace();
   } catch (NumberFormatException e1) {
     System.out.println(e1.getMessage());
     return 0;
   }
   return 0;
 }
コード例 #5
0
 @Override
 public boolean update(Comment comment) {
   String sql =
       "UPDATE TBLCOMMENT SET commenttext=?, videoid=?, userid=?, replycomid=? WHERE commentid=?";
   try (Connection cnn = dataSource.getConnection();
       PreparedStatement ps = cnn.prepareStatement(sql); ) {
     ps.setString(1, comment.getCommentText());
     ps.setInt(2, Integer.parseInt(Encryption.decode(comment.getVideoId())));
     ps.setInt(3, Integer.parseInt(Encryption.decode(comment.getUserId())));
     ps.setInt(4, Integer.parseInt(Encryption.decode(comment.getReplyId())));
     ps.setInt(5, Integer.parseInt(Encryption.decode(comment.getCommentId())));
     if (ps.executeUpdate() > 0) {
       return true;
     }
   } catch (SQLException e) {
     e.printStackTrace();
   } catch (NumberFormatException e1) {
     System.out.println(e1.getMessage());
     return false;
   }
   return false;
 }
コード例 #6
0
  @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;
  }