JDBC 예제(2)

package Exam01;

import java.net.ConnectException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Main {

	public static void main(String[] args) {
		// 1.등록 2.조회 3.수정 4.삭제 5.종료
		Scanner sc = new Scanner(System.in);
        
        // 프로그램 시작
		while (true) {
			System.out.println("1.등록 2.조회 3.수정 4.삭제 5.종료 6.개별조회 >> ");
			int choice = sc.nextInt();
            
            // 등록
			if (choice == 1) {
				System.out.println("===== 등록 =====");
				System.out.print("ID : ");
				String id = sc.next();
				System.out.print("PW : ");
				String pw = sc.next();
				System.out.print("NAME : ");
				String name = sc.next();
				System.out.print("NICK : ");
				String nick = sc.next();

				//객체 선언
				PreparedStatement psmt = null;
				Connection conn = null;

				try {
                	// 컴파일러가 모름 => 따라서 경로지정.
					Class.forName("oracle.jdbc.driver.OracleDriver");
					
					String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
					String db_id = "hr";
					String db_pw = "hr";
                    
                    // 커넥션 열결(생성자)
					conn = DriverManager.getConnection(db_url, db_id, db_pw);

					// 객체가 성공적으로 생성되었을 때
					if (conn != null) {
						System.out.println("DB연결 성공");
					} else {
						System.out.println("DB연결 실패");
					}
                    
					String sql = "INSERT INTO SPRINGMEMBER VALUES(?,?,?,?)";
					psmt = conn.prepareStatement(sql);

					psmt.setString(1, id);
					psmt.setString(2, pw);
					psmt.setString(3, name);
					psmt.setString(4, nick);

					int cnt = psmt.executeUpdate();
					if (cnt > 0) {
						System.out.println("등록성공");
					} else {
						System.out.println("등록실패");
					}
					
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						// nullpointexception 이 아닐때 진행하겠다.
						if (psmt != null) {
							psmt.close();
						}
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			} else if (choice == 2) {
				System.out.println("===== 조회 =====");
				
                // choice 1일때의 connection이 끊겼으니 다시 연결해줘야함.
				PreparedStatement psmt = null;
				Connection conn = null;

				try {
					Class.forName("oracle.jdbc.driver.OracleDriver");

					String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
					String db_id = "hr";
					String db_pw = "hr";

					conn = DriverManager.getConnection(db_url, db_id, db_pw);

					if (conn != null) {
						System.out.println("DB연결 성공");
					} else {
						System.out.println("DB연결 실패");
					}

					String sql = "SELECT * FROM SPRINGMEMBER";
					psmt = conn.prepareStatement(sql);

					//select 문과 연관이 있는 rs객체를 불러온다.
					ResultSet rs = psmt.executeQuery();
					
                    // 다음 행이 존재하는 동안에 반복하여 print한다.
					while (rs.next()) {
						String id = rs.getString(1);
						String pw = rs.getString(2);
						String name = rs.getString(3);
						String nick = rs.getString(4);
						System.out.println("ID : " + id + ", PW : " + pw + ", NAME : " + name + ", NICK : " + nick);
					}

				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (psmt != null) {
							psmt.close();
						}
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}

			} else if (choice == 3) {
				System.out.println("===== 수정 =====");
				System.out.print("ID : ");
				String id = sc.next();
				System.out.print("NICK : ");
				String nick = sc.next();

				// 입력받은 id가 일치하는 것을 찾아 입력한 nick으로 변경

				PreparedStatement psmt = null;
				Connection conn = null;

				try {
					Class.forName("oracle.jdbc.driver.OracleDriver");

					String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
					String db_id = "hr";
					String db_pw = "hr";

					conn = DriverManager.getConnection(db_url, db_id, db_pw);

					if (conn != null) {
						System.out.println("DB연결 성공");
					} else {
						System.out.println("DB연결 실패");
					}

					String sql = "UPDATE SPRINGMEMBER SET NICK = ? WHERE ID = ?";

					psmt = conn.prepareStatement(sql);
					psmt.setString(1, nick);
					psmt.setString(2, id);
					int cnt = psmt.executeUpdate();
					if (cnt > 0) {
						System.err.println("수정성공");
					} else {
						System.err.println("수정실패");
					}
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (psmt != null) {
							psmt.close();
						}
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			} else if (choice == 4) {
				System.out.println("===== 삭제 =====");
				System.out.print("ID : ");
				String name = sc.next();

				PreparedStatement psmt = null;
				Connection conn = null;

				try {
					Class.forName("oracle.jdbc.driver.OracleDriver");

					String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
					String db_id = "hr";
					String db_pw = "hr";

					conn = DriverManager.getConnection(db_url, db_id, db_pw);

					if (conn != null) {
						System.out.println("DB연결 성공");
					} else {
						System.out.println("DB연결 실패");
					}

					String sql = "DELETE FROM SPRINGMEMBER WHERE ID = ?";
					psmt = conn.prepareStatement(sql);
					psmt.setString(1, name);
					int cnt = psmt.executeUpdate();
					if (cnt > 0) {
						System.out.println("삭제성공");
					} else {
						System.out.println("삭제실패");
					}
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (psmt != null) {
							psmt.close();
						}
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			} 
            else if (choice == 5) {
				System.out.println("시스템을 종료합니다.");
				break;
			} else if (choice == 6) {
				System.out.println("===== 개별조회 =====");
				System.out.print("ID : ");
				String searchid = sc.next();
				
                // JDBC Query 다시 시작
				PreparedStatement psmt = null;
				Connection conn = null;

				try {
					Class.forName("oracle.jdbc.driver.OracleDriver");

					String db_url = "jdbc:oracle:thin:@localhost:1521:xe";
					String db_id = "hr";
					String db_pw = "hr";

					conn = DriverManager.getConnection(db_url, db_id, db_pw);

					if (conn != null) {
						System.out.println("DB연결 성공");
					} else {
						System.out.println("DB연결 실패");
					}

					String sql = "SELECT * FROM SPRINGMEMBER WHERE ID = ?";
					psmt = conn.prepareStatement(sql);
					psmt.setString(1, searchid);
					ResultSet rs = psmt.executeQuery();
					
                    // Query 문의 결과값을 기준으로 ResultSet이 나오기 때문에 re.next()를 써도 괜찮다.
					while (rs.next()) {
						String id = rs.getString(1);
						String pw = rs.getString(2);
						String name = rs.getString(3);
						String nick = rs.getString(4);
						System.out.println("ID : " + id + ", PW : " + pw + ", NAME : " + name + ", NICK : " + nick);
					}

				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (psmt != null) {
							psmt.close();
						}
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}

			} else {
				System.out.println("잘못된 명령어입니다.");
			} 
		}
		sc.close();
	}
}