[๊ตฌ๋์์นด๋ฐ๋ฏธ IT๊ตญ๋น์ง์] home.jsp๋ฅผ ์์ฑํ๊ณ , ๊ฒ์๊ธ ์์ธํ์ด์ง์ ๋๊ธ ์์ฑ ๋ฐ ๋ฆฌ์คํธ ๊ธฐ๋ฅ์ ์ถ๊ฐํ๋ค.
home.jsp์ ํ์ด์ง ๊ธฐ๋ฅ์ ์ถ๊ฐํ๊ณ , ์ ๋ฐ์ ์ผ๋ก ๊ฐ๋
์ฑ์ ๋์ด๊ธฐ ์ํ ์์ ์ ํ๋ค.
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.*" %> <!-- HashMap ์ฌ์ฉ -->
<%@ page import = "vo.*" %>
<%
// ์ฝ์์ฐฝ ์ถ๋ ฅ ์์ ์ง์
final String RED = "\u001B[31m";
final String BG_RED = "\u001B[41m";
final String GREEN = "\u001B[32m";
final String BG_GREEN = "\u001B[42m";
final String RESET = "\u001B[0m";
// 1) ์์ฒญ๊ฐ ์ ํจ์ฑ ๊ฒ์ฌ (๋ฆฌ๋ค์ด๋ ์
)
// ์ธ์
๊ฐ์ด ์๋์ง ๋๋ ํ์ด์ง ์ rowPerPage๋ currentPage ๋ฑ..
// 1-1) session JSP ๋ด์ฅ(๊ธฐ๋ณธ)๊ฐ์ฒด
// ์ผ๋จ ์ง๊ธ์ ์์
// 1-2) request / response JSP ๋ด์ฅ(๊ธฐ๋ณธ)๊ฐ์ฒด
// currentPage, rowPerPage, localName
System.out.println(RED + request.getParameter("currentPage") + " <- home param currentPage");
System.out.println(request.getParameter("rowPerPage") + " <- home param rowPerPage");
System.out.println(request.getParameter("localName") + " <- home param localName" + RESET);
int currentPage = 1;
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
}
int rowPerPage = 10;
if(request.getParameter("rowPerPage") != null) {
rowPerPage = Integer.parseInt(request.getParameter("rowPerPage"));
}
String localName = "์ ์ฒด";
if(request.getParameter("localName") != null) {
localName = request.getParameter("localName");
}
System.out.println(GREEN + currentPage + " <- home currentPage");
System.out.println(rowPerPage + " <- home rowPerPage");
System.out.println(localName + " <- home localName" + RESET);
// 2) ๋ชจ๋ธ(=๊ฒฐ๊ณผ์
) ๊ณ์ธต (๊ผญ db์์๋ง ๊ฐ์ ธ์ค๋ ๊ฒ์ ์๋๋ค)
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-1) ์๋ธ๋ฉ๋ด๋ฅผ ์ถ๋ ฅํ๊ธฐ ์ํ ์ฟผ๋ฆฌ ์์ฑ (์๋ธ๋ฉ๋ด ๊ฒฐ๊ณผ์
)
// ์ ์ฒด์ ๊ฐ localName์ count๋ฅผ ์กฐํํ๊ธฐ ์ํด ์ฟผ๋ฆฌ๋ฅผ ํฉ์นจ(UNION ALL ์ฌ์ฉ)
/*
SELECT '์ ์ฒด' localName, COUNT(local_name) cnt FROM board
UNION ALL
SELECT local_name, COUNT(local_name) FROM board GROUP BY local_name
*/
String subMenuSql = "SELECT '์ ์ฒด' localName, COUNT(local_name) cnt FROM board UNION ALL SELECT local_name, COUNT(local_name) FROM board GROUP BY local_name";
PreparedStatement subMenuStmt = conn.prepareStatement(subMenuSql);
ResultSet subMenuRs = subMenuStmt.executeQuery();
System.out.println(BG_GREEN + subMenuStmt + " <- home subMenuStmt");
// HashMap์ ArrayList์ ๋ฃ๊ธฐ
ArrayList<HashMap<String, Object>> subMenuList = new ArrayList<HashMap<String, Object>>();
while(subMenuRs.next()) {
HashMap<String, Object> m = new HashMap<String, Object>();
m.put("localName", subMenuRs.getString("localName"));
m.put("cnt", subMenuRs.getInt("cnt"));
subMenuList.add(m);
}
// 2-2) ์นดํ
๊ณ ๋ฆฌ ๋ณ ๊ฒ์๊ธ 10๊ฐ์ฉ ์ถ๋ ฅ (๊ฒ์ํ ๋ชฉ๋ก ๊ฒฐ๊ณผ์
)
// ๋ชจ๋ธ๊ฐ์ ๊ตฌํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int startRow = (currentPage - 1) * rowPerPage;
// ์ฟผ๋ฆฌ ์์ฑ
PreparedStatement boardStmt = null;
ResultSet boardRs = null;
// ๊ธฐ๋ณธ ์ฟผ๋ฆฌ
String boardSql = "SELECT board_no boardNo, board_title boardTitle, local_name localName, createdate createdate FROM board";
// localName ์ ํ ์ ์ค๊ฐ์ ์ถ๊ฐ๋๋ ์ฟผ๋ฆฌ
String localNameSql = " WHERE local_name = ?";
// ๋ง์ง๋ง์ ๋ค์ด๊ฐ๋ ์ฟผ๋ฆฌ // ์์ฑ์ผ์ ์์ผ๋ก ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ
String lastSql = " ORDER BY createdate DESC LIMIT ?, ?";
if(localName.equals("์ ์ฒด") || localName.equals("")) {
boardSql = boardSql + lastSql;
boardStmt = conn.prepareStatement(boardSql);
boardStmt.setInt(1, startRow);
boardStmt.setInt(2, rowPerPage);
} else {
boardSql = boardSql + localNameSql + lastSql;
boardStmt = conn.prepareStatement(boardSql);
boardStmt.setString(1, localName);
boardStmt.setInt(2, startRow);
boardStmt.setInt(3, rowPerPage);
}
System.out.println(boardStmt + " <- home boardStmt" + RESET);
boardRs = boardStmt.executeQuery(); // DB์ฟผ๋ฆฌ ๊ฒฐ๊ณผ์
๋ชจ๋ธ
// ์ผ๋ฐ์ ์ธ ์๋ฃ๊ตฌ์กฐ๋ก ๋ณ๊ฒฝ ArrayList
ArrayList<Board> boardList = new ArrayList<Board>(); // ์ ํ๋ฆฌ์ผ์ด์
์์ ์ฌ์ฉํ ๋ชจ๋ธ (์ฌ์ด์ฆ 0)
// boardRs -> boardList
while(boardRs.next()) {
Board b = new Board(); // ์๊ฐ while๋ฌธ ๋ฐ์ ์์ผ๋ฉด..! b๋ ๊ณ์ ๋ฐ๋์ด์ผ๋๋๋ฐ ๋ฐ๋์ง ์์
b.boardNo = boardRs.getInt("boardNo");
b.localName = boardRs.getString("localName");
b.boardTitle = boardRs.getString("boardTitle");
b.createdate = boardRs.getString("createdate");
boardList.add(b); // ๋ง์ง๋ง์ ์ฌ๊ธฐ์ b๋ฅผ list์ ์ถ๊ฐํ๊ณ ์๋ก์ด b(new)๋ฅผ ๋ง๋ค์ด์ผ ๊ณ์ํด์ ๋ค๋ฅธ b์ ๊ฐ๋ค์ด list์ ์ถ๊ฐ๋ ๊ฒ
}
// ๋๋ฒ๊น
System.out.println(BG_RED + boardList + " <- home boardList");
System.out.println(boardList.size() + " <- home boardList.size" + RESET);
// 2-3) ํ์ด์ง์ ์ํ ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// ๋ชจ๋ธ๊ฐ์ ๊ตฌํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int totalCount = 0;
int lastPage = 0;
// ์ฟผ๋ฆฌ ์์ฑ
PreparedStatement pageStmt = null;
ResultSet pageRs = null;
// ๊ธฐ๋ณธ ์ฟผ๋ฆฌ
String pageSql = "SELECT count(*) FROM board";
// localName์ ๋ฐ๋ผ where์ ์ถ๊ฐ
if(localName.equals("์ ์ฒด") || localName.equals("")) {
pageStmt = conn.prepareStatement(pageSql);
} else {
pageSql = pageSql + localNameSql;
pageStmt = conn.prepareStatement(pageSql);
pageStmt.setString(1, localName);
}
System.out.println(BG_GREEN + pageStmt + " <- home pageStmt" + RESET);
// totalCount // ์ฟผ๋ฆฌ ์คํ ๊ฒฐ๊ณผ๊ฐ์ ๋ณ์์ ์ ์ฅ
pageRs = pageStmt.executeQuery();
if(pageRs.next()) {
totalCount = pageRs.getInt("count(*)");
}
System.out.println(GREEN + totalCount + " <- home totalCount");
// lastPage
lastPage = totalCount / rowPerPage;
if(totalCount % rowPerPage != 0) {
lastPage = lastPage + 1;
}
System.out.println(lastPage + " <- home lastPage" + RESET);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>home.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!----------------------------------------------------- ๋ก๊ทธ์ธํผ (์ธ์
์ id๊ฐ์ด null์ผ๋๋ง ์ถ๋ ฅ) ---------------------------------------------------->
<div>
<%
if(session.getAttribute("loginMemberId") == null) {
%>
<h3 class="mt-4">๋ก๊ทธ์ธ</h3>
<div class="text-danger">
<% // msg ๋ฐ์์ ์ถ๋ ฅ
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<form action="<%=request.getContextPath()%>/member/loginAction.jsp" method="post">
<table>
<tr>
<td>์์ด๋</td>
<td>
<input type="text" name="memberId">
</td>
</tr>
<tr>
<td>ํจ์ค์๋</td>
<td>
<input type="password" name="memberPw">
</td>
</tr>
</table>
<button type="submit" class="btn btn-success">๋ก๊ทธ์ธ</button>
</form>
<%
}
%>
</div>
<!----------------------------------------------------------- ๋ก๊ทธ์ธํผ ๋ ------------------------------------------------------------------>
<br>
<div class="row">
<!------------------------------------------------ ์๋ธ๋ฉ๋ด(์ธ๋ก) subMenuList๋ชจ๋ธ ์ถ๋ ฅ --------------------------------------------------------->
<div class="col-sm-4">
<h3 class="mt-4">์นดํ
๊ณ ๋ฆฌ</h3>
<ul class="nav nav-pills flex-column">
<%
for(HashMap<String, Object> m : subMenuList) {
%>
<li class="nav-item">
<a href="<%=request.getContextPath()%>/home.jsp?localName=<%=(String)m.get("localName")%>" class="nav-link">
<%=(String)m.get("localName")%>(<%=(Integer)m.get("cnt")%>)
<!-- ๊ฐํ์
์ด Objectํ์
์ด๋ฏ๋ก ํ๋ณํ -->
</a>
</li>
<%
}
%>
</ul>
</div>
<!--------------------------------------------------------- subMenuList ๋ ----------------------------------------------------------------->
<!-------------------------------------------------- ์นดํ
๊ณ ๋ฆฌ๋ณ ๊ฒ์๊ธ 10๊ฐ์ฉ boardList๋ชจ๋ธ ์ถ๋ ฅ------------------------------------------------------>
<div class="col-sm-8">
<h3 class="mt-4">๊ฒ์ํ</h3>
<table class="table container">
<thead class="table-success">
<tr>
<th>์นดํ
๊ณ ๋ฆฌ</th>
<th>์ ๋ชฉ</th>
<th>์์ฑ์ผ์</th>
</tr>
</thead>
<tbody>
<%
for(Board b : boardList) {
%>
<tr>
<td><%=b.localName%></td>
<td>
<a href="<%=request.getContextPath()%>/board/boardOne.jsp?boardNo=<%=b.boardNo%>">
<%=b.boardTitle%>
</a>
</td>
<td><%=b.createdate.substring(0, 10)%></td>
</tr>
<%
}
%>
</tbody>
</table>
<!------- ํ์ด์ง ์์ ------->
<div class="text-center">
<%
if(currentPage > 1) {
%>
<a href="<%=request.getContextPath()%>/home.jsp?currentPage=<%=currentPage - 1%>&rowPerPage=<%=rowPerPage%>&localName=<%=localName%>" class="btn btn-success btn-sm">
์ด์
</a>
<%
}
%>
<%=currentPage%>ํ์ด์ง
<%
if(lastPage > currentPage) {
%>
<a href="<%=request.getContextPath()%>/home.jsp?currentPage=<%=currentPage + 1%>&rowPerPage=<%=rowPerPage%>&localName=<%=localName%>" class="btn btn-success btn-sm">
๋ค์
</a>
<%
}
%>
</div>
<!------- ํ์ด์ง ๋ ------->
</div>
<!--------------------------------------------------------- boardList ๋ ------------------------------------------------------------------>
</div>
</div>
<br>
<!-- include ํ์ด์ง : Copyright -->
<div>
<%
// request.getRequestDispatcher(request.getContextPath()+"./inc/copyright.jsp").include(request, response);
// ๋๋ฌด ๊ธธ๋ค. ๊ทธ๋์ ์ก์
ํ๊ทธ๋ฅผ ํ์ฉํ๋ค!
%>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/copyright.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
</body>
</html>
๊ฐ๋ ์ฑ์ ๋์ด๊ธฐ์ํด ์ฃผ์์ผ๋ก ์น์ ๋๋๊ธฐ
ํ ํ์ด์ง์์ ์ฌ๋ฌ๊ฐ์ ์ฟผ๋ฆฌ ์์ฑ ์ ์ซ์๋ก ๊ตฌ๋ถํ์ง ๋ง๊ณ (ex. sql1 , sql2 ... ) ์ด๋ค sql๋ฌธ์ธ์ง ์ด๋ฆ ์ง๋ ๊ฒ์ด ๊ฐ๋ ์ฑ๋ ์ข๊ณ , ๋๋ฒ๊น ์ ํ์ธํ๊ธฐ ์ฉ์ดํจ!
๊ถ๊ธํ ์
๐ ๊ฒฐ๊ณผ
ํ์ด์ง์ ์ถ๊ฐํ๋ค. ์นดํ
๊ณ ๋ฆฌ ๋ณ๊ฒฝ์(localName ๊ฐ์ ๋ฐ๋ผ) ์ด์ /๋ค์ ๋ฒํผ์ ๋ค๋ฅด๊ฒ ์ถ๋ ฅ.
home.jsp์์ ๊ฒ์๊ธ ์ ๋ชฉ(boardTitle) ํด๋ฆญ ์ ๊ฒ์๊ธ ์์ธ ํ์ด์ง(boardOne.jsp)๋ก ์ด๋ํ๋๋ก ํ๋ค. ๊ฒ์๊ธ ์์ธ ํ์ด์ง์๋ ๊ฒ์๊ธ ์์ธ ๋ณด๊ธฐ/ ๋๊ธ ์
๋ ฅ ํผ/ ๋๊ธ ๋ฆฌ์คํธ๋ฅผ ์ถ๋ ฅํ๋๋ก ํ๋ค. ๋ํ ๋๊ธ ์
๋ ฅ ํผ์ ํ์ ์ ์ฉ์ผ๋ก ๊ตฌํํ๊ณ , ๋๊ธ ๋ฆฌ์คํธ์ ํ์ด์ง์ด ๊ฐ๋ฅํ๋๋ก ํ๋ค.
๋๊ธ ๊ธฐ๋ฅ์ ์ถ๊ฐํ๊ธฐ ์ํด ์๋ก์ด ๋ฐ์ดํฐ ํ
์ด๋ธ์ ์ถ๊ฐํ๋ค. ๊ธฐ๋ณธํค๋ comment_no๋ก ๋์๊ณ ์ด๋ค ๊ฒ์๊ธ(board_no)์, ์ด๋ค ์ฌ์ฉ์(member_id)๊ฐ ์์ฑํ๋์ง ์์์ผํ๊ธฐ ๋๋ฌธ์ ๊ฐ๊ฐ ์ธ๋ํค๋ก ๋์๋ค.
๋๊ธ ์ํ๋ฐ์ดํฐ๋ฅผ ์ถ๊ฐํ๋ค. board_no๋ ํ์ด์ง ์์
ํ
์คํธํ ์ํด 1000,998,996,994,992 ์ด 5๊ฐ์ ๊ฒ์๊ธ์๋ง ์ง์ ํ๋ค.
10์ผ์ฐจ ์์ ๋ join์ด ์ด๋ค ๊ฒ์ธ์ง์ ๋ํด์๋ง ๊ฐ๋ตํ๊ฒ ๋ฐฐ์ ์๋ค.
์ค๋ณต๋ ๋ฐ์ดํฐ ์์ฑ์ ์ค์ฌ์ ์ค๋ฅ๋ฅผ ์ต์ํํ๊ธฐ ์ํด ํ ์ด๋ธ์ ๋ถ๋ฆฌํ๋๋ฐ ์ด๊ฒ์ ์ ๊ทํ๋ผ๊ณ ํ๋ค. ์ ๊ทํํ 2๊ฐ ์ด์์ ๋ฐ์ดํฐ ํ ์ด๋ธ์ ์ฐ๊ฒฐํด์ ์ต์ข ๊ฒฐ๊ณผ๋ฅผ ๋ด๋ ๊ฒ์ join์ด๋ผ๊ณ ๋ถ๋ฅธ๋ค.
/* boardOne.jsp์ ๊ฒฐ๊ณผ์
๋๊ฐ์ง ๋ฐฉ๋ฒ */
/* 1) ๋๋ฒ์ ์ฟผ๋ฆฌ๋ก ๋ฐ๋ก๋ฐ๋ก (userboardํ๋ก์ ํธ์์ ์ฌ์ฉ) */
SELECT board_no, board_title
FROM board
WHERE board_no = 1000;
SELECT comment_no, board_no, comment_content
FROM COMMENT
WHERE board_no = 1000
LIMIT 0 , 10;
/* 2) ํ๋ฒ์ ์ฟผ๋ฆฌ๋ก (์กฐ์ธ ๋ฌธ๋ฒ ์ฌ์ฉ, ์กฐ์ธ ๋ฌธ๋ฒ์ SQL๋ฌธ๋ฒ ์์
์์ ๋ค์...) */
SELECT b.board_title, b.board_no, c.board_no, c.comment_content
FROM board b INNER JOIN comment c
ON b.board_no = c.board_no
WHERE b.board_no = 1000
ORDER BY c.createdate DESC;
๊ฒ์๊ธ์ ์ ๋ณด(board ํ ์ด๋ธ)์ ๋๊ธ ๋ฆฌ์คํธ(comment ํ ์ด๋ธ)์ ๊ฐ์ด ์ถ๋ ฅํ๋ ค๋ฉด ๋๋ฒ์ ์ฟผ๋ฆฌ ์์ฑ์ ํตํด ๊ฐ๊ฐ ์ถ๋ ฅํ๋ ๋ฐฉ๋ฒ๋ ์์ง๋ง, board ํ ์ด๋ธ๊ณผ comment ํ ์ด๋ธ์ joinํ๋ค๋ฉด ํ๋ฒ์ ์ฟผ๋ฆฌ ์์ฑ์ผ๋ก๋ ๊ตฌํ์ด ๊ฐ๋ฅํ ๊ฒ์ด๋ค. ์ด๋ฒ ์๊ฐ์๋ ์ฐ์ ๋๋ฒ์ ์ฟผ๋ฆฌ ์์ฑ์ ํตํ ๋ฐฉ๋ฒ์ผ๋ก ๊ตฌํํ๊ธฐ๋ก ํ๊ณ , ๋ค์์๊ฐ์ ๋ฐฐ์ธ join๋ฌธ์ ๋ฏธ๋ฆฌ ๋ณด์ฌ์ฃผ์ จ๋ค.
join๋ฌธ ์์ฑ ์์๋ ํ ์ด๋ธ์ ๋ณ๋ช (AS)์ ์ง์ ํด์ฃผ๊ณ , ํ ์ด๋ธ๋ช +๋ง์นจํ(.)+์ปฌ๋ผ๋ช ์ผ๋ก ์์ฑํ๋ค.
INNER JOIN
๋ฅผ ์๋ตํ๊ณ ์ผํ(,)๋ก ์์ฑ ๊ฐ๋ฅํ์ง๋ง ํ์ค ๋ฐฉ๋ฒ์ด ์๋๊ธฐ ๋๋ฌธ์ DB๋ง๋ค ์ฌ์ฉ์ด ์๋ ์๋ ์๋ค.
์คํ ์์๋ from์ ์ด ์ ์ผ ๋จผ์ ์คํ๋๋ค. ์ฆ, from์ ์์ ์์ฑ๋ ๋ณ๋ช
(AS)๋ ๋ค๋ฅธ ์ ์์๋ ์ฌ์ฉ์ด ๊ฐ๋ฅํ ๊ฒ์ด๋ค.
join๋ฌธ ์์ฑ์ ON
๋ค์ ์ผ์นํ๋ ์ธ๋ํค์ ์กฐ๊ฑด์ ๋ฃ๋๋ค.
Voํ์
์ Comment ๊ฐ์ฒด๋ฅผ ์์ฑํ์๋ค.
โ ๊ตฌํํด์ผํ ์
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.*" %>
<%@ page import = "vo.*" %>
<%
//์ฝ์์ฐฝ ์ถ๋ ฅ ์์ ์ง์
final String RED = "\u001B[31m";
final String BG_RED = "\u001B[41m";
final String GREEN = "\u001B[32m";
final String BG_GREEN = "\u001B[42m";
final String RESET = "\u001B[0m";
// 1) ์์ฒญ๊ฐ ์ ํจ์ฑ ๊ฒ์ฌ
// currentPage, rowPerPage, boardNo
System.out.println(RED + request.getParameter("currentPage") + " <- boardOne param currentPage");
System.out.println(request.getParameter("rowPerPage") + " <- boardOne param rowPerPage");
System.out.println(request.getParameter("boardNo") + " <- boardOne param boardNo" + RESET);
// boardNo๊ฐ null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด๋ฉด home์ผ๋ก ๋ฆฌ๋ค์ด๋ ์
if(request.getParameter("boardNo") == null
|| request.getParameter("boardNo").equals("")) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
return;
}
int boardNo = Integer.parseInt(request.getParameter("boardNo"));
// currentPage
int currentPage = 1;
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
}
// rowPerPage
int rowPerPage = 10;
if(request.getParameter("rowPerPage") != null) {
rowPerPage = Integer.parseInt(request.getParameter("rowPerPage"));
}
// ๋๋ฒ๊น
System.out.println(GREEN + currentPage + " <- boardOne currentPage");
System.out.println(rowPerPage + " <- boardOne rowPerPage");
System.out.println(boardNo + " <- boardOne boardNo" + RESET);
// 2) ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-1) ํด๋น boardNo์ ๊ฒ์๊ธ ์์ธ๋ณด๊ธฐ - boardOne
// SELECT * FROM board WHERE board_no = ?
PreparedStatement boardOneStmt = null;
ResultSet boardOneRs = null;
String boardOneSql = "SELECT board_no boardNo, local_name localName, board_title boardTitle, board_content boardContent, member_id memberId, createdate createdate, updatedate updatedate FROM board WHERE board_no = ?";
boardOneStmt = conn.prepareStatement(boardOneSql);
boardOneStmt.setInt(1, boardNo);
System.out.println(BG_GREEN + boardOneStmt + " <- boardOne boardOneStmt");
// Voํ์
์ผ๋ก ๋ฐ๊พธ๊ธฐ
boardOneRs = boardOneStmt.executeQuery();
Board board = null;
if(boardOneRs.next()) {
board = new Board();
board.boardNo = boardOneRs.getInt("boardNo");
board.localName = boardOneRs.getString("localName");
board.boardTitle = boardOneRs.getString("boardTitle");
board.boardContent = boardOneRs.getString("boardContent");
board.memberId = boardOneRs.getString("memberId");
board.createdate = boardOneRs.getString("createdate");
board.updatedate = boardOneRs.getString("updatedate");
}
// 2-2) ํด๋น boardNo์ ๋๊ธ ๋ฆฌ์คํธ ์ถ๋ ฅ - commentList
// ๋ชจ๋ธ๊ฐ์ ๊ตฌํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int startRow = (currentPage - 1) * rowPerPage;
// ์ฟผ๋ฆฌ ์์ฑ // ์์ฑ์ผ์ ์์ผ๋ก ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ
// SELECT comment_content, member_id, createdate, updatedate FROM comment WHERE board_no = ? ORDER BY createdate DESC LIMIT ?, ?
PreparedStatement commentListStmt = null;
ResultSet commentListRs = null;
String commentListSql = "SELECT comment_content commentContent, member_id memberId, createdate createdate, updatedate updatedate FROM comment WHERE board_no = ? ORDER BY createdate DESC LIMIT ?, ?";
commentListStmt = conn.prepareStatement(commentListSql);
commentListStmt.setInt(1, boardNo);
commentListStmt.setInt(2, startRow);
commentListStmt.setInt(3, rowPerPage);
System.out.println(commentListStmt + " <- boardOne commentListStmt");
// Voํ์
์ ArrayList๋ก ๋ฐ๊พธ๊ธฐ
commentListRs = commentListStmt.executeQuery();
ArrayList<Comment> commentList = new ArrayList<Comment>();
while(commentListRs.next()) {
Comment c = new Comment();
c.commentContent = commentListRs.getString("commentContent");
c.memberId = commentListRs.getString("memberId");
c.createdate = commentListRs.getString("createdate");
c.updatedate = commentListRs.getString("updatedate");
commentList.add(c);
}
// ๋๋ฒ๊น
System.out.println(BG_RED + commentList + " <- boardOne commentList");
System.out.println(commentList.size() + " <- boardOne commentList.size" + RESET);
// 2-3) ํ์ด์ง์ ์ํ ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// ๋ชจ๋ธ๊ฐ์ ๊ตฌํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int totalCount = 0;
int lastPage = 0;
// ํด๋น boardNo์ ๋๊ธ์ ์ด ๊ฐฏ์๋ฅผ ๊ตฌํ๋ ์ฟผ๋ฆฌ ์์ฑ
PreparedStatement commentPageStmt = null;
ResultSet commentPageRs = null;
String commentPageSql = "SELECT count(*) FROM comment WHERE board_no = ?";
commentPageStmt = conn.prepareStatement(commentPageSql);
commentPageStmt.setInt(1, boardNo);
System.out.println(BG_GREEN + commentPageStmt + " <- boardOne commentPageStmt" + RESET);
// totalCount
commentPageRs = commentPageStmt.executeQuery();
if(commentPageRs.next()) {
// ๊ฒฐ๊ณผ๊ฐ์ ๋ณ์์ ์ ์ฅ
totalCount = commentPageRs.getInt("count(*)");
}
System.out.println(GREEN + totalCount + " <- boardOne totalCount");
// lastPage
lastPage = totalCount / rowPerPage;
if(totalCount % rowPerPage != 0) {
lastPage = lastPage + 1;
}
System.out.println(lastPage + " <- boardOne lastPage" + RESET);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>boardOne.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!-------------------------------------------------- ๊ฒ์๊ธ ์์ธ๋ณด๊ธฐ boardOne๋ชจ๋ธ ์ถ๋ ฅ------------------------------------------------------>
<h3 class="mt-4">๊ฒ์๊ธ ์์ธ๋ณด๊ธฐ</h3>
<table class="table container">
<tr>
<th class="table-success">๊ธ๋ฒํธ</th>
<td>No.<%=board.boardNo%></td>
</tr>
<tr>
<th class="table-success">์นดํ
๊ณ ๋ฆฌ</th>
<td><%=board.localName%></td>
</tr>
<tr>
<th class="table-success">์์ฑ์</th>
<td><%=board.memberId%></td>
</tr>
<tr>
<th class="table-success">์ ๋ชฉ</th>
<td><%=board.boardTitle%></td>
</tr>
<tr>
<th class="table-success">๋ด์ฉ</th>
<td><%=board.boardContent%></td>
</tr>
<tr>
<th class="table-success">์์ฑ์ผ์</th>
<td><%=board.createdate%></td>
</tr>
<tr>
<th class="table-success">์์ ์ผ์</th>
<td><%=board.updatedate%></td>
</tr>
</table>
<div class="text-center">
<a href="" class="btn btn-success">์์ </a>
<a href="" class="btn btn-success">์ญ์ </a>
</div>
<!--------------------------------------------------------- boardOne ๋ ------------------------------------------------------------------>
<br>
<!----------------------------------------------------- ๋๊ธ์
๋ ฅํผ (์ธ์
์ id๊ฐ์ด ์์๋๋ง ์ถ๋ ฅ) --------------------------------------------------->
<div>
<%
// ๋ก๊ทธ์ธ ์ฌ์ฉ์๋ง ๋๊ธ ์
๋ ฅ์ ํ์ฉํ๊ธฐ ์ํด ๋ถ๊ธฐ
if(session.getAttribute("loginMemberId") != null) {
// ํ์ฌ ๋ก๊ทธ์ธ ์ฌ์ฉ์์ ์์ด๋๋ฅผ ๋ณ์์ ๋ฃ๊ธฐ
String loginMemberId = (String)session.getAttribute("loginMemberId"); // ํ๋ณํํด์ฃผ๊ธฐ
%>
<h5 class="mt-4">๋๊ธ์
๋ ฅ</h5>
<div class="text-danger">
<%
// msg ๋ฐ์์ ์ถ๋ ฅ
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg") %>
<%
}
%>
</div>
<form action="<%=request.getContextPath()%>/board/insertCommentAction.jsp" method="post">
<!-- boardNo์ memberId๋ ์
๋ ฅ๊ฐ์ด ์๊ธฐ ๋๋ฌธ์ hidden์ผ๋ก ๋๊น -->
<input type="hidden" name="boardNo" value="<%=board.boardNo%>">
<input type="hidden" name="memberId" value="<%=loginMemberId%>">
<table>
<tr>
<td>๋๊ธ</td>
<td>
<textarea rows="3" cols="80" name="commentContent"></textarea>
</td>
</tr>
</table>
<button type="submit" class="btn btn-success">์
๋ ฅ</button>
</form>
<%
}
%>
</div>
<!----------------------------------------------------------- ๋๊ธ์
๋ ฅํผ ๋ ----------------------------------------------------------------->
<br>
<!------------------------------------------------------- ๋๊ธ๋ฆฌ์คํธ commentList๋ชจ๋ธ ์ถ๋ ฅ ----------------------------------------------------->
<table class="table table-hover">
<thead>
<tr>
<th>๋๊ธ ๋ด์ฉ</th>
<th>์์ฑ์</th>
<th>์์ฑ์ผ์</th>
<th>์์ ์ผ์</th>
<th>์์ </th>
<th>์ญ์ </th>
</tr>
</thead>
<tbody>
<%
for(Comment c : commentList) {
%>
<tr>
<td><%=c.commentContent%></td>
<td><%=c.memberId%></td>
<td><%=c.createdate%></td>
<td><%=c.updatedate%></td>
<td>
<a href="" class="btn btn-outline-secondary btn-sm">์์ </a>
</td>
<td>
<a href="" class="btn btn-outline-secondary btn-sm">์ญ์ </a>
</td>
</tr>
<%
}
%>
</tbody>
</table>
<%
// ๋๊ธ์ด ์์ผ๋ฉด ์ถ๋ ฅ
if(totalCount == 0) {
%>
<h5>๋๊ธ์ด ์์ต๋๋ค ใ
ใ
</h5>
<%
}
%>
<!------- ํ์ด์ง ์์ ------->
<div class="text-center">
<%
if(currentPage > 1) {
%>
<a href="<%=request.getContextPath()%>/board/boardOne.jsp?currentPage=<%=currentPage - 1 %>&rowPerPage=<%=rowPerPage%>&boardNo=<%=boardNo%>" class="btn btn-success btn-sm">
์ด์
</a>
<%
}
%>
<%=currentPage%>ํ์ด์ง
<%
if(lastPage > currentPage) {
%>
<a href="<%=request.getContextPath()%>/board/boardOne.jsp?currentPage=<%=currentPage + 1%>&rowPerPage=<%=rowPerPage%>&boardNo=<%=boardNo%>" class="btn btn-success btn-sm">
๋ค์
</a>
<%
}
%>
</div>
<!------- ํ์ด์ง ๋ ------->
<!--------------------------------------------------------------- commentList ๋ -------------------------------------------------------------->
</div>
<br>
<!-- include ํ์ด์ง : Copyright -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/copyright.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
</body>
</html>
session.getAttribute("loginMemberId")
)์ ๊ฐ์ ๋๊ธด๋ค.
๋ก๊ทธ์ธ ์ํ์ ๋ (์ธ์
๊ฐ X) ํด๋น boardNo์ ๋๊ธ ๋ฆฌ์คํธ ์ถ๋ ฅ (๊ฐ์ฅ ์ต๊ทผ์ ์์ฑ๋ ๋๊ธ์ด ์๋จ์ผ๋ก ์ฌ๋ผ์ค๋๋ก ์ ๋ ฌ), ๋๊ธ 10๊ฐ ์ดํ์์๋ ํ์ด์ง ๋ฐ์X, ๋๊ธ ๋ฆฌ์คํธ ์ฒซํ์ด์ง์์ ์ด์
์ด ์ถ๋ ฅ๋์ง ์์
๋ก๊ทธ์ธ ์ (์ธ์
๊ฐ O) ๋๊ธ ์
๋ ฅ ํผ ์ถ๋ ฅ
๋๊ธ์ด ์กด์ฌํ์ง ์์ ์ ๋ฉ์ธ์ง ์ถ๋ ฅ
๋๊ธ 10๊ฐ ์ด๊ณผ ์ถ๋ ฅ ์ ํ์ด์ง ๋ฐ์O, ํด๋น boardNo์ ๋๊ธ ๋ฆฌ์คํธ ๋ง์ง๋ง ํ์ด์ง์์ ๋ค์
์ด ์ถ๋ ฅ๋์ง ์์
๋๊ธ ์
๋ ฅ(insert) ์ actionํ์ด์ง๋ฅผ ์ง์ ์์ฑํด๋ณด๋ ๊ณผ์ ์๊ฐ์ ๊ฐ์ก๋ค.
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.net.*"%>
<%@ page import = "vo.*" %>
<%@ page import = "java.sql.*" %>
<%
//์ฝ์์ฐฝ ์ถ๋ ฅ ์์ ์ง์
final String RED = "\u001B[31m";
final String BG_RED = "\u001B[41m";
final String GREEN = "\u001B[32m";
final String BG_GREEN = "\u001B[42m";
final String RESET = "\u001B[0m";
// ์ธ์ฝ๋ฉ
request.setCharacterEncoding("utf-8");
// 1) ์์ฒญ๊ฐ ์ ํจ์ฑ ๊ฒ์ฌ
// boardNo, memberId, commentContent
System.out.println(RED + request.getParameter("boardNo") + " <- insertCommentAction param boardNo");
System.out.println(request.getParameter("memberId") + " <- insertCommentAction param memberId");
System.out.println(request.getParameter("commentContent") + " <- insertCommentAction param commentContent" + RESET);
// boardNo, memberId
if(request.getParameter("boardNo") == null
|| request.getParameter("boardNo").equals("")
|| request.getParameter("memberId") == null
|| request.getParameter("memberId").equals("")) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
return;
}
// null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด ์๋๋ฉด ๊ฐ ๋ณ์์ ๋ฐ๊ธฐ
int boardNo = Integer.parseInt(request.getParameter("boardNo"));
String memberId = request.getParameter("memberId");
// commentContent
if(request.getParameter("commentContent") == null
|| request.getParameter("commentContent").equals("")) {
String msg = URLEncoder.encode("๋๊ธ ๋ด์ฉ์ ์
๋ ฅํด์ฃผ์ธ์!", "utf-8");
response.sendRedirect(request.getContextPath() + "/board/boardOne.jsp?boardNo=" + boardNo + "&msg=" + msg);
return;
}
// null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ด ์๋๋ฉด ๊ฐ ๋ณ์์ ๋ฐ๊ธฐ
String commentContent = request.getParameter("commentContent");
// ๋๋ฒ๊น
System.out.println(GREEN + boardNo + " <- insertCommentAction boardNo");
System.out.println(memberId + " <- insertCommentAction memberId");
System.out.println(commentContent + " <- insertCommentAction commentContent" + RESET);
// ํ๋ผ๋ฏธํฐ๊ฐ ํด๋์ค์ ์ ์ฅ
Comment paramComment = new Comment();
paramComment.boardNo = boardNo;
paramComment.memberId = memberId;
paramComment.commentContent = commentContent;
// 2) insert
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// ์ฟผ๋ฆฌ ์์ฑ
// INSERT INTO comment(board_no, comment_content, member_id, createdate, updatedate) VALUES(?, ?, ?, NOW(), NOW())
String sql = "INSERT INTO comment(board_no, comment_content, member_id, createdate, updatedate) VALUES(?, ?, ?, NOW(), NOW())";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, paramComment.boardNo);
stmt.setString(2, paramComment.commentContent);
stmt.setString(3, paramComment.memberId);
System.out.println(BG_GREEN + stmt + " <- insertCommentAction stmt");
// ์ฟผ๋ฆฌ๊ฐ ์ ์คํ๋์๋์ง ํ์ธ
// ํด๋น boardNo์ boardOne ํ์ด์ง๋ก ๋ฆฌ๋ค์ด๋ ์
int row = stmt.executeUpdate(); // 1์ด๋ฉด 1ํ ์ฑ๊ณต
if(row == 1) {
System.out.println("insertCommentAction ๋๊ธ ์
๋ ฅ ์ฑ๊ณต" + RESET);
response.sendRedirect(request.getContextPath() + "/board/boardOne.jsp?boardNo=" + boardNo);
return;
} else {
System.out.println(BG_RED + "insertCommentAction ๋๊ธ ์
๋ ฅ ์คํจ" + RESET);
String msg = URLEncoder.encode("๋๊ธ์ด ๋ฑ๋ก๋์ง ์์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/board/boardOne.jsp?boardNo=" + boardNo + "&msg=" + msg);
return;
}
%>
์ ํจ์ฑ ๊ฒ์ฌ์ ๊ฐ๋ ์ ์์ง๋ ์กฐ๊ธ ์๋ฆฌ์กํ ๊ฒ ๊ฐ๋ค. ์ด๋ค ๊ฐ์ ๊ฒ์ฌํด์ผํ๊ณ , ์ด๋๋ก ๋ฆฌ๋ค์ด๋ ์ ํ ์ง ๊ตฌ์์ ์ ํด์ผํ๊ธฐ ์ํด์๋ ๋์ด์ค๋ ๊ฐ์ด ๋ฌด์์ด๊ณ ๋์ด๊ฐ์ผ๋ ๊ฐ์ด ๋ฌด์์ธ์ง ์ ํ์ ํ๋ ๊ฒ์ด ์ค์ํ ๊ฒ์ด๋ค.
๊ถ๊ธํ ์
๐ ๊ฒฐ๊ณผ
๋๊ธ(commentContent๊ฐ) null ๋๋ ๊ณต๋ฐฑ์ธ ์ํ๋ก ๋๊ธ์
๋ ฅ์ ์๋ํ๋ฉด msg ์ถ๋ ฅ