JDBC - 게시글관리

게시판 관리 - 등록

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class Exam {
  public static void main(String[] args) throws Exception {
    // 게시판 설정
    String title = null;
    String contents = null;

    Scanner keyScan = new Scanner(System.in);

    System.out.println("제목?");
    title=keyScan.nextLine();
    System.out.println("내용?");
    contents=keyScan.nextLine();
    System.out.println("등록하시겠습니까?Y/n) ");
    String input = keyScan.nextLine();

    if(!input.equalsIgnoreCase("y") && input.length() !=0) {
      System.out.println("등록을 취소하였습니다.");
      return;
    }

    // JDBC 데이터베이스연결(Connection) -> Statement 생성 -> executeUpdate();
    try(
        Connection con = DriverManager.getConnection(
            "jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
        Statement stsmt = con.createStatement();) {

      String sql = String.format(
          "insert into x_board(title, contents) values('%s', '%s')", title, contents);
      int count = stsmt.executeUpdate(sql);
      System.out.printf("%d 개 입력 성공!", count);
    }
  }
}

게시글 관리 - 목록

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Exam {
  public static void main(String[] args) throws Exception {

    // JDBC 데이터베이스연결(Connection) -> Statement 생성 -> executeQuery로 쿼리를 수행
    // ->수행결과를 ResultSet에 담아둔다 -> next()로 한 줄씩 읽는다.
    try(
        Connection con = DriverManager.getConnection(
            "jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
        Statement stsmt = con.createStatement();
        ResultSet rs = stsmt.executeQuery(
            "select board_id, title, created_date, view_count from x_board order by board_id desc")
        ) {

      System.out.println("번호, 제목, 등록일, 조회수");
      while(rs.next()) {
        System.out.printf("%d, %s, %s, %d\n",
            rs.getInt("board_id"),
            rs.getString("title"),
            rs.getString("created_date"),
            rs.getInt("view_count"));
      }
    }
  }
}

게시글 관리 - 조회

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Exam {
  public static void main(String[] args) throws Exception {

    // 조회할 게시글 번호
    String no = null;
    // 게시글 번호 입력받음
    Scanner keyScan = new Scanner(System.in);
    System.out.println("번호는?");
    no = keyScan.nextLine();

    // JDBC 부분 
    // DB에 연결(Connection) -> Statement 생성 -> executeQuery()로 쿼리문 실행
    // -> ResultSet에 저장
    Connection con = DriverManager.getConnection(
        "jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
        "select * from x_board where board_id = "+no);

    // next()로 하나씨 출력
    if(rs.next()) {
      System.out.printf("제목 : %s\n", rs.getString("title"));
      System.out.printf("내용 : %s\n", rs.getString("contents"));
      System.out.printf("등록일: %s\n", rs.getString("created_date"));
      System.out.printf("조회수 : %s\n", rs.getString("view_count"));
    } else {
      System.out.println("해당 번호의 게시물이 존재하지 않습니다");
    }
  }
}

게시글 관리 - 변경

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class Exam {
  public static void main(String[] args) throws Exception{

    String no = null;
    String title = null;
    String contents =null;
    Scanner keyScan = new Scanner(System.in);

    // 변경할 게시글을 찾고 변경할 내용을 입력받음
    System.out.print("번호는?");
    no=keyScan.nextLine();

    System.out.print("제목?");
    title = keyScan.nextLine();

    System.out.print("내용?");
    contents = keyScan.nextLine();

    try(
        Connection con = DriverManager.getConnection(
            "jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
        Statement stmt = con.createStatement()){

      String sql = String.format(
          "update x_board set title = '%s', contents = '%s', where board_id = %s",
          title, contents, no);
      int count = stmt.executeUpdate(sql);

      if(count ==0) {
        System.out.println("해당 번호의 게시글이 존재하지 않습니다");
      } else {
        System.out.println("변경하였습니다");
      }
    }
  }
}

게시판 관리 - 삭제

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Scanner;

public class Exam {
  public static void main(String[] args) throws Exception{

    String no = null;

    Scanner keyScan = new Scanner(System.in);
    System.out.print("번호? ");
    no=keyScan.nextLine();

    Connection con = DriverManager.getConnection(
        "jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();
    
    int count = stmt.executeUpdate(
        "delete from x_board where board_id=" + no);

    if(count ==0) {
      System.out.println("해당 번호의 게시글이 존재하지 않습니다");
    } else {
      System.out.println("삭제하였습니다.");
    }
  }
}

JDBC 코드를 별도의 클래스로 캡슐화

BaordDao.class

데이터를 처리하는 코드를 별도의 클래스로 캡슐화 시킨다.
=> Data 영속성(지속성)을 관리하는 클래스를 DAO(Data Access Object)라 부른다.
=> Data 영속성(지속성)?
==> 데이터를 저장하고 유지하는 것, 데이터 퍼시스턴스(persistence)라 부른다.

package com.eomcs.jdbc.ex1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BoardDao {
  
  // 게시글 삭제
  public int delete(int no) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();

    // 첨부파일 삭제
    stmt.executeUpdate("delet from x_board_file where board_id="+no);
    // 게시글 삭제
    return stmt.executeUpdate("delete from x_board where board_id="+no);
  }

  // 게시글 목록보기
  public List<Board> findAll() throws Exception {
    Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();
    ResultSet rs=stmt.executeQuery("select * from x_board order by board_id desc");

    ArrayList<Board> list = new ArrayList<>();
    while(rs.next()) {
      Board board = new Board();
      board.setNo(rs.getInt("board_id"));
      board.setTitle(rs.getString("board_title"));
      board.setContent(rs.getString("board_contents"));
      board.setRegisteredDate(rs.getDate("created_date"));
      board.setViewCount(rs.getInt("view_count"));
      list.add(board);
    }
    return list;
  }

  // 게시글 삽입
  public int insert(Board board) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();

    String sql = String.format(
        "insert into x_board(title,contents) values ('%s', '%s')",
        board.getTitle(), board.getContent());
    return stmt.executeUpdate(sql);
  }

  // 게시글 수정
  public int update(Board board) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();

    String sql = String.format(
        "update x_board set title='%s', contents='%s' where board_id='%d'",
        board.getTitle(), board.getContent(), board.getNo());
    return stmt.executeUpdate(sql);
  }

  // 게시글 조회
  public Board findBy(String no) throws Exception {
    Connection con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/studydb?user=study&password=1111");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select * from x_board where board_id="+no);

    if(rs.next()) {
      Board board = new Board();
      board.setNo(rs.getInt("board_id"));
      board.setTitle(rs.getString("title"));
      board.setContent(rs.getString("content"));
      board.setRegisteredDate(rs.getDate("created_date"));
      board.setViewCount(rs.getInt("view_count"));
      return board;
    } else {
      return null;
    }
  }
}

Board.class

DAO와 서비스 객체 사이에서 데이터를 실어나르는 용도로 사용한다.
=> DTO(Data Transfer Object)라 부른다.
=> 도메인객체(domain)라고 부른다.
=> VO(value Object)라 부른다.

package com.eomcs.jdbc.ex1;

import java.io.Serializable;
import java.util.Date;

// 외부 저장소로 객체를 내보낼 수 있도록 serial 기능을 활성화
public class Board implements Serializable {
  private static final long serialVersionUID = 1L;

  // DB 테이블의 칼럼 값을 저장할 인스턴스 변수를 준비한다. (DB 칼럼명과는 다르게!)
  int no;
  String title;
  String content;
  Date registeredDate;
  int viewCount;

  @Override
  public String toString() {
    return "Board [no=" +no+", title="+title+",content="+content+", registeredDate="+registeredDate+",viewCount="+viewCount+"]";
  }


  // 셋터(set)와 겟터(get) 생성
  public int getNo() {
    return no;
  }

  public void setNo(int no) {
    this.no=no;
  }

  public String getTitle() {
    return title;
  }

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

  public String getContent() {
    return content;
  }

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

  public Date getRegisteredDate() {
    return registeredDate;
  }

  public void setRegisteredDate(Date registeredDate) {
    this.registeredDate=registeredDate;
  }

  public int getViewCount() {
    return viewCount; 
  }

  public void setViewCount(int viewCount) {
    this.viewCount = viewCount;
  }

}

게시글 등록

package com.eomcs.jdbc.ex1;

import java.util.Scanner;

public class Exam {
  public static void main(String[] args) throws Exception{

    Board board = new Board();
    Scanner keyScan = new Scanner(System.in);

    System.out.print("제목?");
    board.setTitle(keyScan.nextLine());

    System.out.print("내용?");
    board.setContent(keyScan.nextLine());
    
    System.out.print("입력하시겠습니까?(Y/n)");
    String input = keyScan.nextLine();

    if(!input.equalsIgnoreCase("y")&&input.length()!=0) {
      System.out.println("등록을 취소 하였습니다");
      return;
    }
    
    BoardDao boardDao = new BoardDao();
    int count = boardDao.insert(board);
    System.out.printf("%d 개 입력 성공!", count);

  }
}