[Web] 5. MVC 패턴(4) - DB연동

[Preview]

전체 흐름 & 해당 글 부분

---

[DB에 접근하기 전 작업]

1. 탐색기 -> C:\oraclexe\app\oracle\product\11.2.0\serverC:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib 폴더로 이동

2. 프로젝트내 WEB-INF의 lib안에 ojdbc6 복사, 붙여넣기

//나중엔 Maven을 이용해 관리한다.

[UserDaoImpl에 추가]

DB정보를 통한 드라이버 실행

	//db 관련 정보
	public static final String DRIVER 	= "oracle.jdbc.driver.OracleDriver";
	public static final String URL 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	public static final String USER 	= "hr";
	public static final String PASSWD	= "hr";
	
	//Driver로딩은 한번만
	public UserDaoImpl() {
		try {
			Class.forName(DRIVER) ;
			//Driver로딩을 도와주는 메소드
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	

[sql파일에 작성하고 실행]

Oracle에 데이터 추가

CREATE TABLE INTERN_USER_TBL (
	ID		VARCHAR2(50)	PRIMARY KEY,
	PWD		VARCHAR2(50) 	NOT NULL,
	NAME	VARCHAR2(40) 	NOT NULL,
	POINT	NUMBER			CHECK( POINT >= 1000),
	DEPT	VARCHAR2(50)	CHECK( DEPT IN ('AI', 'INSA', 'SB', 'EMART'))
);

INSERT INTO INTERN_USER_TBL
VALUES ('sijune', 'sijune', '이시준', 1000, 'EMART');

INSERT INTO INTERN_USER_TBL
VALUES ('park', 'park', '박', 2000, 'SB');

[UserVO 만들기]

담아올 그릇 만들기

package com.sinc.intern.insa.model.vo;

public class UserVO {
	//테이블과 동일한 구조가 필요하다.
	private String id, pwd, name;
	private double point;
	private String dept;
	
	public UserVO() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public UserVO(String id, String pwd, String name, double point, String dept) {
		super();
		this.id = id;
		this.pwd = pwd;
		this.name = name;
		this.point = point;
		this.dept = dept;
	}
	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 getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public double getPoint() {
		return point;
	}
	public void setPoint(double point) {
		this.point = point;
	}
	public String getDept() {
		return dept;
	}
	public void setDept(String dept) {
		this.dept = dept;
	}
	@Override
	public String toString() {
		return "UserVO [id=" + id + ", pwd=" + pwd + ", name=" + name + ", point=" + point + ", dept=" + dept + "]";
	}
}

[UserDaoImpl 수정 ]

Server에서 요청된 유저가 DB에 존재한다면 VO에 담아서 반환

DAO가 DB에 접근하는 Cycle

package com.sinc.intern.insa.model.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import com.sinc.intern.insa.model.vo.UserDTO;
import com.sinc.intern.insa.model.vo.UserVO;

public class UserDaoImpl implements UserDao {

	//db 관련 정보
	public static final String DRIVER 	= "oracle.jdbc.driver.OracleDriver";
	public static final String URL 		= "jdbc:oracle:thin:@127.0.0.1:1521:xe";
	public static final String USER 	= "hr";
	public static final String PASSWD	= "hr";
	
	//Driver로딩은 한번만
	public UserDaoImpl() {
		try {
			Class.forName(DRIVER) ;
			//Driver로딩을 도와주는 메소드
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	public Object selectRow(Object obj) {
		System.out.println("UserDao selectRow") ;
		System.out.println("Dao user : "+obj); 
		Connection        conn  = null ; 
		PreparedStatement pstmt = null ; 
		ResultSet		  rset  = null ; 
		String loginSQL = "SELECT * FROM INTERN_USER_TBL WHERE ID = ? AND PWD = ? " ; 
		UserVO user = null ; 
		try{
			conn  = DriverManager.getConnection(URL, USER, PASSWD) ; 
			pstmt = conn.prepareStatement(loginSQL); 
			pstmt.setString(1, ((UserDTO)obj).getId());
			pstmt.setString(2, ((UserDTO)obj).getPwd());
			rset = pstmt.executeQuery() ; 
			if(rset.next()) {
				user = new UserVO(	rset.getString(1),
									rset.getString(2),
									rset.getString(3),
									rset.getDouble(4),
									rset.getString(5)) ; 
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try{
				if( conn != null ) { conn.close(); }
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
		return user ; 
	}

	@Override
	public List<Object> selectRow() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int insertRow(Object obj) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int updateRow(Object obj) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int deleteRow(Object obj) {
		// TODO Auto-generated method stub
		return 0;
	}
	
}

sijune/sijune 입력시, DB에 있어서 가져온다.

asdf/asdf 입력시, DB에 없다