2023.03.13 Java 복습

20. 데이터베이스 입출력

20.9 데이터 읽기

SELECT문은 executeQuery()메소드를 호출해야한다. 가져온 데이터를 ResultSet에 저장하고 리턴한다.

20.9.1 ResultSet 구조

ResultSet은 SELECT문에 기술된 컬럼으로 구성된 행의 집합이다.
ResultSet의 특징은 커서(cursor)가 있는 행의 데이터만 읽을 수 있다는 것이다.
여기서 커서는 행을 가리키는 포인터를 말한다.

ResultSet은 실제 가져온 데이터 행의 앞과 뒤에 beforeFirst 행과 afterLast 행이 붙는데
최초 커서는 beforeFirst를 가리킨다.
따라서 첫 번째 데이터 행인 first 행을 읽으려면 커서를 이동시켜야 한다.
이때 next() 메소드를 사용한다.

next() 메소드는 커서를 다음 행으로 이동시키는데, 이동한 행에 데이터가 있으면 true를, 없으면 false를 리턴한다.

1개의 데이터행만 가져온다면 IF문 여러개의 행이라면 WHILE문을 통해서 반복호출하면된다.

20.9.2 데이터행 읽기

커서가 있는 데이터행에서 각 컬럼의 값은 Getter메소드로 읽을 수 있다.
컬럼의 데이터 타입에 따라 사용하면된다.

만약 SELECT 문에 연산식이나 함수 호출이 포함되어 있다면 컬럼 이름 대신에 컬럼 순번으로 읽어야 한다.

20.9.3 사용자 정보 읽기

users테이블에서 userid가 winter인 사용자의 정보를 가져와 출력해보자.

userid가 winter인 사용자 정보를 가져오는 SELECT 문은 다음과 같다
SELECT userid, username, userpassword, userage, useremail FROM users WHERE userid= 'winter'

조건절의 값을 ?로 대체하고 매개변수화 하여 String에 넣으면된다.

매개변수화 된 sql문을 PreparedStatement를 넣고 값을 지정한다.

executeQuery() 메소드로 SELECT 문을 실행해서 ResultSet을 얻는다.

// sql문
String sql = "SELECT * FROM users WHERE userid=?";

// prepared
PreparedStatement pstmt = conn.prepareStatement(sql);
// 값대입
pstmt.setString(1, "winter");

// sql문 실행후 resultset
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    User user = new User();
    user.setUserId(rs.getString("userid"));
    user.setUserName(rs.getString("username"));
    user.setUserPassword(rs.getString("userpassword"));
    user.setUserAge(rs.getInt("userage"));
    user.setUserEmail(rs.getString("useremail"));
    System.out.println(user);
} else {
    System.out.println("사용자아이디가 존재하지 않음");
}

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

20.9.4 게시물정보읽기

bwriter가 winter인 게시물 정보를 가져오는 SELECT 문은 다음과 같다
SELECT bno, btitle, bcontent, bwriter, bdate, bfilename, bfiledata FROM boards WHERE bwriter= 'winter';

조건절의 값을 ?로 대체하고 매개변수화 하여 String에 넣으면된다.

매개변수화 된 sql문을 PreparedStatement를 넣고 값을 지정한다.

executeQuery() 메소드로 SELECT 문을 실행해서 ResultSet을 얻는다.

Blob 객체에 저장된 바이너리 데이터를 얻기 위해서는 다음과 같이 입력 스트림 또는 배열을 얻어내야 한다.

Blob blob = board.getBfiledata();
InputStream is =  blob.getBinaryStream();
Blob blob = board.getBfiledata();
byte[] bytes = blob.getBytes(0, blob.length())

InputStream is = blob.getBinaryStream();
OutputStream os = new FileOutputStream("C:/Temp/" + board.getBfilename());
is.transferTo(os);
os.flush();
os.close();
is.close();

---

//sql문
String sql = "SELECT * FROM boards WHERE bwriter=?";

// prepared
PreparedStatement pstmt = conn.prepareStatement(sql);
// 값대입
pstmt.setString(1, "winter");

// sql문 실행후 resultset
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    Board board = new Board();
    board.setBno(rs.getInt("bno"));
    board.setBtitle(rs.getString("btitle"));
    board.setBcontent(rs.getString("bcontent"));
    board.setBwriter(rs.getString("bwriter"));
    board.setBdate(rs.getDate("bdate"));
    board.setBfilename(rs.getString("bfilename"));
    board.setBfiledata(rs.getBlob("bfiledata"));
    System.out.println(board);
    // 파일로저장
    Blob blob = board.getBfiledata();
    if (blob != null) {
        InputStream is = blob.getBinaryStream();
        OutputStream os = new FileOutputStream("C:/Temp/" +
                board.getBfilename());
        is.transferTo(os);
        os.flush();
        os.close();
        is.close();
    }
}
rs.close();

//pstmt 닫기
pstmt.close();

20.10 트랜잭션 처리

트랜잭션transaction은 기능 처리의 최소 단위를 말한다.
하나의 기능은 여러 가지 소작업들로 구성될 수 있다.
최소 단위란 것은 이 소작업들을 분리할 수 없으며, 전체를 하나로 본다는 개념이다.
트랜잭션은 소작업들이 모두 성공하거나 모두 실패해야 한다

예를 들어 계좌 이체는 출금 작업과 입금 작업으로 구성된 트랜잭션이다.
출금과 입금 작업 중 하나만 성공할 수 없으며, 모두 성공하거나 모두 실패되어야 한다.

계좌이체는 DB의 입장에서보면 두개의 계좌 금액을 수정하는 작업이다.
두개의 UPDATE문으로 모두 성공하거나 실패해야한다.

DB는 트랜잭션을 처리하기 위해 커밋commit과 롤백rollback을 제공한다.
JDBC에서는 INSERT, UPDATE, DELETE 문을 실행할 때마다 자동 커밋이 일어난다.
이 기능은 계좌 이체와 같이 두 가지 UPDATE 문을 실행할 때 문제가 된다.
출금 작업이 성공되면 바로 커밋이 되기 때문에 입금 작업의 성공 여부와 상관없이 출금 작업만 별도 처리된다.

따라서 JDBC에서 트랜잭션을 코드로 제어하려면 자동 커밋 기능을 꺼야 한다.
자동 커밋 설정 여부는 Connection의 setAutoCommit() 메소드로 할 수 있다.
다음 코드는 자동 커밋 기능을 끈다.
conn.setAutoCommit(false)

자동 커밋 기능이 꺼지면,
conn.commit(); //커밋하기
conn.rollback(); //롤백하기
위와 같은 코드로 커밋과 롤백을 제어할 수 있다.

트랜잭션을 위한 일반적인 코드작성패턴은 다음과 같다.
//트랜잭션 시작 ----------------------------------------------------
//자동 커밋 기능 끄기
conn.setAutoCommit(false);
//소작업 처리
…
//소작업 처리
…
//커밋 -> 모두 성공 처리
conn.commit();
//트랜잭션 종료 ----------------------------------------------------
에외발생시
//롤백 -> 모두 실패 처리
conn.rollback();
finally에선 원래대로 자동커밋을 켜주어야한다.

트랜잭션을 처리한 이후에는 원래대로 자동 커밋 기능을 켜줘야 한다.
Connection을 다른 기능 처리를 위해 계속 사용해야 한다면 setAutoCommit(true) 코드로 자동 커밋 기능을 켜둬야 한다.
특히 커넥션 풀 Connection Pool을 사용할 때 주의해야 할 부분이다

// 트랜잭션 시작 ---------------------------------------------------
    // 자동 커미 기능 끄기
    conn.setAutoCommit(false);

    // 출금작업
    String sql1 = "UPDATE accounts SET balance=balance-? WHERE ano= ?";
    PreparedStatement pstmt1 = conn.prepareStatement(sql1);
    pstmt1.setInt(1, 10000);
    pstmt1.setString(2, "111-111-1111");
    int rows1 = pstmt1.executeUpdate();
    System.out.println(rows1);
    if (rows1 == 0) {
        throw new Exception("출금되지 않았음");
    }
    pstmt1.close();

    // 입금작업
    String sql2 = "UPDATE accounts SET balance=balance+? WHERE ano= ?";
    PreparedStatement pstmt2 = conn.prepareStatement(sql2);
    pstmt2.setInt(1, 10000);
    pstmt2.setString(2, "222-222-2222");
    int rows2 = pstmt2.executeUpdate();
    if (rows2 == 0) {
        throw new Exception("입금되지 않았음");
    }
    pstmt2.close();

    //수동 커밋 -> 모두 성공처리
    conn.commit();
    System.out.println("계좌이체성공");
} catch (Exception e) {
    try {
        conn.rollback();
        e.printStackTrace();
    } catch (SQLException e1) {
        System.out.println("계좌이체실패");
    }
} finally {
    try {
        //원래대로 자동 커밋 기능 켜기
        conn.setAutoCommit(true);
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
}

20.11 게시판 구현

JDBC를 활용해서 CMD에서 실행되는 게시판을 구현해보자.
게시판은 기본적인 CRUD(Create Read Update Delete)기능이 포함되어 있는 가장 좋은 실습 주제이다.
이것은 어떤 프로젝트를 하든지 다 들어가게 된다.

기능을 하나씩 추가해나가면 된다.

20.11.1 메인메뉴

메인메뉴 게시물 기능 보여주기

public void list() {
    System.out.println();
    System.out.println("[게시물 목록]");
    System.out.println("-------------------------------------------------------");
    System.out.printf("%-6s%-12s%-16s%-40s\n", "no", "writer", "date", "title");
    System.out.println("-------------------------------------------------------");
    System.out.printf("%-6s%-12s%-16s%-40s \n",
    "1", "winter", "2022.01.27", "게시판에 오신 것을 환영합니다.");
    System.out.printf("%-6s%-12s%-16s%-40s \n",
    "2", "winter", "2022.01.27", "올 겨울은 많이 춥습니다.");
    mainMenu();
}

public void mainMenu() {
    System.out.println();
    System.out.println("-------------------------------------------------------");
    System.out.println("메인 메뉴: 1.Create | 2.Read | 3.Clear | 4.Exit");
    System.out.print("메뉴 선택: ");
    System.out.println();
}

20.11.2 메인메뉴선택기능

1~4 선택시 해당 메소드가 실행되도록 작성하기

public void mainMenu() {
    System.out.println();
    System.out.println("-------------------------------------------------------");
    System.out.println("메인 메뉴: 1.Create | 2.Read | 3.Clear | 4.Exit");
    System.out.print("메뉴 선택: ");
    int menuNo = Integer.parseInt(sc.nextLine());
    System.out.println();

    switch (menuNo) {
    case 1:
        create();
        break;
    case 2:
        read();
        break;
    case 3:
        clear();
        break;
    case 4:
        exit();
        break;
    }
}

20.11.3 Board클래스 작성

값을 담을 entity인 Board클래스를 작성한다.
getter와 setter를 작성하고 tostring을 작성한다.

public class Board {
    private int bno;
    private String btitle;
    private String bcontent;
    private String bwriter;
    private Date bdate;
}

20.11.4 게시물 목록 기능

boards테이블에서 모든 게시물 정보들을 가져온 다음 게시물 목록으로 출력시켜보자.
생성자에서 db를 연결한다.

String sql = "SELECT bno, bwriter, bdate, btitle FROM boards ORDER BY bno DESC";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    Board board = new Board();
    board.setBno(rs.getInt("bno"));
    board.setBtitle(rs.getString("btitle"));
    board.setBwriter(rs.getString("bwriter"));
    board.setBdate(rs.getDate("bdate"));

    System.out.printf("%-6s%-12s%-16s%-40s\n",
            board.getBno(),
            board.getBwriter(),
            board.getBdate(),
            board.getBtitle());
}

20.11.5 게시물 생성기능

메인 메뉴에서 ‘1.Create’를 선택하면 새로운 게시물의 제목, 내용, 작성자를 키보드로 입력받고,
보조 메뉴에서 ‘1.Ok’를 선택하면 boards 테이블에 새로운 게시물이 저장되도록 해보자.

public void create() {
    // 입력받기
    Board board = new Board();
    System.out.println("[새 게시물 입력]");
    System.out.print("제목: ");
    board.setBtitle(sc.nextLine());
    System.out.print("내용: ");
    board.setBcontent(sc.nextLine());
    System.out.print("작성자: ");
    board.setBwriter(sc.nextLine());

    //보조메뉴출력
    int menuNo = subMenu();

    if(menuNo == 1) {
        try {
            String sql = "INSERT INTO boards(btitle, bcontent, bwriter, bdate) "
                    + "VALUES (?,?,?,now())";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, board.getBtitle());
            pstmt.setString(2, board.getBcontent());
            pstmt.setString(3, board.getBwriter());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            exit();
        }
    } 
    list();
}

20.11.6 게시물 읽기기능

메인 메뉴에서 ‘2.Read’를 선택했을 때 게시물의 번호를 키보드로 입력받고,
boards 테이블에 있는 해당 게시물을 가져와서 출력해보자.

public void read() {
    // 게시물 읽기
    System.out.println("[게시물 읽기]");
    System.out.print("bno: ");
    int bno = Integer.parseInt(sc.nextLine());

    try {
        String sql = "SELECT * FROM boards WHERE bno = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, bno);

        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            Board board = new Board();
            board.setBno(rs.getInt("bno"));
            board.setBtitle(rs.getString("btitle"));
            board.setBcontent(rs.getString("bcontent"));
            board.setBwriter(rs.getString("bwriter"));
            board.setBdate(rs.getDate("bdate"));
            System.out.println("############");
            System.out.printf("번호: %d\n", board.getBno());
            System.out.printf("제목: %s\n", board.getBtitle());
            System.out.printf("내용: %s\n", board.getBcontent());
            System.out.printf("작성자: %s\n", board.getBwriter());
            System.out.printf("날짜: %s\n", board.getBdate());
            }
        }
        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
        exit();
    }
    // 게시물 목록 출력
    list();
}

20.11.7 게시물 수정기능

게시물 읽기에서 보조 메뉴를 추가하고, 보조 메뉴에서 ‘1.Update’를 선택했을 때
제목, 내용, 작성자의 수정 내용을 입력할 수 있도록 해보자.

public void update(Board board) {
    // 입력받기
    System.out.println("[수정 내용 입력]");
    System.out.print("제목: ");
    board.setBtitle(sc.nextLine());
    System.out.print("내용: ");
    board.setBcontent(sc.nextLine());
    System.out.print("작성자: ");
    board.setBwriter(sc.nextLine());

    // 보조메뉴출력
    int menuNo = subMenu();

    if (menuNo == 1) {
        try {
            String sql = "UPDATE boards SET btitle = ?, bcontent = ? , bwriter= ? WHERE bno = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, board.getBtitle());
            pstmt.setString(2, board.getBcontent());
            pstmt.setString(3, board.getBwriter());
            pstmt.setInt(4, board.getBno());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            exit();
        }
    }
    list();
}

20.11.8 게시물 삭제 기능

public void delete(Board board) {
    // boards 테이블에 게시물 정보삭제
    int menuNo = subMenu();

    if (menuNo == 1) {
        try {
            String sql = "DELETE FROM boards WHERE bno = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, board.getBno());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            exit();
        }
    }
}

20.11.9 게시물 전체 삭제 기능

메인 메뉴에서 ‘3.Clear’를 선택하고 보조 메뉴에서 ‘1.Ok’를 선택했을 때 boards 테이블의 전체
게시물 정보를 삭제하기

public void clear() {
    // boards 테이블에 게시물 정보삭제
    int menuNo = subMenu();

    if (menuNo == 1) {
        try {
            String sql = "TRUNCATE TABLE boards";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            exit();
        }
    }
    list();
}

TRUNCATE와 DELETE의 차이점!!
TRUNCATE는 전체 데이터를 삭제한다
정확하게 말하면 TRUNCATE는 테이블을 DROP후 CREATE한다.
그렇기때문에 AUTO_INCREMENT로 생성된 ID도 초기화되며 다음 생성되는 ID는 1부터 시작한다.

20.11.10 종료기능

메인 메뉴에서 ‘4.Exit’를 선택하면 Connection을 닫고 프로그램을 종료

public void exit() {
    try {
        System.out.println("**게시판 종료**");
        conn.close();
        System.exit(0);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

2023.03.13 후기

자바 스스로 하는 복습의 끝이 나오고 있다.
내일 이어서 로그인기능 등 추가할예정이다.
그리고 jsp와 servlet으로 넘어가고자 한다. 한번 했지만 기억이 잘 나지 않는다. 기본부터 천천히 쌓아가고 싶다.