08. JavaBeans를 이용한 성적 테이블 CRUD 게시판 만들기 ① (DBopen, DBclose, DTO, DAO)

 

○ DBopen

package net.utility;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBopen {
	
	//오라클 DB 연결 메소드
	
	public Connection getConnection() {
		Connection con=null;

		try {
			
			String url     ="jdbc:oracle:thin:@localhost:1521:xe";
	        String user    ="system";
	        String password="1234";	
	        String driver  ="oracle.jdbc.driver.OracleDriver";
	        Class.forName(driver);
			con =DriverManager.getConnection(url, user, password);
						
		} catch (Exception e) {
			System.out.println("오라클DB연결실패: " + e);
		}//end
		
		return con;
		
	}//getConnection()
	

}//class end

 

○ DBclose

package net.utility;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBclose {

	//DB연결 자원 반납
	
	public static void close(Connection con) {
		try {
			if(con!=null) {con.close();}
		} catch (Exception e) { }
	}//end
	
	public static void close(Connection con, PreparedStatement pstmt) {
		try {
			if(pstmt!=null) {pstmt.close();}
		} catch (Exception e) { }	
		try {
			if(con!=null) {con.close();}
		} catch (Exception e) { }
	}//end
	
	public static void close(Connection con, PreparedStatement pstmt, ResultSet rs) {
		try {
			if(rs!=null) {rs.close();}
		} catch (Exception e) { }	
		try {
			if(pstmt!=null) {pstmt.close();}
		} catch (Exception e) { }	
		try {
			if(con!=null) {con.close();}
		} catch (Exception e) { }
	}//end
	
	
	
	//static : 빨리 쓸 수 있다, continue
	//overload
	
}//class end

 

 

○ DTO

package net.sungjuk;

public class SungjukDTO {//Data Transfer Objects 전송객체
	 //Value Object
	 //데이터를 한꺼번에 객체화해서 사용할 수 있다

	//sungjuk테이블의 칼럼명을 기준으로 멤버변수(field) 선언
	private int sno;
	private String uname;
	private int kor;
	private int eng;
	private int mat;
	private int aver;
	private String addr;
	private String wdate;
	
	public SungjukDTO() {	}	//기본생성자 default constructor 
	
	
	//private 멤버변수 각자에 대한 getter와 setter함수 작성
	public int getSno() {
	return sno;
	}
	
	public void setSno(int sno) {
	this.sno = sno;
	}
	
	public String getUname() {
	return uname;
	}
	
	public void setUname(String uname) {
	this.uname = uname;
	}
	
	public int getKor() {
	return kor;
	}
	
	public void setKor(int kor) {
	this.kor = kor;
	}
	
	public int getEng() {
	return eng;
	}
	
	public void setEng(int eng) {
	this.eng = eng;
	}
	
	public int getMat() {
	return mat;
	}
	
	public void setMat(int mat) {
	this.mat = mat;
	}
	
	public int getAver() {
	return aver;
	}
	
	public void setAver(int aver) {
	this.aver = aver;
	}
	
	public String getAddr() {
	return addr;
	}
	
	public void setAddr(String addr) {
	this.addr = addr;
	}
	
	public String getWdate() {
	return wdate;
	}
	
	public void setWdate(String wdate) {
	this.wdate = wdate;
	}
	
	
	//에러잡는 용도
	@Override
	public String toString() {
	return "SungjukDTO [sno=" + sno + ", uname=" + uname + ", kor=" + kor + ", eng=" + eng + ", mat=" + mat
	+ ", aver=" + aver + ", addr=" + addr + ", wdate=" + wdate + "]";
	}


}//class end

 

 

 

○ DAO

package net.sungjuk;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import net.utility.DBclose;
import net.utility.DBopen;

public class SungjukDAO {

	//Data Access Object
	//데이터베이스 관련 비지니스 로직 구현
	
	private DBopen dbopen=null;
	private Connection con=null;
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	private StringBuilder sql=null;
		
	public SungjukDAO() {
		dbopen=new DBopen();
	}
	
	public int insert(String uname, int kor, int eng, int mat, int aver, String addr) {
		
		int cnt=0;
		try {
			
			con=dbopen.getConnection();//DB연결
			
			sql=new StringBuilder();
			sql.append(" INSERT INTO sungjuk(sno, uname, kor, eng, mat, aver, addr, wdate) ");
			sql.append(" VALUES(sungjuk_seq.nextval, ?, ?, ?, ?, ?, ?, sysdate) ");
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(1, uname);
			pstmt.setInt(2, kor);
			pstmt.setInt(3, eng);
			pstmt.setInt(4, mat);
			pstmt.setInt(5, aver);
			pstmt.setString(6, addr);

			cnt=pstmt.executeUpdate();
						
		}catch (Exception e) {
			System.out.println("행추가 실패: " + e);
		}finally{
			DBclose.close(con, pstmt);
		}//try end
		
		return cnt;
		
	}//insert() end
	
	
	public int create(SungjukDTO dto) {
		
		int cnt=0; //성공 또는 실패 여부 반환
		
		try {
			
			con=dbopen.getConnection();//DB연결
			
			sql=new StringBuilder();
			sql.append(" INSERT INTO sungjuk(sno, uname, kor, eng, mat, aver, addr, wdate) ");
			sql.append(" VALUES(sungjuk_seq.nextval, ?, ?, ?, ?, ?, ?, sysdate) ");
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(1, dto.getUname());
			pstmt.setInt(2, dto.getKor());
			pstmt.setInt(3, dto.getEng());
			pstmt.setInt(4, dto.getMat());
			pstmt.setInt(5, dto.getAver());
			pstmt.setString(6, dto.getAddr());

			cnt=pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("행추가 실패: " + e);
		}finally{
			DBclose.close(con, pstmt);
		}//try end
		
		
		return cnt;
		
	}//create() end
	
	
	public ArrayList<SungjukDTO> list() {
		//DB에서 가져온 데이터(rs)를 한꺼번에 모아서(ArrayList)
		//sungjukList.jsp에 전달한다
		
		ArrayList<SungjukDTO> list=null;
		
		
		try {
			
			con=dbopen.getConnection();//DB연결
			
			sql=new StringBuilder();
			sql.append(" SELECT sno, uname, kor, eng, mat, wdate ");
			sql.append(" FROM sungjuk ");
			sql.append(" ORDER BY sno DESC ");
			
			pstmt = con.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				list=new ArrayList<SungjukDTO>();//전체 행을 저장
				do {
					
					SungjukDTO dto = new SungjukDTO();//커서가 가리키는 한 줄 저장
					dto.setSno(rs.getInt("sno"));
					dto.setUname(rs.getString("uname"));
					dto.setKor(rs.getInt("kor"));
					dto.setEng(rs.getInt("eng"));
					dto.setMat(rs.getInt("mat"));
					dto.setWdate(rs.getString("wdate"));
					list.add(dto); //list저장
				}while(rs.next());
									
			}else {
				list=null;
			}//if end
										
		}catch (Exception e) {
			System.out.println("목록불러오기 실패: " + e);
		}finally{
			DBclose.close(con, pstmt, rs);
		}//try end
		
		return list;
		
	}//list() end

	
	
	public SungjukDTO read(int sno) {
		
		SungjukDTO dto = null;
		
		try {
			
			con=dbopen.getConnection();//DB연결
			
			sql=new StringBuilder();
			sql.append(" SELECT sno, uname, kor, eng, mat, aver, addr, wdate ");
			sql.append(" FROM sungjuk ");
			sql.append(" WHERE sno=? ");
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setInt(1, sno);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				dto = new SungjukDTO();//커서가 가리키는 한 줄 저장
				dto.setSno(rs.getInt("sno"));
				dto.setUname(rs.getString("uname"));
				dto.setKor(rs.getInt("kor"));
				dto.setEng(rs.getInt("eng"));
				dto.setMat(rs.getInt("mat"));
				dto.setAver(rs.getInt("aver"));
				dto.setAddr(rs.getString("addr"));
				dto.setWdate(rs.getString("wdate"));
			}else {
				dto=null;
			}//if end
		}catch (Exception e) {
			System.out.println("상세보기 실패: " + e);
		}finally{
			DBclose.close(con, pstmt, rs);
		}//try end
		return dto;
	}//read() end

	
	
	public int delete(int sno) {
		int cnt=0;
		try {
			con=dbopen.getConnection();//DB연결
			
			sql=new StringBuilder();
			sql.append(" DELETE FROM sungjuk ");
			sql.append(" WHERE sno=? ");
			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setInt(1, sno);
			
			cnt=pstmt.executeUpdate();
	
		}catch (Exception e) {
			System.out.println("삭제 실패: " + e);
		}finally{
			DBclose.close(con, pstmt);
		}//try end
		return cnt;
	}//delete() end

	
	public int updateproc(SungjukDTO dto) {	
		int cnt=0; //성공 또는 실패 여부 반환		
		try {			
			con=dbopen.getConnection();//DB연결			
			sql=new StringBuilder();
			sql.append(" UPDATE sungjuk ");
			sql.append(" SET uname=?, kor=?, eng=?, mat=?, aver=?, addr=?, wdate=sysdate ");
			sql.append(" WHERE sno=? ");			
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(1, dto.getUname());
			pstmt.setInt(2, dto.getKor());
			pstmt.setInt(3, dto.getEng());
			pstmt.setInt(4, dto.getMat());
			pstmt.setInt(5, dto.getAver());
			pstmt.setString(6, dto.getAddr());
			pstmt.setInt(7, dto.getSno());
			cnt=pstmt.executeUpdate();			
		} catch (Exception e) {
			System.out.println("행수정 실패: " + e);
		}finally{
			DBclose.close(con, pstmt);
		}//try end
		return cnt;
	}//updateProc() end
	
	
	
	
}//class end