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

DTO : Data transfer Object

DAO : Data Accesse Object

DTO는 로직을 가지고 있지 않고 setter 와 getter 만 가지고 있다.

DAO는 실제로 데이터베이스에 연결하는 로직을 가지고 있다.

 

이번에는 DTO와 DAO를 사용하여 간단한 회원가입 프로그램을 만드는 메인 클래스까지 만들어 볼것이다.

DAO는 interface를 이용할 것이다.

 

아래는 DTO의 생성 예시

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

---

DAO에 사용될 인터페이스

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(String email, String name, String mobile);
}

DAO의 생성

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

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

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

	@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) {

		if (isExists(member.getEmail()) == 0) {
			String sql = "INSERT INTO jdbc_concept2 VALUES(?, ?, ?)";
			PreparedStatement ps = null;
			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=?";
		MemberDTO member = new MemberDTO();
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if (rs.next()) {
				member.setEmail(rs.getString("email"));
				member.setName(rs.getString("name"));
				member.setMobile(rs.getString("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() {
		ArrayList<MemberDTO> members = new ArrayList<MemberDTO>();

		String sql = "SELECT * FROM jdbc_concept2";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				MemberDTO member = new MemberDTO();
				String email = rs.getString("emai;");
				String name = rs.getString("name");
				String mobile = rs.getString("mobile");
				member.setEmail(email);
				member.setName(name);
				member.setMobile(mobile);
				members.add(member);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return members;
	}

	@Override
	public void update(String email, String name, String mobile) {
		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();
		} 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 (SQLException e) {
			e.printStackTrace();
		}
	}
}

---

메인 클래스

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 {
	private static MemberDAOImpl memberDao = new MemberDAOImpl();
	private static Scanner in = new Scanner(System.in);
	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();
		}
		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() {
		// 사용자를 입력 받아 DAO로 전달.

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

		// 중복 확인
		int index = memberDao.isExists(member.getEmail());
		if (index == 0) {
			memberDao.insert(member);
			System.out.println(member.getEmail() + "의 정보를 등록했습니다.");
		} else
			System.out.println(member.getEmail() + "는/은 등록된 이메일입니다.");
	}

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

		int index = memberDao.isExists(email);
		if (index == 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();
	}

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

		int index = memberDao.isExists(email);

		if (index == 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> member = new ArrayList<MemberDTO>();
		if(member.isEmpty()==false) {
			for(MemberDTO m : member) {
				System.out.println("이메일 : " + m.getEmail());
				System.out.println("이름 : " + m.getName());
				System.out.println("전화번호 : " + m.getMobile());
			}
		} else {
			System.out.println("등록 후 이용하세요");
			
		}
	}

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

		int index = memberDao.isExists(email);

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

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

		memberDao.update(email, name, mobile);
		System.out.println(email + "님의 정보가 변경되었습니다.");

	}

}