2023.03.13 JDBC

2023. 3. 14. 16:44

JDBC

1. JDBC란 무엇인가?

자바 개발자인데 db를 사용하고 싶다면 jdbc를 알아야한다.
sql을 직접 사용 할수잇엇고 오라클이나 mysql이 제공하는 프로그램으로 연결인증 문장실행 결과패치등을 햇엇다.
그러나 여기에 담을 수 없는 사람도 도구를 사용할 수없다면? ui로 간접적으로 db를 건들 수 잇다. 표현목록을 다라고 하면 ui를 통해서 달라고 한다.
자바 프로그래머는 사용자 요구를 위한 sql문을 작성할 수 잇다. 코드에서 db를 이용할 수잇는 api를 의미하는 것이다.
db마다 기능은 같지만 ui가 다르다. 오라클과 ms sql의 함수가 다르다. 완전 동일하다고 해도 연결하고자 하는 api자체가 차이가 잇다. 코드에서 db에접속하는걸 고쳐야한다.
그런데 이것을 고치는게 맞느냐? 연결해서 하는데 매우 골치아프다. 그래서 등장한게 jdbc이다.
직접 사용하지 않고 가져다 사용하는 것이다. 조금씩 차이나는 것들을 가져다 쓰는것이다. 깡통이고 인터페이스이다. 차이를 알필요없고 그냥 함수를 가져다 쓰는 것이다.
직접 구동 코드를 가진게 driver이다.
무조건 이순서로 한다.
1.드라이버 로드하기
2.연결생성
3.문장실행
4.결과집합사용하기

2.2. DBMS와 JDBC Driver 준비하기

jdbc드라이버 다운로드

2.3 JDBC 기본 코드의 이해

1.Class.forName("com.mysql.cj.jdbc.Driver"); 드라이버로드 객체생성
2.Connectionm con = DriverManager.getConnection(...);
3.Statement st = con.createStatement();
4.ResultSet rs = st.executeQuery(sql);
new연산자를 사용하지 않고 객체가 생성이 된다. 순차적으로 흐름을 가지게 하는 것이다.
실행이되면 메모리상에 driver가 올라간다. con하면 db와연결을 하고 쿼리를 st로 넣을 수잇다.
결과집합이 만들어지면 이것을 rs로 받아온다.
5.rs.next(); 하나씩 내려감
6.String title = rs.getString("title");

2.4 쿼리 실행하기 실습

1.Class.forName("com.mysql.cj.jdbc.Driver"); 드라이버로드 객체생성
2.Connection con = DriverManager.getConnection(url, "id" , "pwd");
3.Statement st = con.createStatement();
4.ResultSet rs = st.executeQuery(sql);
이것은 무조건 다들어간다.
사용한 리소스들을 닫아줘야한다. 닫는 것은 역순으로 닫는다.
rs.close();
st.close();
con.close();
항상기억하기 성능에 대한 부분이라 닫는게 좋다.

String url = "jdbc:mysql://localhost:3306/dbpractice";
String sql = "SELECT * FROM NOTICE";

5.rs.next();로 값 꺼내기 커서 한칸씩 내려가면서 담음
String title = rs.getString("TITLE");
system.out.println(title);

if(rs.next()) {
String title = rs.getString("TITLE");
System.out.println(title);
}

만약 가져올 값이 없다면 예외가 발생한다. 그래서 if문으로 있다면 보여주게 해줘야한다.

public class Program {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/dbpractice";
        String sql = "SELECT * FROM NOTICE";

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

        if (rs.next()) {
            String title = rs.getString("title");
            System.out.println(title);
        }

        rs.close();
        st.close();
        con.close();
    }
}

5. 혼자 풀어보는 문제 #1

레코드의 모든 컬럼 출력하기

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.printf("id:%d, title:%s, wirterId:%s, regdate:%s, content:%s, hit:%d\n", id, title, writerId,
            regDate, content, hit);
}

6. 문제 #1 풀이겸 문제 #2

자료형별로 getter가 있다.
또다른 문제 조회수가 10이상인 게시글만 출력하기

7. 문제 #2 풀이와 SQL을 잘해야 하는 이유

String sql = "SELECT * FROM NOTICE WHERE HIT >= 10";
SQL문만 바꿔주면 된다.

while문에 if(hit > 10)으로 넣을수도잇지만
만약 게시글이 1억개가 있다고 생각해보자.
네트워크로 굳이 2개만 가져와도 될것을 1억개를 가져와서 동작을 한다면 비효율적이다.

데이터 필터링, 정렬, 그룹화 등의 모든 데이터 연산은 데이터베이스에서 처리한다.
자바는 UI레이아웃만 신경써야한다. 데이터 가공처리는 SQL이 한다. 자바가 하는 일이 훨씬 줄어들게 할 수 있다.

8. 데이터 입력을 위한 쿼리문 준비하기

먼저 SQL툴에서 작성해보고 가져다 쓰는게 좋다. 입력할때는 어떤값인지 아는게 좋다.
SQL툴에서 테이블을 드래그하면 sql문을 자동으로 만들 수있는 기능이잇다.
사용자가 입력하면 필요한 값만 넣어야한다.
id, regdate, hit, pub는 사용자가 입력하는 것으로 하지 않게 해야한다.
id는 서브쿼리를 넣거나 오라클은 시퀀스 MYSQL은 제약조건으로 알아서 늘어나게 할 수 있다.

insert into notice(TITLE, WRITER_ID, CONTENT, REGDATE, HIT, FILES) values('제목12', 'newlec', 'aaa', now(), 0, '');

9. 데이터 입력하기와 PreparedStatement

위에서 작성한 쿼리문을 통해 입력하도록 하자. select가 아닐때는 결과집합이 없다.
select문은 결과집합을 executeQuery로 얻지만 업데이트 인서트 등은 결과집합 resultset이 없기때문에 executeUpdate로 한다.

String title = "test2";
String writerId ="newlec";
String content = "hahaha";
String files = "";

이것들을 넣는다고 생각해보자. 마지막에 합쳐졋을때 온전한 sql문이 될 수잇도록 작성해야한다.
"VALUES( " + title + " )"; 이런식으로 넣을 수도잇다. 그런데 이럴때 양쪽에 ' ' 을 넣어야한다.
"VALUES( " + "'" + title + "'" + ")"; 그러면 이런식으로 매우 복잡해진다.
여기서 문자열을 하지 않고 값을꽃아넣는 방법이 있다. "VALUES(?, ?, ?, ?)";바로 ?로 꽃는것이다.

String sql = "INSERT INTO notice(TITLE, WRITER_ID, CONTENT, HIT, FILES) VALUES(?, ?, ?, ?, ?)";

값을 사용하기 위해서 사용하기위해 이제 statement가아니라 PreparedStatement pstmt = con.prepareStatement(sql);이다.
이값을 채울 수잇도록 할 수잇는 쿼리문을 사용하면된다.
SET에 ? 값을 넣는다. INDEX, 넣을 값 하면된다.

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

int result = st.executeUpdate();
System.out.println(result);

반환타입이 row count이다. 추가가됫다면 1을 반환할것이다.

10. 데이터 수정을 위한 쿼리 준비하기

수정하기 해보자. 간단한 쿼리 작성해보자.

UPDATE NOTICE
SET
TITLE ='TEST3',
CONTENT ='HAHA',
FILES=''
WHERE ID = 17;

WHERE절에 조건을 안달면 다 바뀌어버리니 주의하자.

11. 데이터 수정을 구현하기

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

입력하고자 하는 부분은 다 ?로 바꾸어주면된다.

PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, title);
pstmt.setString(2, content);
pstmt.setString(3, files);
pstmt.setInt(4, 17);

12. 데이터 삭제하기

삭제쿼리는 단순하니 그냥 준비하자.

String sql = "DELETE FROM NOTICE WHERE ID=?";
PreparedStatement st = con.prepareStatement(sql);
st.setInt(1, id);

잘 삭제 된게 보여진다.

13. CRUD를 담당하는 서비스 클래스 생성하기

select(retrieve반환받음) insert(create) update delete를 이작업을 줄여서 말하면 crud라고 한다.
각각 다른 프로그램으로 만들엇다. 이렇게 따로 만들면 재사용이 불가능하다.
재사용을 위해 메인함수에서 떼고 noticeservice만들어서 담아보자.
com.newlecture.app.service 패키지에 NoticeService를 담아보자.
이름이 충돌하지 않도록 도메인을 넣어주는 것이다.

public List<?> getList() {
return ?;
}

리스트로 받아서 getList 메소드로 불러오면 될것 같다.
담을수 있는 그릇은위해 바로 Notice라는 entity를 만들어주자.
편하게 하기 위해서 생성자에 담아서 객체를 만들고 list에 담는다.

예외를 처리하는 것은 ui기때문에 그냥 던진다.

public class NoticeService {
    public List<Notice> getList() throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/dbpractice";
        String sql = "SELECT * FROM NOTICE";

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

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

        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");

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

        rs.close();
        st.close();
        con.close();

        return list;
    }
}

14. NoticeService 구현 마무리

나머지 기능을 구현해보자.

public int insert(Notice notice) {
return 0;
}

데이터는 Notice를 전달받아서 실행하는 것이다.

반복되는 부분들은 필드로 만들어주자.

!!!새로운 사항!!!!
연결객체들은 매번 실행할때마다 따로 있어야한다. 왜그런가?
연결 객체의 수보다 중요한 건 연결을 생성하는 수와 연결이 유지되는 시간이 더 중요하다고 한다.
DB에 접속하는 쪽보다 DB가 접속을 처리하는 부담을 더 느낀다고 한다.
연결은 꼭 사용할 때만 유지하고 바로 닫는 것이 바람직하기 때문에 연결을 미리 열어 놓고 재사용하는 것은 바람직하지 않다고 한다.
그리고 연결을 하지 않는 상태에서 굳이 연결 객체를 미리 준비하는 것도 좋은 방법이 아니다.

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 con = DriverManager.getConnection(url, uid, pwd);
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setString(1, title);
    pstmt.setString(2, writerId);
    pstmt.setString(3, content);
    pstmt.setString(4, files);
    int result = pstmt.executeUpdate();
    System.out.println(result);
    pstmt.close();
    con.close();
    return result;
}

public int update(Notice notice) throws ClassNotFoundException, SQLException {
    String title = notice.getTitle();
    String writerId = notice.getWriterId();
    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 con = DriverManager.getConnection(url, uid, pwd);
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setString(1, title);
    pstmt.setString(2, content);
    pstmt.setString(3, files);
    pstmt.setInt(4, id);
    int result = pstmt.executeUpdate();
    System.out.println(result);
    pstmt.close();
    con.close();
    return result;
}

public int delete(int id) throws ClassNotFoundException, SQLException {
    String sql = "DELETE FROM NOTICE WHERE ID=?";

    Class.forName(driver);
    Connection con = DriverManager.getConnection(url, uid, pwd);
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setInt(1, id);
    int result = pstmt.executeUpdate();
    System.out.println(result);
    pstmt.close();
    con.close();
    return result;
}

15. 사용자 인터페이스 붙이기(공지사항 목록)

notice를 관리하기 위해 notice service를 만들었다.

사용자에게 입력받고 사용자가 볼수잇도록 하는게 중요하다.
사용자 인터페이스란 웹, 자바콘솔등이잇을 것이다.
여기서는 자바 콘솔로 해보자. 페이징도 만들어볼것이다.

NoticeConsole을 만들어준다. 여기에 어떠한 기능 메소드를 추가할 것이냐?
그것은 program5에서하자. 목록을 추가하는 부분과 입력받는 메뉴부분을 따로받아보자.

데이터는 NoticeService로부터 받아오도록 하자.

public class NoticeConsole {
    private NoticeService service;

    public NoticeConsole() {
        service = new NoticeService();
    }

    public void printNoticeList() throws ClassNotFoundException, SQLException {
        List<Notice> list = service.getList();

        System.out.println("---------------------------------------------------------");
        System.out.printf("<공지사항> 총 %d 게시글\n", 12);
        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", 1, 2);
    }

    public int inputNoticeMenu() {
        return 0;
    }
}

16. 공지사항 메뉴 붙이기

목록 밑에 공지사항을 관리하는 메뉴를 만들어보자.
nextint하면 버퍼가 남거나 문자가아니면 오류가 발생할 수 있으니 line로 받자.

public int inputNoticeMenu() {
    Scanner sc = new Scanner(System.in);

    System.out.print("1.상세조회/ 2.이전/ 3.다음/ 4.글쓰기/ 5.종료 >");    
    String menu_ = sc.nextLine();
    int menu = Integer.parseInt(menu_);

    return menu;
}

강제 종료하는 방법도 있겠지만 종료는 라벨을 달아서 break하는거로 해보자.

Exit :
while (true) {
    console.printNoticeList();
    int menu = console.inputNoticeMenu();

    switch (menu) {
    case 1: // 상세조회
        break;
    case 2: // 이전
        break;
    case 3: // 다음
        break;
    case 4: // 글쓰기
        break;
    case 5: // 종료
        System.out.println("BYE~ ");
        break Exit;
    default: 
        System.out.println("<사용방법> 메뉴는 1~4까지만 입력 가능");
        break;
    }
}

17. 페이징을 위한 쿼리 만들기

오라클은 로우넘버를 뽑아서 페이징을 한다.
오라클은 정렬한다음 또 정렬해야한다.

SELECT * FROM (
SELECT ROWNUM NUM, N.* FROM (
SELECT * FROM NOTICE ORDER BY REGDATE DESC
) N
)
WHERE NUM BETWEEN 1 AND 10;

MYSQL은 limit offset을 사용할 수 있다.
페이징이 더 쉽다.
3가지 방법이 있다.

Select  * from 테이블명 orders LIMIT 숫자1;
숫자만큼의 행 출력
Ex) 5행 출력
select * from NOTICE ORDERS LIMIT 5;

Select * from 테이블명 orders LIMIT 숫자1 OFFSET 숫자2;
LIMIT 숫자 : 출력할 행의 수
OFFSET 숫자 : 몇번째 row부터 출력할 지. (1번째 row면 0)
Ex) 5행씩 출력
1페이지 : select * from NOTICE ORDERS LIMIT 5 OFFSET 0;
2페이지 : select * from NOTICE ORDERS LIMIT 5 OFFSET 5;

Select * from 테이블명 orders LIMIT 숫자1(a), 숫자2(b);
숫자1 : a번째 row부터 출력
숫자2 : b개의 행 출력
Ex) 5행씩 출력
1페이지 : select * from NOTICE ORDERS LIMIT 0, 5;
2페이지 : select * from NOTICE ORDERS LIMIT 5, 10;

3번째 방법을 사용해보자.
REGDATE로 역정렬하고 글이 최신순부터 나오게하려면
SELECT * FROM (SELECT * FROM NOTICE ORDER BY REGDATE DESC) ORDERS LIMIT 0, 5;

18. 페이징 쿼리 이용하기

꽃기 위해 값을 ?로 해야한다.

시작 숫자는 1 11 21의 등차수열이다.
end는 5 고정이다.
int start = 0 + (page-1) * 5; // 1 , 11, 21 ,31
int end = 5; //5 10 15 20 페이지 인데 5개 고정으로 보여줌

오라클은 값을 계속 늘려줘야한다. 1~~5 6~~10
int start = 1+ (page -1 *10); //a1 + (n-1)*d(공차); 1,11,21
int end= 10*page; //10 20 30 40

19. 목록을 위한 View 생성하기

공지사항을 가지고 num을 붙이고 햇다. 정렬인상태로 붙엿다.
이런형태의 데이터가 없어서 서브쿼리를 하나씩 넣은 것이다.
이것을 뷰로 만들버리면 편하다.

create view NOTICE_VIEW
AS
select * from (SELECT * FROM NOTICE ORDER BY REGDATE DESC) A;

이걸 가져다 사용하면
SQL문이 한줄로 줄어버리게 된다.
String sql = "SELECT * FROM NOTICE_VIEW ORDERS LIMIT ?, ?";

20. 이전 / 다음 구현하기

현재 페이지가 이전페이지인지 다음페이지인지 기억을 하는게 필요하다.
page값을 메인이 가지고 있어야하는지 console이 가지고 있어야하는지는 선택이다.
console이 page를 사용해야하니 console이 가지고 있는게 낫다.

public void movePrevList() {
    if (page == 1) {
        System.out.println("이전 페이지가 없습니다.");
        return;
    }
    this.page--;
}

public void moveNextList() {
     if(page == lastPage ) { 
        System.out.println("다음 페이지가 없습니다."); 
        return; 
     }
    this.page++;
}

21. 게시글 갯수 구하기

총개수를 얻어내보자.
5개씩이니 몇개 게시글인지를 나누면 총 몇페이지인지 알 수 잇다.
private int count;를 넣어보자.
콘솔의 리스트에서 count = service.getCount();를 넣어주자.
이 count를 어디서 얻어오냐? 리스트를 출력할때 count를 계속가져와야한다.
이런 단일 값을 얻어온다고 할때 Scalar값을 얻어온다라고 한다.

public int getCount() throws SQLException, ClassNotFoundException {
    int count = 0;
    String sql = "SELECT COUNT(ID) COUNT from notice";
    Class.forName(driver);
    Connection con = DriverManager.getConnection(url, uid, pwd);
    PreparedStatement pstmt = con.prepareStatement(sql);
    ResultSet rs = pstmt.executeQuery();

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

    rs.close();
    pstmt.close();
    con.close();

    return count;
}

22. 마지막 페이지 구하기

lastPage를 지역변수로 만드는게 좋은지 전역변수로 만드는게 좋은지 고민해봐야한다.

count는 리스트를 구할때마다 값을 구해야한다. 그래서 공유하는 값으로 사용하면 좋지않다.

int lastPage = count/10;
lastPage = count%10>0?lastPage+1:lastPage ;
총게시물을 보여줄 게시물수로 나누면 총페이지수가 나오긴하는데 나머지가 잇을경우가 문제이다.
나머지가 잇을 경우에는 +1을 하도록 3항연산자를 해주자.
다음페이지에서의 count와 목록의 count가 다를 수잇기때문에 사용하기 위해서는 다시 service객체를 만들어준다.

public void printNoticeList() throws ClassNotFoundException, SQLException {
    List<Notice> list = service.getList(page);
    int count = service.getCount();
    int lastPage = count / 5; // 100 -> 20 90 -> 18
    lastPage = count % 5 > 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 void moveNextList() throws ClassNotFoundException, SQLException {
    int count = service.getCount();
    int lastPage = count / 5; // 100 -> 20 90 -> 18
    lastPage = count % 5 > 0 ? lastPage + 1 : lastPage;

    if (page == lastPage) {
        System.out.println("다음 페이지가 없습니다.");
        return;
    }
    this.page++;
}

23. 검색 메뉴 붙이기

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();
}

24. 검색 서비스 추가하기

String sql = "select * from notice_view WHERE " + field + " LIKE ? LIMIT ?, ?";
pstmt.setString(1, "%" + query + "%"); 넣어주면된다.

2023.03.13 후기

갑자기 내가 급해졋다. 서블릿 기초 보고 jsp시작해보려고 한다.
공부하며 기록을 보니 저번 JDBC를 할때도 이런 고민을 했던 것같다.
항상고민에 빠지게 된다.
본인 스스로 노력을 많이 해야된다고 하지만 솔직히 여전히 길을 잃는 느낌이다.
그래도 블로그는 꾸준히 작성하고 있다.
벌써 2023년이 세달이 지났다. 모든 사람이 좋은 일만 있으면 좋겠다.

'기초단계 > DB&JDBC' 카테고리의 다른 글

2023.03.12 DB (0)	2023.03.13
2023.03.09 DB (0)	2023.03.09
2023.03.08 DB (0)	2023.03.08
2023.03.07 DB (0)	2023.03.07
2023.03.06 DB (1)	2023.03.06

발전하고 싶은 춘핑이