DAO, DTO

허니몬·2023년 11월 5일
DAODTOjsp
0

(핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축 / JSP

목록 보기
9/9

member

DTO

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;
	}
}

DAO

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;
	}
}

board

DTO

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;
	}
	
}

DAO

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;
	}
}

imageboard

DTO

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;
	}
	
	
}

DAO

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;
	}
}

context.xml 위치

META-INF 안에
new -> xml 파일 생성

profile
허니몬
Fintech
이전 포스트

JSP/day42-43 / 23.10.31(화)~11.01(수) / (핀테크) Spring 및 Ai 기반 핀테크 프로젝트 구축

0개의 댓글