JAVA + DB 실습(2)

<< Main >>

package jdbc_concept4;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Main {
	private static Scanner sc = new Scanner(System.in);
	private static MemberServiceImpl ms = new MemberServiceImpl();

	public static void main(String[] args) {
		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(sc.next());
			} catch (Exception e) {
				continue;
			}
			switch (select) {
			case 1:
				insert();
				break;
			case 2:
				selectEmail();
				break;
			case 3:
				delete();
				break;
			case 4:
				selectAll();
				break;
			case 5:
				update();
				break;
			case 6:
				exit();
				System.exit(0);
			default:
				System.out.println("메뉴 확인 후 다시 입력하세요.");
			}
		}
	}

	private static void insert() {
		MemberDTO member = new MemberDTO();
		System.out.print("이메일 입력 : ");
		member.setEmail(sc.next());
		System.out.print("이름 입력 : ");
		member.setName(sc.next());
		System.out.print("전화번호 입력 : ");
		member.setMobile(sc.next());
		
		ms.insert(member);
	}

	private static void selectEmail() {
		System.out.print("검색하실 이메일 입력 : ");
		ms.selectEmail(sc.next());
	}

	private static void delete() {
		System.out.print("삭제하실 이메일 입력 : ");
		ms.delete(sc.next());
	}

	private static void selectAll() {
		ms.selectAll();
	}

	private static void update() {
		MemberDTO member = new MemberDTO();
		System.out.print("수정하실 이메일 입력 : ");
		member.setEmail(sc.next());
		System.out.print("수정하실 이름 입력 : ");
		member.setName(sc.next());
		System.out.print("수정하실 전화번호 입력 : ");
		member.setMobile(sc.next());
		
		ms.update(member);
	}

	private static void exit() {
		ms.exit();
	}
}

 

<< MemberDTO >>

package jdbc_concept4;

public class MemberDTO {
	private String email;
	private String name;
	private String mobile;
	
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	
}

 

<< MemberService Interface >>

package jdbc_concept4;

import java.sql.ResultSet;

public interface IMemberDAO {

	public void insert(MemberDTO member);
	public ResultSet selectEmail(String email);
	public int delete(String email);
	public ResultSet selectAll();
	public void update(MemberDTO member);
	
}

 

<< MemberService >>

package jdbc_concept4;

import java.sql.ResultSet;
import java.sql.SQLException;

public class MemberServiceImpl {

	private MemberDAO memberDao = new MemberDAO();
	private ResultSet rs;

	public void insert(MemberDTO member) {
		rs = memberDao.selectEmail(member.getEmail());

		try {
			if (rs.next()) {
				System.out.println(member.getEmail() + "는 이미 등록된 정보입니다.");
				return;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		memberDao.insert(member);
		System.out.println("등록 완료");
	}

	public void selectEmail(String email) {
		rs = memberDao.selectEmail(email);
		try {
			if (rs.next()) {
				System.out.printf("이메일 : %s\t이름 : %s\t전화번호 : %s\n", rs.getString("email"), rs.getString("name"), rs.getString("mobile"));
				return;
			}
			System.out.println(email + "은 없는 정보입니다.");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete(String email) {
		int verify = memberDao.delete(email);
		if (verify != 1) {
			System.out.println(email + "는 없는 정보입니다.");
			return;
		}
		System.out.println("삭제가 완료되었습니다.");
	}

	public void selectAll() {
		rs = memberDao.selectAll();
		if (rs == null) {
			System.out.println("등록된 정보가 없습니다.");
			return;
		}
		try {
			while (rs.next()) {					
				System.out.printf("이메일 : %s\t이름 : %s\t전화번호 : %s\n",
						rs.getString("email"), rs.getString("name"), rs.getString("mobile"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void update(MemberDTO member) {
		rs = memberDao.selectEmail(member.getEmail());

		try {
			if (rs.next()) {
				memberDao.update(member);
				System.out.println("수정 완료");
				return;
			}
			System.out.println(member.getEmail() + "는 없는 정보입니다.");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void exit() {
		memberDao.exit();
		System.out.println("프로그램을 종료합니다");
		System.exit(0);
	}
}

 

<< MemberDAO >>

package jdbc_concept4;

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

public class MemberDAO implements IMemberDAO {

	private Connection connect;
	private PreparedStatement ps;
	private ResultSet rs;
	
	public MemberDAO() {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("드라이버를 찾을 수 없습니다.");
			e.printStackTrace();
		}
		
		try {
			connect = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","oracle", "oracle");
		} catch (SQLException e) {
			System.out.println("DB연결 과정에서 오류가 발생했습니다.");
			e.printStackTrace();
		}
	}

	@Override
	public void insert(MemberDTO member) {
		String insertQuery = "INSERT INTO jdbc_concept4 VALUES(?, ?, ?)";
		try {
			ps = connect.prepareStatement(insertQuery);
			ps.setString(1, member.getEmail());
			ps.setString(2, member.getName());
			ps.setString(3, member.getMobile());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public ResultSet selectEmail(String email) {
		String selectQuery = "SELECT * FROM jdbc_concept4 WHERE email = ?";
		try {
			ps = connect.prepareStatement(selectQuery);
			ps.setString(1, email);
			return ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public int delete(String email) {
		String deleteQuery = "DELETE FROM jdbc_concept4 WHERE email = ?";
		int verify = 0;
		
		try {
			ps = connect.prepareStatement(deleteQuery);
			ps.setString(1, email);
			verify = ps.executeUpdate();
			return verify;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return verify;
	}

	@Override
	public ResultSet selectAll() {
		String allQuery = "SELECT * FROM jdbc_concept4";
		try {
			ps = connect.prepareStatement(allQuery);
			// 모든 정보의 조회가 DB에서 2번이나 일어나기 때문에 좋지 못한 코드?
			rs = ps.executeQuery();
			return rs.next() ? ps.executeQuery() : null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public void update(MemberDTO member) {
		String updateQuery = "UPDATE jdbc_concept4 SET name = ?, mobile = ? WHERE email = ?";
		try {
			ps = connect.prepareStatement(updateQuery);
			ps.setString(1, member.getName());
			ps.setString(2, member.getMobile());
			ps.setString(3, member.getEmail());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void exit() {
		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (connect != null)
				connect.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}