package dao;

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

import dto.MemberDTO;

public class MemberDAO {

	// 연결 정보
	private String url = "jdbc:oracle:thin:@localhost:1521:xe";
	private String id = "DBTEST";
	private String pwd = "1234";
	
	public MemberDAO() {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("로딩 성공");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Connection getConnection() {
		Connection con = null;
		try {
			// DB 연결 객체 생성
			con = DriverManager.getConnection(url, id, pwd);
			System.out.println("연결 성공!!");
		} catch (Exception e) {
			System.out.println("연결 실패~");
			e.printStackTrace();
		}
		return con;
	}
	// getConnection() end
	
	// 추가
	public int insert(MemberDTO dto) {
		
		Connection con = null;
		PreparedStatement pstmt = null;
		int su = 0;
		
		try {
			
			String sql = "insert into member values(?, ?, ?, sysdate)";
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setInt(2, dto.getAge());
			pstmt.setDouble(3, dto.getHeight());
			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;
		
	}
	// insert() end
	
	// 조회
	public void select() {
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		
		try {
			
			String sql = "select * from member";
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			res = pstmt.executeQuery();
			
			while(res.next()) {
				String name = res.getString("name");
				int age = res.getInt("age");
				double height = res.getDouble("height");
				String logtime = res.getString("logtime");
				System.out.println(name + "\\t" + age + "\\t" + height + "\\t" + logtime);
			}
			
		} 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();
			}
		}
		
	}
	// select() end
	
	// 수정 : 이름 조회해서 수정
	public int update(MemberDTO dto) {
		
		Connection con = null;
		PreparedStatement pstmt = null;
		int su = 0;
		
		try {
			
			String sql = "update member set age=?, height=? where name=?";
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, dto.getAge());
			pstmt.setDouble(2, dto.getHeight());
			pstmt.setString(3, dto.getName());
			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;
		
	}
	// update() end
	
	// 삭제
	public int delete(String name) {
	
		Connection con = null;
		PreparedStatement pstmt = null;
		int su = 0;
		
		try {
			
			String sql = "delete member where name=?";
			con = getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			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;
		
	}
	
}