국비학원 18일차) Oracle과 Java 연동

 

 

JDBC란?
자바와 DB를 연결해주는 표준 규칙(?)

1. driver loading
DB 드라이버 가져오기

2. Connection ( import java.sql.* )
오라클과 자바의 다리 역할, 프로그램과 DB를 연결해주는 것

3. Statement (자동차) 생성해서 씀
sql문을 담아서 DB로 직접적으로 전달
Connection 하위에 연결
PreparedStatement를 많이 씀(?,?,?,?) 불러와서 씀

4.Query 실행
executeQuery - SECLT
executeUpdate - INSERT, DELETE, UPDATE

5.결과

6.Connection 종료

 

 

DB 클래스

 

package step5;

import java.sql.*;
import java.util.Scanner;

public class DB {

	public Connection conn = null;
	public PreparedStatement pstmt = null;
	public ResultSet rs = null;	
	public String driver;
	public String url;
	public String uid = "hr";
	public String upw = "hr";

	public DB() { //driver랑 url 주소 미리 생성자로 초기화
		driver = "oracle.jdbc.driver.OracleDriver";
		url = "jdbc:oracle:thin:@localhost:1521";
	}

	public void con() { //기본 커넥션하기 위한 준비
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,uid,upw);
		} catch(Exception e) {
			System.out.println(e);
		}
	}

	public void discon() { //커넥션 됐던 거 끄기
		try {
			if(rs!=null) {
				rs.close();
			}
			pstmt.close();
			conn.close();

		} catch(Exception e) {
			System.out.println(e);
		}
	}

	public void enroll() { // INSERT 추가

		con();

		Scanner sc = new Scanner(System.in);
		System.out.println("이메일 : ");
		String email = sc.next();

		String sql = "INSERT INTO A_"
				+ " VALUES(A_SEQ.NEXTVAL, ?)";

		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			pstmt.executeUpdate();

			discon();

		} catch(Exception e) {
			System.out.println(e);
		}
	}

	public void print() { // SELECT 출력
		con();
		String sql = "SELECT * FROM A_";
		try {

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getInt(1)+" "+rs.getString(2));
			}
			
			discon();

		} catch(Exception e) {
			System.out.println(e);
		}
	}
	
	public boolean search(int num) { // SELECT WHERE 검색
		
		con();
		String sql = "SELECT * FROM A_ WHERE num = ?";
		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				System.out.println(rs.getString(2));
				return true;
			}

		} catch(Exception e) {
			System.out.println(e);
		}
		discon();
		return false;
	}
	
	public void update() { // UPDATE 수정
		
		con();
		Scanner sc = new Scanner(System.in);
		System.out.println("수정할 사람의 번호 입력 : ");
		int j = sc.nextInt();
		System.out.println("수정할 사람의 이메일 입력 : ");
		String email = sc.next();

		String sql = "UPDATE A_"
				+ " SET EMAIL = ?"
				+ " WHERE NUM = ?";
		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, email);
			pstmt.setInt(2, j);
			pstmt.executeUpdate();
			
			discon();

		} catch(Exception e) {
			System.out.println(e);
		}
		
	}
	
	public void delete() { // DELETE 삭제
		
		con();
		Scanner sc = new Scanner(System.in);
		System.out.println("삭제할 사람의 번호 입력 : ");
		int j = sc.nextInt();

		String sql = "DELETE A_"
				+ " WHERE NUM = ?";
		try {

			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, j);
			pstmt.executeUpdate();
			
			discon();

		} catch(Exception e) {
			System.out.println(e);
		}
		
	}


}

 

 

메인

package step5;

import java.util.Scanner;

public class DBMain {

	public static void main(String[] args) {
		
		DB d = new DB();
		Scanner sc = new Scanner(System.in);
		boolean flag = true;
		int i = 0;
		
		while(flag) {
			System.out.println("1.등록  2.명단확인 3.검색  4.수정  5.삭제  6.종료");
			i=sc.nextInt();
			
			switch(i) {
			case 1: // INSERT 등록
				d.enroll();
				break;
			case 2: // SELECT 출력
				d.print();
				break;
			case 3: // SELECT WHERE 검색
				int j;
				System.out.println("검색할 번호를 입력 : ");
				j = sc.nextInt();
				d.search(j);
				break;
			case 4: // UPDATE 수정
				d.update();
				break;
			case 5: // DELECT 삭제
				d.delete();
				break;
			case 6:
				flag=false;
				break;
				default :
					System.out.println("잘못 입력");
			}
			
		}
		
	}

}