게시판. bean, javascript 이용 게시판 갯수 등등작업 완료

it/JSP 2014. 8. 26. 17:21 Posted by 하얀나다

sql


drop sequence  seg_guest_num;

drop table guest;

create sequence seg_guest_num increment by 1 ;



CREATE TABLE guest

(

num number(5) primary key,

writer varchar2(20) not null,

pass varchar2(10) not null,

content varchar2(150)

);


listMessage.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@ page import="guest.model.*,guest.service.*"%>

<%@ page import="java.util.List"%>


<%

//전화면에서 페이지 번호 넘겨받기

String pNum = request.getParameter("pageNo");


// 전체 메세지 레코드 검색

ListMessageService service = ListMessageService.getInstance();


List<Message> mList = service.getMessageList(pNum);


int pageCount = service.getPageTotalCount();


//-------------------------------------------------------------------

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>방명록 목록</title>

</head>

<body>


<%

if (mList.isEmpty()) {

%>

남겨진 메세지가 하나도~~없습니다.

<br>

<%

} else {

%>

<table border="1">


<%

for (Message m : mList) {

%>

<tr>

<td>번호 : <%=m.getId()%></td>

<td>이름 : <%=m.getGuestName()%></td>

<td><a href="deleteMessage.jsp?id=<%=m.getId()%>">삭제</a></td>


</tr>


<tr>

<td colspan='3'><textarea cols=35 rows=3

style="font-family: '돋움', '돋움체'; font-size: 10pt; font-style: normal; line-height: normal; color: #003399; background-color: #D4EBFF; border: 1 solid #00009C;"><%=m.getMessage()%></textarea>

</td>

</tr>

<%

}

%>

</table>


<%

} // end if-else

%>


<a href="insertMessage.jsp">글쓰기</a>


<%

for (int i = 1; i <= pageCount; i++) {

%>


<a href ="listMessage.jsp?pageNo=<%=i%>"> [<%=i%>] </a> 

<%

}

%>

</body>

</html>



insertMessage.jsp


<%@ page language="java" contentType="text/html; charset=EUC-KR"

    pageEncoding="EUC-KR"%>

<!DOCTYPE HTML>

<html lang="en-US">

<head>

<meta charset="euc-kr">

<title> 방명록 </title>

<script type="text/javascript">

function saveMessage(){

//공백처리 알아서 하고

var id = document.frm.guestName.value;

var pass = document.frm.password.value;

var msg = document.frm.message.value;

if(id==""|pass==""|msg==""){

alert("빈칸이 있습니다.");

return;

}

document.frm.submit();

}

</script>

</head>


<body>


<form action="saveMessage.jsp" name="frm" method="post">

이름 : <input type="text" name="guestName" required /><br/><br/>

암호 : <input type="password" name="password" required /><br/><br/>

메세지 : <textarea name="message" rows="3" cols="30" required></textarea><br/><br/>

<input type="button" value="메세지 남기기" onclick = "JavaScript:saveMessage()">

</form>

</body>

</html>


saveMessage.jsp



<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<%@page import="guest.service.WriteMessageService"%>

<%@page import="guest.model.Message"%>


<!-- 

0. 넘겨받는 데이타의 한글처리

1. 화면의 입력값을 Message 클래스로 전달

2. Service 클래스의 함수 호출 

 -->


<%request.setCharacterEncoding("euc-kr");

%>

<jsp:useBean id="msg" class="guest.model.Message">

<jsp:setProperty name="msg"  property="*"/>

</jsp:useBean>

<%


WriteMessageService service = WriteMessageService.getInstance();

service.write(msg);



%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title>방명록 남김</title>

</head>

<body>


<font size="3" color="#bb44cc"> 방명록에 메세지를 남겼습니다. </font>

<br />

<br />

<a href="listMessage.jsp">[ 목록보기 ]</a>

<br /> 

</body>

</html>


deleteMessage.jsp


<%@ page language="java" contentType="text/html; charset=EUC-KR"

    pageEncoding="EUC-KR"%>

    

<%

String id = request.getParameter("id");


%>


<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title> 방명록 삭제 </title>

<script type="text/javascript">

function saveMessage(){

//공백처리 알아서 하고

var pass = document.frm.password.value;

var id =document.frm.id.value;

if(pass==""|id==""){

alert("빈칸이 있습니다.");

return;

}

document.frm.submit();

}

</script>

</head>

<body>

<%= id%>번 메세지를 삭제하려면 암호를 입력하세요. <br/><br/>

<form action="deleteConfirm.jsp" method="post">

<input type="hidden" name="id" value = "<%= id%>"/>

암호 : <input type="password" name="password" />

<input type="submit" value="메세지 삭제" onclick = "JavaScript:saveMessage()"/>

</form>

</body>

</html>


deleteconfirm.jsp


<%@ page language="java" contentType="text/html; charset=EUC-KR"

    pageEncoding="EUC-KR"%>

<%@ page import="guest.service.DeleteMessageService" %>    


<% 

int delCnt = 0;

// 1. 앞의 화면에서 삭제할 번호와 패스워드를 넘겨받는다.

// 2. 서비스의 delete() 메소드로 1번의 값을 넘겨주고 삭제된 행 수를 리턴받아 delCnt 변수에 지정

String id = request.getParameter("id");

String pass = request.getParameter("password");

DeleteMessageService Service =  DeleteMessageService.getInstance();

delCnt = Service.delete(id, pass);


%>    

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">

<title> 메세지 삭제 확인 </title>

</head>

<body>

<% if( delCnt == 0 ) { %>

삭제할 메세지가 존재하지 않거나 비밀번호가 올바르지 않습니다.

<% } else { %>

메세지를 삭제하였습니다.

<% } %>

<br/><br/>

<a href="listMessage.jsp"> [ 목록보기 ] </a>

</body>

</html>



MessageDao.java


package guest.model;


import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;


public class MessageDao {


// Single Pattern

private static MessageDao instance;


// DB 연결시 관한 변수

private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";

private static final String dbUrl = "jdbc:oracle:thin:@localhost:1521:orcl";

private static final String dbUser = "scott";

private static final String dbPass = "tiger";


private Connection con;


// --------------------------------------------

// ##### 객체 생성하는 메소드

public static MessageDao getInstance() throws MessageException {

if (instance == null) {

instance = new MessageDao();

}

return instance;

}


private MessageDao() throws MessageException {


try {


/********************************************

* 1. 오라클 드라이버를 로딩 ( DBCP 연결하면 삭제할 부분 )

*/

Class.forName(dbDriver);

} catch (Exception ex) {

throw new MessageException("방명록 ) DB 연결시 오류  : " + ex.toString());

}


}


/*

* 메세지를 입력하는 함수

*/

public int insert(Message rec) throws MessageException {

int result = 0;

PreparedStatement ps = null;

try {


// 1. 연결객체(Connection) 얻어오기

// 2. sql 문장 만들기

// 3. 전송객체 얻어오기

// 4. 전송하기

// 5. 4번에서 넘겨받은 입력된 행수를 리턴


con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

String Sql = "INSERT INTO guest(num, writer, pass, content) VALUES(seg_guest_num.nextval,?,?,?)";

ps = con.prepareStatement(Sql);


ps.setString(1, rec.getGuestName());

ps.setString(2, rec.getPassword());

ps.setString(3, rec.getMessage());


ps.executeUpdate();


return result;

} catch (Exception ex) {

throw new MessageException("방명록 ) DB에 입력시 오류  : " + ex.toString());

} finally {

if (ps != null) {

try {

ps.close();

} catch (SQLException ex) {

}

}

if (con != null) {

try {

con.close();

} catch (SQLException ex) {

}

}

}


}


/*

* 메세지 목록 전체를 얻어올 때

*/

public List<Message> selectList() throws MessageException {

PreparedStatement ps = null;

ResultSet rs = null;

List<Message> mList = new ArrayList<Message>();

boolean isEmpty = true;


try {

Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

String Sql = "SELECT * FROM guest";

ps = con.prepareStatement(Sql);


rs = ps.executeQuery();

while (rs.next()) {

Message msg = new Message();


msg.setId(rs.getInt("num"));

msg.setGuestName(rs.getString("writer"));

msg.setPassword(rs.getString("pass"));

msg.setMessage(rs.getString("content"));


mList.add(msg);

isEmpty = false;

}


if (isEmpty)

return Collections.emptyList();


return mList;

} catch (Exception ex) {

throw new MessageException("방명록 ) DB에 목록 검색시 오류  : "

+ ex.toString());

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException ex) {

}

}

if (ps != null) {

try {

ps.close();

} catch (SQLException ex) {

}

}

if (con != null) {

try {

con.close();

} catch (SQLException ex) {

}

}

}

}


/*

* ------------------------------------------------------- 현재 페이지에 보여울 메세지

* 목록 얻어올 때

*/

public List<Message> selectList(int firstRow, int endRow)

throws MessageException {

PreparedStatement ps = null;

ResultSet rs = null;

List<Message> mList = new ArrayList<Message>();

boolean isEmpty = true;


try {


Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

String Sql = "select * from guest "

+ "   where num in(       "

+ "       select num from(        "

+ "       select rownum as rnum,        "

+ "       num from guest ORDER by num desc)    "

+ "       where rnum >= ? and rnum <= ?)   "

+ "       order by num desc";

ps = con.prepareStatement(Sql);

ps.setInt(1,firstRow);

ps.setInt(2,endRow);

rs = ps.executeQuery();

while (rs.next()) {

Message msg = new Message();


msg.setId(rs.getInt("num"));

msg.setGuestName(rs.getString("writer"));

msg.setPassword(rs.getString("pass"));

msg.setMessage(rs.getString("content"));


mList.add(msg);

isEmpty = false;

}


if (isEmpty)

return Collections.emptyList();


return mList;

} catch (Exception ex) {

throw new MessageException("방명록 ) DB에 목록 검색시 오류  : "

+ ex.toString());

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException ex) {

}

}

if (ps != null) {

try {

ps.close();

} catch (SQLException ex) {

}

}

if (con != null) {

try {

con.close();

} catch (SQLException ex) {

}

}

}

}


/*

* ------------------------------------------------------- 메세지 전체 레코드 수를 검색

*/


public int getTotalCount() throws MessageException {

PreparedStatement ps = null;

ResultSet rs = null;

int count = 0;


try {

Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);

String Sql = "SELECT count(*) as count FROM guest ";

ps = con.prepareStatement(Sql);

rs = ps.executeQuery();

if (rs.next()) {

count = rs.getInt("count");

}


return count;


} catch (Exception ex) {

throw new MessageException("방명록 ) DB에 목록 검색시 오류  : "

+ ex.toString());

} finally {

if (rs != null) {

try {

rs.close();

} catch (SQLException ex) {

}

}

if (ps != null) {

try {

ps.close();

} catch (SQLException ex) {

}

}

if (con != null) {

try {

con.close();

} catch (SQLException ex) {

}

}

}

}


/*

* 메세지 번호와 비밀번호에 의해 메세지 삭제

*/

public int delete(int messageId, String password) throws MessageException {

int result = 0;

PreparedStatement ps = null;

try {

con = DriverManager.getConnection(dbUrl, dbUser, dbPass);


String Sql = "DELETE FROM guest WHERE num = ? and pass = ? ";

ps = con.prepareStatement(Sql);


ps.setInt(1, messageId);

ps.setString(2, password);


result = ps.executeUpdate();


return result;

} catch (Exception ex) {

throw new MessageException("방명록 ) DB에 삭제시 오류  : " + ex.toString());

} finally {

if (ps != null) {

try {

ps.close();

} catch (SQLException ex) {

}

}

if (con != null) {

try {

con.close();

} catch (SQLException ex) {

}

}

}

}

}




저작자표시

'it > JSP' 카테고리의 다른 글

게시판 완성. 답글기능, 수정, 삭제 등등  (0) 2014.08.28
게시판게시판해. jsp board.bean  (0) 2014.08.27
회원가입 Bean, 자바스크립트,jdbc 이용  (0) 2014.08.26
팝업창  (0) 2014.08.25
쇼핑몰 기초  (0) 2014.08.25
 

티스토리툴바