DB - DB, 이클립스 연동 후 회원정보 프로그램 구축하는 퀴즈 02 (2022-08-08)

이번 게시물에서는 Main 코드에서 쓰던

검증 파트

중복된 이메일이 있으면 회원정보가 등록이 안되거나

 

회원정보가 없으면 이메일로 검색할 때 회원정보가 없다 라고 뜨는.....

 

그것을 Main.java에 그대로 쓰고, DB로 옮겨지는 작업을 MemberDAO로 분리해보도록 하자. (또 분리하기 시작한다....)

 

MemberDTO.java

 

package jdbc_concept3;

//입,출력에 직접적으로 나타나는 코드들

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

}

 

인터페이스 - IMemberDAO.java

package jdbc_concept3;

//자식 클래스에게 빈 설계도를 넘겨줌.

import java.util.ArrayList;

public interface IMemberDAO {

	public int isExists(String email);

	public void insert(MemberDTO member);

	public MemberDTO selectEmail(String email);

	public int delete(String email);

	public ArrayList<MemberDTO> selectAll();

	public void update(MemberDTO member);
}

 

MemberDAOImpl.java

 

package jdbc_concept3;

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

//main코드에서 하던 검증 및 저장의 역할을 하는
//DB와 관련된 코드

public class MemberDAOImpl implements IMemberDAO {
	private Connection con;
	private PreparedStatement ps;
	private ResultSet rs;

	public MemberDAOImpl() {
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
		String user = "<권한받은 계정>";
		String password = "<설정한 비밀번호>";

		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public 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;
	}

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

	@Override
	public MemberDTO 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()) {
				MemberDTO member = new MemberDTO();
				member.setEmail(rs.getString("email"));
				member.setName(rs.getString("name"));
				String mobile = rs.getString("mobile");
				member.setMobile(mobile);
				return member;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int delete(String email) {
		String sql = "DELETE FROM jdbc_concept2 WHERE email=?";
		int check = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, email);
			check = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return check;
	}

	@Override
	public ArrayList<MemberDTO> selectAll() {
		String sql = "SELECT * FROM jdbc_concept2";
		ArrayList<MemberDTO> members = new ArrayList<>();

		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setEmail(rs.getString("email"));
				member.setName(rs.getString("name"));
				String mobile = rs.getString("mobile");
				member.setMobile(mobile);

				members.add(member);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return members;
	}

	@Override
	public void update(MemberDTO member) {
		String sql = "UPDATE jdbc_concept2 SET name=?, mobile=? WHERE email=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, member.getName());
			ps.setString(2, member.getMobile());
			ps.setString(3, member.getEmail());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void disconnection() {

		try {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
			if (con != null)
				con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}

 

Main.java

 

package jdbc_concept3;

import java.util.ArrayList;
import java.util.Scanner;

//메뉴를 선택하고 사용자가 입력값을 적으면
//Main에서 입력값에 대한 검증을 한 후,
//MemberDAOImpl로 가서 DB에 저장

public class Main {
	private static MemberDAOImpl memberDao = new MemberDAOImpl();
	private static Scanner in = new Scanner(System.in);

	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(in.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:
				disconnection();
				System.out.println("프로그램을 종료합니다.");
				System.exit(0);
			default:
				System.out.println("메뉴 확인 후 다시 입력하세요.");
			}
		}
	}

	private static void disconnection() {
		memberDao.disconnection();
	}

	private static void insert() {
		MemberDTO member = new MemberDTO();

		System.out.println("=== 회원 등록 ===");
		System.out.print("이메일 : ");
		member.setEmail(in.next());

		// 중복 확인
		int count = memberDao.isExists(member.getEmail());
		if (count == 0) {
			System.out.print("이름 : ");
			member.setName(in.next());
			System.out.print("전화번호 : ");
			member.setMobile(in.next());
			memberDao.insert(member);
		} else
			System.out.println(member.getEmail() + "는/은 등록된 이메일입니다.");
	}

	private static void selectEmail() {
		System.out.println("=== 회원 검색 ===");
		System.out.print("이메일 : ");
		String email = in.next();

		int count = memberDao.isExists(email);
		if (count == 0) {
			System.out.println(email + "는/은 등록된 정보가 아닙니다.");
		} else {
			MemberDTO selectData = memberDao.selectEmail(email);
			if (selectData == null) {
				System.out.println("정보를 얻어 올 수 없는 상황");
			} else {
				System.out.println("이메일 : " + selectData.getEmail());
				System.out.println("이름 : " + selectData.getName());
				System.out.println("전화번호 : " + selectData.getMobile());
			}
		}
		System.out.println();
	}

	private static void delete() {
		System.out.println("=== 회원 삭제 ===");
		System.out.print("이메일 : ");
		String email = in.next();

		int count = memberDao.isExists(email);

		if (count == 0) {
			System.out.println(email + "는/은 등록된 정보가 아닙니다.");
			return;
		}

		int rowCount = memberDao.delete(email);
		if (rowCount == 1)
			System.out.println(email + "님의 정보가 삭제되었습니다.");
		else if (rowCount == 0)
			System.out.println(email + "님의 정보가 삭제되지 않았습니다. 다시 시도하세요.");
	}

	private static void selectAll() {
		ArrayList<MemberDTO> members = memberDao.selectAll();

		if (members.isEmpty() == true) {
			System.out.println("등록 후 이용하세요.");
		} else {
			for (MemberDTO m : members) {
				System.out.println("이메일 : " + m.getEmail());
				System.out.println("이름 : " + m.getName());
				System.out.println("전화번호 : " + m.getMobile());
				System.out.println();
			}
		}
	}

	private static void update() {
		System.out.println("=== 회원 수정 ===");
		System.out.print("이메일 : ");
		String email = in.next();

		int count = memberDao.isExists(email);

		if (count == 0) {
			System.out.println(email + "는/은 등록된 정보가 아닙니다.");
			return;
		}

		System.out.print("이름 : ");
		String name = in.next();
		System.out.print("전화번호 : ");
		String mobile = in.next();

		MemberDTO member = new MemberDTO();
		member.setEmail(email);
		member.setName(name);
		member.setMobile(mobile);

		memberDao.update(member);

		System.out.println(email + "님의 정보가 수정되었습니다.");
	}
}