1.게시판 리스트(bbslist.jsp)
<%@page import="dao.BbsDao"%>
<%@page import="dto.BbsDto"%>
<%@page import="java.util.List"%>
<%@page import="dto.MemberDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
MemberDto login = (MemberDto)session.getAttribute("login");
if(login == null){
%>
<script>
alert('로그인 해 주십시오');
location.href = "login.jsp";
</script>
<%
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>게시판</h1>
<div align="center">
<table border="1">
<col width="70"><col width="600"><col width="100"><col width="150">
<thead>
<tr>
<th>번호</th><th>제목</th><th>조회수</th><th>작성자</th>
</tr>
</thead>
<tbody>
<%
if(list == null || list.size() == 0){
%>
<tr>
<td colspan="4">작성된 글이 없습니다</td>
</tr>
<%
}else{
for(int i = 0;i < list.size(); i++){
BbsDto dto = list.get(i); //하나씩 bbs가 넘어옴
%>
<tr>
<th><%=i + 1 %></th>
<td>
//글에 대한 번호는 가지고 가야 함
<a href="bbsdetail.jsp?=seq=<%=bbs.getSeq() %>
<a%=bbs.getTitle() %>
</a>
</td>
<td align="center"><%=bbs.getId() %></td>
<%
}
}
%>
</table>
</div>
<a href="bbswrite.jsp">글쓰기</a>
</body>
</html>
2.게시판 글쓰기(bbswrite.jsp)
<%@page import="dto.MemberDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
MemberDto login = (MemberDto)session.getAttribute("login");
if(login == null){
%>
<script>
alert('로그인 해 주십시오');
location.href = "login.jsp";
</script>
<%
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>bbs write</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
</head>
<body>
<h1>글쓰기</h1>
<div align="center">
<form action="bbswriteAf.jsp" id="frm" method="post">
<table border="1">
<col width="200"><col width="400">
<tr>
<th>아이디</th>
<td>
<%-- <input type="text" name="id" size="50px" value="<%=login.getId() %>" readonly="readonly"> --%>
<%=login.getId() %>
<input type="hidden" name="id" value="<%=login.getId() %>">
</td>
</tr>
<tr>
<th>제목</th>
<td>
<input type="text" id="title" name="title" size="50px" placeholder="제목기입">
</td>
</tr>
<tr>
<th>내용</th>
<td>
<textarea rows="20" cols="50px" id="content" name="content" placeholder="내용기입"></textarea>
</td>
</tr>
<tr>
<td colspan="2">
<!-- <input type="submit" value="글쓰기"> -->
<button type="button">글쓰기</button>
</td>
</tr>
</table>
</form>
</div>
<script type="text/javascript">
$(document).ready(function() {
$("button").click(function() {
if($("#title").val().trim() == "" ){
alert("제목을 기입해 주십시오");
return;
}else if($("#content").val().trim() == "" ){
alert("내용을 기입해 주십시오");
return;
}else{
$("#frm").submit();
}
});
});
</script>
</body>
</html>3.게시글 작성 결과(bbswriteAf.jsp)
-로그인 이후의 처리를 담은 jsp파일
<%@page import="dto.BbsDto"%>
<%@page import="dao.BbsDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
String title = request.getParameter("title");
String content = request.getParameter("content");
BbsDao dao = BbsDao.getInstance();
boolean isS = dao.writeBbs( new BbsDto(id, title, content) );
if(isS){
%>
<script type="text/javascript">
alert("추가되었습니다");
location.href = "bbslist.jsp";
</script>
<%
}else{
%>
<script type="text/javascript">
alert("다시 작성해 주십시오");
location.href = "bbswrite.jsp";
</script>
<%
}
%>
4. DB Table(BBS)
drop table bbs;
create table bbs(
seq int auto_increment primary key,
id varchar(50) not null,
ref decimal(8) not null,
step decimal(8) not null,
depth decimal(8) not null,
title varchar(200) not null,
content varchar(4000) not null,
wdate timestamp not null,
del decimal(1) not null,
readcount decimal(8) not null
);
alter table bbs
add foreign key(id) references member(id);
insert into bbs(id, ref, step, depth, title, content, wdate, del, readcount)
values('id', (select ifnull(max(ref), 0)+1 from bbs b), 0, 0, 'title', 'content', now(), 0, 0));
select * from bbs;5.BbsDao.java
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import db.DBClose;
import db.DBConnection;
import dto.BbsDto;
public class BbsDao {
private static BbsDao dao = null;
private BbsDao() {
DBConnection.initConnection();
}
public static BbsDao getInstance() {
if(dao == null) {
dao = new BbsDao();
}
return dao;
}
public List<BbsDto> getBbsList() {
String sql = " select seq, id, ref, step, depth,"
+ " title, content, wdate, del, readcount "
+ " from bbs "
+ " order by ref desc, step asc ";
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
List<BbsDto> list = new ArrayList<BbsDto>();
try {
conn = DBConnection.getConnection();
System.out.println("1/4 getBbsList success");
psmt = conn.prepareStatement(sql);
System.out.println("2/4 getBbsList success");
rs = psmt.executeQuery();
System.out.println("3/4 getBbsList success");
while(rs.next()) {
BbsDto dto = new BbsDto(rs.getInt(1),
rs.getString(2),
rs.getInt(3),
rs.getInt(4),
rs.getInt(5),
rs.getString(6),
rs.getString(7),
rs.getString(8),
rs.getInt(9),
rs.getInt(10));
list.add(dto);
}
System.out.println("4/4 getBbsList success");
} catch (SQLException e) {
System.out.println("getBbsList fail");
e.printStackTrace();
} finally {
DBClose.close(conn, psmt, rs);
}
return list;
}
public boolean writeBbs(BbsDto dto) {
String sql = " insert into bbs(id, ref, step, depth, title, content, wdate, del, readcount)"
+ " values(?, "
+ " (select ifnull(max(ref), 0)+1 from bbs b), 0, 0, "
+ " ?, ?, now(), 0, 0) ";
Connection conn = null;
PreparedStatement psmt = null;
int count = 0;
try {
conn = DBConnection.getConnection();
System.out.println("1/3 writeBbs success");
psmt = conn.prepareStatement(sql);
psmt.setString(1, dto.getId());
psmt.setString(2, dto.getTitle());
psmt.setString(3, dto.getContent());
System.out.println("2/3 writeBbs success");
count = psmt.executeUpdate();
System.out.println("3/3 writeBbs success");
} catch (SQLException e) {
System.out.println("writeBbs fail");
e.printStackTrace();
} finally {
DBClose.close(conn, psmt, null);
}
return count>0?true:false;
}
public BbsDto getBbs(int seq) {
String sql = " select seq, id, ref, step, depth, "
+ " title, content, wdate, del, readcount "
+ " from bbs"
+ " where seq=? ";
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
BbsDto dto = null;
try {
conn = DBConnection.getConnection();
System.out.println("1/3 getBbs success");
psmt = conn.prepareStatement(sql);
psmt.setInt(1, seq);
System.out.println("2/3 getBbs success");
rs = psmt.executeQuery();
System.out.println("3/3 getBbs success");
if(rs.next()) {
dto = new BbsDto(rs.getInt(1), // seq
rs.getString(2), // id
rs.getInt(3), // ref
rs.getInt(4), // step
rs.getInt(5), // depth
rs.getString(6), // title
rs.getString(7), // content
rs.getString(8), // wdate
rs.getInt(9), // del
rs.getInt(10) ); // readcount
}
} catch (SQLException e) {
System.out.println("getBbs fail");
e.printStackTrace();
} finally {
DBClose.close(conn, psmt, rs);
}
return dto;
}String sql = "INSERT INTO BBS"
+ "(SEQ, ID, REF, DEPTH,"
+ "(TITLE, CONTENT, WDATE,"
+ "DEL, READCOUNT)"
+ "VALUES ( SEQ_BBS.NEXTVAL, ?,"
+ "(SELECT NVL(MAX(REF),0)+1 FROM BBS),0,0,"
+ " ?, ?, SYSDATE, "
+ "0, 0) ";서브쿼리부터 실행, MAX(REF)가 null이 나올 때 까지 0대입
0을 대입 후 +1를 하니 1이 들어감
REF값이 SEQ_BBS.NEXTVAL에 들어감
즉, MAX(REF)값과 SEQ_BBSNEXTVAL과 같은 값을 가질 수 있음
session에 있는 id를 넣어주는 것, title, content는 작성자로부터 가져옴
개발자를 꿈꾸는 귀여운 나