7_JDBC_(개인프로젝트)

study/JDBC · 2019. 12. 7. 08:19

JDBC 개인프로젝트

driver.properties

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:xe
user=DEOK
password=DEOK

common.JDBCTemplate

package common;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCTemplate {
	
	/* 기존 DB 처리 작업 시 마다
	 * 새로운 Connection 객체를 생성하여 DB 연결을 진행함.
	 * 이렇게 하면 한 사용자가 여러 개의 커넥션을 생성하게됨.
	 * --> 메모리 낭비 증가, 객체 생성 소멸에 따른 시간 소비
	 * 
	 * 이를 해결하기 위해서
	 * 프로그램 전체에서 생성할 수 있는 Connection객체를 한 개로 제한
	 * -> 메모리 낭비 및 DB 연결 객체수 오버를 방지
	 * --> 싱글톤(SingleTone) 패턴
	 * 
	 * * 싱글톤 패턴이란
	 * - 프로그램 구동 시 메모리에 객체를 단 하나만 기록되게 하는
	 *   디자인 패턴
	 *   
	 * - 한 클래스의 모든 필드, 메소드를 static으로 선언하여
	 *   static 영역 자체에 해당 클래스 객체를 만드는 형태 작성
	 */
	
	// 한 개의 공용 Connection 객체를 저장할 참조 변수 선언
	// 단, 외부에서 직접 접근할 수 없도록 private으로 선언
	private static Connection conn = null;
	// #싱글톤패턴으로 선언하기위해 어쩔수 없이 static을 붙임
	
	// DB 연결을 위한 Connection 객체 요청 메소드 작성
	public static Connection getConnection() {
		
		// 프로그램 실행 후 한번도 getConnection() 메소드가 
		// 호출되지 않았을 때 
		// -> Connection 객체 생성
		if(conn == null) {
			/* 이전 프로젝트에서 Connection 생성 과정
			 * - JDBC 드라이버 로드
			 * - DB 연결을 위한 정보(url, id, pwd)
			 * 이러한 내용들을 직접 작성함. (정적코딩)
			 * --> 추후 DB정보가 변경되는 경우
			 * 	      코드 자체를 수정해서 다시 컴파일, 배포해야함.
			 *     --> 유지보수 불편
			 *     
			 * 이를 해결하기 위해 Properties 파일을 사용.
			 * 프로그램 실행 시 동적으로 Properties 파일에서
			 * DB 연결 정보를 읽어오도록 코딩. (동적 코딩)
			 * 
			 * -> driver.properties 파일 작성
			 * 		 
			 */
			
			// 외부에서 DB 연결 정보 읽어올 Properties 객체 생성
			try {
				Properties prop = new Properties();
				
				// driver.properties 파일에서 정보를 읽어옴
				prop.load(new FileReader("driver.properties"));
				// -> IOException 발생 가능성이 있음.
				
				// driver.properties에서 읽어드린 정보를 이용해
				// DB와 연결할 Connection 객체 생성
				
				// jdbc 드라이버 로드
				Class.forName(prop.getProperty("driver"));
				
				// Connection 객체 생성
				conn = DriverManager.getConnection(
						prop.getProperty("url"),
						prop.getProperty("user"),
						prop.getProperty("password"));
				
				// Auto Commit 비활성화
				conn.setAutoCommit(false);
				
			} catch(Exception e) {
				e.printStackTrace();
			}
		} 
		
		return conn;
	}
	
	
	// DB 연결 관련 자원 반환 메소드 close() 작성
	public static void close(Statement stmt) {
		// PreparedStatement는 statement의 자식
		// -> 상속 관계 -> 다형성 적용 -> 매개변수로 부모타입 사용가능
		
		try {
			if(stmt != null && !stmt.isClosed()) {
				stmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void close(ResultSet rset) {
		// PreparedStatement는 statement의 자식
		// -> 상속 관계 -> 다형성 적용 -> 매개변수로 부모타입 사용가능
		
		try {
			if(rset != null && !rset.isClosed()) {
				rset.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void close(Connection conn) {
		// PreparedStatement는 statement의 자식
		// -> 상속 관계 -> 다형성 적용 -> 매개변수로 부모타입 사용가능
		
		try {
			if(conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	// 처리 결과에 따른 트랜잭션 처리도 공통적인 업무임.
	// --> static으로 선언하여 코드길이 감소, 재사용성의 증가
	public static void commit(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) {
				conn.commit();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void rollback(Connection conn) {
		try {
			if(conn != null && !conn.isClosed()) {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
}

run.Run

package run;

import view.MemberView;

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

model.vo.Crt

package model.vo;

public class Crt{
	
	private int crtNo;
	private String crtName;
	private String crtClass;
	private int crtLevel;
	private String crtOwner;
	
	public Crt() {
		// TODO Auto-generated constructor stub
	}
	
	
	public Crt(String crtName, String crtClass) {
		this.crtName = crtName;
		this.crtClass = crtClass;
	}

	
	public Crt(String crtName, String crtClass, int crtLevel, String crtOwner) {
		super();
		this.crtName = crtName;
		this.crtClass = crtClass;
		this.crtLevel = crtLevel;
		this.crtOwner = crtOwner;
	}


	public Crt(int crtNo, String crtName, String crtClass, int crtLevel, String crtOwner) {
		this(crtName, crtClass);
		this.crtNo = crtNo;
		this.crtLevel = crtLevel;
		this.crtOwner = crtOwner;
	}

	public int getCrtNo() {
		return crtNo;
	}

	public void setCrtNo(int crtNo) {
		this.crtNo = crtNo;
	}

	public String getCrtName() {
		return crtName;
	}

	public void setCrtName(String crtName) {
		this.crtName = crtName;
	}

	public String getCrtClass() {
		return crtClass;
	}

	public void setCrtClass(String crtClass) {
		this.crtClass = crtClass;
	}

	public int getCrtLevel() {
		return crtLevel;
	}

	public void setCrtLevel(int crtLevel) {
		this.crtLevel = crtLevel;
	}

	public String getCrtOwner() {
		return crtOwner;
	}

	public void setCrtOwner(String crtOwner) {
		this.crtOwner = crtOwner;
	}

	@Override
	public String toString() {
		return "Crt [crtNo=" + crtNo + ", crtName=" + crtName + ", crtClass=" + crtClass + ", crtLevel="
				+ crtLevel + ", crtOwner=" + crtOwner + "]";
	}
	
	
}

model.vo.Member

package model.vo;

import java.sql.Date;

public class Member {
	private String memberId;
	private String memberPwd;
	private String memberName;
	private char gender;
	private String email;
	private int age;
	private Date enrollDate;
	
	public Member() {
		// TODO Auto-generated constructor stub
	}
	
	

	public Member(String memberName, char gender, String email, int age) {
		super();
		this.memberName = memberName;
		this.gender = gender;
		this.email = email;
		this.age = age;
	}
	
	


	public Member(String memberId, String memberName, char gender, String email, int age, Date enrollDate) {
		this(memberName, gender, email, age);
		this.memberId = memberId;
		this.enrollDate = enrollDate;
	}



	public Member(String memberId, String memberPwd, String memberName, char gender, String email, int age) {
		this(memberName, gender, email, age);
		this.memberId = memberId;
		this.memberPwd = memberPwd;

	}



	public Member(String memberId, String memberPwd, String memberName, char gender, String email, int age,
			Date enrollDate) {
		this(memberId, memberPwd, memberName, gender, email, age);
		this.enrollDate = enrollDate;
	}

	public String getMemberId() {
		return memberId;
	}

	public void setMemberId(String memberId) {
		this.memberId = memberId;
	}

	public String getMemberPwd() {
		return memberPwd;
	}

	public void setMemberPwd(String memberPwd) {
		this.memberPwd = memberPwd;
	}

	public String getMemberName() {
		return memberName;
	}

	public void setMemberName(String memberName) {
		this.memberName = memberName;
	}

	public char getGender() {
		return gender;
	}

	public void setGender(char gender) {
		this.gender = gender;
	}

	public String getEmail() {
		return email;
	}

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

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public Date getEnrollDate() {
		return enrollDate;
	}

	public void setEnrollDate(Date enrollDate) {
		this.enrollDate = enrollDate;
	}

	@Override
	public String toString() {
		return "Member [memberId=" + memberId + ", memberPwd=" + memberPwd + ", memberName=" + memberName
				+ ", gender=" + gender + ", email=" + email + ", age=" + age + ", enrollDate=" + enrollDate + "]";
	}
	
	
}

view.MemberView

package view;

import static common.JDBCTemplate.getConnection;

import java.sql.Connection;
import java.util.List;
import java.util.Scanner;

import controller.MemberController;
import model.dao.MemberDAO;
import model.vo.Crt;
import model.vo.Member;

/**회원메인메뉴
 * @author Deok
 *
 */
public class MemberView {
	
	private Scanner sc = new Scanner(System.in);
	
	/**
	 * 
	 */
	public void mainMenu() {
		
		MemberController mController = new MemberController();
		
		int sel = 0;
		
		do {
			if(MemberController.LoginMember == null) {
				System.out.println(" Welcome ninige");
				System.out.println(" *** 메인 메뉴 *** ");
				System.out.println("1. 로그인");
				System.out.println("2. 회원가입");
				System.out.println("0. 프로그램종료");
				
				System.out.print("메뉴 선택 : ");
				sel = sc.nextInt();
				sc.nextLine();
				
				switch(sel) {
				case 1: mController.login(); break;
				case 2: mController.insertMember(); break;
				case 0: System.out.println("바이바이"); break;
				default : System.out.println("잘못 입력. 다시입력해요");
				}
			} else {
				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.println("7. 캐릭터 삭제");
				System.out.println("0. 프로그램 종료");
				System.out.print("메뉴 선택 : ");
				sel = sc.nextInt();
				sc.nextLine();
				
				switch(sel) {
				case 1 : System.out.println("로그아웃되었슴");
				 	MemberController.LoginMember = null; break;
				case 2 : mController.selectMember(); break;
				case 3 : mController.updateMember(); break;
				case 4 : mController.insertCrt(); break;
				case 5 : mController.selectAll(); break;
				case 6 : mController.selectCrt(); break;
				case 7 : mController.deleteCrt(); break;
				case 0 : mController.gameOver(); 
						System.out.println("다음이시간에~");break;
				default : System.out.println("잘못입력 다시입력");
				}
			}
		} while (sel !=0 );
		
	}

	/** 2. 회원가입
	 * @return member : Member
	 */
	public Member insertMember() {
		System.out.print("아이디 : ");
		String memberId = sc.nextLine();
		
		System.out.print("비밀번호 : ");
		String memberPwd = sc.nextLine();
		
		System.out.print("이름 : ");
		String memberName = sc.nextLine();
		
		System.out.print("성별(M/F) : ");
		char gender = sc.nextLine().toUpperCase().charAt(0);
		
		System.out.print("이메일 : ");
		String email = sc.nextLine();

		System.out.print("나이 : ");
		int age = sc.nextInt();
		sc.nextLine();

		Member member = new Member(memberId, memberPwd, memberName, gender, email, age);
		
		return member;
	}

	public void displaySuccess(String msg) {
		System.out.println("서비스 요청 성공 : " + msg);
		
	}
	
	public void displayFail(String msg) {
		System.out.println("서비스 요청 실패 : " + msg);
	}
	
	public void displayError(String msg, Exception e) {
		System.out.println("서비스 요청 중 오류 발생 : " + msg);
		e.printStackTrace();
	}

	public Member inputLogin() {
		Member loginMember = new Member();
		System.out.println("--- 로그인 ---");
		System.out.print("계정 : ");
		loginMember.setMemberId(sc.nextLine());
		System.out.print("비밀번호 : ");
		loginMember.setMemberPwd(sc.nextLine());
		
		return loginMember;
	}

	/** 비밀번호 입력
	 * @return String
	 */
	public String inputPw() {
		System.out.println("=== 비밀번호입력 ===");
		System.out.print("비밀번호 : ");
		return sc.nextLine();
	}

	public Member updateMember() {
		System.out.println("=== 수정할 정보 입력 ===");
		System.out.print("이름 : ");
		String memberName = sc.nextLine();
		
		System.out.print("성별(M/F) : ");
		char gender = sc.nextLine().toUpperCase().charAt(0);
		
		System.out.print("이메일 : ");
		String email = sc.nextLine();

		System.out.print("나이 : ");
		int age = sc.nextInt();
		sc.nextLine();
		
		Member member = new Member(memberName, gender, email, age);
		return member;
	}

	/** 회원정보조회
	 * @param member : Member
	 */
	public void selectMember(Member member) {
		System.out.printf("%-10s %-10s %-3s %-15s %-10s %-15s\n",
				"ID", "NAME", "GENDER", "EMAIL", "AGE", "CREATE_DATE");
		System.out.println("------------------------------------------");
		System.out.printf("%-10s %-10s %-3s %-15s %-10d %-15s\n",
					member.getMemberId(), member.getMemberName(), 
					member.getGender(), member.getEmail(),
					member.getAge(), member.getEnrollDate());
	
	}

	public Crt insertCrt() {
		
		System.out.println("---캐릭터 생성 페이지---");
		System.out.print("캐릭터명 : ");
		String crtName = sc.nextLine();
		System.out.print("클래스 (전사/마법사/도적) : ");
		String crtClass = sc.nextLine();
		Crt crt = new Crt(crtName, crtClass);
		return crt;
	}

	public void selectAll(List<Crt> cList) {
		System.out.printf("%-3s %-15s %-10s %-15s\n",
				"NAME", "CLASS", "LEVEL", "MEMBER_ID");
		System.out.println("------------------------------------------");
		for(Crt c : cList) {
			System.out.printf("%-3s %-15s %-10d %-15s\n",
					c.getCrtName(), c.getCrtClass(), c.getCrtLevel(), c.getCrtOwner());
		}

	}

	public String delName() {
		System.out.print("삭제할캐릭터입력 : ");
		return sc.nextLine();
	}
	
	public char delCheck() {
		System.out.print("진짜 지울꺼야? Y/N : ");
		return sc.nextLine().toUpperCase().charAt(0);
	}

}

controller.MemberController

package controller;

import java.util.List;

import model.service.MemberService;
import model.vo.Crt;
import model.vo.Member;
import view.MemberView;

public class MemberController {

	private MemberView view = new MemberView();
	private MemberService mService = new MemberService();
	public static Member LoginMember = null;
	
	/**
	 * 회원가입
	 */
	public void insertMember() {
		Member member = view.insertMember();
		
		try {
			int result = mService.insertMember(member);
			
			if(result > 0) {
				view.displaySuccess(result + "계정이추가됨");
			} else {
				view.displayFail("계정추가실패");
			}
		} catch (Exception e) {
			view.displayError("데이터 삽입과정중 오류", e);
			
		}
	}

	/**
	 * 회원로그인
	 */
	public void login() {
		
		Member inputLogin = view.inputLogin();
		
		try {
			
			LoginMember = mService.login(inputLogin);
			
			if(LoginMember != null) {
				view.displaySuccess("로그인 성공");
			} else {
				view.displayFail("로그인 실패");
			}
		} catch (Exception e) {
			view.displayError("로그인중 에러발생", e);
		}
	}

	/**
	 * 계정정보수정
	 */
	public void updateMember() {
		
		String inputPw = view.inputPw();
		
		if(!LoginMember.getMemberPwd().equals(inputPw)) {
			view.displayFail("비밀번호가다릅니다, 넌누구?");
			return;
		}
		Member member = view.updateMember();
		try {
			int result = mService.updateMember(member);
			if(result > 0) {
				view.displaySuccess("정보수정성공!");
			} else {
				view.displayFail("정보수정실패!");
			}
		} catch (Exception e) {
			view.displayError("수정중 에러", e);
		}
		
		
	}

	/**
	 * 계정정보조회
	 */
	public void selectMember() {
		
		String inputPw = view.inputPw();
		
		if(!LoginMember.getMemberPwd().equals(inputPw)) {
			view.displayFail("비밀번호가다릅니다, 넌누구?");
			return;
		}
		try {
			Member member = mService.selectMember();
			if(member != null) {
				view.displaySuccess("계정정보조회성공");
				view.selectMember(member);
			} else {
				view.displayFail("계정정보가없수다");
			}
			
		} catch (Exception e) {
			view.displayError("조회중 에러", e);
		}
		
		
		
	}

	/**
	 * 케릭터 생성
	 */
	public void insertCrt() {
		Crt crt = view.insertCrt();
		
		try {
			int result = mService.insertCrt(crt);
			if(result > 0) {
				view.displaySuccess("케릭터생성성공");
			} else {
				view.displayFail("캐릭터생성실패");
			}
		} catch (Exception e) {
			view.displayError("생성과정중에러", e);
		} 
		
	}

	public void selectAll() {
		
		
		try {
			List<Crt> cList = mService.selectAll();
			
			if(!cList.isEmpty()) {
				view.selectAll(cList);
			} else {
				view.displayFail("조회결과 없음");
			}
		} catch (Exception e) {
			view.displayError("전체케릭터조회중오류", e);
		}
	}

	public void selectCrt() {
		
		try {
			List<Crt> cList = mService.selectCrt();
			
			if(!cList.isEmpty()) {
				view.selectAll(cList);
			} else {
				view.displayFail("조회결과 없음");
			}
		} catch (Exception e) {
			view.displayError("내케릭터조회중오류", e);
		}
		
	}

	public void deleteCrt() {
		
		String delName = view.delName();
		char check = view.delCheck();
		if(check=='N') return;
		try {
			
			int result = mService.deleteCrt(delName);
			if(result>0) {
				view.displaySuccess("삭제완료");
			} else {
				view.displayFail("니꺼 아니걸랑?아님 없거나?");
			}
		} catch (Exception e) {
			view.displayError("삭제중 오류", e);
		}
	}

	public void gameOver() {
		mService.gameOver();
		
	}


	
}

model.service.MemberService

package model.service;
import static common.JDBCTemplate.*;
import java.sql.Connection;
import java.util.List;

import model.dao.MemberDAO;
import model.vo.Crt;
import model.vo.Member;

public class MemberService {

	public int insertMember(Member member) throws Exception {

		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		int result = memberDAO.insertMember(conn, member);


		if(result > 0) {
			commit(conn);
		} else { // 삽입 실패 시
			rollback(conn);
		}

		return result;
	}
	
	public Member login(Member inputLogin) throws Exception{
		
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		
		return memberDAO.login(conn, inputLogin);
	}

	public int updateMember(Member member) throws Exception{
		
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		int result = memberDAO.updateMember(conn, member);
		
		if(result > 0) {
			commit(conn);
		} else {
			rollback(conn);
		}
		return result;
	}

	public Member selectMember() throws Exception{
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		
		return memberDAO.selectMember(conn);
	}
	
	public int insertCrt(Crt crt) throws Exception{
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		
		int result = memberDAO.insertCrt(conn, crt);
		if(result > 0) {
			commit(conn);
		} else {
			rollback(conn);
		}
		return result;
	}

	public List<Crt> selectAll() throws Exception {
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		return memberDAO.selectAll(conn);
	}

	public List<Crt> selectCrt() throws Exception{
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		return memberDAO.selectCrt(conn);
	}

	public int deleteCrt(String delName) throws Exception{
		Connection conn = getConnection();
		MemberDAO memberDAO = new MemberDAO();
		int result = memberDAO.deleteCrt(conn, delName);
		
		if(result > 0) {
			commit(conn);
		} else {
			rollback(conn);
		}
		return result;
	}

	public void gameOver() {
		// TODO Auto-generated method stub
		close(getConnection());
	}
}

model.dao.MemberDAO

package model.dao;

import static common.JDBCTemplate.*;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.naming.PartialResultException;
import javax.naming.spi.DirStateFactory.Result;

import controller.MemberController;
import model.vo.Crt;
import model.vo.Member;

public class MemberDAO {
	
	private Properties prop = null;
	public MemberDAO() throws Exception {
		prop = new Properties();
		prop.load(new FileReader("query.properties"));
	}
	
	public int insertMember(Connection conn, Member member) 
			throws Exception{
		PreparedStatement pstmt = null;
		int result = 0;
		String query = prop.getProperty("insertMember");
		
		try {
			pstmt = conn.prepareStatement(query);
			
			pstmt.setString(1, member.getMemberId());
			pstmt.setString(2, member.getMemberPwd());
			pstmt.setString(3, member.getMemberName());
			pstmt.setString(4, member.getGender()+"");
			pstmt.setString(5, member.getEmail());
			pstmt.setInt(6, member.getAge());
			
			result = pstmt.executeUpdate();
			
		} finally {
			close(pstmt);
		}
		return result;
	}

	public Member login(Connection conn, Member inputLogin) 
			throws Exception{
		
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Member member = null;
		
		String query = prop.getProperty("loginMember");
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, inputLogin.getMemberId());
			pstmt.setString(2, inputLogin.getMemberPwd());
			rset = pstmt.executeQuery();
			
			if(rset.next()) {
				String memberId = rset.getString("MEMBER_ID");
				String memberPwd = rset.getString("MEMBER_PWD");
				String memberName = rset.getString("MEMBER_NAME");
				char gender = rset.getString("GENDER").charAt(0);
				String email = rset.getString("EMAIL");
				int age = rset.getInt("AGE");
				Date enrollDate = rset.getDate("ENROLL_DATE");
				
				member = new Member(memberId, memberPwd, memberName, gender, email, age, enrollDate);
			}
		} finally {
			close(pstmt);
			close(rset);
			
		}
		return member;
	}

	public int updateMember(Connection conn, Member member)
			throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int result = 0;
		String query = prop.getProperty("updateMember");
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, member.getMemberName());
			pstmt.setString(2, member.getGender()+"");
			pstmt.setString(3, member.getEmail());
			pstmt.setInt(4, member.getAge());
			pstmt.setString(5, MemberController.LoginMember.getMemberId());
			
			result = pstmt.executeUpdate();
			
		} finally {
			close(rset);
			close(pstmt);
		}
		
		return result;
	}

	public Member selectMember(Connection conn) throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Member member = null;
		String query = prop.getProperty("selectMember");
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, MemberController.LoginMember.getMemberId());
			rset = pstmt.executeQuery();
			if(rset.next()) {
				String memberId = rset.getString("MEMBER_ID");
				String memberName = rset.getString("MEMBER_NAME");
				char gender = rset.getString("GENDER").charAt(0);
				String email = rset.getString("EMAIL");
				int age = rset.getInt("AGE");
				Date enrollDate = rset.getDate("ENROLL_DATE");
				
				member = new Member(memberId, memberName, gender, email, age, enrollDate);
				
			}
		} finally {
			close(rset);
			close(pstmt);
		}
		
		return member;
	}

	public int insertCrt(Connection conn, Crt crt) throws Exception{
		PreparedStatement pstmt = null;
		int result = 0;
		String query = prop.getProperty("insertCrt");
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, crt.getCrtName());
			pstmt.setString(2, crt.getCrtClass());
			pstmt.setString(3, MemberController.LoginMember.getMemberId());
			result = pstmt.executeUpdate();
			
		} finally {
			
			close(pstmt);
		}
		return result;
	}

	public List<Crt> selectAll(Connection conn)	throws Exception {
		Statement stmt = null;
		ResultSet rset = null;
		List<Crt> cList = null;
		
		String query = prop.getProperty("selectAll");
		
		try {
			stmt = conn.createStatement();
			rset = stmt.executeQuery(query);
			cList = new ArrayList<Crt>();
			Crt crt = null;
			
			while(rset.next()) {
				String crtName = rset.getString("CRT_NAME");
				String crtClass = rset.getString("CRT_CLASS");
				int crtLevel = rset.getInt("CRT_LEVEL");
				String crtOwner = rset.getString("CRT_OWNER");
				
				crt = new Crt(crtName, crtClass, crtLevel, crtOwner);
				
				cList.add(crt);
			}
		} finally {
			close(rset);
			close(stmt);
		}
		return cList;
	}

	public List<Crt> selectCrt(Connection conn) throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<Crt> cList = null;
		
		String query = prop.getProperty("selectCrt");
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, MemberController.LoginMember.getMemberId());
			rset = pstmt.executeQuery();
			cList = new ArrayList<Crt>();
			Crt crt = null;
			
			while(rset.next()) {
				String crtName = rset.getString("CRT_NAME");
				String crtClass = rset.getString("CRT_CLASS");
				int crtLevel = rset.getInt("CRT_LEVEL");
				String crtOwner = rset.getString("CRT_OWNER");
				
				crt = new Crt(crtName, crtClass, crtLevel, crtOwner);
				
				cList.add(crt);
			}
		} finally {
			close(rset);
			close(pstmt);
		}
		return cList;
	}

	public int deleteCrt(Connection conn, String delName)
			throws Exception{
		PreparedStatement pstmt = null;
		int result = 0;
		String query = prop.getProperty("deleteCrt");
		
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, delName);
			pstmt.setString(2, MemberController.LoginMember.getMemberId());
			result = pstmt.executeUpdate();
		} finally {
			close(pstmt);
		}
		return result;
	}

}

query.properties

insertMember=INSERT INTO MEMBER VALUES(?, ?, ?, ?, ?, ?, DEFAULT)
loginMember=SELECT * FROM MEMBER WHERE MEMBER_ID = ? AND MEMBER_PWD = ?
updateMember=UPDATE MEMBER SET MEMBER_NAME = ?, GENDER = ?, EMAIL = ?, AGE = ? WHERE MEMBER_ID = ?
selectMember=SELECT * FROM MEMBER WHERE MEMBER_ID = ?
insertCrt=INSERT INTO CRT VALUES(SEQ_CRT.NEXTVAL, ?, ?, DEFAULT, ?, DEFAULT)
selectAll=SELECT CRT_NAME, CRT_CLASS, CRT_LEVEL, CRT_OWNER FROM CRT WHERE DELETE_YN = 'N'
selectCrt=SELECT CRT_NAME, CRT_CLASS, CRT_LEVEL, CRT_OWNER FROM CRT WHERE DELETE_YN = 'N' AND CRT_OWNER = ?
deleteCrt=UPDATE CRT SET DELETE_YN = 'Y' WHERE CRT_NAME = ? AND CRT_OWNER = ?

 

'study > JDBC' 카테고리의 다른 글

8_JDBC_(에러, 용어정리)  (0) 2019.12.10
6_JDBC_(API,라이브러리,스트링버퍼)  (0) 2019.12.06
5_JDBC_(LIKE 사용시 문자가공)  (0) 2019.12.05
4_JDBC_(Service, Template)  (0) 2019.12.04
3_JDBC_(DML)  (0) 2019.12.03

관련글

8_JDBC_(에러, 용어정리) 6_JDBC_(API,라이브러리,스트링버퍼) 5_JDBC_(LIKE 사용시 문자가공) 4_JDBC_(Service, Template)

티스토리툴바