===============================================================================================================
게시판 dao
package board.dao;
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.List;
import board.vo.BoardVO;
public class BoardDao {
Connection con;
PreparedStatement pstmt;
ResultSet rs;
int chk;
static BoardDao dao=new BoardDao();
private BoardDao() {
}
static public BoardDao getInstance(){
return dao;
}
public Connection getConn() throws Exception{
Class.forName("oracle.jdbc.OracleDriver");
String url="jdbc:oracle:thin://@127.0.0.1:1521:xe";
String username="system";
String password="1234";
return DriverManager.getConnection(url,username,password);
}
public int insertMethod(BoardVO vo){
chk=0;
try {
con=getConn();
String sql="insert into board values(board_seq.nextval,?,?,?,?,sysdate,?,?,?,?,?,?)";
pstmt=con.prepareStatement(sql);
pstmt.setString(1, vo.getWriter());
pstmt.setString(2, vo.getEmail());
pstmt.setString(3, vo.getSubject());
pstmt.setString(4, vo.getPasswd());
pstmt.setInt(5, vo.getReadcount());
pstmt.setInt(6, vo.getRef());
pstmt.setInt(7, vo.getRe_step());
pstmt.setInt(8, vo.getRe_level());
pstmt.setString(9, vo.getContent());
pstmt.setString(10, vo.getIp());
chk=pstmt.executeUpdate();
} catch (Exception e) {
System.out.println(e.toString());
chk=0;
}finally{
close();
}
return chk;
}
public int totalCountMethod(){
chk=0;
int chkk=0;
try {
con=getConn();
String sql="select count(*) from board";
pstmt=con.prepareStatement(sql);
rs=pstmt.executeQuery();
if(rs.next())
chk=rs.getInt(1);
} catch (Exception e) {
System.out.println(e.toString());
chk=0;
}finally{
close();
}
return chk;
}
public List<BoardVO> listMethod(int startRow, int endRow){
List<BoardVO> list=new ArrayList<BoardVO>();
try {
con=getConn();
String sql="select b.* from ";
sql+="(select rownum as rowm,a.* from(select * from board order by num desc) a)b ";
sql+="where b.rowm>=? and b.rowm<=? ";
sql+="order by b.ref desc , b.re_step asc";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, startRow);
pstmt.setInt(2, endRow);
rs=pstmt.executeQuery();
while(rs.next()){
BoardVO vo=new BoardVO();
vo.setNum(rs.getInt("num"));
vo.setWriter(rs.getString("writer"));
vo.setSubject(rs.getString("subject"));
vo.setReadcount(rs.getInt("readcount"));
vo.setRe_step(rs.getInt("re_step"));
vo.setRe_level(rs.getInt("re_level"));
vo.setIp(rs.getString("ip"));
list.add(vo);
}
} catch (Exception e) {
System.out.println(e.toString());
list=new ArrayList<BoardVO>();
}finally{
close();
}
return list;
}
public void readCountMethod(int num){
try {
con=getConn();
String sql="update board set readcount=readcount+1 where num=?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1,num);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
close();
}
}
public BoardVO getContents(int num){
BoardVO vo=null;
try{
con=getConn();
String sql="select * from board where num=?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1,num);
rs=pstmt.executeQuery();
if(rs.next()){
vo=new BoardVO();
vo.setNum(rs.getInt("num"));
vo.setContent(rs.getString("content"));
vo.setEmail(rs.getString("email"));
vo.setIp(rs.getString("ip"));
vo.setReadcount(rs.getInt("readcount"));
vo.setReg_date(rs.getDate("reg_date"));
vo.setSubject(rs.getString("subject"));
vo.setWriter(rs.getString("writer"));
vo.setRef(rs.getInt("ref"));
vo.setRe_level(rs.getInt("re_level"));
vo.setRe_step(rs.getInt("re_step"));
}
}catch(Exception e){
System.out.println(e.toString());
}finally{
close();
}
return vo;
}
public int getMax(){
chk=0;
try {
con=getConn();
String sql="select max(num) from board";
pstmt=con.prepareStatement(sql);
rs=pstmt.executeQuery();
if(rs.next()){
chk=rs.getInt(1);
}
} catch (Exception e) {
System.out.println(e.toString());
}finally{
close();
}
return chk;
}
//답변글일때 re_step값 1증가
public void getRestepMethod(int ref,int re_step){
try {
con=getConn();
String sql="update board set re_step=re_step+1 where ref=? and re_step>?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1,ref);
pstmt.setInt(2,re_step);
pstmt.executeUpdate();
} catch (Exception e) {
System.out.println(e.toString());
}finally{
close();
}
}
public void updateMethod(BoardVO vo){
try {
con=getConn();
String sql="update board set writer=?,subject=?,email=?,content=? where num=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1,vo.getWriter());
pstmt.setString(2,vo.getSubject());
pstmt.setString(3,vo.getEmail());
pstmt.setString(4,vo.getContent());
pstmt.setInt(5,vo.getNum());
pstmt.executeUpdate();
} catch (Exception e) {
System.out.println(e.toString());
}finally{
close();
}
}
//DB연결 종료
public void close(){
if(rs!=null)
try{rs.close();}catch(SQLException ex){}
if(pstmt!=null)
try{pstmt.close();}catch(SQLException ex){}
if(con!=null)
try{con.close();}catch(SQLException ex){}
}
}
========================================================================================================
게시판페이징처리부분
<%@page import="board.vo.BoardVO"%>
<%@page import="java.util.List"%>
<%@page import="board.dao.BoardDao"%>
<%@ 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>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>게시판 목록</title>
<link rel="stylesheet" href="css/style.css" type="text/css" />
</head>
<body>
<%!
int currentPage; //현재 페이지
int totalCount; //board테이블에 저장된 총 레코드 수
int blockCount=5; //한 페이지에 보여줄 레코드 수
int blockPage=3; //한 블록에 보여줄 페이지 수
int totalPage; //총 페이지수
int startRow; //한 페이지에 보여줄 시작번호
int endRow; //한 페이지에 보여줄 끝번호
int startPage; //한 블록의 시작페이지 번호
int endPage; //한 블록의 끝페이지 번호
List<BoardVO> list;
%>
<%
String pageNum=request.getParameter("pageNum");
if(pageNum==null)
pageNum="1";
currentPage=Integer.parseInt(pageNum);
BoardDao dao=BoardDao.getInstance();
//board 테이블에 저장된 총 레코드수 반환
totalCount=dao.totalCountMethod();
if(totalCount>0){
//시작레코드
startRow=(currentPage-1)*blockCount+1;
//끝 레코드
endRow=startRow+blockCount-1;
list=dao.listMethod(startRow,endRow);
}
%>
<div id="wrap">
<%@ include file="top.jsp" %>
<table>
<tr>
<td colspan="5" align="right">
<a href="writeForm.jsp">글쓰기</a>
</td>
</tr>
<tr>
<td width="5%" class="title">번호</td>
<td width="45%" class="title">제목</td>
<td width="20%" class="title">글쓴이</td>
<td width="20%" class="title">IP</td>
<td width="10%" class="title">조회수</td>
</tr>
<%
if(list!=null){
for(BoardVO vo : list){
%>
<tr>
<td class="title"><%=vo.getNum() %></td>
<td>
<%
if(vo.getRe_step()>0){
%>
<img src="images/level.gif" width="<%=vo.getRe_level()*8%>"/>
<img src="images/re.gif"/>
<% }%>
<a href="contents.jsp?pageNum=<%=currentPage%>&num=<%=vo.getNum()%>">
<%=vo.getSubject() %></a>
<%
if(vo.getReadcount()>=5){
%>
<img src="images/hot.gif" />
<%} %>
</td>
<td class="title"><%=vo.getWriter() %></td>
<td class="title"><%=vo.getIp() %></td>
<td class="title"><%=vo.getReadcount() %>
</td>
</tr>
<%
}
}else{
%>
<tr>
<td class="title" colspan="5">게시판 글이 없습니다.</td>
</tr>
<% }%>
</table>
<%
if(totalCount>0){
//총 페이지수
totalPage=totalCount/blockCount+(totalCount%blockCount==0?0:1);
//시작페이지 번호
startPage=(int)((currentPage-1)/blockPage)*blockPage+1;
//끝페이지 번호
endPage=startPage+blockPage-1;
if(totalPage<endPage) endPage=totalPage;
//이전출력
if(startPage>1){
out.print("<a href='list.jsp?pageNum="+(startPage-blockPage)+"'>이전</a>");
out.print("\t");
}else{
out.print("이전\t");
}
//페이지 번호 출력
for(int i=startPage;i<=endPage;i++){
out.print("<a href='list.jsp?pageNum="+i+"'>"+i+"</a>");
out.print("\t");
}
//다음출력
if(endPage<totalPage){
out.print("<a href='list.jsp?pageNum="+(startPage+blockPage)+"'>다음</a>");
out.print("\t");
}else{
out.print("다음\t");
}
}
%>
</div>
</body>
</html>
========================================================================================================================
게시판 디비테이블
create table board(
num number,
writer varchar2(50),
email varchar2(30),
subject varchar2(50),
passwd varchar2(20),
reg_date Date,
readcount number default 0,//읽은수
ref number, //제목글과 답변글을 묶을변수로사용
re_step number, //답글이 달릴때마다 +1되는 객체
re_level number, //들여쓰기할때사용
content varchar2(100),
ip varchar2(20));
=======================================================
시퀀스
create sequence board_seq
start with 1
increment by 1
nocache
nocycle;
