[JSP] #5-2 DBOpen, DBClose, SungjukDTO, SungjukDAO

1. 데이터베이스 연결

----------------------------- DBOpen.java

 

package net.utility;

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

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

 

 

2. 데이터베이스 연결 해제

----------------------------- DBClose.java

 

package net.utility;

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

public class DBClose {

	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
	
} // class end

 

3. DAO

----------------------------- SugnjukDAO.java

package net.sungjuk;

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

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();
	} // end
		
	public int insert(String uname, int kor, int eng, int mat, int aver, String addr) {
		int cnt = 0; // 성공 또는 실패 여부 반환
		try {
			con=dbopen.getConnection();
			
			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) {
			// TODO: handle exception
		} finally {
			DBClose.close(con, pstmt);
		}
		return cnt;
	} // insert() end
	
	public int create(SungjukDTO dto) {
		int cnt = 0; // 성공 또는 실패 여부 반환
		try {
			con=dbopen.getConnection();
			
			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) {
			// TODO: handle exception
		} finally {
			DBClose.close(con, pstmt);
		}
		return cnt;
	} // create() end
	
} // class end

 

 

4. DTO [매우 중요; Value Object라고도 표현]

----------------------------- SungjukDTO.java

 

 DB의 테이블에 생성한 칼럼명을 그대로 변수로 저장

package net.sungjuk;

public class SungjukDTO { // Data Transfer Object 전송객체
						  // 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
	 
	// 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