package member.dto;
/*
CREATE TABLE member(
name VARCHAR2(30) NOT NULL, -- 이름
id VARCHAR2(30) PRIMARY KEY, -- id
pwd VARCHAR2(30) NOT NULL, -- 비밀번호
gender VARCHAR2(3), -- 성별
email VARCHAR2(20), -- 이메일
domain VARCHAR2(20), -- domain
tel VARCHAR2(13), -- 전화번호
addr VARCHAR2(100), -- 주소
logtime DATE -- 생성일자
);
*/
public class MemberDTO {
private String name;
private String id;
private String pwd;
private String gender;
private String email;
private String domain;
private String tel;
private String addr;
public MemberDTO() {
super();
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getDomain() {
return domain;
}
public void setDomain(String domain) {
this.domain = domain;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
}
package member.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import member.dto.MemberDTO;
import oracle.jdbc.proxy.annotation.Pre;
public class MemberDAO {
// 연결 정보
private Connection con;
private PreparedStatement pstmt;
private ResultSet res;
private DataSource ds;
public MemberDAO() {
try {
// InitalContect 객체 생성해서 설정된 정보 가져오기 : JNDI
Context context = new InitialContext();
// Context 클래스의 lookup() 메서드는 'java:comp/env/jdbc/oracle' 을 가지고 DataSource 객체를 구합니다
// - lookup() 메서드를 사용해서 naming 서비스에서 자원을 찾습니다
// - JNDI 의 이름은 'java:comp/env/' 에 등록되어 있습니다
ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
public int write(MemberDTO dto) {
int res = 0;
try {
String sql ="INSERT INTO MEMBER VALUES(?,?,?,?,?,?,?,?,SYSDATE)";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getName());
pstmt.setString(2, dto.getId());
pstmt.setString(3, dto.getPwd());
pstmt.setString(4, dto.getGender());
pstmt.setString(5, dto.getEmail());
pstmt.setString(6, dto.getDomain());
pstmt.setString(7, dto.getTel());
pstmt.setString(8, dto.getAddr());
res = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(con!=null) con.close();
if(pstmt!=null) pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return res;
}// write() end
public boolean isExistId(String id) {
boolean ex = false;
try {
String sql = "SELECT * FROM member WHERE ID=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
res = pstmt.executeQuery();
if(res.next()) {
ex = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(res!=null) res.close();
if(pstmt!=null) pstmt.close();
if(con!=null) con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return ex;
}
// 로그인
public String login(String id, String pwd) {
String name ="";
try {
String sql = "SELECT*FROM member WHERE id=? and pwd=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.setString(2, pwd);
res = pstmt.executeQuery();
if(res.next()) {
name = res.getString("name");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(res!= null) res.close();
if(pstmt!= null) pstmt.close();
if(con!= null) con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return name;
}
// 회원정보 가져오기
public MemberDTO bring(String id) {
MemberDTO data = null;
try {
data = new MemberDTO();
String sql = "SELECT*FROM member WHERE id=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
res = pstmt.executeQuery();
if(res.next()) {
data.setName(res.getString("name"));
data.setId(res.getString("id"));
data.setPwd(res.getString("pwd"));
data.setGender(res.getString("gender"));
data.setEmail(res.getString("email"));
data.setDomain(res.getString("domain"));
data.setTel(res.getString("tel"));
data.setAddr(res.getString("addr"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(res!=null)res.close();
if(pstmt!=null)pstmt.close();
if(con!=null)con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return data;
}
public int update(MemberDTO dto) {
int res = 0;
try {
String sql ="UPDATE member SET name=?,pwd=?,gender=?,email=?,domain=?,tel=?,addr=? where id=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getName());
pstmt.setString(2, dto.getPwd());
pstmt.setString(3, dto.getGender());
pstmt.setString(4, dto.getEmail());
pstmt.setString(5, dto.getDomain());
pstmt.setString(6, dto.getTel());
pstmt.setString(7, dto.getAddr());
pstmt.setString(8, dto.getId());
res = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(con!=null) con.close();
if(pstmt!=null) pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return res;
}
}
package board.dto;
/*
-- board 테이블( 게시판 )
CREATE TABLE board(
seq NUMBER NOT NULL, -- 글번호
id VARCHAR2(30) NOT NULL, -- 아이디
name VARCHAR2(30) NOT NULL, -- 작성자
subject VARCHAR2(50) NOT NULL, -- 제목
content VARCHAR2(1000) NOT NULL, -- 내용
hit NUMBER DEFAULT 0, -- 조회수
logtime DATE DEFAULT SYSDATE -- 작성일자
);
-- seq 컬럼에 적용하는 board_seq 시퀀스 객체 생성
CREATE SEQUENCE board_seq NOCACHE NOCYCLE;
*/
public class BoardDTO {
private int seq;
private String id;
private String name;
private String subject;
private String content;
private int hit;
private String logtime;
public BoardDTO() {
super();
}
public int getSeq() {
return seq;
}
public void setSeq(int seq) {
this.seq = seq;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSubject() {
return subject;
}
public void setSubject(String subject) {
this.subject = subject;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
public String getLogtime() {
return logtime;
}
public void setLogtime(String logtime) {
this.logtime = logtime;
}
}
package board.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import board.dto.BoardDTO;
public class BoardDAO {
private Connection con;
private PreparedStatement pstmt;
private ResultSet res;
private DataSource ds;
public BoardDAO() {
try {
// InitalContect 객체 생성해서 설정된 정보 가져오기 : JNDI
Context context = new InitialContext();
// Context 클래스의 lookup() 메서드는 'java:comp/env/jdbc/oracle' 을 가지고 DataSource 객체를 구합니다
// - lookup() 메서드를 사용해서 naming 서비스에서 자원을 찾습니다
// - JNDI 의 이름은 'java:comp/env/' 에 등록되어 있습니다
ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
// 글쓰기
public int write(BoardDTO dto) {
int su = 0;
try {
String sql = "INSERT INTO board VALUES(board_seq.NEXTVAL,?,?,?,?,0,SYSDATE)";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
String[] arr = {dto.getId(), dto.getName(), dto.getSubject(), dto.getContent()};
for (int i = 1; i <= 4; i++) {
pstmt.setString(i, arr[i - 1]);
}
// pstmt.setString(1, dto.getId());
// pstmt.setString(2, dto.getName());
// pstmt.setString(3, dto.getSubject());
// pstmt.setString(4, dto.getContent());
su = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return su;
}
// db 통로
public void streamClose(ResultSet res, PreparedStatement pstmt, Connection con) {
try {
if (res != null)
res.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}// streamClase end
public void streamClose(PreparedStatement pstmt, Connection con) {
try {
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}// streamClase end
public ArrayList<BoardDTO> boardList(int startNum, int lastNum) {
ArrayList<BoardDTO> list = new ArrayList<>();
BoardDTO dto = null;
try {
String sql ="SELECT seq,id,name,subject,content,hit,TO_CHAR(logtime,'YYYY.MM.DD') AS logtime FROM "
+ "(SELECT rownum rn, tt. * FROM "
+ "(SELECT * FROM board ORDER BY SEQ DESC) tt) "
+ "WHERE rn>=? and rn<=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, startNum);
pstmt.setInt(2, lastNum);
res = pstmt.executeQuery();
while(res.next()) {
dto = new BoardDTO();
dto.setSeq(res.getInt("seq"));
dto.setId(res.getString("id"));
dto.setName(res.getString("name"));
dto.setSubject(res.getString("subject"));
dto.setContent(res.getString("content"));
dto.setHit(res.getInt("hit"));
dto.setLogtime(res.getString("logtime"));
list.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return list;
}// boardList() end
public int getTotalArticle() {
int total =0;
try {
String sql = "SELECT COUNT(*) FROM board";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
res = pstmt.executeQuery();
if(res.next()) {
total =res.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return total;
}// getTotalArticle() end
public BoardDTO boardView(int seq) {
BoardDTO dto=null;
try {
String sql = "SELECT * FROM board WHERE seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, seq);
res = pstmt.executeQuery();
while(res.next()) {
dto = new BoardDTO();
dto.setSeq(res.getInt("seq"));
dto.setId(res.getString("id"));
dto.setName(res.getString("name"));
dto.setSubject(res.getString("subject"));
dto.setContent(res.getString("content"));
dto.setHit(res.getInt("hit"));
dto.setLogtime(res.getString("logtime"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return dto;
}
//조회수 증가
public void updateHit(int seq) {
try {
String sql ="UPDATE board SET hit=hit+1 where seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,seq);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(pstmt, con);
}
} // updateHit() end
//글수정
public int modify(BoardDTO dto) {
int su =0;
try {
String sql ="UPDATE board SET subject=?,content=? where seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getSubject());
pstmt.setString(2, dto.getContent());
pstmt.setInt(3, dto.getSeq());
su = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(pstmt, con);
}
return su;
}//modify() end
public int delete(int seq) {
int su = 0;
try {
String sql = "delete from board where seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, seq);
su = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(pstmt, con);
}
return su;
}
}
package imageboard.dto;
public class ImageBoardDTO {
private int seq;
private String imageId;
private String imageName;
private int imagePrice;
private int imageQty;
private String imageContent;
private String imageFile;
private String logtime;
public ImageBoardDTO() {
super();
}
public ImageBoardDTO(int seq, String imageId, String imageName, int imagePrice, int imageQty, String imageContent,
String imageFile, String logtime) {
super();
this.seq = seq;
this.imageId = imageId;
this.imageName = imageName;
this.imagePrice = imagePrice;
this.imageQty = imageQty;
this.imageContent = imageContent;
this.imageFile = imageFile;
this.logtime = logtime;
}
public int getSeq() {
return seq;
}
public void setSeq(int seq) {
this.seq = seq;
}
public String getImageId() {
return imageId;
}
public void setImageId(String imageId) {
this.imageId = imageId;
}
public String getImageName() {
return imageName;
}
public void setImageName(String imageName) {
this.imageName = imageName;
}
public int getImagePrice() {
return imagePrice;
}
public void setImagePrice(int imagePrice) {
this.imagePrice = imagePrice;
}
public int getImageQty() {
return imageQty;
}
public void setImageQty(int imageQty) {
this.imageQty = imageQty;
}
public String getImageContent() {
return imageContent;
}
public void setImageContent(String imageContent) {
this.imageContent = imageContent;
}
public String getImageFile() {
return imageFile;
}
public void setImageFile(String imageFile) {
this.imageFile = imageFile;
}
public String getLogtime() {
return logtime;
}
public void setLogtime(String logtime) {
this.logtime = logtime;
}
}
package imageboard.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import board.dto.BoardDTO;
import imageboard.dto.ImageBoardDTO;
public class ImageBoardDAO {
private Connection con;
private PreparedStatement pstmt;
private ResultSet res;
private DataSource ds;
public ImageBoardDAO() {
try {
// InitalContect 객체 생성해서 설정된 정보 가져오기 : JNDI
Context context = new InitialContext();
// Context 클래스의 lookup() 메서드는 'java:comp/env/jdbc/oracle' 을 가지고 DataSource 객체를 구합니다
// - lookup() 메서드를 사용해서 naming 서비스에서 자원을 찾습니다
// - JNDI 의 이름은 'java:comp/env/' 에 등록되어 있습니다
ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
} catch (Exception e) {
e.printStackTrace();
}
}
// db 통로
public void streamClose(ResultSet res, PreparedStatement pstmt, Connection con) {
try {
if (res != null)
res.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}// streamClase end
public int imageboardWrite(ImageBoardDTO dto) {
int res=0;
try {
String sql = "INSERT INTO imageboard VALUES(seq_imageboard.NEXTVAL,?,?,?,?,?,?,SYSDATE)";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getImageId());
pstmt.setString(2, dto.getImageName());
pstmt.setInt(3, dto.getImagePrice());
pstmt.setInt(4, dto.getImageQty());
pstmt.setString(5, dto.getImageContent());
pstmt.setString(6, dto.getImageFile());
res = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(null, pstmt, con);
}
return res;
}
public ArrayList<ImageBoardDTO> iamgeboardList(int startNum, int lastNum) {
ArrayList<ImageBoardDTO> list = new ArrayList<>();
ImageBoardDTO dto = null;
try {
String sql ="SELECT seq,imageId,ImageName,ImagePrice,ImageQty,ImageContent,ImageFile,TO_CHAR(logtime,'YYYY.MM.DD') AS logtime FROM "
+ "(SELECT rownum rn, tt. * FROM "
+ "(SELECT * FROM imageboard ORDER BY SEQ DESC) tt) "
+ "WHERE rn>=? and rn<=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, startNum);
pstmt.setInt(2, lastNum);
res = pstmt.executeQuery();
while(res.next()) {
dto = new ImageBoardDTO();
dto.setSeq(res.getInt("seq"));
dto.setImageId(res.getString("imageId"));
dto.setImageName(res.getString("imageName"));
dto.setImagePrice(res.getInt("imagePrice"));
dto.setImageQty(res.getInt("imageQty"));
dto.setImageContent(res.getString("imageContent"));
dto.setImageFile(res.getString("imageFile"));
dto.setLogtime(res.getString("logtime"));
list.add(dto);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return list;
}// boardList() end
public int getTotalArticle() {
int total =0;
try {
String sql = "SELECT COUNT(*) FROM imageboard";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
res = pstmt.executeQuery();
if(res.next()) {
total =res.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return total;
}// getTotalArticle() end
public ImageBoardDTO imageboardView(int seq) {
ImageBoardDTO dto=null;
try {
String sql = "SELECT * FROM imageboard WHERE seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, seq);
res = pstmt.executeQuery();
while(res.next()) {
dto = new ImageBoardDTO();
dto.setSeq(res.getInt("seq"));
dto.setImageId(res.getString("imageId"));
dto.setImageName(res.getString("imageName"));
dto.setImagePrice(res.getInt("imagePrice"));
dto.setImageQty(res.getInt("imageQty"));
dto.setImageContent(res.getString("imageContent"));
dto.setImageFile(res.getString("imageFile"));
dto.setLogtime(res.getString("logtime"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return dto;
}
public int modify(ImageBoardDTO dto, int seq) {
int su =0;
try {
String sql ="UPDATE imageboard SET imageId=?,imageName=?,imagePrice=?,imageQty=?,imageContent=?,imageFile=? where seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getImageId());
pstmt.setString(2, dto.getImageName());
pstmt.setInt(3, dto.getImagePrice());
pstmt.setInt(4, dto.getImageQty());
pstmt.setString(5, dto.getImageContent());
pstmt.setString(6, dto.getImageFile());
pstmt.setInt(7, seq);
su = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return su;
}
public int delete(int seq) {
int su = 0;
try {
String sql = "DELETE FROM imageboard where seq=?";
con = ds.getConnection();
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, seq);
su = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
streamClose(res, pstmt, con);
}
return su;
}
}
META-INF 안에
new -> xml 파일 생성