DB - DB연동을 통한 회원관리 프로그램 구축 하는 퀴즈(2022-08-09)

인터페이스 - IDAO.java

package jdbc_concept5.DAO;

import java.util.ArrayList;

import jdbc_concept5.DTO.MemberDTO;

public interface IDAO {
	public MemberDTO selectId(String id);
	public int insertMember(MemberDTO member);
	public ArrayList<MemberDTO> selectAll();
	public int updateMember(MemberDTO member);
	public int deleteMember(String id);
	public int isExists(String id);
}

MemberDAO.java

package jdbc_concept5.DAO;

/*CREATE TABLE jdbc_concept5(
		  2  num number,
		  3  id varchar2(20),
		  4  pw varchar2(20),
		  5  name varchar2(20),
		  6  email varchar2(50)
		  7  );*/

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

import jdbc_concept5.DTO.MemberDTO;

public class MemberDAO implements IDAO {
	private Connection con;
	private PreparedStatement ps;
	private ResultSet rs;

	public MemberDAO() {
		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 id) {
		String sql = "SELECT count(id) FROM jdbc_concept5 WHERE id=?";
		int count = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public MemberDTO selectId(String id) {
		String sql = "SELECT * FROM jdbc_concept5 WHERE id=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setId(rs.getString("id"));
				member.setNum(rs.getInt("num"));
				member.setPw(rs.getString("pw"));
				member.setName(rs.getString("name"));
				member.setEmail(rs.getString("email"));
				return member;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int insertMember(MemberDTO member) {
		/*- num	 	: int	// 회원번호
		- id		: String	// 아이디
		- pw		: String	// 패스워드
		- name		: String	// 이름
		- email		: String	// 이메일*/

		String sql = "INSERT INTO jdbc_concept5 VALUES(?, ?, ?, ?, ?)";
		int check = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, member.getNum());
			ps.setString(2, member.getId());
			ps.setString(3, member.getPw());
			ps.setString(4, member.getName());
			ps.setString(5, member.getEmail());
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return check;
	}

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

		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				MemberDTO member = new MemberDTO();
				member.setNum(rs.getInt("num"));
				member.setId(rs.getString("id"));
				member.setPw(rs.getString("pw"));
				member.setName(rs.getString("name"));
				member.setEmail(rs.getString("email"));

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

	@Override
	public int updateMember(MemberDTO member) {
		String sql = "UPDATE jdbc_concept5 SET num=?, pw=?, name=?, email=? WHERE id=?";
		int check = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, member.getNum());
			ps.setString(2, member.getPw());
			ps.setString(3, member.getName());
			ps.setString(4, member.getEmail());
			ps.setString(5, member.getId());

		} catch (Exception e) {
			e.printStackTrace();
		}
		return check;
	}

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

	public void disconnection() {

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

	}

}

인터페이스 - IDTO.java

package jdbc_concept5.DTO;

public interface IDTO {
	public int getNum();
	public void setNum(int num) ;
	
	public String getId();
	public void setId(String id);
	
	public String getPw() ;
	public void setPw(String pw) ;
	
	public String getName() ;
	public void setName(String name) ;
	
	public String getEmail() ;
	public void setEmail(String email);
}

MemberDTO.java

package jdbc_concept5.DTO;

public class MemberDTO implements IDTO {
	private int num;
	private String id;
	private String pw;
	private String name;
	private String email;

	@Override
	public int getNum() {
		return num;
	}

	@Override
	public void setNum(int num) {
		this.num = num;

	}

	@Override
	public String getId() {
		return id;
	}

	@Override
	public void setId(String id) {
		this.id = id;

	}

	@Override
	public String getPw() {
		return pw;
	}

	@Override
	public void setPw(String pw) {
		this.pw = pw;

	}

	@Override
	public String getName() {
		return name;
	}

	@Override
	public void setName(String name) {
		this.name = name;

	}

	@Override
	public String getEmail() {
		return email;
	}

	@Override
	public void setEmail(String email) {
		this.email = email;

	}

}

인터페이스 - IView.java

package jdbc_concept5.view;

public interface IView {
	public void mainMenu() ; 
	public void deleteMember();
	public void updateMember();
	public void insertMember();
	public void selectId();
	public void selectAll();
}

MemberView.java

package jdbc_concept5.view;

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

import jdbc_concept5.DAO.MemberDAO;
import jdbc_concept5.DTO.MemberDTO;

public class MemberView implements IView{
	private static Scanner in = new Scanner(System.in);
	private static MemberDAO service = new MemberDAO();

	@Override
	public void mainMenu() {
		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("0. 프로그램 종료");
			System.out.print("선택 > ");
			int select;
			try {
				select = Integer.parseInt(in.next());
			} catch (Exception e) {
				continue;
			}
			switch (select) {
			case 0 :
				disconnection();
				System.out.println("프로그램을 종료합니다.");
				System.exit(0);
			case 1 : 
				selectAll();
				break;
			case 2 :
				selectId();
				break;
			case 3 :
				insertMember();
				break;
			case 4 :
				updateMember();
				break;
			case 5 :
				deleteMember();
				break;
			default:
				System.out.println("메뉴 확인 후 다시 입력하세요.");
			}
		}
		
	}
	
	private static void disconnection() {
		service.disconnection();
	}
	

	@Override
	public void deleteMember() {
		System.out.println("=== 회원 정보 삭제 ===");
		System.out.print("아이디 : ");
		String id = in.next();
		
		int count = service.isExists(id);
		
		if(count == 0) {
			System.out.println(id + "는/은 등록된 정보가 아닙니다.");
			return;
		}
		int rowCount = service.deleteMember(id);
		if(rowCount == 1)
			System.out.println(id + "님의 정보가 삭제되었습니다.");
		
	}

	@Override
	public void updateMember() {
		System.out.println("=== 회원 정보 변경 ===");
		System.out.print("아이디 : ");
		String id = in.next();
		
		int count = service.isExists(id);
		
		if(count == 0) {
			System.out.println(id + "는/은 등록된 정보가 아닙니다.");
			return;
		}
		
		System.out.print("패스워드 : ");
		String pw = in.next();
		System.out.print("이름 : ");
		String name = in.next();
		System.out.print("이메일 : ");
		String email = in.next();
		
		MemberDTO member = new MemberDTO();
		member.setPw(pw);
		member.setName(name);
		member.setEmail(email);
		
		service.updateMember(member);
		System.out.println(id + "님의 정보가 수정되었습니다.");
	}

	@Override
	public void insertMember() {
		MemberDTO member = new MemberDTO();
		
		System.out.println("=== 회원 정보 입력 ===");
		System.out.print("아이디 : "); 
		member.setId(in.next());
		
		int count = service.isExists(member.getId());
		if(count == 0) {
		System.out.print("번호 : "); member.setNum(in.nextInt());
		System.out.print("패스워드 : "); member.setPw(in.next());
		System.out.print("이름 : "); member.setName(in.next());
		System.out.print("이메일 : "); member.setEmail(in.next());
		service.insertMember(member);
		}else
		System.out.println(member.getId() + "는/은 이미 등록된 아이디입니다.");
	}

	@Override
	public void selectId() {
		System.out.println("=== 회원 정보 조회 ===");
		System.out.print("아이디 : ");
		String id = in.next();
		
		int count = service.isExists(id);
		if(count == 0) {
			System.out.println(id + "는/은 등록된 정보가 없습니다.");
		} else {
			MemberDTO selectData = service.selectId(id);
			System.out.println("번호 : " + selectData.getNum());
			System.out.println("아이디 : " + selectData.getId());
			System.out.println("패스워드 : " + selectData.getPw());
			System.out.println("이름 : " + selectData.getName());
			System.out.println("이메일 : " + selectData.getEmail());
		}
		System.out.println();
		
	}

	@Override
	public void selectAll() {
		ArrayList<MemberDTO> members = service.selectAll();
		
		if(members.isEmpty() == true) {
			System.out.println("등록 후 이용해주시길 바랍니다.");
		}else {
			for(MemberDTO m : members) {
				System.out.println("번호 : " + m.getNum());
				System.out.println("아이디 : " + m.getId());
				System.out.println("패스워드 : " + m.getPw());
				System.out.println("이름 : " + m.getName());
				System.out.println("이메일 : " + m.getEmail());
				System.out.println();
			}
		}
		
	}

	

}

Main.java

package jdbc_concept5.run;

import jdbc_concept5.view.MemberView;

public class Main {

	public static void main(String[] args) {
		MemberView mv = new MemberView();
		mv.mainMenu();
	}
}