5주차 - 자바 오라클 데이터베이스 (3)

아래는 이전에 배운 INSERT, UPDATE, DELETE, SELECT와 메서드를 만드는 방법 등을 이용해

간단한 회원가입 프로그램을 만드는 예제

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

public class Main_Answer {
	/*
	 * 테이블 생성
	 *  - 테이블 이름 : jdbc_concept2
	 
CREATE TABLE jdbc_concept2(
  email varchar2(100),
  name varchar2(20),
  mobile varchar2(15)
);

SQL> commit;
Commit complete.
*/
	private static Connection con;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;
	
	public static void main(String[] args) {
		
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "oracle";
		String password=  "oracle";
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	
		
		Scanner in = new Scanner(System.in);
		String email = "", name="", mobile="";
		
		while (true) {
			System.out.println("1. 회원 등록");
			System.out.println("2. 회원 검색");
			System.out.println("3. 회원 삭제");
			System.out.println("4. 회원 모두 보기");
			System.out.println("5. 회원 수정");
			System.out.println("6. 프로그램 종료");
			System.out.print("선택 > ");
			int select;
			try {
				select = Integer.parseInt(in.next());
			} catch (Exception e) {
				continue;
			}
			switch (select) {
			case 1:
				System.out.println("=== 회원 등록 ===");
				System.out.print("이메일 : "); email = in.next();
				System.out.print("이름 : ");	name = in.next();
				System.out.print("전화번호 : "); mobile = in.next();
				insert(email, name, mobile);
				break;
			case 2:
				System.out.println("=== 회원 검색 ===");
				System.out.print("이메일 : "); email = in.next();
				selectEmail(email);
				break;
			case 3:
				System.out.println("=== 회원 삭제 ===");
				System.out.print("이메일 : "); email = in.next();
				delete(email);
				break;
			case 4:	selectAll(); break;
			case 5:
				System.out.println("=== 회원 수정 ===");
				System.out.print("이메일 : "); email = in.next();
				System.out.print("이름 : ");	name = in.next();
				System.out.print("전화번호 : "); mobile = in.next();
				update(email, name, mobile);
				break;
			case 6:
				try {
					if(rs != null) 
						rs.close();
					if(ps != null) 
						ps.close();
					if(con != null)
						con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				System.out.println("프로그램을 종료합니다.");
				System.exit(0);
			default:
				System.out.println("메뉴 확인 후 다시 입력하세요.");
			}
		}
	}
	
	private static int isExists(String email) {
		String sql = "SELECT count(email) FROM jdbc_concept2 WHERE email=?";

		int count = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, email);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return count;
	}
	private static void insert(String email, String name, String mobile) {

		if(isExists(email) == 0) {
			String sql = "INSERT INTO jdbc_concept2 VALUES(?, ?, ?)";
			PreparedStatement ps = null;
			try {
				ps = con.prepareStatement(sql);
				ps.setString(1, email);
				ps.setString(2, name);
				ps.setString(3, mobile);
				
				ps.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}
			System.out.println(email + "의 정보를 등록했습니다.");
		}else {
			System.out.println(email + "는/은 등록된 이메일입니다.");
		}
	}
	
	private static void selectEmail(String email) {
		String sql = "SELECT * FROM jdbc_concept2 WHERE email=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if(rs.next()) {
				System.out.println("이메일 : " + rs.getString("email"));
				System.out.println("이름 : " + rs.getString("name"));
				System.out.println("전화번호 : " + rs.getString("mobile"));
			}else {
				System.out.println(email + "는/은 등록된 정보가 아닙니다.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	private static void delete(String email) {
		int result = isExists(email);
		
		if(result != 1) {
			System.out.println(email + "는/은 등록된 정보가 아닙니다.");
			return;
		}
		
		String sql = "DELETE FROM jdbc_concept2 WHERE email=?";
	
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, email);
			int check = ps.executeUpdate();
			if(check == 1)
				System.out.println(email + "님의 정보가 삭제되었습니다.");
			else
				System.out.println(email + "님의 정보가 삭제되지 않았습니다. 다시 시도하세요.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void update(String email, String name, String mobile) {
		int result = isExists(email);
		
		if(result != 1) {
			System.out.println(email + "는/은 등록된 정보가 아닙니다.");
			return;
		}
		
		String sql = "UPDATE jdbc_concept2 SET name=?, mobile=? WHERE email=?";
	
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, mobile);
			ps.setString(3, email);
			ps.executeUpdate();
			System.out.println(email + "님의 정보가 변경되었습니다.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void selectAll() {
		String sql = "SELECT * FROM jdbc_concept2";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				System.out.println("이메일 : " + rs.getString("email"));
				System.out.println("이름 : " + rs.getString("name"));
				System.out.println("전화번호 : " + rs.getString("mobile"));
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}