JDBC (Java DataBase Connectivity)
- Java에서 DB에 연결할 수 있게 해주는 Java API (Java에서 제공하는 코드)
-> java.sql 패키지에 존재함
Java 코드를 이용해 EMPLOYEE 테이블에서 사번, 이름, 부서코드, 직급코드, 급여, 입사일 조회 후 이클립스 콘솔에 출력!
1. JDBC 객체 참조용 변수 선언
Connection conn = null; // java.sq.Connection // 특정 DBMS와 연결하기 위한 정보를 저장한 객체 // == DBeaver에서 사용하는 DB연결과 같은 역할의 객체 // (DB 서버 주소, 포트번호, DB 이름, 계정명, 비밀번호) Statement stmt = null; // java.sql.Statement // -1) SQL을 Java -> DB에 전달 // -2) DB에서 SQL 수행한 결과를 반환 받아옴 (DB -> Java) ResultSet rs = null; // java.sql.ResultSet // - SELECT 조회 결과를 저장하는 객체
2. DriverManager 객체를 이용해서 Connection 객체 생성하기
java.sql.DriverManager // - DB 연결 정보와 JDBC 드라이버를 이용해서 // 원하는 DB와 연결할 수 있는 Connection 객체를 생성하는 객체
2-1) Oracle JDBC Driver 객체를 메모리에 로드하기
try{ Class.forName("oracle.jdbc.driver.OracleDriver"); // Class.forName("패키지명 + 클래스명"); // - 해당 클래스를 읽어 메모리에 적재 // -> JVM이 프로그램 동작에 사용할 객체를 생성하는 구문 // oracle.jdbc.driver.OracleDriver // - Oracle DBMS 연결 시 필요한 코드가 담겨있는 클래스 // ojdbc 라이브러리 파일 내에 존재함. // Oracle에서 만들어서 제공하는 클래스
2-2) DB 연결 정보 작성
String type = "jdbc:oracle:thin:@"; // 드라이버의 종류 String host = "localhost"; // DB 서버 컴퓨터의 IP 또는 도메인주소 // localhost == 현재 컴퓨터 String port = ":1521"; // 프로그램 연결을 위한 port 번호 String dbName = ":XE"; // DBSM 이름 (XE == eXpress Edition) // 합치면 jdbc:oracle:thin:@localhost:1521:XE String userName = "kh" // 사용자 계정명 String password = "kh1234"; // 계정 비밀번호
2-3) DB연결 정보와 DriverManager를 이용해서 Connection 객체 생성
conn = DriverManager.getConnection(type+host+port+dbNAme, userName, password); // Connection 객체가 잘 생성되었는지 확인 (객체 주소 반환) System.out.println(conn); // oracle.jdbc.driver.T4CConnection@6973bf95
3. SQL 작성
- 주의사항!
JDBC 코드에서 SQL 작성 시 세미콜론(;) 작성하면 안된다!!
-> "sql 명령어가 올바르게 종료되지 않았습니다" 예외 발생
// EMPLOYEE 테이블에서 // 사번, 이름, 부서코드, 직급코드, 급여, 입사일 조회 String sql = "SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY, HIRE_DATE " // < 공백 한 칸 + "FROM EMPLOYEE";
4. Statement 객체 생성
- Connection 이라는 통로를 다니는 셔틀버스
stmt = conn.createStatement(); // 연결된 DB에 SQL을 전달하고 결과를 반환 받을 역할을 할 Statement 객체를 생성해둠
5. Statement 객체를 이용해서 SQL 수행 후 결과 반환 받기
rs = stmt.executeQuery(sql); 1) ResultSet Statement.executeQuery(sql); -> sql이 SELECT 문일 때 결과로 ResultSet 객체 반환 2) int Statement.executeUpdate(sql); -> sql이 DML(INSERT, UPDATE, DELETE)일 때 실행 메서드 -> 결과로 int 반환 (삽입, 수정, 삭제된 행의 개수)
6. 조회 결과가 담겨있는 ResultSet을 커서(cursor)를 이용해서 1행 씩 접근해 각 행에 작성된 컬럼 값 얻어오기
// boolean ResultSet.next() //-> 커서를 다음 행으로 이동 시킨 후 // 이동된 행에 값이 있으면 true, 없으면 false 반환 // 맨 처음 호출 시 1행부터 시작 while(rs.next()) { // 자료형 ResultSet.get자료형(컬럼명 | 순서); // - 현재 행에서 지정된 컬럼의 값을 얻어와 반환 // -> 지정된 자료형 형태로 값이 반환됨 // (자료형을 잘못 지정하면 예외 발생) // [Java] [DB] // String CHAR, VARCHAR2 // int, long NUMBER (정수만 저장된 컬럼) // float, double NUMBER (정수 + 실수) // java.sql.Date DATE String empId = rs.getString("EMP_ID"); String empName = rs.getString("EMP_NAME"); String deptCode = rs.getString("DEPT_CODE"); String jobCode = rs.gotString("JOB_CODE"); int salary = rs.getInt("SALARY"); Date hireDate = rs.getDate("HIRE_DATE"); System.out.printf("사번 : %s / 이름 : %s / 부서코드 : %s / 직급코드 : %s / " + "급여 : %d / 입사일 : %s \n", empId, empName, deptCode, jobCode, salary, hireDate.toString()); } // while 문 종료 } // try 문 종료 catch (ClassNotFoundException e) { System.out.println("해당 Class를 찾을 수 없습니다."); e.printStackTrace(); } catch (SQLException e) { // SQLException : DB 연결과 관련된 모든 예외의 최상위 부모 e. printStackTrace(); } finally {
7. 사용완료 된 JDBC 객체 자원 반환 (close)
- 자원반환을 하지 않으면 DB와 연결된 Connection이 그대로 남아 있어서
다른 클라이언트가 (ex. Java프로그램) 추가적으로 연결되지 못하는 문제가 발생될 수 있다.
-> DBMS는 최대 Connection 수 개수 제한을 하고 있기 때문에!!
finally { try{ // 만들어진 역순으로 close 수행하는 것을 권장 if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } } }
JDBC 2
1과 중복 주석은 제외, 그룹 합쳐서 안에서 단원 나눌거임!!
입력 받은 급여보다 초과해서 받는 사원의 사번, 이름, 급여 조회 ## 1) JDBC 객체 참조용 변수 선언 Connection conn = null; // DB 연결 정보 저장 객체 Statement stmt = null; // SQL 수행, 결과 반환용 객체 ResultSet rs = null; // SELECT 수행 결과 저장 객체 Scanner sc = null; // 키보드 입력용 객체 try{ ## 2) DriverManager 객체를 이용해서 Connection 객체 생성 ## 2-1) Oracle JDBC Driver 객체 메모리 로드 Class.forName("oracle.jdbc.driver.OracleDriver"); ## 2-2) DB 연결 정보 작성 String type = "jdbc:oracle:thin:@"; String host = "localhost"; String port = ":1521"; String dbName = ":XE"; String userName = "kh"; String password = "kh1234"; ## 2-3) DB 연결 정보와 DriverManager를 이용해서 Connection 생성 conn = DriverManager.getConnection(type+host+port+dbName, userName, password); ## 3) SQL 작성 // 입력 받은 급여 -> Scanner 필요 // int input 사용 sc = new Scanner(System.in); System.out.print("급여 입력 : "); int input = sc.nextInt(); String sql = "SELECT EMP_ID, EMP_NAME, SALARY " + "FROM EMPLOYEE " + "WHERE SALARY > " + input; ## 4) Statement 객체 생성 stmt = conn.createStatement(); ## 5) Statement 객체를 이용하여 SQL 수행 후 결과 반환 받기 rs = stmt.executeQuery(sql); ## 6) 조회 결과가 담겨있는 ResultSet을 커서를 이용해 1행 씩 접근해 각 행에 작성된 컬럼값 얻어오기 -> while 반복문으로 데이터 꺼내어 출력 while(rs.next()) { String empId = rs.getString("EMP_ID"); String empName = rs.getString("EMP_NAME"); int salary = rs.getInt("SALARY"); System.out.printf("%s / %s / %d원 \n", empId, empName, salary); } } catch (Exception e) { // 최상위 예외인 Exception을 이용해서 모든 예외를 처리 // -> 다형성 업캐스팅 적용 e.printStackTrace(); } finally { ## 7) 사용 완료된 JDBC 객체 자원 반환 (close) try { if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); if(sc != null) sc.close(); } catch (Exception e) { e.printStackTrace(); } } } }
JDBC 3
1,2와 중복 제외. 새로운 개념만 주석
입력 받은 최소 급여 이상 입력 받은 최대 급여 이하를 받는 사원의 사번, 이름, 급여를 급여 내림차순으로 조회 -> 이클립스 콘솔 출력 [실행화면] 최소 급여 : 1000000 최대 급여 : 3000000 사번 / 이름 / 급여 Connection conn = null; Statement stmt = null; ResultSet rs = null; Scanner sc = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); String type = "jdbc:oracle:thin:@"; String host = "localhost"; String port = ":1521"; String dbName = ":XE"; String userName = "kh"; String password = "kh1234"; conn = DriverManager.getConnection(type+host+port+dbName, userName, password); sc = new Scanner(System.in); System.out.print("최소 급여 : "); int min = sc.nextInt(); System.out.print("최대 급여 : "); int max = sc.nextInt(); // Java 13부터 지원하는 Text Block (""") 문법 // 자동으로 개행 포함 + 문자열 연결이 처리됨 // 기존처럼 + 연산자로 문자열을 연결할 필요가 없음 String sql = """ SELECT EMP_ID, EMP_NAME, SALARY FROM EMPLOYEE WHERE SALARY BETWEEN """ + min + " AND " + max + "ORDER BY SALARY DESC"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()) { String empId = rs.getString("EMP_ID"); String empName = rs.getString("EMP_NAME"); int salary = rs.getInt("SALARY"); System.out.printf("%s / %s / %d \n", empId, empName, salary); } } catch(Exception e) { e.printStackTrace(); } finally { try { if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); if(sc != null) sc.close(); } catch (Exception e) { e.printStackTrace(); } } } }
JDBC 4
1,2,3과 중복제외. 새로운 개념 주석
부서명을 입력받아 해당 부서에 근무하는 사원의 사번, 이름, 부서명, 직급명을 직급코드 오름차순으로 조회 [실행화면] 부서명 입력 : 총무부 200 / 선동일 / 총무부 / 대표 202 / 노옹철 / 총무부 / 부사장 201 / 송종기 / 총무부 / 부사장 부서명 입력 : 개발팀 일치하는 부서가 없습니다! hint : SQL 에서 문자열은 양쪽 '' (홑따옴표) 필요 ex) 총무부 입력 -> '총무부' Connection conn = null; Statement stmt = null; ResultSet rs = null; Scanner sc = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); String type = "jdbc:oracle:thin:@"; String host = "localhost"; String port = ":1521"; String dbName = ":XE"; String userName = "kh"; String password = "kh1234"; conn = DriverManager.getConnection(type+host+port+dbName, userName, password); sc = new Scanner(System.in); System.out.print("부서명 입력 : "); String dept = sc.nextLine(); String sql = """ SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME FROM EMPLOYEE JOIN JOB ON(EMPLOYEE.JOB_CODE = JOB.JOB_CODE) LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) WHERE DEPT_TITLE = '""" + dept + "' ORDER BY EMPLOYEE.JOB_CODE"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); 여기서 2가지 이용법!! 1) flag 이용법 boolean flag = true; while(rs.next()) { String empId = rs.getString("EMP_ID"); String empName = rs.getString("EMP_NAME"); String deptTitle = rs.getString("DEPT_TITLE"); String jobName = rs.getString("JOB_NAME"); System.out.printf("%s / %s / %s / %s \n", empId, empName, deptTitle, jobName); flag = false; } if(flag){ System.out.println("일치하는 부서가 없습니다!"); } 2) return 이용법 if(!rs.next()) { // 조회 결과가 없다면 System.out.println("일치하는 부서가 없습니다!"); return; // 밑에 있는 일반 코드는 수행 안 하지만 finally는 수행을 하고 종료시킴 } do { String empId = rs.getString("EMP_ID"); String empName = rs.getString("EMP_NAME"); String deptTitle = rs.getString("DEPT_TITLE"); String jobName = rs.getString("JOB_NAME"); System.out.printf("%s / %s / %s / %s \n", empId, empName, deptTitle, jobName); } while(rs.next()); } catch (Exception e) { e.printStackTrace(); } finally { try { if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(conn != null) conn.close(); if(sc != null) sc.close(); } catch (Exception e) { e.printStackTrace(); } } } }
JDBC 5
- java.sql.PreparedStatement 란?
- SQL 중간에 ?(위치홀더, placeholder)를 작성하여
? 자리에 java 값을 대입할 준비가 되어있는 Statement - Statement의 자식임
- PreparedStatement, Statement 는 SELECT / DML 둘 다 가능
장점
1. SQL 작성이 간단해짐
2. ? 에 값 대입 시 자료형에 맞는 형태의 리터럴로 대입됨
ex) String 대입 -> '값' (자동으로 ''추가)
ex) int 대입 -> 값
3. 성능, 속도에서 우위를 가지고 있음
아이디, 비밀번호, 이름을 입력받아 TB_USER 테이블에 삽입(INSERT) 하기 ## 1) JDBC 객체 참조 변수 선언 Connection conn = null; PreparedStatement pstmt = null; Scanner sc = null; // SELECT가 아니기 때문에 ResultSet 필요 없음! try { ## 2) DriverManager 객체를 이용해서 Connection 객체 생성 ## 2-1) Oracle JDBC Driver 객체 메모리 로드 Class.forName("oracle.jdbc.driver.OracleDriver"); String type = "jdbc:oracle:thin:@"; String host = "localhost"; String port = ":1521"; String dbName = ":XE"; String userName = "kh"; String password = "kh1234"; ## 2-3) DB 연결 정보와 DriverManager를 이용해서 Connection 생성 conn = DriverManager.getConnection(type+host+port+dbName, userName, password); ## 3) SQL 작성 sc = new Scanner(System.in); System.out.print("아이디 입력 : "); String id = sc.nextLine(); System.out.print("비밀번호 입력 : "); String pw = sc.nextLine(); System.out.print("이름 입력 : "); String name = sc.nextLine(); String sql = """ INSERT INTO TB_USER VALUES(SEQ_USER_NO.NEXTVAL, ?, ?, ?, DEFAULT ) """; ## 4) PreparedStatement 객체 생성 -> 객체 생성과 동시에 SQL이 담겨지게 됨 -> 미리 ?(위치홀더)에 값을 받을 준비를 해야되기 때문에.. pstmt = conn.prepareStatement(sql); ## 5) ? 위치홀더 알맞은 값 대입 // pstmt.set 자료형 (?순서, 대입할 값) pstmt.setString(1, id); pstmt.setString(2, pw); pstmt.setString(3, name); // -> 여기까지 작성하면 SQL 완료된 상태! // DML 수행 전에 해줘야 할 것! // AutoCommit 끄기! // -> 왜 끄는 건가? 개발자가 트랜잭션을 마음대로 제어하기 conn.setAutoCommit(false); ## 6) SQL(INSERT) 수행 후 결과(int) 반환 받기 // -> 보통 DML 실패 0, 성공 시 0 초과된 값이 반환된다. // pstmt에서 executeQuery(), executeUpdate() 매개변수 자리에 아무것도 없어야 한다! int result = pstmt.executeUpdate(); ## 7) result 값에 따른 결과 + 트랜잭션 제어처리 if(result > 0) { // INSERT 성공 시 System.out.println(name + "님이 추가 되었습니다."); conn.commit(); // COMMIT 수행 -> DB에 INSERT 영구 반영 } else { // INSERT 실패 System.out.println("추가 실패!"); conn.rollback(); // 실패 시 ROLLBACK } } catch (Exception e) { e.printStackTrace(); } finally { ## 8) 사용한 JDBC 객체 자원 반환 try { if(pstmt != null) pstmt.close(); if(conn != null) conn.close(); if(sc != null) sc.close(); } catch(Exception e) { e.printStackTrace(); } } } }
JDBC 6
아이디, 비밀번호, 이름을 입력받아 아이디, 비밀번호가 일치하는 사용자의 이름을 수정(UPDATE) 1. PreparedStatement 이용하기 2. commit/rollback 처리하기 3. 성공 시 "수정 성공! 출력 / 실패 시 "아이디 또는 비밀번호 불일치" 출력 ## 1) JDBC 객체 참조변수 선언 + 키보드 입력용 객체 sc선언 Connection conn = null; PreparedStatement pstmt = null; Scanner sc = null; try { ## 2) Connection 객체 생성 (DriverManager를 통해서) ## 2-1) OracleDriver 메모리에 로드 Class.forName("oracle.jdbc.driver.OracleDriver"); ## 2-2) DB 연결정보 작성 String url = "jdbc:oracle:thin:@localhost:1521:XE"; // 합쳐서!! String userName = "kh"; String password = "kh1234"; ## 2-3) DB 연결 정보와 DriverManager를 이용해서 Connection 생성 conn = DriverManager.getConnection(url, userName, password); ## 3) SQL 작성 + AutoCommit 끄기 conn.setAutoCommit(false); sc = new Scanner(System.in); System.out.print("아이디 입력 : "); String id = sc.nextLine(); System.out.print("비밀번호 입력 : "); String pw = sc.nextLine(); System.out.print("수정할 이름 입력 : "); String name = sc.nextLine(); String sql = """ UPDATE TB_USER SET USER_NAME = ? WHERE USER_ID = ? AND USER_PW = ? """; ## 4) PreparedStatement 객체 생성 pstmt = conn.prepareStatement(sql); ## 5) ? 에 알맞은 값 세팅 pstmt.setString(1, name); pstmt.setString(2, id); pstmt.setString(3, pw); ## 6) SQL 수행 후 결과값 반환받기 int result = pstmt.executeUpdate(); ## 7) result 값에 따라 결과 처리 + commit / rollback if(result > 0) { System.out.println("수정 성공!"); conn.commit(); } else { System.out.println("아이디 또는 비밀번호 불일치"); conn.rollback(); } } catch(Exception e) { e.printStackTrace(); } finally { try { if(pstmt != null) pstmt.close(); if(conn != null) conn.close(); if(sc != null) sc.close(); } catch (Exception e) { e.printStackTrace(); } } } }
JDBC 7
EMPLOYEE 테이블에서 사번, 이름, 성별, 급여, 직급명, 부서명을 조회 단, 입력 받은 조건에 맞는 결과만 조회하고 정렬할 것 조건 1 : 성별 (M, F) 조건 2 : 급여 범위 조건 3 : 급여 오름차순/내림차순 [실행화면] 조회할 성별(M/F) : F 급여 범위(최소, 최대 순서로 작성) : 3000000 4000000 급여 정렬(1.ASC, 2.DESC) : 2 사번 | 이름 | 성별 | 급여 | 직급명 | 부서명 -------------------------------------------------------- 218 | 이오리 | F | 3890000 | 사원 | 없음 203 | 송은희 | F | 3800000 | 차장 | 해외영업2부 212 | 장쯔위 | F | 3550000 | 대리 | 기술지원부 222 | 이태림 | F | 3436240 | 대리 | 기술지원부 207 | 하이유 | F | 3200000 | 과장 | 해외영업1부 210 | 윤은해 | F | 3000000 | 사원 | 해외영업1부 Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; Scanner sc = null; try { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = ("jdbc:oracle:thin:@localhost:1521:XE"); String userName = "kh"; String password = "kh1234"; conn = DriverManager.getConnection(url, userName, password); sc = new Scanner(System.in); System.out.print("조회할 성별(M/F) : "); String gender = sc.next().toUpperCase(); System.out.print("급여 범위(최소, 최대 순서로 작성) : "); int min = sc.nextInt(); int max = sc.nextInt(); System.out.print("급여 정렬(1.ASC 2.DESC) : "); int sort = sc.nextInt(); String sql = """ SELECT EMP_ID 사번, EMP_NAME 이름, DECODE(SUBSTR(EMP_NO, 8, 1), '1', 'M', '2', 'F') GENDER, SALARY 급여, JOB_NAME 직급명, NVL(DEPT_TITLE, '없음') DEPT_TITLE FROM EMPLOYEE LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) JOIN JOB USING (JOB_CODE) WHERE DECODE(SUBSTR(EMP_NO, 8, 1), '1', 'M', '2', 'F') = ? AND SALARY BETWEEN ? AND ? ORDER BY SALARY """; // 급여의 오름차순인지 내림차순인지 조건에 따라 SQL 보완하기 if(sort == 1) sql += " ASC"; else sql += " DESC"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, gender); pstmt.setInt(2, min); pstmt.setInt(3, max); rs = pstmt.executeQuery(); System.out.println("사번 | 이름 | 성별 | 급여 | 직급명 | 부서명"); System.out.println("=============================================="); boolean flag = true; // true : 조회결과가 없음, false : 조회결과 존재함! while(rs.next()) { flag = false; // while문이 1회 이상 반복함 == 조회결과가 1행이라도 있다! String empId = rs.getString("사번"); String empName = rs.getString("이름"); String gen = rs.getString("GENDER"); int salary = rs.getInt("급여"); String jobName = rs.getString("직급명"); String deptTitle = rs.getString("DEPT_TITLE"); System.out.printf("%-4s | %3s | %-4s | %7d | %-3s | %s \n", empId, empName, gen, salary, jobName, deptTitle); } if(flag) { // flag == true인 경우 -> while문 안쪽 수행 X System.out.println("조회 결과 없음"); } } catch(Exception e) { e.printStackTrace(); } finally { try { if(rs != null) rs.close(); if(pstmt != null) pstmt.close(); if(conn != null) conn.close(); if(sc != null) sc.close(); } catch(Exception e) { e.printStackTrace(); } } } }
인생은 변수