댓글 count 구현

<%=code%> table --------JOIN------- comment tabel   

 

1. list.jsp 

 1) sql 구문에 join 하기

* as @@@    --> @@@로 이름 정정

String sql = "select *,(select count(*) from comment where com_table='"+code+"' and com_uid="+code+".uid) as comment_count from "+code+" where gongji != '1' order by uid desc limit "+first+","+num_per_page+" ";

if(request.getParameter("search") != null ) {
	sql = "select *,(select count(*) from comment where com_table='"+code+"' and com_uid="+code+".uid) as comment_count from "+code+" where gongji != '1' and "+field+" like '%"+search+"%' order by uid desc limit "+first+","+num_per_page+" ";
}

Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

ㄴ> 모든 칼럼, comment_count(내가 만들어낸 칼럼)

ㄴ> select count(*) from comment where com_table=' "+code+" ' and com_uid="+code+".uid 

       :   각 테이블의 code가 같고, uid가 같은 댓글의 총 수 

 

2) rs로 가져오기

<%
while(rs.next()) {
	int com_count = rs.getInt("comment_count");
}

%>

<td>
	<%=subject%> (<%=com_count %>)    <!-- 제목(댓글 개수) -->
</td>

 

2. view.jsp 에도 동일함

게시글에 join하기(댓글창에는 안됨)
// 데이터 하나씩 가져오기
String sql ="select *,(select count(*) from comment where com_table='"+code+"' and com_uid="+code+".uid) as comment_count from "+code+" where uid="+uid+" ";

Connection con = DriverManager.getConnection(url, user, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

int com_count = 0;  // 전역변수
if(rs.next()) {
	com_count = rs.getInt("comment_count");   // 지역변수
}


<td align=left height=15><%=com_count %></td>