@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> 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 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;
}
@Override
public int countReplyComment(String videoId, String replyId) {
String sql =
"SELECT COUNT(CM.commentid) "
+ "FROM TBLCOMMENT CM "
+ "WHERE CM.videoid=? and CM.replycomid=?";
try (Connection cnn = dataSource.getConnection();
PreparedStatement ps = cnn.prepareStatement(sql); ) {
ps.setInt(1, Integer.parseInt(Encryption.decode(videoId)));
ps.setInt(2, Integer.parseInt(Encryption.decode(replyId)));
ResultSet rs = ps.executeQuery();
if (rs.next()) return rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} catch (NumberFormatException e1) {
System.out.println(e1.getMessage());
return 0;
}
return 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;
}
@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;
}
@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;
}
@Override
public int countCommentOnVideo(String videoId) {
String sql = "SELECT COUNT(videoid) FROM TBLCOMMENT WHERE videoid=?";
try (Connection cnn = dataSource.getConnection();
PreparedStatement ps = cnn.prepareStatement(sql); ) {
ps.setInt(1, Integer.parseInt(Encryption.decode(videoId)));
ResultSet rs = ps.executeQuery();
if (rs.next()) return rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} catch (NumberFormatException e1) {
System.out.println("Error");
return 0;
}
return 0;
}
@Override
public boolean delete(String commentId) {
String sql = "DELETE FROM TBLCOMMENT WHERE commentid=?";
try (Connection cnn = dataSource.getConnection();
PreparedStatement ps = cnn.prepareStatement(sql); ) {
ps.setInt(1, Integer.parseInt(Encryption.decode(commentId)));
if (ps.executeUpdate() > 0) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (NumberFormatException e1) {
System.out.println("Error");
return false;
}
return false;
}
@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);
}