package 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;

	String jdbc_driver = "oracle.jdbc.driver.OracleDriver";
	String jdbc_url = "jdbc:orcle:thin@168.126.146.45:1521:orcl";

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

			conn = DriverManager.getConnection(jdbc_url, "20172312", "980328");

		} 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_id desc";
		try {
			pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				AddrBook addrbook = new AddrBook();
					
					addrbook.setAb_id(rs.getInt("ab_id"));
					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());
		} catch(SQLException e) {
			e.printStackTrace();
			return false;
		}
		finally {
			disconnect();
		}
		return true;
		}
	
	public AddrBook getDB(int ab_id) {
		connect();
		
		String sql = "select * from addrbook where ab_id=?";
		AddrBook addrbook = new AddrBook();
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, ab_id);
			ResultSet rs = pstmt.executeQuery();
			
			rs.next();
			addrbook.setAb_id(rs.getInt("ab_id"));
			addrbook.setAb_name(rs.getString("ab_name"));
			addrbook.setAb_email(rs.getString("ab_email"));
			addrbook.setAb_birth(rs.getString("ab_birth"));
			addrbook.setAb_tel(rs.getString("ab_tel"));
			addrbook.setAb_comdept(rs.getString("ab_comdept"));
			addrbook.setAb_memo(rs.getString("ab_memo"));
			rs.close();
		}  catch (SQLException e) {
			e.printStackTrace();
		
		}
		finally {
			disconnect();
		}
		return addrbook;
	}
	}