자바기초) JDBC 활용 퀴즈 : 연락처 프로그램 6

연락처 프로그램5를 바탕으로 JDBC를 활용해 DB에서 데이터가 저장되는 연락처 프로그램을 만들어 보아라.

---

조건

1. 연락처 데이터베이스 테이블이 이미 존재함을 가정한다.

2. 테이블명 : EX_CONTACT
   컬럼 :
   CONTACT_ID(NUMBER) : 연락처 인덱스 - PK
   NAME(VARCHAR2) : 연락처 이름 - NOT NULL
   PHONE(VARCHAR2) : 연락처 번호 - NOT NULL
   EMAIL(VARCHAR2) : 연락처 이메일 - NOT NULL

3. 데이터 구조 - ContactVO
   contactId(int) : 연락처 인덱스
   name(String) : 연락처 이름
   phone(String) : 연락처 번호
   email(String) : 연락처 이메일

---

OracleQuery 인터페이스 (새로 추가된 인터페이스)

// JDBC에서 DB 접속에 사용될 상수들, SQL 문장들 정의
public interface OracleQuery {
	public static final String URL = "jdbc:oracle:thin:@localhost:1521:xe"; // 접속할 오라클 DB 경로
	public static final String USER = "scott";
	public static final String PASSWORD = "tiger";
	public static final String TABLE_NAME = "EX_CONTACT";
	public static final String COL_CONTACT_ID = "CONTACT_ID";
	public static final String COL_NAME = "NAME";
	public static final String COL_PHONE = "PHONE";
	public static final String COL_EMAIL = "EMAIL";
	public static final String SQL_SELLECT_ALL = "SELECT " + COL_CONTACT_ID + ", " + COL_NAME + ", " + COL_PHONE + ", "
			+ COL_EMAIL + " FROM " + TABLE_NAME + " ORDER BY " + COL_CONTACT_ID;
	public static final String SQL_INSERT = "INSERT INTO " + TABLE_NAME + " VALUES (CONTACT_SEQ.nextval, ?, ?, ?)";
	public static final String SQL_SELECT_BY_CONTACT_ID = "SELECT " + COL_CONTACT_ID + ", " + COL_NAME + ", "
			+ COL_PHONE + ", " + COL_EMAIL + " FROM " + TABLE_NAME + " WHERE " + COL_CONTACT_ID + " = ?";
	public static final String SQL_UPDATE = "UPDATE " + TABLE_NAME + " SET " + COL_NAME + " = ? , " + COL_PHONE
			+ " = ? , " + COL_EMAIL + " = ? " + "WHERE " + COL_CONTACT_ID + " = ?";
	public static final String SQL_DELETE = "DELETE " + TABLE_NAME + " WHERE " + COL_CONTACT_ID + " = ?";
	public static final String SQL_CHECKID = "SELECT COUNT("+COL_CONTACT_ID+") FROM "+TABLE_NAME+" WHERE " + COL_CONTACT_ID + " = ?";
}

---

ContactVO 클래스 (변경 내용 있음)

package edu.java.contact06;

public class ContactVO {
	private int contactId;
	private String name;
	private String phone;
	private String email;

	public ContactVO() {

	}

	public ContactVO(int contactId, String name, String phone, String email) {
		this.contactId = contactId;
		this.name = name;
		this.phone = phone;
		this.email = email;
	}

	public int getContactId() {
		return contactId;
	}

	public void setContactId(int contactId) {
		this.contactId = contactId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@Override
	public String toString() {
		return "ContactVO [contactId=" + contactId + ", name=" + name + ", phone=" + phone + ", email=" + email + "]";
	}

}

---

CONTACTDAO (변경 내용 없음)

public interface ContactDAO {
	public abstract int insert(ContactVO vo);

	public abstract ArrayList<ContactVO> select();

	public abstract ContactVO select(int index);

	public abstract int update(int index, ContactVO vo);
	
	public abstract int delete(int index);
}

---

CONTACTDAOImple (**변경 내용 있음)

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

import oracle.jdbc.OracleDriver;

public class ContactDAOImple implements ContactDAO, OracleQuery {

	private static ContactDAOImple instance = null;

	private int count;

	private ContactDAOImple() { // 싱글톤 객체 초기화 시 바로 파일 확인 하도록 함.
	}

	public static ContactDAOImple getInstance() {
		if (instance == null) {
			instance = new ContactDAOImple();
		}
		return instance;
	}

	public int getCount() {

		return count;
	}

	@Override
	public int insert(ContactVO vo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int result = 0;
		try {
			DriverManager.registerDriver(new OracleDriver());
			System.out.println("드라이버 로드 성공");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			System.out.println("DB 연결 성공");
			pstmt = conn.prepareStatement(OracleQuery.SQL_INSERT);

			pstmt.setString(1, vo.getName());
			pstmt.setString(2, vo.getPhone());
			pstmt.setString(3, vo.getEmail());
			result = pstmt.executeUpdate();
			count++;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
		System.out.println(result);
		return result; // 0 : 실패, 1 : 성공
	}

	@Override
	public ArrayList<ContactVO> select() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<ContactVO> list = new ArrayList<>();
		try {
			DriverManager.registerDriver(new OracleDriver());
			System.out.println("드라이버 로드 성공");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			System.out.println("DB 연결 성공");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(OracleQuery.SQL_SELLECT_ALL);

			while (rs.next()) {
				int contactId = rs.getInt(1);
				String name = rs.getString(2);
				String phone = rs.getString(3);
				String email = rs.getString(4);
				list.add(new ContactVO(contactId, name, phone, email));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				conn.close();
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		System.out.println(list.toString());
		return list;
	}

	@Override
	public ContactVO select(int index) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ContactVO vo = null;

		try {
			DriverManager.registerDriver(new OracleDriver());
			System.out.println("드라이버 로드 성공");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			System.out.println("DB 연결 성공");

			pstmt = conn.prepareStatement(SQL_SELECT_BY_CONTACT_ID);
			pstmt.setInt(1, index);
			rs = pstmt.executeQuery();

			if (rs.next()) {
				int contactId = rs.getInt(1);
				String name = rs.getString(2);
				String phone = rs.getString(3);
				String email = rs.getString(4);
				vo = new ContactVO(contactId, name, phone, email);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println(vo.toString());
		return vo;
	}

	@Override
	public int update(int index, ContactVO vo) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int result = 0;
		try {
			DriverManager.registerDriver(new OracleDriver());
			System.out.println("드라이버 로드 성공");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			System.out.println("DB 연결 성공");

			pstmt = conn.prepareStatement(SQL_UPDATE);

			pstmt.setString(1, vo.getName());
			pstmt.setString(2, vo.getPhone());
			pstmt.setString(3, vo.getEmail());
			pstmt.setInt(4, index);

			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		System.out.println(result);
		return result;
	}

	@Override
	public int delete(int index) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			DriverManager.registerDriver(new OracleDriver());
			System.out.println("드라이버 로드 성공");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			System.out.println("DB 연결 성공");
			pstmt = conn.prepareStatement(SQL_DELETE);

			pstmt.setInt(1, index);

			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		count--;
		System.out.println(result);
		return result;
	}

	// DB의 존재하지 않는 값에 접근으로 인한 에러 대비
	public int checkId(int index) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			DriverManager.registerDriver(new OracleDriver());
			System.out.println("드라이버 로드 성공");
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			System.out.println("DB 연결 성공");

			pstmt = conn.prepareStatement(SQL_CHECKID);
			pstmt.setInt(1, index);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
			System.out.println("1 유 0 무 :" + result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}

}

---

Main (변경 내용 있음) 코드 모듈화

package edu.java.contact06;

import java.awt.EventQueue;
import java.awt.Font;

import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JScrollPane;
import javax.swing.SwingConstants;
import javax.swing.table.DefaultTableModel;
import javax.swing.JTextArea;
import javax.swing.JTable;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.util.ArrayList;
import java.awt.event.ActionEvent;

public class Main {

	private JFrame frame;
	private JTextField textName;
	private JTextField textIndex;
	private JTextField textPhone;
	private JTextField textEmail;
	JTextArea textArea;
	JTextArea textArea_1;

	private ContactDAO dao;

	/* 스윙 테이블을 사용하기 위한 멤버 변수 선언 */
	private JTable table;
	private String[] colNames = { "ID", "이름", "전화번호", "이메일" };
	// 테이블 헤더에 들어갈 이름들
	private Object[] records = new Object[colNames.length];
	// 테이블 데이터를 저장할 배열 객체
	private DefaultTableModel model; // 테이블 형태를 만들 모델 변수

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					Main window = new Main();
					window.frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the application.
	 */
	public Main() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		dao = ContactDAOImple.getInstance();
		frame = new JFrame();
		frame.setBounds(100, 100, 639, 599);
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.getContentPane().setLayout(null);

		JLabel lblTitle = new JLabel("연락처 프로그램");
		lblTitle.setFont(new Font("굴림", Font.BOLD, 40));
		lblTitle.setHorizontalAlignment(SwingConstants.CENTER);
		lblTitle.setBounds(12, 10, 599, 50);
		frame.getContentPane().add(lblTitle);

		JLabel lblName = new JLabel("이름");
		lblName.setFont(new Font("굴림", Font.BOLD, 30));
		lblName.setBounds(33, 69, 128, 50);
		frame.getContentPane().add(lblName);

		JLabel lblPhone = new JLabel("전화번호");
		lblPhone.setFont(new Font("굴림", Font.BOLD, 30));
		lblPhone.setBounds(33, 127, 128, 50);
		frame.getContentPane().add(lblPhone);

		JLabel lblEmail = new JLabel("이메일");
		lblEmail.setFont(new Font("굴림", Font.BOLD, 30));
		lblEmail.setBounds(33, 187, 128, 50);
		frame.getContentPane().add(lblEmail);

		textName = new JTextField();
		textName.setFont(new Font("굴림", Font.PLAIN, 20));
		textName.setBounds(173, 69, 206, 50);
		frame.getContentPane().add(textName);
		textName.setColumns(10);

		textPhone = new JTextField();
		textPhone.setFont(new Font("굴림", Font.PLAIN, 20));
		textPhone.setColumns(10);
		textPhone.setBounds(173, 127, 206, 50);
		frame.getContentPane().add(textPhone);

		textEmail = new JTextField();
		textEmail.setFont(new Font("굴림", Font.PLAIN, 20));
		textEmail.setColumns(10);
		textEmail.setBounds(173, 187, 206, 50);
		frame.getContentPane().add(textEmail);

		JButton btnInsert = new JButton("등록");
		btnInsert.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				insert();
			}
		});
		btnInsert.setBounds(33, 247, 97, 40);
		frame.getContentPane().add(btnInsert);

		textIndex = new JTextField();
		textIndex.setBounds(142, 247, 97, 40);
		frame.getContentPane().add(textIndex);
		textIndex.setColumns(10);

		JButton btnUpdate = new JButton("수정");
		btnUpdate.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				update();
			}
		});
		btnUpdate.setBounds(33, 297, 97, 40);
		frame.getContentPane().add(btnUpdate);

		JButton btnDelete = new JButton("삭제");
		btnDelete.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				delete();
			}
		});
		btnDelete.setBounds(141, 297, 97, 40);
		frame.getContentPane().add(btnDelete);

		JButton btnAllSearch = new JButton("전체검색");
		btnAllSearch.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				selectAll();
				selectAllContactTable();
			}
		});
		btnAllSearch.setBounds(245, 297, 97, 40);
		frame.getContentPane().add(btnAllSearch);

		JButton btnSearch = new JButton("검색");
		btnSearch.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				selectById();
			}
		});
		btnSearch.setBounds(245, 247, 97, 40);
		frame.getContentPane().add(btnSearch);

		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(33, 350, 309, 69);
		frame.getContentPane().add(scrollPane);

		textArea = new JTextArea();
		scrollPane.setViewportView(textArea);

		JScrollPane scrollPane_1 = new JScrollPane();
		scrollPane_1.setBounds(33, 429, 309, 69);
		frame.getContentPane().add(scrollPane_1);

		textArea_1 = new JTextArea();
		scrollPane_1.setViewportView(textArea_1);

		JScrollPane scrollPane_2 = new JScrollPane();
		scrollPane_2.setBounds(354, 247, 257, 251);
		frame.getContentPane().add(scrollPane_2);

		// 테이블 초기화

		model = new DefaultTableModel(colNames, 0) {
			public boolean isCellEditable(int row, int column) {
				if (column >= 0) {
					return false;
				} else {
					return true;
				}
			}

		};

		table = new JTable(model);
		table.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseClicked(MouseEvent e) {
				int row = table.getSelectedRow();
				int col = table.getSelectedColumn();
				Object value = table.getValueAt(row, col);
				System.out.println(value);
			}
		});
		table.setFont(new Font("굴림", Font.PLAIN, 15));
		scrollPane_2.setViewportView(table);
		selectAllContactTable();

	}

	private void insert() {
		textArea.setText("");
		textArea_1.setText("");

		String name = textName.getText();
		String phone = textPhone.getText();
		String email = textEmail.getText();

		if (name.equals("") || phone.equals("") || email.equals("")) {
			textArea_1.setText("모두 입력 해주세요.");
		} else {
			ContactVO vo = new ContactVO();
			vo.setName(name);
			vo.setPhone(phone);
			vo.setEmail(email);
			int result = dao.insert(vo);
			if (result == 1) {
				textArea_1.setText("연락처가 등록 되었습니다.");
			}
		}
	}

	private void update() {
		textArea.setText("");
		textArea_1.setText("");

		if (!textIndex.getText().equals("")) {
			int contactId = Integer.parseInt(textIndex.getText());
			int result = ((ContactDAOImple) dao).checkId(contactId);
			if (result == 1) {
				String name = textName.getText();
				String phone = textPhone.getText();
				String email = textEmail.getText();
				if (name.equals("") || phone.equals("") || email.equals("")) {
					textArea_1.setText("모두 입력 해주세요.");
				} else {
					ContactVO vo = new ContactVO(contactId, name, phone, email);
					result = dao.update(contactId, vo);
					if (result == 1) {
						textArea_1.setText(contactId + "번 ID의 연락처가 수정 되었습니다.");

					}
				}
			} else {
				System.out.println("ID를 확인 해주세요.");
			}
		} else {
			textArea_1.setText("ID를 확인 해주세요.");
		}

	}
	private void selectAll() {
		textArea.setText("");
		textArea_1.setText("");
		ArrayList<ContactVO> list = dao.select();
		for (int i = 0; i < list.size(); i++) {
			textArea.append(list.get(i).toString() + "\n");
		}
	}
	private void selectById() {
		textArea.setText("");
		textArea_1.setText("");
		if (!textIndex.getText().equals("")) {
			int contactId = Integer.parseInt(textIndex.getText());
			int result = ((ContactDAOImple) dao).checkId(contactId);
			if (result == 1) {
				ContactVO vo = dao.select(contactId);
				textArea.setText(vo.toString());
			} else {
				textArea_1.setText("ID를 확인 해주세요.");
			}

		} else {
			textArea_1.setText("ID를 확인 해주세요.");
		}
	}
	private void delete() {
		textArea.setText("");
		textArea_1.setText("");
		if (!textIndex.getText().equals("")) {
			int contactId = Integer.parseInt(textIndex.getText());
			int result = ((ContactDAOImple) dao).checkId(contactId);
			if (result == 1) {
				result = dao.delete(contactId);
				if (result == 1) {
					textArea_1.setText(contactId + "번 ID의 연락처가 삭제 되었습니다.");

				}
			} else {
				textArea_1.setText("ID를 확인 해주세요.");
			}
		} else {
			textArea_1.setText("ID를 확인 해주세요.");
		}
	}

	private void selectAllContactTable() {

		ArrayList<ContactVO> list = dao.select();
		model.setNumRows(0);

		for (int i = 0; i < list.size(); i++) {
			records[0] = list.get(i).getContactId();
			records[1] = list.get(i).getName();
			records[2] = list.get(i).getPhone();
			records[3] = list.get(i).getEmail();
			model.addRow(records);
		}

	}
}