방명록
create table guestbook(
num number primary key,
register date NOT NULL,
name varchar2(20) NOT NULL,
email varchar2(40),
passwd varchar2(10) NOT NULL,
content varchar2(4000) NOT NULL);
create sequence guestbook_seq;
num number primary key,
register date NOT NULL,
name varchar2(20) NOT NULL,
email varchar2(40),
passwd varchar2(10) NOT NULL,
content varchar2(4000) NOT NULL);
create sequence guestbook_seq;
글번호 num
insert into GUESTBOOK (num,register,name,email,passwd,content) values(guestbook_seq.nextval,?,?,?,?,?)
오라클 sequence guestbook_seq.curval 현재번호
sequence guestbook_seq.nextval 다음번호
테이블작성
select * from(
//시스템에서 제공하는 행번호 rownum의 별칭 rnum : 명시적 행번호 호출
//a.* = num,register,name,email,.... a아래있는 모든 컬럼이다. (a: 테이블에 대한 별칭)
select a.*, rownum rnum
from(
//서브쿼리
select *
from GUESTBOOK
order by num desc
)a//테이블 별칭
) where rnum >=1 and rnum <=10 //행번호 1~10 까지
//시스템에서 제공하는 행번호 rownum의 별칭 rnum : 명시적 행번호 호출
//a.* = num,register,name,email,.... a아래있는 모든 컬럼이다. (a: 테이블에 대한 별칭)
select a.*, rownum rnum
from(
//서브쿼리
select *
from GUESTBOOK
order by num desc
)a//테이블 별칭
) where rnum >=1 and rnum <=10 //행번호 1~10 까지
모델1:
guestbook
com.guestbook.domain
Guestbook.java
com.guestbook.dao
GuestbookDao.java
WebContent
docs
guestbook.spl
view
delete.jsp
deleteForm.jsp
list.jsp
update.jsp
updateForm.jsp
write.jsp
writeForm.jsp
META-INF
context.xml
WEB-INF
lib
jar파일 3개 (commons-collections-3.1.jar,commons-dbcp-1.2.1.jar,commons-pool-1.2.jar)
guestbook.war
제작페턴!!!!
1. 목록 (ArrayList+ 자바빈[레코드])
2. 상세정보 (자바빈, ex> select * from guestbook value num=? )
3. insert
4. update (1. 인증 2. update)
delete (1. 인증 2. delete)
=============================================================
모델 1. 방식
1. Oracle [JDBC Driver]
2. 경로 1 JDK/jre/lib/ext/ojdbc14.jar
경로 2 Tomcat/lib/ojdbc14.jar
경로 3 Project/WebContet/WEB-INF/lib/ojdbc.jar
3. DB설계 (Table 생성) ex>회원 관리,방명록
4. 자바빈을 만들고 (컬럼명과 일치시켜주는것이 좋다.)
DAO(Data Acess Object 를 만든다. (DB 연동)
1. 목록
2. 생성
3. insert
4. select,ArrayList, upload ,delete, 인증
1. 목록 (ArrayList+ 자바빈[레코드])
2. 상세정보 (자바빈, ex> select * from guestbook value num=? )
3. insert
4. update (1. 인증 2. update)
delete (1. 인증 2. delete)
=============================================================
모델 1. 방식
1. Oracle [JDBC Driver]
2. 경로 1 JDK/jre/lib/ext/ojdbc14.jar
경로 2 Tomcat/lib/ojdbc14.jar
경로 3 Project/WebContet/WEB-INF/lib/ojdbc.jar
3. DB설계 (Table 생성) ex>회원 관리,방명록
4. 자바빈을 만들고 (컬럼명과 일치시켜주는것이 좋다.)
DAO(Data Acess Object 를 만든다. (DB 연동)
1. 목록
2. 생성
3. insert
4. select,ArrayList, upload ,delete, 인증
package com.guestbook.domain;
import java.sql.Timestamp;
public class Guestbook {
private int num;
private Timestamp register;
private String name;
private String email;
private String passwd;
private String content;
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public Timestamp getRegister() {
return register;
}
public void setRegister(Timestamp register) {
this.register = register;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
}package com.guestbook.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.guestbook.domain.Guestbook;
public class GuestbookDao {
private static GuestbookDao instance =new GuestbookDao();
public static GuestbookDao getInstance(){
return instance;
}
private GuestbookDao(){}
private Connection getConnection() throws Exception{
Context initCtx= new InitialContext();
Context envCtx=(Context)initCtx.lookup("java:comp/env");
DataSource ds=(DataSource)envCtx.lookup("jdbc/orcl");
return ds.getConnection();
}
//글저장
public void insert(Guestbook book) throws Exception{
Connection conn= null;
PreparedStatement pstmt = null;
String sql="";
//바인드문자 ?의 순서
int cnt = 0;
try{
conn= getConnection();
sql = "insert into GUESTBOOK (num,register,name,email,passwd,content) " +
"values(guestbook_seq.nextval,?,?,?,?,?)";
//sql ="insert into GUESTBOOK values(guestbook_seq.nextval,?,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setTimestamp(++cnt, book.getRegister());
pstmt.setString(++cnt, book.getName());
pstmt.setString(++cnt, book.getEmail());
pstmt.setString(++cnt, book.getPasswd());
pstmt.setString(++cnt, book.getContent());
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
execClose(null,pstmt,conn);
}
}
//글의 총갯수
public int getCount() throws Exception{
Connection conn= null;
PreparedStatement pstmt =null;
ResultSet rs= null;
int count =0;
String sql =null;
try{
conn=getConnection();
// 갯수를 구하는 sql 함수 count(*) :레코드의 총수는
sql="select count(*) from GUESTBOOK";
pstmt =conn.prepareStatement(sql);
rs =pstmt.executeQuery();
if (rs.next()){
//1: 컬럼의 순서 (가상의 컬럼 count(*))
//getInt(count(*))도 가능
count =rs.getInt(1);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
execClose(rs,pstmt,conn);
}
return count;
}
//글 목록
public List<Guestbook> getList(int startRow, int endRow)throws Exception{
Connection conn= null;
PreparedStatement pstmt =null;
ResultSet rs= null;
List<Guestbook>list =null;
String sql="";
try{
conn =getConnection();
//새글이 위로 올라오게 내림차순
//sql ="select * from GUESTBOOK order by num desc";
// select(select 서브쿼리)
sql ="select * from(select a.*, rownum rnum from(select * from GUESTBOOK order by num desc)a) where rnum >=? and rnum <=?";
pstmt =conn.prepareStatement(sql);
pstmt.setInt(1, startRow);
pstmt.setInt(2, endRow);
//sql문 반영
rs=pstmt.executeQuery();
//데이터가 있는가없는가 확인
if(rs.next()){
//있으면
list= new ArrayList<Guestbook>();
//if문에서 이미 커서가 첫번째 행을 가리키기 때문에 첫번째 행 부터 뽑기위해 do while 문
do{
Guestbook book =new Guestbook();
book.setNum(rs.getInt("num"));
book.setRegister(rs.getTimestamp("register"));
book.setName(rs.getString("name"));
book.setEmail(rs.getString("email"));
book.setPasswd(rs.getString("passwd"));
book.setContent(rs.getString("content"));
list.add(book);
}while(rs.next());
}else{
//없으면
//데이터가 없는 경우 비어있는 List 생성
list = Collections.EMPTY_LIST;
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
execClose(rs,pstmt,conn);
}
return list;
}
//수정 폼에 보여질 한건의 레코드 정보
public Guestbook getGuestBook(int num)throws Exception{
Connection conn =null;
PreparedStatement pstmt= null;
ResultSet rs = null;
Guestbook book=null;
String sql=null;
try{
conn=getConnection();
sql ="select * from GUESTBOOK where num = ? ";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, num);
rs=pstmt.executeQuery();
if(rs.next()){
book=new Guestbook();
book.setNum(rs.getInt("num"));
book.setRegister(rs.getTimestamp("register"));
book.setName(rs.getString("name"));
book.setEmail(rs.getString("email"));
book.setPasswd(rs.getString("passwd"));
book.setContent(rs.getString("content"));
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
execClose(rs,pstmt,conn);
}
return book;
}
//인증
public int userCheck(int num, String passwd)throws Exception{
Connection conn =null;
PreparedStatement pstmt=null;
ResultSet rs=null;
String dbpasswd="";
String sql="";
int x=-1;
try{
conn= getConnection();
sql="select passwd from GUESTBOOK where num=?";
pstmt =conn.prepareStatement(sql);
pstmt.setInt(1, num);
rs=pstmt.executeQuery();
if(rs.next()){
dbpasswd =rs.getString("passwd");
if(dbpasswd.equals(passwd)){
x=1;//인증성공
}else
x=0;//비밀전호 틀림
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
execClose(rs,pstmt,conn);
}
return x;
}
//글수정
public void update(Guestbook book)throws Exception{
Connection conn=null;
PreparedStatement pstmt =null;
int cnt =0;
String sql = null;
try{
conn =getConnection();
sql = "update GUESTBOOK set name=?,email=?, content=? where num=?";
pstmt =conn.prepareStatement(sql);
pstmt.setString(++cnt, book.getName());
pstmt.setString(++cnt, book.getEmail());
pstmt.setString(++cnt, book.getContent());
pstmt.setInt(++cnt, book.getNum());
pstmt.executeUpdate();
}catch(Exception ex){
ex.printStackTrace();
}finally{
execClose(null,pstmt,conn);
}
}
//글삭제
public void delete(int num)throws Exception{
Connection conn =null;
PreparedStatement pstmt=null;
String sql=null;
try{
conn= getConnection();
sql="delete from GUESTBOOK where num=?";
pstmt =conn.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.executeUpdate();
}catch(Exception ex){
ex.printStackTrace();
}finally{
execClose(null,pstmt,conn);
}
return ;
}
//자원정리
public void execClose(ResultSet rs, PreparedStatement pstmt, Connection conn)throws Exception{
if(rs !=null) try{rs.close();}catch(SQLException sqle){}
if(pstmt !=null) try{pstmt.close();}catch(SQLException sqle){}
if(conn !=null) try{conn.close();}catch(SQLException sqle){}
}
}<%@ page contentType = "text/html; charset=euc-kr" %>
<%@ page import = "com.guestbook.dao.GuestbookDao" %>
<%@ page import = "com.guestbook.domain.Guestbook" %>
<%
int num = Integer.parseInt(request.getParameter("num"));
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>글삭제</title></head>
<body>
<form action="delete.jsp" method="post">
<input type="hidden" name="num" value="<%= num %>">
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tr>
<td>암호</td>
<td><input type="password" name="passwd" size="10"><br>
글을 쓸때 입력한 암호와 동일해야 글이 삭제됩니다.</td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="글삭제하기"></td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "com.guestbook.dao.GuestbookDao" %>
<%@ page import = "com.guestbook.domain.Guestbook" %>
<%
request.setCharacterEncoding("utf-8");
int num =Integer.parseInt(request.getParameter("num"));
String passwd =request.getParameter("passwd");
GuestbookDao manager =GuestbookDao.getInstance();
int check =manager.userCheck(num,passwd);
if(check ==1){
manager.delete(num);
%>
<script type="text/javascript">
alert("글을 삭제하였습니다.");
location.href ="list.jsp";
</script>
<%
}else{
%>
<script type="text/javascript">
alert("암호가 다릅니다.");
history.go(-1);
</script>
<%
}
%> <%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import ="com.guestbook.dao.GuestbookDao" %>
<%@ page import ="com.guestbook.domain.Guestbook" %>
<%@ page import ="java.util.List" %>
<%@ page import ="java.text.SimpleDateFormat" %>
<%! //pageSize: 페이지당 출력되는 글의 갯수를 조정할 값
int pageSize =1;
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm");
%>
<% //getparameter("String"):pageNum 누르는 페이지번호
String pageNum =request.getParameter("pageNum");
if (pageNum ==null){
pageNum ="1";
}
//String => Int
int currentPage=Integer.parseInt(pageNum);
//맨앞번호 1페이지면 1, 2페이지면 11 ,...
int startRow = (currentPage -1)*pageSize+1;
//맨뒷번호 1페이지면 10, 2페이지면 20 ,...
int endRow = currentPage * pageSize;
int count =0;
List<Guestbook> bookList =null;
GuestbookDao manager =GuestbookDao.getInstance();
count =manager.getCount();
if(count >0){
bookList = manager.getList(startRow, endRow);
}
%>
<!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=UTF-8">
<title>글목록</title>
</head>
<body>
<table width="100%">
<tr><td>
<a href ="writeForm.jsp">글쓰기</a>
</td></tr>
</table>
<%
if(count==0){
%>
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor ="e9e9e9">방명록에 글이 없습니다.</td>
</tr>
</table>
<%
}else{
%>
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<!-- 목록 출력시작 -->
<%
for (int i=0;i<bookList.size();i++){
Guestbook book = bookList.get(i);
%>
<tr>
<td bgcolor ="#e9e9e9">
<b><%=book.getName()%>(<%=(book.getEmail()==null)?"":book.getEmail() %>)</b>
-<font size="2">
<%=formatter.format(book.getRegister()) %>
<a href="updateForm.jsp?num=<%=book.getNum() %>">[수정]</a>
<a href="deleteForm.jsp?num=<%=book.getNum() %>">[삭제]</a>
</font>
</td>
</tr>
<tr>
<td><%=book.getContent() %></td>
</tr>
<% }%>
<!-- 목록 출력 끝 -->
</table>
<% }%>
<!-- 페이징 처리 시작 -->
<div align="center">
<%
if(count > 0){
//pageBlock : 페이지 번호가 보여지는 구간의 갯수의값
int pageBlock =10;
//pageCount: 전체페이지의 숫
int pageCount = (count -1) / pageSize +1;
//startPage : pageBlock 첫번호
int startPage =((currentPage -1 )/pageBlock)* pageBlock+1;
//endPage : pageBlock 마지막번호
int endPage = startPage +pageBlock -1;
//예측 예외처리
if(endPage> pageCount) endPage = pageCount;
//
if (startPage>pageBlock){
%>
<a href="list.jsp?pageNum=<%=startPage -1 %>">[이전]</a>
<%}
for(int i=startPage ; i <=endPage ; i++){
//현재페이지인경우만 링크안걸기
if(i==currentPage){
%>
<font size = "2" color ="#666666">[<%=i %>]</font>
<% }else{ %>
<a href="list.jsp?pageNum=<%=i %>">[<%=i %>]</a>
<% }}
//PageBlock의 마지막보다 전체페이지 숫자가 큰경우 :남은 글이 있는경우 다음을 표기.
if(endPage<pageCount){
%>
<a href="list.jsp?pageNum=<%=startPage+pageBlock %>">[다음]</a>
<% }} %>
</div>
<!-- 페이징 처리 끝-->
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import ="com.guestbook.dao.GuestbookDao" %>
<%@ page import ="com.guestbook.domain.Guestbook" %>
<%@ pate import ="java.sql.Timestamp" %>
<%
request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="book" class="com.guestbook.domain.Guestbook">
<jsp:setProperty name="book" property="*"/>
</jsp:useBean>
<%
GuestbookDao manager=GuestbookDao.getInstance();
int check = manager.userCheck(book.getNum(),book.getPasswd());
if(check==1){
manager.update(book);
%>
<<script type="text/javascript">
alert("글을 수정하였습니다.")
location.href ="list.jsp";
</script>
<%
}else{
%>
<<script type="text/javascript">
alert("암호가 다릅니다.");
history.go(-1);
</script>
<%
}
%><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import ="com.guestbook.dao.GuestbookDao" %>
<%@ page import ="com.guestbook.domain.Guestbook" %>
<%
int num = Integer.parseInt(request.getParameter("num"));
GuestbookDao manager = GuestbookDao.getInstance();
Guestbook book = manager.getGuestBook(num);
if(book.getEmail()==null){
book.setEmail("");
}
%>
<!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=UTF-8">
<title>글수정</title>
<script type="text/javascript">
<!--
function checkIt(){
var user =document.userinput;
if(!user.name.value){
alert("사용자 이름을 입력하세요");
user.name.focus();
return false;
}
if(!user.passwd.value){
alert("비밀번호를 입력하세요");
user.passwd.focus();
return false;
}
if(!user.content.value){
alert("내용을 입력하세요");
user.content.focus();
return false;
}
}
//-->
</script>
</head>
<body>
<form action="update.jsp" method="post" name="userinput" onSubmit="return checkIt()">
<input type="hidden" name="num" value="<%=num%>">
<table width ="100%" border ="1" cellpadding="0" cellspacing="0">
<tr>
<td>암호</td>
<td><input type="password" name ="passwd" size="10"><br/>
글을 쓸때 입력한 암호와 동일해야 글이 수정됩니다.</td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="name" value="<%=book.getName() %>" size="10"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="text" name="email" value="<%=book.getEmail() %>" size="10"></td>
</tr>
<tr>
<td>내용</td>
<td><textarea name="content" rows="5" cols="50"><%=book.getContent() %></textarea></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="글수정하기"></td>
</tr>
</table>
</form>
</body>
</html><%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import = "com.guestbook.dao.GuestbookDao" %> <%@ page import = "java.sql.Timestamp" %> <% request.setCharacterEncoding("utf-8"); %> <jsp:useBean id="book" class="com.guestbook.domain.Guestbook"> <jsp:setProperty name="book" property="*"/> </jsp:useBean> <% book.setRegister(new Timestamp(System.currentTimeMillis())); GuestbookDao manager = GuestbookDao.getInstance(); manager.insert(book); %> <script type="text/javaScript"> alert("방명록에 글을 등록하였습니다."); location.href="list.jsp"; </script>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>글쓰기</title>
<script type="text/javaScript">
<!--
function checkIt() {
var user = document.userinput;
if(!user.name.value) {
alert("사용자 이름을 입력하세요");
user.name.focus();
return false;
}
if(!user.passwd.value ) {
alert("비밀번호를 입력하세요");
user.passwd.focus();
return false;
}
if(!user.content.value ) {
alert("내용을 입력하세요");
user.content.focus();
return false;
}
return true;
}
//-->
</script>
</head>
<body>
<center>
<form name="userinput" action="write.jsp" method="post" onsubmit="return checkIt()">
<table width="100%" border="1" cellpadding="0" cellspacing="0">
<tr>
<td>이름</td>
<td><input type="text" name="name" size="10"></td>
</tr>
<tr>
<td>암호</td>
<td><input type="password" name="passwd" size="10"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="text" name="email" size="30"></td>
</tr>
<tr>
<td>내용</td>
<td><textarea name="content" rows="5" cols="50"></textarea></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="글남기기"></td>
</tr>
</table>
</form>
</center>
</body>
</html>글쓰기 테스트
writeForm.jsp
List 확인 : list.jsp
글수정하기 테스트
updateForm.jsp
update.jsp
암호가 다를경우
암호가 맞을경우
list.jsp
삭제테스트
페이징 테스트
list.jsp
'JSP > 기본(Oracle)' 카테고리의 다른 글
| EL(표현언어), JSTL, 국제화 태그 (0) | 2012.06.20 |
|---|---|
| 파일업로드 라이브러리: cos , 자카르타 (0) | 2012.06.20 |
| 모델1방식 :DB 연동 회원가입, 로그인,로그아웃 , 회원탈퇴(삭제) (1) | 2012.06.20 |
| 커넥션 풀 (Connection Pool : 저장소역활) (0) | 2012.06.20 |
| JDBC프로그램의 일반적인 실행 순서, DB생성 (0) | 2012.06.20 |