<이공계전문기술연수> 7. JDBC(Java Database Connectivity)

@JDBC

-> Java Database Connectivity 의 약자
-> 자바언어에서 DataBase에 접근 할 수 있게 해주는 Programming API
-> 각 DMBS 제조사에서 jar 파일을 구현해서 제공
-> java.jql 패키지에서 관리


@OJDBC
-> 오라클에서 제공하는 오라클 DB와 자바가 연결하기 위한 라이브러리
오라클버전별로 파일이름이 다름
-oracle 7 : classes111.zip
-oracle 8 : classes12.zip
-oracle 9i : ojdbc14.jar
-oracle 10g : ojdbc14.jar
-oracle 11g : ojdbc6.jar


C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib -> 여기서 가져옴
C:\Program Files\Java\jdk1.8.0_144\jre\lib\ext -> 여기에 넣어야됨 API

기본 문제셋 인코딩 방식 통일
-> Java 에서 사용하는 문자셋은 ms949
-> oracle에서 사용하는 문자셋은 utf-8
-> UTF-8로 통합


1. windows -> preferences - > general -> worksapce -> other : UTF-8
2. windows -> preferences - > general -> editors -> spelling -> other : UTF-8
3. windows -> preferences - > JSON -> JSON files -> encoding : UTF-8
4. windows -> preferences - > web -> CSS files -> encoding : UTF-8
5. windows -> preferences - > web -> HTML files -> encdoing : UTF-8
6. windows -> preferences - > web -> jsp files -> encdoing : UTF-8
7. windows -> preferences - > XML files -> encdoing : UTF-8  
8. 프로젝트 만들고 - > properties -> libraries -> 등록

 

 

JDBC 사용 객체

## DriverManeger ##
-> JDBC 드라이버를 통해서 커넥션을 만드는 역할

## Connection ##
-> 특정 데이터베이스와 연결된 커넥션

## Statement ##
-> Connection 객체에 의해 프로그램에 리턴되는 객체
-> Query 문을 수행하는 역할 (Query문을 String 객체에 담아서 전달)

## PreparedStatement ##
-> Statement를 상속해서 만든 객체

## ResultSet ##
-> Select문 질의 성공 시 리턴되는 데이터를 담는 객체
-> cursor(커서)를 가지고 특정 행 대한 참조를 조작
 내가 바라보고 있는 행의 위치>

 

## PreparedStatment ## Statement 
-> Connection 객체의 preparedStatement() 메소드를 사용하여 객체를 생성 
-> SQL 구문을 미리 컴파일하고, 실행시간동안 인수값을 위한 공간을 확보 할 수 있다. 
-> (변수자리) 
-> 각각의 인수에 대해 위치홀더(?)를 사용하여 SQL문장을 정의 

SELECT * FROM MEMBER WHERE MEMBER_ID = 'USER01'; 
SELECT * FROM MEMBER WHERE MEMBER_ID = ? 

setString() 메소드로 값을 셋팅해서 사용 
setString(1, 'USER01'); 

 

@ 트랜잭션
-> 한번에 처리되어야 할 최소 작업단위

ex1) 1개의 트랜잭션에 DML 3개가 존재할때

 

Controller
Connection 객체 생성
1. DAO 호출 시 Connection 객체를 매개변수로 전달
2. DAO 호출 시 Connection 객체를 매개변수로 전달

 

5. 리턴받은 데이터를 확인하면 commit , rollback 수행
6. Connection 객체 소멸
7. View를 이용하여 결과 출력

DAO

3. 매개변수로 받은 Connection 객체를 사용해서 DML 수행
4. 결과값 리턴

 

<<실습>>

Eclipse package

(1) src.org.kh.model.vo

package org.kh.medel.vo;

import java.sql.Date;

public class Member {
	private String memberId;
	private String memberPw;
	private String memberName;
	private String email;
	private int age;
	private String addr;
	private String gender;
	private Date enrollDate;
	
	public Member() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	public Member(String memberId, String memberPw, String memberName, String email, int age, String addr,
			String gender, Date enrollDate) {
		super();
		this.memberId = memberId;
		this.memberPw = memberPw;
		this.memberName = memberName;
		this.email = email;
		this.age = age;
		this.addr = addr;
		this.gender = gender;
		this.enrollDate = enrollDate;
	}
	public Member(String memberId, String memberPw, String memberName, String email, int age, String addr,
			String gender) {
		super();
		this.memberId = memberId;
		this.memberPw = memberPw;
		this.memberName = memberName;
		this.email = email;
		this.age = age;
		this.addr = addr;
		this.gender = gender;
		
	}
	public String getMemberId() {
		return memberId;
	}
	public void setMemberId(String memberId) {
		this.memberId = memberId;
	}
	public String getMemberPw() {
		return memberPw;
	}
	public void setMemberPw(String memberPw) {
		this.memberPw = memberPw;
	}
	public String getMemberName() {
		return memberName;
	}
	public void setMemberName(String memberName) {
		this.memberName = memberName;
	}
	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 String getAddr() {
		return addr;
	}
	public void setAddr(String addr) {
		this.addr = addr;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public Date getEnrollDate() {
		return enrollDate;
	}
	public void setEnrollDate(Date enrollDate) {
		this.enrollDate = enrollDate;
	}
	
	@Override
	public String toString() {
		return memberId+"\t"+memberName+"\t"+email+"\t"+age+"\t"+addr+"\t"+
				gender+"\t"+enrollDate;
	}
	
	
	
}

(2) src.org.kh.controller

package org.kh.controller;

import java.util.ArrayList;

import org.kh.medel.dao.MemberDao;
import org.kh.medel.vo.Member;
import org.kh.view.MemberView;

public class MemberController {
	MemberDao md = new MemberDao();
	MemberView mv = new MemberView();
	
	public void updateMember(String searchId) {
		Member m = md.searchId(searchId);
		if(m==null) {
			mv.printMsg("회원정보가 없습니다.");
			return;
		}
		Member updateMember = mv.updateMember();
		updateMember.setMemberId(searchId);
		int result = md.updateMember(updateMember);
		
		if(result > 0) {
			mv.printMsg("삭제성공");
		}else {
			mv.printMsg("회원정보가 없습니다.");
		}
	}
	
	public void printAll(){
		ArrayList<Member> list = md.printAll();
		for(Member m : list) {
			mv.printMsg(m.toString());
		}
	}
	
	public void searchId(String searchId) {
		Member m = md.searchId(searchId);
		
		if(m != null) {
			mv.printMsg(m.toString());
		}else {
			mv.printMsg("회원 정보가 없습니다.");
		}
	}
	
	public void searchName(String name){
		ArrayList<Member> list = md.searchName(name);
		if(!list.isEmpty())
		{
			for(Member m : list) {
				mv.printMsg(m.toString());
			}
		}else {
			mv.printMsg("회원정보가 없습니다.");
		}
	}
	
	public void insertMember(String insertId) {
		Member m = md.searchId(insertId);
		if(m != null) {
			mv.printMsg("이미 존재하는 회원입니다.");
			return;
		}
		
		Member m2 = mv.insertMember(insertId);
		int result = md.insertMember(m2);
		
		if(result > 0) {
			mv.printMsg("회원가입 성공");
		}else {
			mv.printMsg("회원가입 실패");
		}
	}
	
//	public void updateMember(String[] ar) {
//		int result = md.updateMember(ar);
//		if(result > 0) {
//			mv.printMsg("삭제성공");
//		}else {
//			mv.printMsg("회원정보가 없습니다.");
//		}
//	}
//	
	public void deleteMember(String id) {
		int result = md.deleteMember(id);
		if(result > 0) {
			mv.printMsg("삭제성공");
		}else {
			mv.printMsg("회원정보가 없습니다.");
		}
	}
}

(3) src.org.kh.model.dao

package org.kh.medel.dao;

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

import org.kh.medel.vo.Member;

public class MemberDao {
	public ArrayList<Member> printAll() {
		// 조회 결과를 담을 list객체 생성
		ArrayList<Member> list = new ArrayList<Member>();
		// Connection 객체
		Connection conn = null;
		// SQL구문 사용 객체
		Statement stmt = null;
		// select 결과 저장 객체
		ResultSet rset = null;
		// 전송할 쿼리문
		String query = "select * from member";

		try {
			// 1. 사용할 DB에 대한 드라이버 등록(클래스 등록)
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 2. 등록된 클래스를 이용해서 DB연결
			// -> 실패하면 NULL, -> 성공시 Connection 객체생성
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "jdbc", "1234");
			// 3. 쿼리문을 실행할 Statement 객체를 생성
			stmt = conn.createStatement();
			// 4. 쿼리문 전송하고 실행결과 받기
			rset = stmt.executeQuery(query);
			while (rset.next()) {
				String memberId = rset.getString("member_id");
				String memberPw = rset.getString("member_pw");
				String memberName = rset.getString("member_name");
				String email = rset.getString("email");
				int age = rset.getInt("age");
				String addr = rset.getString("addr");
				String gender = rset.getString("gender");
				Date enrollDate = rset.getDate("enroll_date");
				Member m = new Member(memberId, memberPw, memberName, email, age, addr, gender, enrollDate);
				list.add(m);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rset.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	return list;
	}
	
	public Member searchId(String searchId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Member m = null;
		String query = "select * from member where member_id =?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","jdbc","1234");
			
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, searchId);
			rset = pstmt.executeQuery();
			if(rset.next()) {
				m = new Member();
				m.setMemberId(rset.getString("member_id"));
				m.setMemberPw(rset.getString("member_pw"));
				m.setMemberName(rset.getString("member_name"));
				m.setAddr(rset.getString("addr"));
				m.setAge(rset.getInt("age"));
				m.setEmail(rset.getString("email"));
				m.setEnrollDate(rset.getDate("enroll_date"));
				m.setGender(rset.getString("gender"));
				
			}
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return m;
		
	}
	
	public ArrayList<Member> searchName(String name){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		ArrayList<Member> list = new ArrayList<Member>();
		Member m = null;
		String query = "select * from member where member_name like ?";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, "%"+name+"%");
			rset = pstmt.executeQuery();
			
			while(rset.next()) {
				m = new Member();
				m.setMemberId(rset.getString("member_id"));
				m.setMemberPw(rset.getString("member_pw"));
				m.setMemberName(rset.getString("member_name"));
				m.setAddr(rset.getString("addr"));
				m.setAge(rset.getInt("age"));
				m.setEmail(rset.getString("email"));
				m.setEnrollDate(rset.getDate("enroll_date"));
				m.setGender(rset.getString("gender"));
				
				list.add(m);
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				rset.close();
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public int insertMember(Member m) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String query = "insert into member values(?,?,?,?,?,?,?,sysdate)";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","jdbc","1234");
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, m.getMemberId());
			pstmt.setString(2, m.getMemberPw());
			pstmt.setString(3, m.getMemberName());
			pstmt.setString(4, m.getEmail());
			pstmt.setInt(5, m.getAge());
			pstmt.setString(6, m.getAddr());
			pstmt.setString(7, m.getGender());
			
//			pstmt.setString(8, "sysdate");
			
			result = pstmt.executeUpdate();
			
			if(result>0) {
				conn.commit();
			}else {
				conn.rollback();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return result;
		
	}
	
	public int updateMember(Member updateMember) {
		
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		//update member set member_name = 'member_name', email = 'email', addr = 'addr', 
		//gender ='gender' where memberId = 'memberId';
		
//		String query = "update member set member_name = '"+updateMember.getMemberName()+"', "
//						+"email = '"+updateMember.getEmail()+"', addr = '"+updateMember.getAddr()+"', gender ='"+updateMember.getGender()+"' where member_id = '"+updateMember.getMemberId()+"'";
				
		String query = "update member set member_name = ?, email = ?, addr = ?, gender = ? where member_id = ?";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","jdbc","1234");
			
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1,updateMember.getMemberName());
			pstmt.setString(2,updateMember.getEmail());
			pstmt.setString(3,updateMember.getAddr());
			pstmt.setString(4,updateMember.getGender());
			pstmt.setString(5,updateMember.getMemberId());
			result = pstmt.executeUpdate();
			if(result>0) {
				conn.commit();
			}else {
				conn.rollback();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return result;
		
		
		
	}
	
	
	public int deleteMember(String id) {
		int result = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		//update member set member_name = 'member_name', email = 'email', addr = 'addr', 
		//gender ='gender' where memberId = 'memberId';
		
		String query = "delete from member where member_id = ?";
				
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","jdbc","1234");
			
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1,id);
			result = pstmt.executeUpdate();
			if(result>0) {
				conn.commit();
			}else {
				conn.rollback();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return result;
		
	}
	
	
	
	
	
	
	
	
	
	
	
}

(4) src.org.kh.view

package org.kh.view;

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

import org.kh.controller.MemberController;
import org.kh.medel.vo.Member;

public class MemberView {
	Scanner sc = new Scanner(System.in);
	public void mainMenu() {
		MemberController mc = new MemberController();
		while(true) {
			System.out.println("=== 회원 관리 프로그램 ===");
			System.out.println("1. 회원 정보 전체 조회"); // select
			System.out.println("2. 회원 아이디 조회");	// select
			System.out.println("3. 회원 이름으로 검색"); // select
			System.out.println("4. 회원가입");		// insert
			System.out.println("5. 회원 정보 변경");	// update
			System.out.println("6. 회원 탈퇴");		// delete
			System.out.println("0. 프로그램 종료");
			System.out.println("7. 테스트"); 
			System.out.print("선택 > ");
			int choice = sc.nextInt();
			
			switch(choice) {
			case 1: 
				mc.printAll();
				break;
			case 2: 
				mc.searchId(searchId());
				break;
			case 3: 
				mc.searchName(searchName());
				break;
			case 4: 
				mc.insertMember(insertId());
				break;
			case 5:
				mc.updateMember(insertId());
				break;
			case 6: 
				mc.deleteMember(deleteMember());
				break;
			case 7:
				break;
			case 0:
				System.out.print("정말로 종료하시겠습니까?(y/n) : ");
				String end = sc.next();
				if(end.toLowerCase().charAt(0) == 'y') {
					return;
				}
				break;
			}
		}
	}

//	public void printAll() {
//		mc.printAll();
//	}
	public String insertId() {
		System.out.print("아이디를 입력하세요 : ");
		return sc.next();
		
	}
	public Member updateMember() {
		Member m = new Member();
		System.out.print("수정 할 이름 입력 : ");
		String memberName = sc.next();
		System.out.print("수정 할 이메일 입력 : ");
		String email = sc.next();
		System.out.print("수정 할 주소 입력 : ");
		String addr = sc.next();
		System.out.print("수정 할 성별 입력 : ");
		String gender = sc.next();
		m.setMemberName(memberName);
		m.setEmail(email);
		m.setAddr(addr);
		m.setGender(gender);
		return m;	
	}
	
	public String searchId() {
		System.out.print("조회할 아이디를 입력하세요 : ");
		String searchId = sc.next();
		return searchId;
		
	}
	
	public String searchName() {
		System.out.print("조회할 이름을 입력하세요 : ");
		String name = sc.next();
		return name;
		
	}
	public Member insertMember(String insertId) {
		String memberId = insertId;
		System.out.print("pwd 입력 : ");
		String memberPw = sc.next();
		System.out.print("이름 입력 : ");
		String memberName = sc.next();
		System.out.print("이메일 입력 : ");
		String email = sc.next();
		System.out.print("나이 입력 : ");
		int age = sc.nextInt();
		System.out.print("주소 입력 : ");
		String addr = sc.next();
		System.out.print("성별 입력(남자/여자) : ");
		String gender = sc.next();
		Member m = new Member( memberId, memberPw, memberName, email, age, addr,gender, null);
		return m;
	}
//	public String[] updateMember() {
//		String[] ar  = new String[5];
//		System.out.print("ID 입력 : ");
//		ar[0] = sc.next();
//		System.out.print("수정 할 이름 입력 : ");
//		ar[1] = sc.next();
//		System.out.print("수정 할 이메일 입력 : ");
//		ar[2] = sc.next();
//		System.out.print("수정 할 주소 입력 : ");
//		ar[3] = sc.next();
//		System.out.print("수정 할 성별 입력 : ");
//		ar[4] = sc.next();
//		
//		return ar;
//	}
	
	public String deleteMember(){
		System.out.print("삭제 할 회원 ID 입력: ");
		String id = sc.next();
		
		return id;
	}
	
	public void printMsg(String msg) {
		System.out.println(msg);
	}
}

(5) src.org.kh.run

package org.kh.run;

import org.kh.view.MemberView;

public class MemberRun {

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