JDBC(Java DataBase Connectivity) API

📕API(Application Programming Interface)

• 자바 데이터 베이스 연결 기술 명세서: 인터페이스로 구성
• 구현체는 각 DB업체가 구성(데이터베이스 드라이버)

java.sql 패키지 -> JDBC API
java.sql 문서

---

• 도커 넘 무거워서 이미지 새로 만들기
  

• 컨테이너 만들기
  docker run --name oracle-xe -d -p 1521:1521 -e ORACLE_PASSWORD=oracle gvenzl/oracle-xe:18
  

-d: 백그라운드 실행
-p: 포트 1521
--name: 명칭 바꾸기

컨테이너 만들어졌다 ~~

얘는 스콧은 없다! 연습할때는 원래 쓰던거로 사용하기

• debeaver연동하기
  

만들었던 study계정 연결

#study에서 테이블 추가
--테이블 생성
CREATE TABLE MEMBER (
	USER_NO NUMBER(10) PRIMARY KEY,
	USER_ID VARCHAR2(30) UNIQUE NOT NULL,
	USER_PW VARCHAR2(65) NOT NULL,
	USER_NM VARCHAR2(40) NOT NULL,
	MOBILE VARCHAR2(15),
	REG_DT DATE DEFAULT SYSDATE,
	MOD_DT DATE 
);

--시퀀스 객체 만들기
CREATE SEQUENCE SEQ_MEMBER;

---

Oracle JDBC 드라이버 설치

ojdbc11.jar -> 의존성에 추가하기

Ojdbc11 » 23.4.0.24.05 버전
gradle(short)
implementation 'com.oracle.database.jdbc:ojdbc11:23.4.0.24.05'

dependencies {
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11:23.4.0.24.05' //동적로딩형태
    testImplementation platform('org.junit:junit-bom:5.10.0')
    testImplementation 'org.junit.jupiter:junit-jupiter'
}

한글깨지는 오류 해결을 위해

lombok도 추가

dependencies {
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11:23.4.0.24.05'
    implementation 'com.oracle.database.nls:orai18n:23.4.0.24.05'
    compileOnly 'org.projectlombok:lombok:1.18.32'
    annotationProcessor 'org.projectlombok:lombok:1.18.32'
    testImplementation platform('org.junit:junit-bom:5.10.0')
    testImplementation 'org.junit.jupiter:junit-jupiter'
}

DriverManager 클래스 -> Connection
DataSource 인터페이스 -> Connection: 커넥션 풀(연결 객체 저장소)

연동 과정

1) java.sql.* 패키지 임포트

2) JDBC 드라이버 로딩

• Class.forName(..) -> ojdbc11.jar
  oracle.jdbc.driver.OracleDriver
  • 동적로딩

3) 데이터베이스 접속을 위한 Connection객체 생성
DriverManager : 접속을 할때마다 Connection 객체를 생성 -> 효율성X, 성능 X
    Connection getConnection()
    Connection getConnection(String url, String user, String password)

• url: jdbc:oracle:this:@localhost:1521:XE

---

DataSource 인터페이스: 커넥션 풀을 제공하는 라이브러리 구현체가 있다.
       (Tomcat JDBC, HikariCP)
Connection getConnection() - Connection객체가 반환값

4) 쿼리문을 실행하기 위한
🌼
Statement/PreparedStatement/CallableStatement 객체 생성

5) 쿼리 실행

6) 쿼리 실행 결과 값(int, ResultSet) 사용

7) 사용된 객체(ResultSet, Statement/PreparedStatement/CallableStatement, Connection) 종료

JDBC 드라이버 로딩 및 DBMS 접속

1) JDBC 드라이버 로딩하기

🔼드라이버 로딩

2) Connection 객체 생성하기

3) 데이터베이스 연결 닫기

Connection 인터페이스에는 연결 종료를 위한 AutoCloseable을 상속받고있다.
자원 자동해제의 기준에 부합하다.
try with resource문 -> 자원 자동해제

데이터베이스 쿼리 실행

1. Statement 객체로 데이터 접근하기

Connection
   Statement createStatement();

1) 🔖ResultSet executeQuery(String sql)

• 조회를 하는 쿼리 수행
  • SELECT(조회)
• 반환값: ResultSet -> 조회한 레코드의 조회 시작 주소값, 커서이동 다음행을 조회
  

2) 🔖int executeUpdate(String sql)

• 변경을 가하는 쿼리 수행
  • INSERT, UPDATE, DELETE( 추가, 수정, 삭제)
• 반환값: 반영된 레코드 개수
  

db에 업로드 잘 되었당

3) close()

---

💻전체 test 코드

package exam01;

import org.junit.jupiter.api.Test;

import java.sql.*;
import java.time.LocalDateTime;

public class Ex01 {
    @Test
    void test1(){
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }

        String url = "jdbc:oracle:thin:@localhost:1521:XE";//같은 위치에 있으면 localhost
        //현재 같은 서버에 존재하니까 localhost, 기본포트 1521, 연습용 계정 사용시 EE
        //다른 서버에 있으면 ip주소나 도메인 주소 입력 ex)3.100.2.111

        String user = "STUDY";
        //연습용 계정은 SCOTT
        String password = "oracle"; // SCOTT은 tiger

//        try{
//            Connection conn = DriverManager.getConnection(url, user, password);
//            //db에 연결할 수 있는 핵심적인 메서드이다.
//            System.out.println(conn);//접속확인
//        }catch (SQLException e){
//            e.printStackTrace();
//        }
        try(Connection conn = DriverManager.getConnection(url, user, password);
            Statement stmt = conn.createStatement()){//Statement 객체로 sql문 실행 할수있다.

            String sql = "INSERT INTO MEMBER (USER_NO, USER_ID, USER_PW, USER_NM, MOBILE) VALUES (SEQ_MEMBER.NEXTVAL, 'USER01', '123456', '사용자01', '01000000000')";

            int cnt = stmt.executeUpdate(sql);
            System.out.println(cnt);

        }catch (SQLException e){
            e.printStackTrace();
        }



    }

    @Test
    void test2(){
        try {
            // 오라클 드라이버 동적 로딩
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }

        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String user = "STUDY";
        String password = "oracle";

        try(Connection conn = DriverManager.getConnection(url, user, password);
            Statement stmt = conn.createStatement()) {
            String keyword = "사용자";
            //키워드가 포함되었는지 안되었는지로 판단
            String sql = "SELECT * FROM MEMBER WHERE USER_NM LIKE '%"+keyword+"%'";
            //String sql = "SELECT * FROM MEMBER";
            ResultSet rs = stmt.executeQuery(sql);
            //조회 결과 여기서 한행씩 찾아서 가져옴
            while (rs.next()){
                long userNo = rs.getLong("USER_NO"); //컬럼명 직접 명시해야 순서가 바뀌어도 문제가 없게 된다.
                String userId = rs.getString("USER_ID");
                String userPw = rs.getString("USER_PW");
                String userNm = rs.getString("USER_NM");
                String mobile = rs.getString("MOBILE");
                //날짜와 시간 가져오기
                LocalDateTime regDt = rs.getTimestamp("REG_DT").toLocalDateTime();
                System.out.printf("USER_NO: %d, USER_ID: %s, USER_PW: %s, USER_NM: %s, MOBILE: %s%n, REG_DT: %s%n", userNo, userId, userPw, userNm, mobile, regDt);
            }
            rs.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
}

보안적인 측면이나 성능을 따졌을때 PreparedStatement 사용하는것이 더 좋다!

---

2. 🌟PreparedStatement 객체로 데이터 접근하기

• statement 대신쓰기
• 악의적인거 걸러줌,,

1) 동적인 쿼리에 사용

2) 하나의 객체로 여러 번의 쿼리를 실행할 수 있으며, 동일한 쿼리문을 특정 값만 바꾸어서 여러 번 실행해야 할 때, 매개변수가 많아서 쿼리문을 정리해야 할 때 유용

• 값을 ? 파라미터로 미리 정의, 값을 set자로형 메서드로 지정
• SQL주입(Injection)을 방지

Connection
   PreparedStatement prepareStatement(String sql);

• 값이 할당될 부분은 SQL에 ?로 기입

set 자료형(int ?의 위치번호, 값);

🔖ResultSet executeQuery() : SELECT
🔖int executeUpdate(): INSERT, UPDATE, DELETE
-> statement와 차이점은 매개변수에 sql에 들어가지않는다. prepareStatement(String sql)에서 미리 준비하기때문

package exam01;

import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import java.sql.*;
import java.time.LocalDateTime;

public class Ex02 {

    private String url = "jdbc:oracle:thin:@localhost:1521:XE";
    private String user = "STUDY";
    private String password = "oracle";

    @BeforeAll //모든 테스트 메서드 호출 전에 단 한번실행 - 공통 초기화
    static void init(){ //공통 초기화 한번 실행
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    @Test
    void test1(){

        String keyword = "사용자";
        String sql = "SELECT * FROM MEMBER WHERE USER_NM LIKE ?"; //변수가 교체 될 부분은 ?로 작성
        // 내부적으로 보안적으로 취약한 부분은 제외하고 값을 넣어줌

        //조회
        try(Connection conn = DriverManager.getConnection(url,user,password);
            PreparedStatement pstmt = conn.prepareStatement(sql)){

            //물음표의 위치는 1개밖에 없다 현재는 하나밖에없으니...
            pstmt.setString(1,"%"+keyword+"%");
            
            // 한칸씩 커서이동하면서 데이터 조회
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                long userNo = rs.getLong("USER_NO"); 
                String userId = rs.getString("USER_ID");
                String userPw = rs.getString("USER_PW");
                String userNm = rs.getString("USER_NM");
                String mobile = rs.getString("MOBILE");

                LocalDateTime regDt = rs.getTimestamp("REG_DT").toLocalDateTime();
                System.out.printf("USER_NO: %d, USER_ID: %s, USER_PW: %s, USER_NM: %s, MOBILE: %s%n, REG_DT: %s%n", userNo, userId, userPw, userNm, mobile, regDt);
            }
            rs.close();
        }catch (SQLException e){
            e.printStackTrace();
        }

    }
}

---

---

Connection
   PreparedStatement prepareStatement(String sql, String[] columnNames);
-> columnsNames: 증감번호 기본키를 명시
ResultSet getGeneratedKeys()를 통해서 기본키 조회

사용자 04

#STUDY 스크립트
CREATE OR REPLACE PROCEDURE REGISTER_MEMBER(
	P_USER_ID VARCHAR2,
	P_USER_PW VARCHAR2,
	P_USER_NM VARCHAR2,
	P_MOBILE VARCHAR2
)
IS
BEGIN
	INSERT INTO MEMBER (USER_NO, USER_ID, USER_PW, USER_NM,MOBILE)
		VALUES(SEQ_MEMBER.NEXTVAL, P_USER_ID, P_USER_PW,P_USER_NM, P_MOBILE);
END REGISTER_MEMBER;


SELECT * FROM USER_ERRORS;

CallableStatement

• 프로시저 호출용

---

3. CallableStatement 객체로 데이터 접근하기

1) 프로시저 실행시 사용

3. 쿼리문 실행 결과 값 가져오기
   1) ResultSet 객체의 메서드
   

boolean next(): 다음 행으로 이동, 다음 행이 있으면 true, 없으면 false

자료형 get자료형(int 컬럼 순서번호);

자료형 get자료형(String 컬럼명);

String getString(): 문자형 데이터(CHAR, VARCHAR2, CLOB..)
int getInt() long getLong(): 정수형 데이터
float getFloat() double getDouble(): 실수형 데이터

java.sql.Time: 시간 - getTime()
java.sql.Date: 날짜 - getDate()
java.sql.Timestamp: 날짜 + 시간 - getTimestamp()

가공하기 편한 java Time패키지 이용

🔽test2 실행결과

---

🔸검색 기능 추가

test2실행시 사용자01 조회 성공

---

커넥션 풀

Tomcat JDBC
의존성: tomcat-jdbc

gracle(short) 의존성 추가

dependencies {
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11:23.4.0.24.05'
    implementation 'com.oracle.database.nls:orai18n:23.4.0.24.05'
    compileOnly 'org.projectlombok:lombok:1.18.32'
    annotationProcessor 'org.projectlombok:lombok:1.18.32'
    implementation 'org.apache.tomcat:tomcat-jdbc:10.1.24'
    testImplementation platform('org.junit:junit-bom:5.10.0')
    testImplementation 'org.junit.jupiter:junit-jupiter'
}

---

HikariCP

Gradle short 의존성 추가