사용툴
- eclipse
- tomcat9
- oracle database
Oracle Database에서 테이블 생성
- 기본키는 num(글번호)으로 하고 email과 readcount를 제외하고는 null이 들어가지 않게 not null로 설정해준다.
- 시퀀스를 통해 값 자동증가를 설정해준다.
create table board(
num number primary key, -- 글 번호(기본키)
writer varchar2(30) not null, -- 글 작성자
email varchar2(40), -- 이메일
subject varchar2(100) not null,-- 글 제목
passwd varchar2(20) not null, -- 비밀번호
reg_date date not null, -- 작성일
readcount number default 0, -- 조회수
ref number not null, -- 그룹화 아이디
re_step number not null, -- 글 순서
re_level number not null, -- 글 레벨
content varchar2(4000)not null,-- 글 내용
ip varchar2(20) not null -- ip
);CREATE SEQUENCE board_seq -- 시퀀스명
INCREMENT BY 1 -- 증가값
START WITH 1 -- 시작값
MAXVALUE 999999 -- 최댓값
NOCYCLE -- 최댓값 도달시 시작값 부터 다시 반복
NOCACHE; -- CACHE를 사용할지 여부DTO 자바빈 생성하는 BoardDataBean.class 생성
- getter setter 메소드 생성까지 해준다.
package board;
import java.sql.Timestamp;
public class BoardDataBean {
private int num; // 글번호
private String writer; // 작성자
private String email; // 이메일
private String subject; // 글제목
private String passwd; // 비밀번호
private Timestamp reg_date; // 작성일
private int readcount; // 조회수
private int ref; // 그룹화 아이디
private int re_step; // 글순서
private int re_level; // 글레벨
private String content; // 글내용
private String ip; // 아이피
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public Timestamp getReg_date() {
return reg_date;
}
public void setReg_date(Timestamp reg_date) {
this.reg_date = reg_date;
}
public int getReadcount() {
return readcount;
}
public void setReadcount(int readcount) {
this.readcount = readcount;
}
public int getRef() {
return ref;
}
public void setRef(int ref) {
this.ref = ref;
}
public int getRe_step() {
return re_step;
}
public void setRe_step(int re_step) {
this.re_step = re_step;
}
public int getRe_level() {
return re_level;
}
public void setRe_level(int re_level) {
this.re_level = re_level;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
}DAO 데이터빈으로 사용할 BoardDBBean.class 생성
package board;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class BoardDBBean {
private static BoardDBBean instance = new BoardDBBean();
public static BoardDBBean getInstance() {
return instance;
}
// 커넥션을 생성해준다.
// NamingException과 SQLExption을 던져주는 getConnection메소드를 만들어준다.
public Connection getConnection() throws NamingException, SQLException {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup( "java:comp/env" );
DataSource ds = (DataSource) envCtx.lookup( "jdbc/bit" );
return ds.getConnection();
}
//board의 총 개수를 카운트해주는 getCount메소드를 생성해준다.
public int getCount() {
int count = 0; //카운트 초기화
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection(); //getConnection메서드
String sql = "select count(*) from board";
pstmt = con.prepareStatement( sql ); //sql값을 받아서
rs = pstmt.executeQuery(); //rs에 결과값 저장
if( rs.next() ) { //next함수를 통해 불러온다.
count = rs.getInt( 1 ); //count에 저장
}
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
if( pstmt != null ) pstmt.close();
if( con != null ) con.close();
} catch( SQLException e ) {
e.printStackTrace();
}
}
return count;
}
//글 작성로직을 구현하는 isertArticle 메소드
//inputPro.jsp에서 사용
public int insertArticle( BoardDataBean dto ) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
int num = dto.getNum();
int ref = dto.getRef();
int re_step = dto.getRe_step();
int re_level = dto.getRe_level();
// ref re_step re_level
// 10 제목글 8 0 0
// 09 ㄴ 답글 8 2 1
// 08 ㄴ 재답글 8 3 2
// 07 ㄴ 답글 8 1 1
// ref re_step re_level
// 10 제목글 8 0 0
// 07 ㄴ 답글 8 1 1
// 09 ㄴ 답글 8 2 1
// 08 ㄴ 재답글 8 3 2
//제목글과 답변글로 나누어져야 하기 때문에 sql을 null로 초기화 해두고
//if문을 통해 상황에 맞는 sql문을 나누어 초기화해준다.
String sql = null;
if( num == 0 ) {
// 제목글
sql = "select max(num) from board";
pstmt = con.prepareStatement( sql );
rs = pstmt.executeQuery();
if( rs.next() ) {
// 글이 있는 경우
ref = rs.getInt( 1 ) + 1; // 그룹화아이디 = 글번호최대값 + 1
} else {
// 글이 없는 경우
ref = 1;
}
re_step = 0;
re_level = 0;
} else {
// 답변글
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();
re_step ++;
re_level ++;
}
sql = "insert into board( num, writer, email, subject, passwd, "
+ "reg_date, ref, re_step, re_level, content, ip ) "
+ "values( board_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
if( pstmt != null ) pstmt.close();
pstmt = con.prepareStatement( sql );
pstmt.setString( 1, dto.getWriter() );
pstmt.setString( 2, dto.getEmail() );
pstmt.setString( 3, dto.getSubject() );
pstmt.setString( 4, dto.getPasswd() );
pstmt.setTimestamp( 5, dto.getReg_date() );
pstmt.setInt( 6, ref );
pstmt.setInt( 7, re_step );
pstmt.setInt( 8, re_level );
pstmt.setString( 9, dto.getContent() );
pstmt.setString( 10, dto.getIp() );
result = pstmt.executeUpdate();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
if( pstmt != null ) pstmt.close();
if( con != null ) con.close();
} catch( SQLException e ) {
e.printStackTrace();
}
}
return result;
}
//각각의 데이터빈들을 담을 배열 getArticles
//ArrayList를 사용해준다.
//글목록 리스트를 받을것이기 때문에 start와 end를 매개변수로 받는다.
public ArrayList<BoardDataBean> getArticles( int start, int end ) {
ArrayList <BoardDataBean> dtos = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
String sql = "select num,writer,email,subject,passwd,";
sql+= "reg_date,ref,re_step,re_level,content,ip,readcount,r ";
sql+= "from (select num,writer,email,subject,passwd,reg_date,ref,re_step";
sql+= ",re_level,content,ip,readcount,rownum r from ";
sql+= "(select num,writer,email,subject,passwd,reg_date,ref,re_step,re_level ";
sql+= ",content,ip,readcount from board order by ref desc, re_step asc) ";
sql+= "order by ref desc, re_step asc ) where r >= ? and r <= ?";
pstmt = con.prepareStatement( sql );
pstmt.setInt( 1, start );
pstmt.setInt( 2, end );
rs = pstmt.executeQuery();
if( rs.next() ) {
dtos = new ArrayList <BoardDataBean> ();
do {
BoardDataBean dto = new BoardDataBean();
dto.setNum( rs.getInt( "num" ) );
dto.setWriter( rs.getString( "writer" ) );
dto.setEmail( rs.getString( "email" ) );
dto.setSubject( rs.getString( "subject" ) );
dto.setPasswd( rs.getString( "passwd" ) );
dto.setReg_date( rs.getTimestamp( "reg_date" ) );
dto.setReadcount( rs.getInt( "readcount" ) );
dto.setRef( rs.getInt( "ref" ) );
dto.setRe_step( rs.getInt( "re_step" ) );
dto.setRe_level( rs.getInt( "re_level" ) );
dto.setContent( rs.getString( "content" ) );
dto.setIp( rs.getString( "ip" ) );
dtos.add( dto );
} while( rs.next() );
}
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
if( pstmt != null ) pstmt.close();
if( con != null ) con.close();
} catch( SQLException e ) {
e.printStackTrace();
}
}
return dtos;
}
//선택한 글의 정보를 가져올 getArticle 메소드
//선택한 글이기 때문에 글번호 num을 매개변수로 받는다.
public BoardDataBean getArticle( int num ) {
BoardDataBean dto = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
String sql = "select * from board where num=?";
pstmt = con.prepareStatement( sql );
pstmt.setInt( 1, num );
rs = pstmt.executeQuery();
if( rs.next() ) {
dto = new BoardDataBean();
dto.setNum( rs.getInt( "num" ) );
dto.setWriter( rs.getString( "writer" ) );
dto.setEmail( rs.getString( "email" ) );
dto.setSubject( rs.getString( "subject" ) );
dto.setPasswd( rs.getString( "passwd" ) );
dto.setReg_date( rs.getTimestamp( "reg_date" ) );
dto.setReadcount( rs.getInt( "readcount" ) );
dto.setRef( rs.getInt( "ref" ) );
dto.setRe_step( rs.getInt( "re_step" ) );
dto.setRe_level( rs.getInt( "re_level" ) );
dto.setContent( rs.getString( "content" ) );
dto.setIp( rs.getString( "ip" ) );
}
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if( rs != null ) rs.close();
if( pstmt != null ) pstmt.close();
if( con != null ) con.close();
} catch( SQLException e ) {
e.printStackTrace();
}
}
return dto;
}
} // class
걍이렇게돼브렀다리