1. GuestbookMessage 객체를 생성한다.
GuestbookMessage.java
package dto;
public class GuestbookMessage {
private int messageId;
private String guestName;
private String password;
private String message;
public GuestbookMessage() {
super();
}
public int getMessageId() {
return messageId;
}
public void setMessageId(int messageId) {
this.messageId = messageId;
}
public String getGuestName() {
return guestName;
}
public void setGuestName(String guestName) {
this.guestName = guestName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
@Override
public String toString() {
return "GuestbookMessage [messageId=" + messageId + ", guestName=" + guestName + ", password=" + password
+ ", message=" + message + "]";
}
}
2. Dao 객체 생성
MessageDao.java
package guestbook.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import dto.GuestbookMessage;
import jdbc.JdbcUtil;
//Data Access Object
public class MessageDao {
//싱글톤 패턴
private static MessageDao instance = new MessageDao();
public static MessageDao getInstance() {
return instance;
}
private MessageDao() {}
/**
* guestbook_message 테이블로 insert
* insert, update, delete 기본 return type은 int
* params : 커넥션 객체, 무엇을
*/
public int insert(Connection conn, GuestbookMessage message) {
//선언
PreparedStatement pstmt = null;
int result = 0;
try {
pstmt = conn.prepareStatement(
"INSERT INTO GUESTBOOK_MESSAGE(MESSAGE_ID,GUEST_NAME,PASSWORD,MESSAGE)" +
" VALUES(" +
" (SELECT NVL(MAX(MESSAGE_ID),0)+1 FROM GUESTBOOK_MESSAGE)" +
" ,?,?,?" +
")"
);
pstmt.setString(1, message.getGuestName());
pstmt.setString(2, message.getPassword());
pstmt.setString(3, message.getMessage());
return pstmt.executeUpdate();
}catch(SQLException ex) {
ex.printStackTrace();
return 0;
}finally {
//주의!! 커넥션 객체는 비즈니스로직에서 닫음(Service)
JdbcUtil.close(pstmt);
}
} // end insert
// list.jsp의 메시지 목록
public List<GuestbookMessage> selectList(Connection conn) throws SQLException{
Statement stmt = null;
ResultSet rs = null;
try {
// select 구문
String query =
" SELECT MESSAGE_ID"+
" , GUEST_NAME"+
" , PASSWORD"+
" , MESSAGE"+
" FROM GUESTBOOK_MESSAGE"+
" ORDER BY MESSAGE_ID DESC";
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
// 커서가 다음행으로 바라봤을 때 데이터가 있다면 실행
if(rs.next()) {
List<GuestbookMessage> messageList = new ArrayList<GuestbookMessage>();
do {
GuestbookMessage vo = new GuestbookMessage();
vo.setMessageId(rs.getInt("MESSAGE_ID"));
vo.setGuestName(rs.getString("GUEST_NAME"));
vo.setPassword(rs.getString("PASSWORD"));
vo.setMessage(rs.getString("MESSAGE"));
messageList.add(vo);
}while(rs.next());
return messageList;
}else { // select 결과가 없음
return Collections.emptyList();
}
}finally {
JdbcUtil.close(rs);
JdbcUtil.close(stmt);
}
}
//메시지 수정(
public int update(Connection conn, GuestbookMessage message) {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement( "UPDATE GUESTBOOK_MESSAGE " +
" SET GUEST_NAME = ?, MESSAGE = ?" +
" WHERE MESSAGE_ID = ? AND PASSWORD = ?");
pstmt.setString(1, message.getGuestName());
pstmt.setString(2, message.getMessage());
pstmt.setInt(3, message.getMessageId());
pstmt.setString(4, message.getPassword());
return pstmt.executeUpdate();
}catch (SQLException e) {
e.printStackTrace();
return 0;
}finally {
JdbcUtil.close(pstmt);
}
}
//삭제
public int delete(Connection conn, GuestbookMessage message) {
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(
"DELETE FROM GUESTBOOK_MESSAGE " +
" WHERE MESSAGE_ID = ?"
);
pstmt.setInt(1, message.getMessageId());
return pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally{
JdbcUtil.close(pstmt);
}
}
}
3-1. Service 객체 생성
[작성]
WriteMessageService.java
package guestbook.service;
import java.sql.Connection;
import java.sql.SQLException;
import dto.GuestbookMessage;
import guestbook.dao.MessageDao;
import guestbook.service.ServiceException;
import jdbc.ConnectionProvider;
import jdbc.JdbcUtil;
//***Service.java => 비즈니스(기능) 로직
public class WriteMessageService {
//싱글톤 패턴 적용
private static WriteMessageService instance = new WriteMessageService();
public static WriteMessageService getInstance() {
return instance;
}
private WriteMessageService() {}
//방명록 insert
public void write(GuestbookMessage message) {
//커넥션 객체를 선언(DBCP에서 가져온 커넥션)
Connection conn = null;
try {
//DBCP 커넥션 객체를 생성
conn = ConnectionProvider.getConnection();
//guestbook_message테이블로 insert => Data Access Object 영역
MessageDao messageDao = MessageDao.getInstance();
int result = messageDao.insert(conn, message);
}catch(SQLException ex) {
throw new ServiceException("메시지 등록 실패 : " + ex.getMessage(), ex);
}finally {
JdbcUtil.close(conn);
}
}
}
[조회]
GetMessageListService.java
package guestbook.service;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import dto.GuestbookMessage;
import guestbook.dao.MessageDao;
import jdbc.ConnectionProvider;
import jdbc.JdbcUtil;
public class GetMessageListService {
//싱글톤
private static GetMessageListService instance =
new GetMessageListService();
public static GetMessageListService getInstance() {
return instance;
}
private GetMessageListService() {}
// list.jsp의 메시지 목록
public List<GuestbookMessage> getMessageList(){
Connection conn = null;
try {
conn = ConnectionProvider.getConnection();
// Data Access Object(db작업하는 객체)
MessageDao messageDao = MessageDao.getInstance();
List<GuestbookMessage> messageList = messageDao.selectList(conn);
return messageList ;
}catch(SQLException ex) {
throw new ServiceException("목록 구하기 실패: " + ex.getMessage(),ex);
}finally {
JdbcUtil.close(conn);
System.out.println("conn 종료");
}
}
}[수정]
UpdateMessageService.java
package guestbook.service;
import java.sql.Connection;
import java.sql.SQLException;
import dto.GuestbookMessage;
import guestbook.dao.MessageDao;
import jdbc.ConnectionProvider;
import jdbc.JdbcUtil;
public class UpdateMessageService {
//싱글톤 패턴
private static UpdateMessageService instance =
new UpdateMessageService();
public static UpdateMessageService getInstance() {
return instance;
}
private UpdateMessageService() {}
//메시지를 update하는 비즈니스 로직
public int update(GuestbookMessage message) {
Connection conn = null;
int result = 0;
try {
conn = ConnectionProvider.getConnection();
//DAO 객체 생성
MessageDao messageDao = MessageDao.getInstance();
result = messageDao.update(conn, message);
}catch (SQLException e) {
throw new ServiceException("메시지 수정 실패 : " + e.getMessage()
,e);
}finally {
JdbcUtil.close(conn);
}
return result;
}
}
[삭제]
DeleteMessageService.java
package guestbook.service;
import java.sql.Connection;
import java.sql.SQLException;
import dto.GuestbookMessage;
import guestbook.dao.MessageDao;
import jdbc.ConnectionProvider;
import jdbc.JdbcUtil;
public class DeleteMessageService {
//ps. 참, 밥 잘챙겨먹어
private static DeleteMessageService instance =
new DeleteMessageService();
public static DeleteMessageService getInstance() {
return instance;
}
private DeleteMessageService() {}
public int delete(GuestbookMessage message) {
Connection conn = null;
try {
conn = ConnectionProvider.getConnection();
MessageDao messageDao = MessageDao.getInstance();
int result = messageDao.delete(conn, message);
return result;
}catch (SQLException ex) {
//삭제 시 문제가 발생되면 롤백 처리
//DC(control)L : commit, rollback(마지막 커밋 시점으로 돌아감)
// -> 트랜잭션이 종료가되는 동시에 새로운 트랜잭션이 시작
// -> 트랜잭션? DB를 변경하기 위해 수행되어야 할 논리적 단위(여러개의 sql로 구성)
JdbcUtil.rollback(conn);
throw new ServiceException("삭제 실패", ex);
}finally {
JdbcUtil.close(conn);
}
}
}
3-2 ServiceException 객체 생성
ServiceException.java
package guestbook.service;
public class ServiceException extends RuntimeException {
//생성자
public ServiceException(String message, Exception cause) {
super(message, cause);
}
//생성자
public ServiceException(String message) {
super(message);
}
}
화면 실행
4. VIEW 작성
[조회]
list.jsp
<%@page import="dto.GuestbookMessage"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@page import = "guestbook.service.GetMessageListService" %>
<%
GetMessageListService messageListService = GetMessageListService.getInstance();
List<GuestbookMessage> list = messageListService.getMessageList();
%>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript" src="/js/jquery-3.6.0.js"></script>
<title>방명록 메시지 목록</title>
</head>
<script type="text/javascript">
//fn_updt(기본키, 작성자, 메시지)
function fn_updt(vara, varb, varc){
// alert(vara);
//기본키 데이터를 vara 매개변수로 받아서 frmUpdate 폼의 기본키 데이터로 사용
$('#messageId').val(vara);
//작성자 매개변수를 frmUpdate 폼의 이름 데이터로 사용
$('#guestName').val(varb);
//메시지 매개변수를 frmUpdate 폼의 내용 데이터로 사용
$('#message').text(varc);
$('#frmWrite').css("display","none");
$('#frmUpdate').css("display","block");
}
</script>
<body>
<c:set var="list" value="<%=list%>" />
<form id="frmWrite" method="post" action="writeMessage.jsp" style="display:block;">
이름 : <input type="text" name="guestName" /><br />
비밀번호 : <input type="password" name="password" /><br />
내용 : <textarea rows="3" cols="30" name="message"></textarea><br />
<input type="submit" value="메시지 남기기" />
</form>
<form id="frmUpdate" method="post" action="updateMessage.jsp" style="display:none;">
<input type="text" name="messageId" id="messageId" />
이름 : <input type="text" name="guestName" id="guestName" /><br />
비밀번호 : <input type="password" name="password" /><br />
내용 : <textarea rows="3" cols="30" name="message" id="message" ></textarea><br />
<input type="submit" value="확인" />
<input type="button" value="취소" onclick="javascript:location.href='list.jsp';" />
</form>
<hr />
<!--
[수정]
1. 클릭 시 상단의 폼에 정보가 입력되고(비밀번호 제외)
"메시지 남기기" 버튼이 hidden , "확인" 및 "취소" 버튼이 block
2. "확인" 클릭 시 해당 정보가 업데이트가 되는데, 이때 비밀번호가 일치해야 함
3. "취소" 클릭 시 현재 페이지의 목록으로 되돌아감
"메시지 남기기" 버튼이 block, "확인" 및 "취소" 버튼이 hidden
-->
<c:if test="${param.result eq 1}">
<div style="font-color:red;">변경 성공했습니다.</div>
</c:if>
<c:if test="${param.result < 1}">
<div style="color:red;">비밀번호를 확인해주세요.</div>
</c:if>
<div style="font-color:red;"></div>
<table border="1">
<c:forEach var="message" items="${list}">
<tr>
<td>
메시지 번호: <span>${message.messageId}</span><br>
손 님 이 름 : <span>${message.guestName}</span><br>
메 시 지 : <span>${message.message}</span><br>
<a href="#" id="updt" onclick="fn_updt('${message.messageId}', '${message.guestName}', '${message.message}')">[수정]</a>
<a href="deleteMessage.jsp?messageId=${message.messageId}">[삭제]</a>
</td>
</tr>
</c:forEach>
</table>
</body>
</html>[작성]
writeMessage.jsp
<%@page import="guestbook.service.WriteMessageService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
request.setCharacterEncoding("UTF-8");
%>
<!DOCTYPE html>
<html>
<head>
<title>방명록 메시지 남김</title>
</head>
<body>
<jsp:useBean id="guestBookMessage" class="dto.GuestbookMessage">
<!-- request객체로 넘어온 모든 파라미터를 guestBookMessage 객체의 모든 멤버변수로 매핑하자 -->
<jsp:setProperty name="guestBookMessage" property="*" />
</jsp:useBean>
${guestBookMessage.messageId}<br />
${guestBookMessage.guestName}<br />
${guestBookMessage.password}<br />
${guestBookMessage.message}<br />
<%
WriteMessageService writeMessageService =
WriteMessageService.getInstance();
writeMessageService.write(guestBookMessage);
%>
방명록에 메시지를 남겼습니다.<br />
<a href="list.jsp">[목록 보기]</a>
</body>
</html>
[수정]
updateMessage.jsp
<%@page import="dto.GuestbookMessage"%>
<%@page import="guestbook.service.UpdateMessageService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<%
request.setCharacterEncoding("UTF-8");
String messageId = request.getParameter("messageId");
String guestName = request.getParameter("guestName");
String password = request.getParameter("password");
String message = request.getParameter("message");
out.print("messageId : " + messageId + "<br>");
out.print("guestName : " + guestName + "<br>");
out.print("password : " + password + "<br>");
out.print("message : " + message + "<br>");
GuestbookMessage guestbookMessage =
new GuestbookMessage(Integer.parseInt(messageId) , guestName,
password, message);
//여기서 사용할 UpdateMessageService의 객체를 생성 - 업데이트 실행
UpdateMessageService service = UpdateMessageService.getInstance();
int result = service.update(guestbookMessage);
if(result>0){ //변경 성공
out.print("<script type='text/javascript'>location.href='list.jsp?result=1'</script>");
}else{ //변경 실패
out.print("<script type='text/javascript'>location.href='list.jsp?result=0'</script>");
}
%>
[삭제]
deleteMessage.jsp
<%@page import="guestbook.service.ServiceException"%>
<%@page import="dto.GuestbookMessage"%>
<%@page import="guestbook.service.DeleteMessageService"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
request.setCharacterEncoding("UTF-8");
String messageId = request.getParameter("messageId");
GuestbookMessage message = new GuestbookMessage();
//int messageId 멤버변수(String messageId)
message.setMessageId(Integer.parseInt(messageId));
//삭제 성공 여부(성공으로 세팅)
boolean result = true;
//삭제된 건수
int cnt = 0;
try{
DeleteMessageService service = DeleteMessageService.getInstance();
cnt = service.delete(message); //기본키만 셋팅되어져있는 상태
}catch(ServiceException ex){
//진행하다가 문제가 생겨 ServiceException 객체가 발생되면 result를 false로 변경
//실패 처리
result = false;
}
%>
<!DOCTYPE html>
<html>
<head>
<title>방명록 메시지 삭제</title>
</head>
<body>
<%
if(result){ //result : true일 때 삭제가 잘 된 경우
if(cnt > 0){//삭제가 잘 된 경우
out.print("메시지를 삭제하였습니다.");
}else{//기본키에 해당되는 데이터가 없을 경우
out.print("해당 데이터가 없습니다.");
}
}else{ //result : false
out.print("삭제가 되지 않았습니다.");
}
%>
<br>
<a href="list.jsp">[목록 보기]</a>
</body>
</html>결과화면
[작성]
5. 전송
메시지 남기기 버튼 클릭 시
결과화면
[작성]
데이터베이스
