package com.jang.addrbook;

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

public class AddrBean {

	Connection conn = null;
	PreparedStatement pstmt = null;

	// oracle 연결정보
	String jdbc_driver = "oracle.jdbc.driver.OracleDriver";
	String jdbc_url = "jdbc:oracle:thin:@168.126.146.45:1521:orcl";

	void connect() {
		try {
			Class.forName(jdbc_driver);

			conn = DriverManager.getConnection(jdbc_url,"20172302","981220");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	void disconnect() {
		if(pstmt!=null) {
			try {
				pstmt.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null) {
			try {
				conn.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 전체 회원 목록을 가져오는 메서드
	public ArrayList<AddrBook> getDBList(){
		connect();
		ArrayList<AddrBook> datas = new ArrayList<AddrBook>();

		String sql = "select * from addrbook order by ab_num desc";
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				AddrBook addrbook = new AddrBook();

				addrbook.setAb_num(rs.getInt("ab_num"));
				addrbook.setAb_name(rs.getString("ab_name"));
				addrbook.setAb_email(rs.getString("ab_email"));
				addrbook.setAb_comdept(rs.getString("ab_comdept"));
				addrbook.setAb_birth(rs.getString("ab_birth"));
				addrbook.setAb_tel(rs.getString("ab_tel"));
				addrbook.setAb_memo(rs.getString("ab_memo"));
				datas.add(addrbook);
			}
			rs.close();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			disconnect();
		}
		return datas;
	}

	public boolean insertDB(AddrBook addrbook) {
		connect();

		String sql = "insert into addrbook values(ab_id_seq.nextval,?,?,?,?,?,?)";

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, addrbook.getAb_name());
			pstmt.setString(2, addrbook.getAb_email());
			pstmt.setString(3, addrbook.getAb_comdept());
			pstmt.setString(4, addrbook.getAb_birth());
			pstmt.setString(5, addrbook.getAb_tel());
			pstmt.setString(6, addrbook.getAb_memo());
			pstmt.executeUpdate();

		}catch(SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			disconnect();
		}
		return true;
	}

	public AddrBook getDB(int ab_num) {
		connect();

		String sql = "select * from addrbook where ab_num=?";
		AddrBook addrbook = new AddrBook();

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, ab_num);
			ResultSet rs = pstmt.executeQuery();

			rs.next();
			addrbook.setAb_num(rs.getInt("ab_num"));
			addrbook.setAb_name(rs.getString("ab_name"));
			addrbook.setAb_email(rs.getString("ab_email"));
			addrbook.setAb_comdept(rs.getString("ab_comdept"));
			addrbook.setAb_birth(rs.getString("ab_birth"));
			addrbook.setAb_tel(rs.getString("ab_tel"));
			addrbook.setAb_memo(rs.getString("ab_memo"));
			rs.close();

		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			disconnect();
		}
		return addrbook;
	}

	public boolean updateDB(AddrBook addrbook) {

		connect();

		String sql = "update addrbook set ab_name=?, ab_email=?, ab_comdept=?, ab_birth=?, ab_tel=?, ab_memo=? where ab_num=?";

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, addrbook.getAb_name());
			pstmt.setString(2, addrbook.getAb_email());
			pstmt.setString(3, addrbook.getAb_comdept());
			pstmt.setString(4, addrbook.getAb_birth());
			pstmt.setString(5, addrbook.getAb_tel());
			pstmt.setString(6, addrbook.getAb_memo());
			pstmt.setInt(7, addrbook.getAb_num());
			pstmt.executeUpdate();
		}catch(SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			disconnect();
		}

		return true;
	}

	public boolean deleteDB(int num) {
		connect();

		String sql = "delete from addrbook where ab_num=?";

		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,num);
			pstmt.executeUpdate();
//			ResultSet rs = pstmt.executeQuery();
//			rs.close();
		}catch(SQLException e) {
			e.printStackTrace();
			return false;
		}finally {
			disconnect();
		}
		return true;
	}


}