DB에 한 아이디로 여러번 좋아요 누르는 것을 방지하기 위해 liketable을 세움
좋아요를 눌렀을 때 liketable 을 insert 해주고 다시 좋아요를 눌러 취소했을 때 liketable을 delete 해줬다
CREATE SEQUENCE LIKE_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE LIKETABLE(
SEQ NUMBER(8) PRIMARY KEY,
BBS_CATEGORY NUMBER(8), //게시판 별로 번호 부여
TARGET_USER_SEQ NUMBER(8), // 유저의 시퀀스 값
FOREIGN KEY (TARGET_USER_SEQ) REFERENCES 유저테이블(seq),
TARGET_BBS_SEQ NUMBER(8) // 좋아요를 누른 게시판 시퀀스
);
public void likeTB_insert(int target_user_seq, int target_bbs_seq) {
String sql = "INSERT INTO LIKETABLE (SEQ, BBS_CATEGORY, TARGET_USER_SEQ, TARGET_BBS_SEQ) "
+ " VALUES(LIKE_SEQ.NEXTVAL, 3, ?, ?) ";
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = DBConnection.makeConnection();
System.out.println("2/6 likeTB_insert Success");
psmt = conn.prepareStatement(sql);
System.out.println("3/6 likeTB_insert Success");
psmt.setInt(1, target_user_seq);
psmt.setInt(2,target_bbs_seq);
System.out.println(sql);
psmt.executeUpdate();
System.out.println("4/6 likeTB_insert Success");
} catch (SQLException e) {
System.out.println("likeTB_insert fail");
System.out.println(e.getMessage());
System.out.println(e.getErrorCode());
System.out.println(e.getSQLState());
} finally {
DBClose.close(psmt, conn, rs);
}
}
public void likeTB_delete(int target_user_seq, int target_bbs_seq) {
String sql = " delete from liketable where BBS_CATEGORY = 3 AND target_user_seq = ? and target_bbs_seq = ? ";
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = DBConnection.makeConnection();
System.out.println("2/6 likeTB_delete Success");
psmt = conn.prepareStatement(sql);
System.out.println("3/6 likeTB_delete Success");
psmt.setInt(1, target_user_seq);
psmt.setInt(2,target_bbs_seq);
System.out.println(sql);
psmt.executeUpdate();
System.out.println("4/6 likeTB_delete Success");
} catch (SQLException e) {
System.out.println("likeTB_delete fail");
System.out.println(e.getMessage());
System.out.println(e.getErrorCode());
System.out.println(e.getSQLState());
} finally {
DBClose.close(psmt, conn, rs);
}
}public boolean Prevent_duplication(int target_user_seq, int target_bbs_seq) {
String sql = "select*from liketable where BBS_CATEGORY = 3 AND target_user_seq = ? and target_bbs_seq = ? ";
int count = 0;
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = DBConnection.makeConnection();
System.out.println("2/6 Prevent_duplication Success");
psmt = conn.prepareStatement(sql);
System.out.println("3/6 Prevent_duplication Success");
psmt.setInt(1, target_user_seq);
psmt.setInt(2,target_bbs_seq);
System.out.println(sql);
count = psmt.executeUpdate();
System.out.println("4/6 Prevent_duplication Success");
} catch (SQLException e) {
System.out.println("Prevent_duplication fail");
System.out.println(e.getMessage());
System.out.println(e.getErrorCode());
System.out.println(e.getSQLState());
count = -1;
} finally {
DBClose.close(psmt, conn, rs);
}
return count > 0 ? true : false;
}
들어가있지 않은 데이터면 true, 이미 들어가있으면 false를 출력하도록 하였다.
cotroller 부분
-좋아요 눌렀을 때
String Sseq = req.getParameter("seq");
int seq = Integer.parseInt(Sseq); //글의 시퀀스
String Suser = req.getParameter("userid"); // 좋아요 누른 유저의 시퀀스
int user = Integer.parseInt(Suser);
System.out.println("seq " + seq + " userid " + user);
CommuBbsService comService = CommuBbsService.getInstance(); // 서비스 불러옴
int like_count = 0;
HashMap status = new HashMap<>(); //hashMap을 통해 상태를 보낸다
boolean check = comService.Prevent_duplication(user, seq);
// 이미 유저가 좋아요를 눌렀는지 아닌지 확인
if (check) { // 좋아요 이미 누름
// 테이블에서 해당 행을 삭제( 추가) 한다.
comService.likeTB_delete(user, seq);
// status, like count 를 json으로 전송한다.
status.put("status", 404);
} else { //좋아요 안누름
// 테이블에서 해당 행을 삭제( 추가) 한다.
comService.likeTB_insert(user, seq);
// status, like count 를 json으로 전송한다.
status.put("status", 200);
}
// 테이블을 게시글 seq 로 count(*)
like_count = comService.getLikeCount(seq);
status.put("like_count", like_count);
String json = new Gson().toJson(status); // json파일을 Gson 파일로 변환
System.out.println(json);
resp.getWriter().write(json);
-처음 게시물 읽었을 때 좋아요 갯수 불러올 때
else if (command.equals("read")) {
String Sseq = req.getParameter("seq");
int seq = Integer.parseInt(Sseq);
HttpSession session = req.getSession();
User current_user = (User) session.getAttribute("current_user");
if (Delegator.checkSession(req, resp)) {
CommuBbsService comService = CommuBbsService.getInstance();
CommuBbsCommentService commentService = CommuBbsCommentService.getInstance();
List comments = commentService.getAllComments(seq);
comService.readCount(seq);
CommuBbsDto comdto = comService.getCommu(seq);
boolean isLiked = comService.Prevent_duplication(current_user.getSeq(), seq);
req.setAttribute("comments", comments);
req.setAttribute("comdto", comdto);
req.setAttribute("like_count", comService.getLikeCount(seq));
req.setAttribute("isLiked", isLiked);
dispatch("CommuBbsDetail.jsp", req, resp);view 부분
<div class="col-md-4" id="likeArea">
<button type="button" id="btnLike">
<mg src="${ isLiked == true ? './img/heart.png' : './img/empty_heart.png' }"
id="like_img" height="50px" width="50px">
</button>
<span id="like_count">${like_count }</span>
<mg src="./img/comment_1.png" height="30px" width="30px">
<span id="commentCount">${comments.size() }</span>
</div>
좋아요 눌렀을 때, 안 눌렀을 때 이미지 다르게 해줌
$('#btnLike').click(function () {
$.ajax({
url:"CommuBbsController",
data: {command: 'like', seq: ${comdto.seq }, userid: ${current_user.seq }},
type:"post",
success : function (data) {
var result = JSON.parse(data);
if(result.status == 404){
$('img#like_img').attr('src', './img/empty_heart.png');
} else {
$('img#like_img').attr('src', './img/heart.png');
}
$('span#like_count').html(result.like_count);
})
});
View
<c:choose>
<c:when test="${item.likecount==0 }">
<img src="./img/empty_heart.png" height="40">
<span id="likecount">${item.likecount }</span>
</c:when>
<c:otherwise>
<img src="./img/heart.png" height="40">
<span id="likecount">${item.likecount }</span>
</c:otherwise>
</c:choose>
dto에 likecount 추가
Dao
public List<AfterBbsDto> getAfterlBbsList(){
String sql = "SELECT A.SEQ, A.TITLE, A.PIC1, A.CONTENT, A.TARGET_USER_SEQ, "
+ " A.REG_DATE, A.LAST_UPDATE, A.DEL, A.READCOUNT,B.EMAIL AS USER_EMAIL,"
+ " (select count(*) from liketable b where a.seq = b.target_bbs_seq and b.bbs_category=2) as likecount "
+ " FROM AFTERBBS A, DENGUSER B "
+ " WHERE A.TARGET_USER_SEQ = B.SEQ AND DEL=0 "
+ " ORDER BY REG_DATE DESC ";
List<AfterBbsDto> list = new ArrayList<AfterBbsDto>();
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = DBConnection.makeConnection();
System.out.println("2/6 getAfterlBbsList Success");
psmt = conn.prepareStatement(sql);
System.out.println("spl = " + sql);
System.out.println("3/6 getAfterlBbsList Success");
rs = psmt.executeQuery();
System.out.println("4/6 getAfterlBbsList Success");
while(rs.next()){
int i = 1;
AfterBbsDto dto = new AfterBbsDto(rs.getInt(i++),//int seq,
rs.getString(i++),//String title,
rs.getString(i++),//String pic1,
rs.getString(i++),//String content,
rs.getInt(i++),//int userSeq,
rs.getString(i++),//String rdate,
rs.getString(i++),//String ldate,
rs.getInt(i++),
rs.getInt(i++),
rs.getString(i++),
rs.getInt(i++));
list.add(dto);
}
System.out.println("5/6 getAfterlBbsList Success");
} catch (SQLException e) {
e.printStackTrace();
System.out.println("getAfterlBbsList fail");
} finally{
DBClose.close(psmt, conn, rs);
System.out.println("6/6 getAfterlBbsList Success");
}
return list;
}
리스트에 불러오는 쿼리문에 해당 카테고리에 대한 게시물의 좋아요 갯수 불러옴
(select count(*)
from liketable b
where a.seq = b.target_bbs_seq
and b.bbs_category=2) as likecount