사용툴
- 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
);
CREATE SEQUENCE board_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 999999
NOCYCLE
NOCACHE;
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;
}
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();
}
public int getCount() {
int count = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
String sql = "select count(*) from board";
pstmt = con.prepareStatement( sql );
rs = pstmt.executeQuery();
if( rs.next() ) {
count = rs.getInt( 1 );
}
} 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;
}
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();
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;
} 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;
}
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;
}
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;
}
}