[TIL-24/230915][뉴렉처] JDBC, MySQL

개인 프로젝트를 만들던 중, 구글링과 강의를 통해 이해가 아닌 외워서 쓰던 Connection, PreparedStatemnet, ResultSet에 대해서 궁금해졌다.

마침 내가 요즘 듣고 있는 뉴렉처라는 유튜버님의 강의에 JDBC 파트가 있었고, 오늘은 JDBC 강의(총 25강)을 듣고 정리하겠다.

JDBC란 무엇인가

정의

Java Database Connectivity의 약자

역할

Java 기반의 애플리케이션의 데이터를 데이터베이스에 저장 및 업데이트하거나
데이터베이스에 저장된 데이터를 Java에서 사용할 수 있도록 하는 Java API이다.

JDBC는 각각 다른 회사의 DB를 연결할 때 차이를 극복해주는 역할

JDBC 표준 인터페이스

JDBC 동작 흐름

JDBC는 Java 애플리케이션 내에서 JDBC API를 사용하여 데이터베이스에 접근하는 단순한 구조
JDBC API를 사용하기 위해서는 JDBC 드라이버를 먼저 로딩한 후 데이터베이스와 연결하게 된다.

JDBC 드라이버

데이터베이스와 통신을 담당하는 인터페이스
MySQL, Oracle, MS SQL 등과 같은 데이터베이스에 알맞은 JDBC 드라이버를 구현하여 제공
JDBC 드라이버의 구현체를 이용해서 특정 벤더의 데이터베이스에 접근할 수 있음

JDBC API 사용 흐름

커넥션 풀(Connection Pool)

JDBC API를 사용하여 데이터베이스와 연결하기 위해 Connection 객체를 생성하는 작업은 비용이 많이 드는 작업 중 하나

커넥션 객체를 생성하는 과정

애플리케이션에서 DB 드라이버를 통해 커넥션 조회
DB 드라이버는 DB와 TCP/IP 커넥션을 연결(3 way handshake와 같은 네트워크 연결 동작 발생)
DB 드라이버는 TCP/IP 커넥션이 연결되면 아이디와 패스워드, 기타 부가 정보를 DB에 전달
DB는 아이디, 패스워드를 통해 내부 인증을 거친 후 내부에 DB를 생성
DB는 커넥션 생성이 완료되었다는 응답을 보냄
DB 드라이버는 커넥션 객체를 생성해서 클라이언트에 반환

위와 같이 커넥션을 새로 만드는 것은 비용이 많이 들고 비효율적이다.

이러한 문제를 해결하기 위해 애플리케이션 로딩 시점에 Connection 객체를 미리 생성하고, 애플리케이션에서 데이터베이스 연결이 필요할 경우 미리 준비된 Connection 객체를 사용하여 애플리케이션의 성능을 향상하는 커넥션 풀이 등장하게 된다.

Connection Pool

Connection 객체를 미리 생성하여 보관하고 애플리케이션이 필요할 때 꺼내서 사용할 수 있도록 관리해준다.

애플리케이션을 시작하는 시점에 커넥션 풀은 필요한 만큼 커넥션을 미리 생성하여 보관
서비스의 특징과 스펙에 따라 생성되는 Connection 객체의 수는 다르지만 보통 기본값으로 10개 생성
커넥션 풀에 들어있는 Connection 객체는 TCP/IP로 DB와 연결되어 있는 상태이기 때문에 즉시 SQL을 DB에 전달 가능
즉, DB 드라이버를 통해 새로운 커넥션을 획득하는 것이 아닌 이미 생성되어 있는 커넥션을 참조하여 사용
커넥션 풀에 있는 커넥션을 요청하면 커넥션 풀은 자신이 가지고 있는 커넥션 객체 중 하나를 반환

→ DB 드라이버를 통해 커넥션을 조회, 연결, 인증, SQL을 실행하는 시간 등 커넥션 객체를 생성하기 위한 과정 생략 가능

*SpringBoot 2.0 이후 HikariCP를 기본 DBCP로 채택하여 사용되고 있음.

HikariCP

가벼운 용량과 빠른 속도를 가지는 우수한 성능의 JDBC Connection Pool Framework
미리 정해놓은 크기만큼의 Connection을 Connection Pool에 담아 놓음
이후 요청이 들어오면 Thread가 Connection을 요청 → Connection Pool에 있는 Connection을 연결해줌.

JDBC의 기본 코드

1. JDBC 드라이버 로드하기

==> Class.forName("com.mysql.cj.jdbc.Driver");

2. 연결 객체 생성하기(①에서 로드한 드라이버의 매니저에서 Connection 객체를 가져온다.)

==> Connection con = DriverManager.getConnection(url, "아이디", "비밀번호");

3. 쿼리 실행 객체 생성하기(②에서 만든 연결 객체로 Statement객체를 가져온다.)

==> Statement st = con.createStatement();

4. 쿼리 실행 후 결과집합 반환

==> Result rs = st.executeQuery(sql);

5. 결과를 포인터(커서)를 옮기며 결과집합 사용(Before Of File(처음) ~ End Of File(끝)까지)

==> rs.next();

6. 해당 포인터의 데이터를 뽑아온다.

==> String title = rs.getString("title"); //결과 집합에서 title컬럼을 가져오는 예시

검색(select)

String url = "jdbc:mysql://localhost:3306/practice";
String sql = "SELECT * FROM NOTICE WHERE HIT > 10";

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, "prao", "123");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

while (rs.next()) {
    int id = rs.getInt("id");
    String title = rs.getString("title");
    String writerId = rs.getString("writer_id");
    Date regdate = rs.getDate("regdate");
    String content = rs.getString("content");
    int hit = rs.getInt("hit");

    System.out.println("id : " + id);
    System.out.println("title : " + title);
    System.out.println("writerId : " + writerId);
    System.out.println("regdate : " + regdate);
    System.out.println("content : " + content);
    System.out.println("hit : " + hit);
    System.out.println("====================");
}

rs.close();
st.close();
conn.close();

위의 코드를 실행하면 조회수가 10을 초과하는 NOTICE에 있는 레코드의 모든 컬럼을 출력한다.

필터링은 SQL문을 통해서 한다.

코드에서 if문을 통해서도 할 수 있지만 SQL을 사용하는 것이 훨씬 효율적이다.
SQL에서 필터링을 거친 후 while문을 도는 것이 필터링을 거치지 않은 데이터를 if문으로 필터링하는 것보다 훨씬 적은 비용이 소모

삽입(insert), 수정(update), 삭제(delete)

PreparedStatement

데이터 검색(select)

st.executeQuery(sql); → 결과집합 ResultSet을 반환

데이터 삽입(insert), 수정(update), 삭제(delete)

st.executeUpdate(sql); → 결과집합을 반환하지 않고, 영향 받은 행의 개수를 반환

삽입, 수정, 삭제 등을 할 때 Statement만을 사용한다면 값을 넣을 때 쿼리식이 복잡해진다.

삽입(insert)

String title = "TEST2";
String writeId = "newlec";
String content = "hahaha";
String files = "";

String sql = "INSERT INTO notice ( " +
   " title, " +
   " writer_id, " +
   " content, " +
   " files " +
") VALUES ( " +
   " '" + title + "', " +
   " '" + writeId + "', " +
   " '" + content + "', " +
   " '' " +
")";

위와 같이 복잡한 쿼리식을 개선하기 위해 PreparedStatement 객체를 사용한다.

사용방법

1. sql문에 입력할 데이터(변수) 값을 ?로 치환한다.

String sql = "INSERT INTO notice ( " +
                " title, " +
                " writer_id, " +
                " content, " +
                " files " +
                ") VALUES (?, ?, ?, ?)";

2. 실행 전, 물음표를 채워넣을 역할을 하는 객체 PreparedStatement를 생성해야 한다.

PreparedStatement는 st.executeQuery(sql);와 같이 실행할 때 sql문을 옮기지 않고,

미리 값을 채운 sql문을 준비해서 실행만 할 수 있게 한다.

PreparedStatement st = con.prepareStatement(sql); //PreparedStatemnet 객체 자체가 sql문을 갖고 있다.

3. 자료형에 따른 값을 인덱스 1부터 채워준다. st.set자료형(물음표 인덱스 순서, 데이터(변수));

st.setString(1, title);
st.setString(2, writerId);
st.setString(3, content);
st.setString(4, files);

4. 실행 st.executeUpdate();을 실행

st.executeUpdate();을 쓸 때 괄호 안에 sql을 넣으면 안됨!

실행의 결과값은 영향을 받은 행의 개수를 반환한다.(int result = st.executeUpdate();)

수정(update)

String title = "test3";
String content = "content3";
String files = "";
int id = 5;

String url = "jdbc:mysql://localhost:3306/practice";
String sql = "UPDATE NOTICE SET TITLE = ?, CONTENT = ?, FILES = ? WHERE ID = ?";

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, "prao", "pass");
PreparedStatement st = conn.prepareStatement(sql);
st.setString(1, title);
st.setString(2, content);
st.setString(3, files);
st.setInt(4, id);

int result = st.executeUpdate();

System.out.println(result);

st.close();
conn.close();

삭제(delete)

int id = 5;

String url = "jdbc:mysql://localhost:3306/practice";
String sql = "DELETE FROM NOTICE WHERE ID=?";

Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, "prao", "123");
PreparedStatement st = conn.prepareStatement(sql);
st.setInt(1, id);

int result = st.executeUpdate();

System.out.println(result);

st.close();
conn.close();

콘솔에 출력하는 게시판 만들기 연습

데이터 입력: insert into ~ 를 통해 db에 데이터 입력

entity, service, console 디렉토리 생성 후 각각 클래스 파일 생성

최종 코드는 아래와 같다.

entity - Notice (VO): getter와 setter 메서드만 존재

public class Notice {
    private int id;
    private String title;
    private String writerId;
    private Date regdate;
    private String content;
    private int hit;
    private String files;

    public Notice() {
    }

    public Notice(int id, String title, String writerId, Date regdate, String content, int hit, String files) {
       this.id = id;
       this.title = title;
       this.writerId = writerId;
       this.regdate = regdate;
       this.content = content;
       this.hit = hit;
       this.files = files;
    }

    public int getId() {
       return id;
    }

    public void setId(int id) {
       this.id = id;
    }

    public String getTitle() {
       return title;
    }

    public void setTitle(String title) {
       this.title = title;
    }

    public String getWriterId() {
       return writerId;
    }

    public void setWriterId(String writerId) {
       this.writerId = writerId;
    }

    public Date getRegdate() {
       return regdate;
    }

    public void setRegdate(Date regdate) {
       this.regdate = regdate;
    }

    public String getContent() {
       return content;
    }

    public void setContent(String content) {
       this.content = content;
    }

    public int getHit() {
       return hit;
    }

    public void setHit(int hit) {
       this.hit = hit;
    }

    public String getFiles() {
       return files;
    }

    public void setFiles(String files) {
       this.files = files;
    }
}

service - NoticeService(DAO): DB와 연결하는 Data Access Object

public class NoticeService {
    private String url = "jdbc:mysql://localhost:3306/practice";
    private String uid = "prao";
    private String pwd = "123";
    private String driver = "com.mysql.cj.jdbc.Driver";

    // 페이지별 공지사항 목록을 가져오는 메서드
    public List<Notice> getList(int page, String field, String query) throws ClassNotFoundException, SQLException {

        // SQL 쿼리를 동적으로 생성합니다.
        String sql = "SELECT * FROM NOTICE WHERE " + field + " LIKE ? ORDER BY REGDATE DESC LIMIT 10 OFFSET ?";

        // 드라이버 로드 및 데이터베이스 연결
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, uid, pwd);
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, "%" + query + "%");
        st.setInt(2, (page - 1) * 10);
        ResultSet rs = st.executeQuery();

        List<Notice> list = new ArrayList<>();

        while (rs.next()) {
            // 결과 집합에서 데이터를 추출하여 Notice 객체 생성 후 리스트에 추가
            int id = rs.getInt("id");
            String title = rs.getString("title");
            String writerId = rs.getString("writer_id");
            Date regDate = rs.getDate("regdate");
            String content = rs.getString("content");
            int hit = rs.getInt("hit");
            String files = rs.getString("files");

            Notice notice = new Notice(id, title, writerId, regDate, content, hit, files);
            list.add(notice);
        }

        // 리소스 정리
        rs.close();
        st.close();
        conn.close();

        return list;
    }

    // 공지사항 전체 개수를 가져오는 메서드
    public int getCount() throws ClassNotFoundException, SQLException {
        int count = 0;
        String sql = "SELECT COUNT(ID) COUNT FROM NOTICE";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, uid, pwd);
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(sql);

        if (rs.next()) {
            count = rs.getInt("COUNT");
        }

        // 리소스 정리
        rs.close();
        st.close();
        conn.close();

        return count;
    }

    // 공지사항을 삽입하는 메서드
    public int insert(Notice notice) throws ClassNotFoundException, SQLException {

        String title = notice.getTitle();
        String writerId = notice.getWriterId();
        String content = notice.getContent();
        String files = notice.getFiles();

        String sql = "INSERT INTO NOTICE (title, writer_id, content, files) VALUES (?, ?, ?, ?)";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, uid, pwd);
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, title);
        st.setString(2, writerId);
        st.setString(3, content);
        st.setString(4, files);

        int result = st.executeUpdate();

        // 리소스 정리
        st.close();
        conn.close();

        return result;
    }

    // 공지사항을 업데이트하는 메서드
    public int update(Notice notice) throws ClassNotFoundException, SQLException {

        String title = notice.getTitle();
        String content = notice.getContent();
        String files = notice.getFiles();
        int id = notice.getId();

        String sql = "UPDATE NOTICE SET TITLE = ?, CONTENT = ?, FILES = ? WHERE ID=?";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, uid, pwd);
        PreparedStatement st = conn.prepareStatement(sql);
        st.setString(1, title);
        st.setString(2, content);
        st.setString(3, files);
        st.setInt(4, id);

        int result = st.executeUpdate();

        // 리소스 정리
        st.close();
        conn.close();
        return result;
    }

    // 공지사항을 삭제하는 메서드
    public int delete(int id) throws ClassNotFoundException, SQLException {

        String sql = "DELETE FROM NOTICE WHERE ID=?";

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, uid, pwd);
        PreparedStatement st = conn.prepareStatement(sql);
        st.setInt(1, id);

        int result = st.executeUpdate();

        // 리소스 정리
        st.close();
        conn.close();
        return result;
    }
}

console - NoticeConsole(View): console을 통해 view 구현

package com.JDBCPrj.app.console;

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

import com.JDBCPrj.app.entity.Notice;
import com.JDBCPrj.app.service.NoticeService;

public class NoticeConsole {

    private NoticeService service;
    private int page;
    private String searchField;
    private String searchWord;

    // 생성자를 통해 초기화
    public NoticeConsole() {
        service = new NoticeService();
        page = 1;
        searchField = "TITLE"; // 기본 검색 필드를 제목으로 설정
        searchWord = "";
    }

    // 공지사항 목록을 화면에 출력
    public void printNoticeList() throws SQLException, ClassNotFoundException {
        List<Notice> list = service.getList(page, searchField, searchWord);
        int count = service.getCount();
        int lastPage = count / 10;
        lastPage = count % 10 > 0 ? lastPage + 1 : lastPage;

        // 헤더 출력
        System.out.println("----------------------------------------------");
        System.out.printf("<공지사항> 총 %d 게시글\n", count);
        System.out.println("----------------------------------------------");

        // 공지사항 목록 출력
        for (Notice n : list) {
            System.out.printf("%d. %s / %s / %s\n", n.getId(), n.getTitle(), n.getWriterId(), n.getRegdate());
        }
        System.out.println("----------------------------------------------");
        System.out.printf("              %d/%d pages\n", page, lastPage);
    }

    // 사용자로부터 메뉴 선택을 받아 반환
    public int inputNoticeMenu() {
        Scanner sc = new Scanner(System.in);
        System.out.print("1.상세조회 / 2.이전 / 3.다음 / 4.글쓰기 / 5.검색 / 6.종료 >> ");

        String menu_ = sc.nextLine();
        int menu = Integer.parseInt(menu_);

        return menu;
    }

    // 이전 페이지로 이동
    public void movePrevList() {
        if (page == 1) {
            System.out.println("----------------------------------------------");
            System.out.println("[첫 페이지입니다.]");
            System.out.println("----------------------------------------------");
            return;
        }

        page--;
    }

    // 다음 페이지로 이동
    public void moveNextList() throws SQLException, ClassNotFoundException {
        int count = service.getCount();
        int lastPage = count / 10;
        lastPage = count % 10 > 0 ? lastPage + 1 : lastPage;

        if (page == lastPage) {
            System.out.println("----------------------------------------------");
            System.out.println("[마지막 페이지입니다.]");
            System.out.println("----------------------------------------------");
            return;
        }

        page++;
    }

    // 검색어 입력 받기
    public void inputSearchWord() {
        Scanner sc = new Scanner(System.in);
        System.out.println("검색 범주(title/content/writerId) 중 하나를 입력하세요.");
        System.out.print(" > ");
        searchField = sc.nextLine();

        System.out.print("검색어 > ");
        searchWord = sc.nextLine();
    }
}

위의 DAO, DO, VIEW를 활용한 Program

public class Program5 {

	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		NoticeConsole console = new NoticeConsole();
		// int page;

		EXIT:
		while (true) {
			console.printNoticeList();
			int menu = console.inputNoticeMenu();
			switch (menu) {
				case 1:    //상세조회
					break;
				case 2:    //이전
					console.movePrevList();
					// page--;
					break;
				case 3:    //다음
					console.moveNextList();
					// page++;
					break;
				case 4:    //글쓰기
					break;
				case 5:    //검색
					console.inputSearchWord();
					break;
				case 6: //종료
					System.out.println("이용해주셔서 감사합니다.");
					break EXIT;
				default:
					System.out.println("<<사용방법>> 메뉴는 1~6까지만 입력할 수 있습니다.");
					break;
			}
		}

	}
}

Console 구동

위와 같이 이전, 다음, 검색, 종료를 구현하였다. 상세조회 및 글쓰기는 콘솔에서 아직 구현하지 못하였고, 다음으로 진행할 webpage에서 구현해볼 계획이다.

JSP와 Servlet은 예전 기술이고 요즘은 Spring이다라는 말만 듣고 처음에 Spring을 공부했는데, 이 기술이 왜 등장했는지, 어떤 불편함을 개선한 기술인지를 모르니 크게 와닿지 않았고, 그저 강의를 들으며 코드를 치는 기분이었다.

하지만 뉴렉처님의 JDBC, JSP, Servlet 등의 강의를 들으면서 점차 내가 그때 따라쳤던 코드가 어떤 코드였는지, 그리고 스프링의 장점이 무엇인지, 스프링의 Controller, Service, Repository가 Servlet과 JSP로 만든 프로젝트에선 어떤 부분인지 등 많은 것을 배우고 있다.

요즘 이렇게 몰입하여 공부하고 TIL을 정리하니 이전보다 학습의 효과가 훨씬 좋아졌다는 것이 체감된다.

오늘은 목표로 잡은 강의의 양이 많아서 TIL을 정리하니 새벽 4시가 다되었는데, 목표치를 끝내서 기분이 좋다.

내일은 다시 JSP를 활용한 게시판 만들기에 초점을 두고 공부를 이어나가겠다.

참고자료

https://www.youtube.com/@newlec1

뉴렉처

"유료" 온라인 강의 사이트 뉴렉처에서 제공하는 프로그래밍 강의 채널입니다. 교육서비스 : (10월 새롭게 오픈될 예정입니다.) https://www.newlecture.com 교재: https://javaweb.tistory.com https://answeris.tistory.

www.youtube.com

https://ittrue.tistory.com/250

[Java] JDBC란 무엇인가? - Java Database Connectivity

JDBC란? JDBC(Java Database Connectivity)는 Java 기반 애플리케이션의 데이터를 데이터베이스에 저장 및 업데이트하거나, 데이터베이스에 저장된 데이터를 Java에서 사용할 수 있도록 하는 자바 API이다. JDB

ittrue.tistory.com

저작자표시 비영리 변경금지

'TIL > NewLecture' 카테고리의 다른 글

[TIL-25/230916][뉴렉처] GET/POST, JSP, MVC Model (0)	2023.09.16
[TIL-22/230913][뉴렉처] GET, POST, Filter, Application, Session, Cookie (0)	2023.09.14