[MVC 패턴] Java로 MVC 패턴 구현하기 (3) - DB 연결

테이블 생성

• 회원 테이블 생성

CREATE TABLE MEMBER(
	NUM INT PRIMARY KEY,
	NAME VARCHAR(20) NOT NULL,
	AGE INT DEFAULT 20
);

 

MemberVO

• 테이블의 Column와 VO의 필드가 매칭되도록 작성

public class MemberVO {
	private int num;
	private String name;
	private int age;
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
	@Override
	public String toString() {
		return "MemberVO [num=" + num + ", name=" + name + ", age=" + age + "]";
	}
}

 

MemberDAO

• JDBC를 이용하여 Oracle DB와 연결후 사용

package model;

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

/*[1]DBMS에 맞는 드라이버 데이터를 로드(적재)
 * [2]DB에 연결
 * [3]DB 사용:데이터를 read,write
 * [4]DB와의 연결을 해제 */

public class MemberDAO {
	public boolean insert(MemberVO mvo) {
		final String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} finally {
			System.out.println("/t로그 : 드라이버 로드 완료");
		}

		// [2]
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "이름";
		final String passwd = "비밀번호";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DriverManager.getConnection(url, user, passwd);

			// [3]
			stmt = conn.createStatement();
			stmt.executeQuery("INSERT INTO MEMBER VALUES((SELECT NVL(MAX(NUM),0)+1 FROM MEMBER),'" + mvo.getName()
					+ "'," + mvo.getAge() + ")");
			System.out.println("   로그: INSERT 성공");

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally { // [4]
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return true;
	}

	public boolean update(MemberVO mvo) {
		final String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} finally {
			System.out.println("/t로그 : 드라이버 로드 완료");
		}

		// [2]
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "subin";
		final String passwd = "1234";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DriverManager.getConnection(url, user, passwd);

			// [3]
			stmt = conn.createStatement();
			stmt.executeQuery("UPDATE MEMBER SET AGE=" + mvo.getAge() + "WHERE NUM=" + mvo.getNum());
			System.out.println("\t로그: UPDATE 성공");

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally { // [4]
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return true;

	}

	public boolean delete(MemberVO mvo) {
		final String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} finally {
			System.out.println("/t로그 : 드라이버 로드 완료");
		}

		// [2]
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "subin";
		final String passwd = "1234";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DriverManager.getConnection(url, user, passwd);

			// [3]
			stmt = conn.createStatement();
			stmt.executeQuery("DELETE FROM MEMBER WHERE NUM=" + mvo.getNum());
			System.out.println("\t로그: DELETE 성공");

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally { // [4]
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return true;
	}

	//회원 검색
	public MemberVO selectOne(MemberVO mvo) {
		final String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} finally {
			System.out.println("/t로그 : 드라이버 로드 완료");
		}

		// [2]
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "subin";
		final String passwd = "1234";
		Connection conn = null;
		Statement stmt = null;
		MemberVO data = null;
		try {
			conn = DriverManager.getConnection(url, user, passwd);
			// [3]
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM MEMBER WHERE NUM=" + mvo.getNum());
			if (rs.next()) {
				data = new MemberVO();
				data.setNum(rs.getInt("NUM"));
				data.setName(rs.getString("NAME"));
				data.setAge(rs.getInt("AGE"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return data;
	}
	
    //전체 회원 목록
	public ArrayList<MemberVO> selectAll(MemberVO bvo) {
		ArrayList<MemberVO> datas = null;
		ResultSet rs;

		final String driverName = "oracle.jdbc.driver.OracleDriver";
		try {
			Class.forName(driverName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} finally {
			System.out.println("/t로그 : 드라이버 로드 완료");
		}

		// [2]
		final String url = "jdbc:oracle:thin:@localhost:1521:xe";
		final String user = "subin";
		final String passwd = "1234";
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DriverManager.getConnection(url, user, passwd);
			// [3]
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM MEMBER");
			while (rs.next()) {
				datas = new ArrayList<MemberVO>();
				MemberVO data = new MemberVO();
				data.setNum(rs.getInt("NUM"));
				data.setName(rs.getString("NAME"));
				data.setAge(rs.getInt("AGE"));
				datas.add(data);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return datas;
	}
}

 

 

View

package view;

import java.util.Scanner;

import model.MemberVO;

public class View {
	Scanner sc;
	public View() {
		sc = new Scanner(System.in);
	}
    
        public int getAction() {
            System.out.println();
            System.out.println("1. INSERT");
            System.out.println("2. SELECTALL");
            System.out.println("3. SELECTONE");
            System.out.println("4. UPDATE");
            System.out.println("5. DELETE");
            System.out.print("입력 >> ");
            int act = sc.nextInt();
            return act;
	}
	
	//추가할 회원의 정보 입력받기
    	//MemberVO 객체로 return
	public MemberVO getMemberInfo() {
            MemberVO mvo = new MemberVO();
            System.out.print("이름을 입력하세요 >> ");
            String name = sc.next();
            mvo.setName(name);
            System.out.print("나이를 입력하세요 >> ");
            mvo.setAge(sc.nextInt());
            return mvo;
	}
	
	public void printTrue() {
            System.out.println("성공했습니다.");
	}
	
	public void printFalse() {
            System.out.println("실패했습니다.");
	}
	
	// 회원 번호 입력받기
	public int updateGetNum() {
            System.out.println("수정할 회원의 번호를 입력하세요 >> ");
            int num = sc.nextInt();
            return num;
	}
	
    	//수정할 나이 입력받기
	public int updateGetAge() {
            System.out.println("나이를 입력하세요 >> ");
            int age = sc.nextInt();
            return age;
	}
	
    	//삭제할 회원 번호 입력받기
	public MemberVO delete() {
            MemberVO mvo = new MemberVO();
            System.out.println("삭제할 학생의 번호를 입력하세요 >> ");
            mvo.setNum(sc.nextInt());
            return mvo;
	}
    
    	//전체 회원 목록 출력
   	public void printDatas(ArrayList<MemberVO> datas) {
            for (MemberVO m : datas) {
                System.out.println(m);
            }
	}
	
    	//회원 검색 결과 출력
	public void printData(MemberVO data) {
            if (data == null) {
                System.out.println("해당 학생은 없습니다...");
                return;
            }
            System.out.println();
            System.out.println(data);
	}
}

 

 

Controller

package ctrl;

import model.MemberDAO;
import model.MemberVO;
import view.View;

public class Ctrl {
	public static void main(String[] args) {
		View view = new View();
		MemberDAO model = new MemberDAO();

		while (true) {
			int act = view.getAction();
			if (act == 1) {
				// INSERT
				MemberVO mvo = view.getMemberInfo();
				if (model.insert(mvo)) {
					view.printTrue();
				} else {
					view.printFalse();
				}
			} else if (act == 2) {
				// 전체 회원 목록
				view.printDatas(model.selectAll(null));
			} else if (act == 3) {
				// 특정회원 검색
				MemberVO mvo = new MemberVO();
				mvo.setNum(view.getNum());
				view.printData(model.selectOne(mvo));
			} else if (act == 4) {
				// 나이 수정
				MemberVO mvo = new MemberVO();
				mvo.setNum(view.getNum());
				mvo.setAge(view.updateGetAge());
				if (model.update(mvo)) {
					view.printTrue();
				}
			} else if (act == 5) {
				// 회원 삭제
				MemberVO mvo = view.delete();
				if (model.delete(mvo)) {
					view.printTrue();
				} else {
					view.printFalse();
				}
			} else {
				break;
			}
		}
	}
}