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

이번엔

 

DB연결을 하는 MemberDAO

 

검증을 주로하는 MemberService

 

사용자의 입력값을 받는 Main으로 나누어보자.

 

바뀐건 거의 없다...

컬렉션 계열인 List나 Map이 하던 DB역할을 진짜 DB역할로 넘겨주는 것

 

추후에 좌표 남기겠지만, 마지막에 했던 것들을 스켈레톤 코드를 남길테니 시간 되는대로 한번 다시 작성해보길 바란다.

(필자 너한테 하는 말이다. 꼭 해라)

 

 

MemberDTO.java

 

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

 

인터페이스 - IMemberDAO.java

 

package jdbc_concept4;

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

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

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

}

 

인터페이스 - IMemberService.java

 

package jdbc_concept4;

public interface IMemberService {
	public void insert(MemberDTO member);
	public void selectEmail(String email);
	public void delete(String email);
	public void selectAll();
	public void update(String email, String name, String mobile);
}

 

MemberServiceImpl.java

 

package jdbc_concept4;

import java.util.ArrayList;

public class MemberServiceImpl implements IMemberService {
	/*
	 * 입력 값 전달받아 검증하기. DAO로 CRUD를 하기 위한 데이터를 전달 결과를 받을게 있다면 받아서 결과 출력, 결과를 받을게 없고
	 * 출력이 있으면 결과 출력
	 */

	private MemberDAOImpl memberDao;

	public MemberServiceImpl() {
		memberDao = new MemberDAOImpl();
	}

	@Override
	public void insert(MemberDTO member) {
		// 중복 확인
		int count = memberDao.isExists(member.getEmail());
		if (count == 0) {
			memberDao.insert(member);
		} else
			System.out.println(member.getEmail() + "는/은 등록된 이메일입니다.");

	}

	@Override
	public void selectEmail(String email) {
		int count = memberDao.isExists(email);
		if (count == 0) {
			System.out.println(email + "는/은 등록된 정보가 아닙니다.");
		} else {
			MemberDTO selectData = memberDao.selectEmail(email);
			System.out.println("이메일 : " + selectData.getEmail());
			System.out.println("이름 : " + selectData.getName());
			System.out.println("전화번호 : " + selectData.getMobile());
		}
		System.out.println();
	}

	@Override
	public void delete(String email) {
		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 + "님의 정보가 삭제되지 않았습니다. 다시 시도하세요.");
	}

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

	@Override
	public void update(String email, String name, String mobile) {
		int count = memberDao.isExists(email);

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

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

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

	}

	public void disconnection() {
		memberDao.disconnection();
	}
}

 

Main.java

 

package jdbc_concept4;

import java.util.Scanner;

public class Main {
	private static MemberServiceImpl memberService = new MemberServiceImpl();
	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() {
		memberService.disconnection();
	}
	
	private static void insert() {
		MemberDTO member = new MemberDTO();
	
		System.out.println("=== 회원 등록 ===");
		System.out.print("이메일 : "); member.setEmail(in.next());
		System.out.print("이름 : ");	member.setName(in.next());
		System.out.print("전화번호 : "); member.setMobile(in.next());
		memberService.insert(member);
	}
	
	private static void selectEmail() {
		System.out.println("=== 회원 검색 ===");
		System.out.print("이메일 : "); 
		String email = in.next();
		memberService.selectEmail(email);
	}

	private static void delete() {
		System.out.println("=== 회원 삭제 ===");
		System.out.print("이메일 : ");
		String email = in.next();
		memberService.delete(email);
	}
	
	private static void selectAll() {
		memberService.selectAll();
	}
	
	private static void update() {
		System.out.println("=== 회원 수정 ===");
		System.out.print("이메일 : "); String email = in.next();
		System.out.print("이름 : ");	String name = in.next();
		System.out.print("전화번호 : "); String mobile = in.next();
		memberService.update(email, name, mobile);
	}
}