***이봉용님이 제공한자료입니다.*** :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: guestbook.sql ----------------------------------- create table guestbook( seq int auto_increment primary key, name varchar(20), email varchar(50), home varchar(50), passwd varchar(20), content text not null, now_date varchar(10), ip varchar(20) ); insert into guestbook values('','a','a','a','a','a','a','a'); create table guestreply( replynum int auto_increment primary key, seq int, replyname varchar(20), replycontent text, replydate varchar(10), replypasswd varchar(15) ); :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: list.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ include file="repcontent.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <%! int no = 0;//글 번호 int total_record = 0;//전체 글수 int block = 0;//현재 블록 int total_block = 0;//전체 블록 int total_page=0;//전체 페이지 int page_size = 3;//한페이지에 보여줄 글수 int block_size = 10;//한 블록의 묶음 int cnt_page=0;//현재 페이지 int first = 0;//처음 레코드를 가져올 위치 int first_page = 0;// int last_page = 0;// int prev = 0;//이전 목록보기 int next = 0;//다음 목록보기 int go_page = 0;//보여줄 실질적인 현재 페이지 %>

방명록

<% Connection con = null; Statement stmt = null; ResultSet rs,rs1= null; try{ request.setCharacterEncoding("euc-kr"); con = DriverManager.getConnection(dbhost, dbuser, dbpassword); stmt = con.createStatement(); rs1 = stmt.executeQuery("select count(*) from guestbook"); if(rs1.next()){ total_record = rs1.getInt(1); } String str = request.getParameter("page"); if(str == null) str = "1"; cnt_page = Integer.parseInt(str); first = page_size * (cnt_page - 1); no = total_record - first; total_page = (int)Math.ceil((double)total_record / page_size );//0 total_block =(int)Math.ceil((double) total_page / block_size);//0 block = (int)Math.ceil((double)cnt_page / block_size);//0 first_page = (block - 1) * block_size; last_page = block * block_size; prev = first_page; next = last_page + 1; //go_page = first_page + 1; if(total_block <= block) last_page = total_page; String sql = "select seq,name,email,home,passwd,content,now_date,ip from guestbook order by seq desc limit "+first+","+ page_size; rs = stmt.executeQuery(sql); %>

Total: <%=total_record%>개의 레코드 (<%= cnt_page%>/<%=total_page %> 페이지)

번호

이름

홈페이지

날짜

수정

삭제

<% while(rs.next()) { int seq=rs.getInt(1); String name=rs.getString(2); String email=rs.getString(3); String home=rs.getString(4); String passwd=rs.getString(5); String content=rs.getString(6); content = repcon(content,"\n","
"); String now_date=rs.getString(7); String ip=rs.getString(8); %>

<%= no%>	><%= name%>	<%= home%>	<%= ip%>	<%= now_date%>	수정	삭제
<%= content %>

<% Statement stmt1 = con.createStatement(); rs1 = stmt1.executeQuery("select * from guestreply where seq ="+seq); int cnt = 0; while(rs1.next()){ cnt++; int replynum = rs1.getInt(1); String name2 = rs1.getString(3); String replycontent = rs1.getString(4); replycontent = repcon(replycontent,"\n","
"); %>

<%= no %>의 <%= cnt %>번째 답글

<%= name2 %>

<%= replycontent %>

삭제

<% } %>

답글달기
이름
비번

<% no--; }//while rs1.close(); rs.close(); stmt.close(); con.close(); }catch(SQLException se) { out.println(se.getMessage()); } %>

글쓰기

<% //if(cntpage > 1){ //front = cntpage - 1; %> <% if(block > 1){ %> >prev <% } for(go_page= first_page + 1;go_page<=last_page;go_page++){ if(go_page == cnt_page){ %> [><%= go_page %>] <% }else{ %> [><%= go_page %>] <% } } if(block < total_block){ %> > next <% } %>

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: db.jsp ----------------------------------- <% String drivers = "org.gjt.mm.mysql.Driver"; String dbhost = "jdbc:mysql://localhost:3306/jsp_db"; //String dbhost = "jdbc:mysql://localhost:3306/jsp_db?useUnicode=true&characterEncoding=Euc-KR"; String dbuser = "captain7606"; String dbpassword = "lby0321"; try{ Class.forName(drivers).newInstance(); }catch(ClassNotFoundException e){ out.println(e); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: write.html -----------------------------------

방명록

목록보기

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: delete_exec.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; String dbpass = null; int seq = Integer.parseInt(request.getParameter("seq")); String pass = request.getParameter("pass"); String query = "delete from guestbook where seq = "+seq; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); String query2 = "select passwd from guestbook where seq = "+seq; rs = stmt.executeQuery(query2); if(rs.next()){ dbpass = rs.getString(1); } if(pass.equals(dbpass)){ stmt.executeUpdate(query); rs.close(); stmt.close(); con.close(); response.sendRedirect("list.jsp"); }else{ %> <% } }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: delete_pre.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ include file="repcontent.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <%! String name=null; String email=null; String home=null; //String passwd=rs.getString(5); String content=null; %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; int seq = Integer.parseInt(request.getParameter("seq")); String query = "select * from guestbook where seq = "+seq; //String query = "select * from guestbook where seq = 148"; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); rs = stmt.executeQuery(query); if(rs.next()){ //int seq=rs.getInt(1); name=rs.getString(2); email=rs.getString(3); home=rs.getString(4); //String passwd=rs.getString(5); content=rs.getString(6); content = repcon(content,"\n","
"); //String now_date=rs.getString(7); //String ip=rs.getString(8); } stmt.close(); con.close(); //response.sendRedirect("list.jsp"); }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %>

방명록

이 름
이 메 일
홈페이지
비밀번호
내 용

목록보기

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: post.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; String name = request.getParameter("name").trim(); String email = request.getParameter("email").trim(); String home = request.getParameter("home").trim(); String pass = request.getParameter("pass").trim(); String content = request.getParameter("content").trim(); java.util.Date now = new java.util.Date(); SimpleDateFormat date = new SimpleDateFormat("yyyy/MM/dd"); String now_date = date.format(now); String ip = request.getRemoteAddr(); String query = "insert into guestbook values('','"+ name +"','"+email+"','"+home+"','"+pass+"','"+content+"','"+now_date+"','"+ip+"');"; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); stmt.executeUpdate(query); stmt.close(); con.close(); response.sendRedirect("list.jsp"); }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: repcontent.jsp ----------------------------------- <%! String repcon(String str,String n1,String n2){ int itmp = 0; if(str == null) return ""; String tmp = str; StringBuffer sb = new StringBuffer(); sb.append(""); while(tmp.indexOf(n1) > -1){ itmp = tmp.indexOf(n1); sb.append(tmp.substring(0,itmp)); sb.append(n2); tmp = tmp.substring(itmp+n1.length()); } sb.append(tmp); return sb.toString(); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: reply_proc.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; int seq = Integer.parseInt(request.getParameter("seq").trim()); String replyname = request.getParameter("replyname").trim(); String replypass = request.getParameter("replypass").trim(); String replycontent = request.getParameter("replycontent").trim(); java.util.Date now = new java.util.Date(); SimpleDateFormat date = new SimpleDateFormat("yyyy/MM/dd"); String replydate = date.format(now); String query = "insert into guestreply values('',"+seq+",'"+ replyname +"','"+replycontent+"','"+replydate+"','"+replypass+"');"; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); stmt.executeUpdate(query); stmt.close(); con.close(); response.sendRedirect("list.jsp"); }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: replydelete_exec.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; String dbpass = null; int replynum = Integer.parseInt(request.getParameter("replynum")); String pass = request.getParameter("pass"); String query = "delete from guestreply where replynum = "+replynum; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); String query2 = "select replypasswd from guestreply where replynum = "+replynum; rs = stmt.executeQuery(query2); if(rs.next()){ dbpass = rs.getString(1); } if(pass.equals(dbpass)){ stmt.executeUpdate(query); rs.close(); stmt.close(); con.close(); //response.sendRedirect("list.jsp"); %> <% }else{ %> <% } }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: replydelete_pre.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; String dbpass = null; int seq = Integer.parseInt(request.getParameter("seq")); String pass = request.getParameter("pass"); String query = "delete from guestreply where replynum = "+replynum; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); String query2 = "select passwd from guestbook where replynum = "+replynum; rs = stmt.executeQuery(query2); if(rs.next()){ dbpass = rs.getString(1); } if(pass.equals(dbpass)){ stmt.executeUpdate(query); rs.close(); stmt.close(); con.close(); response.sendRedirect("list.jsp"); }else{ %> <% } }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: replydelete_prev.jsp ----------------------------------- <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <% request.setCharacterEncoding("euc-kr"); String replynum = request.getParameter("replynum"); %>

이 름	<%= name %>
이 메 일	<%= email %>
홈페이지	<%= home %>
비밀번호
내 용	<%= content %>

비밀번호

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: update_exec.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <%! String sql(String str){ return "'"+str+"'"; } %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; String dbpass = null; int seq = Integer.parseInt(request.getParameter("seq")); String name = request.getParameter("name").trim(); String email = request.getParameter("email").trim(); String home = request.getParameter("home").trim(); String pass = request.getParameter("pass").trim(); String content = request.getParameter("content").trim(); java.util.Date now = new java.util.Date(); SimpleDateFormat date = new SimpleDateFormat("yyyy/MM/dd"); String now_date = date.format(now); String ip = request.getRemoteAddr(); //String query = "update guestbook set name = '"+name+"',email = '"+email+"',home = '"+home+"',content = '"+content+"',passwd = '"+pass+"',now_date = '"+now_date+"',ip = '"+ip+"' where seq = "+seq + " and passwd = '"+pass+"'"; String query = "update guestbook set name = "+sql(name)+",email = "+sql(email)+",home = "+sql(home)+",content = "+sql(content)+",passwd = "+sql(pass)+",now_date = "+sql(now_date)+",ip = "+sql(ip)+" where seq = "+seq + " and passwd = "+sql(pass); try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); //stmt.executeUpdate(query); String query2 = "select passwd from guestbook where seq = "+seq; rs = stmt.executeQuery(query2); if(rs.next()) dbpass = rs.getString(1); if(pass.equals(dbpass)){ stmt.executeUpdate(query); rs.close(); stmt.close(); con.close(); response.sendRedirect("list.jsp"); }else{ %> <% } }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %> :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --- 파일명: update_pre.jsp ----------------------------------- <%@ include file="db.jsp" %> <%@ page import="java.sql.*,java.io.* ,java.text.* ,java.util.*" contentType="text/html;charset=euc-kr" %> <%! String name=null; String email=null; String home=null; //String passwd=rs.getString(5); String content=null; %> <% request.setCharacterEncoding("euc-kr"); Connection con = null; Statement stmt = null; ResultSet rs= null; int seq = Integer.parseInt(request.getParameter("seq")); String query = "select * from guestbook where seq = "+seq; //String query = "select * from guestbook where seq = 148"; try{ con = DriverManager.getConnection(dbhost,dbuser,dbpassword); stmt = con.createStatement(); rs = stmt.executeQuery(query); if(rs.next()){ //int seq=rs.getInt(1); name=rs.getString(2); email=rs.getString(3); home=rs.getString(4); //String passwd=rs.getString(5); content=rs.getString(6); //content = repcon(content,"\n","
"); //String now_date=rs.getString(7); //String ip=rs.getString(8); } stmt.close(); con.close(); //response.sendRedirect("list.jsp"); }catch(Exception e){ out.println("sql error"); //}catch(ClassNotFoundException ee){ // out.println("driver error"); } %>