[JDBC] 프로그래밍, 게시판 만들기

ho's·2022년 7월 24일
0

위 글은 김성박선생님의 자바 강의를 바탕으로 쓰여졌습니다.
더 자세한 내용은 아래 링크를 통해 알아보실 수 있습니다.

부부개발단 유투브


JDBC 프로그래밍

1) DBMS접속(Connection, DriverManager라는 클래스)

  • DriverManager라는 클래스는 내부적으로 JDBC Driver를 사용

2) autocommit을 true, false로 할 것인지 결정

  • autocommit을 false로 한다는 것은 트랜잭션을 내가 관리하겠다.

3) SQL 준비 (Connection, PreparedStatement)

  • SQL의 ? 부분을 바인딩

4) SQL 실행 (PreparedStatement, ResultSet(Select))

5) DBMS에서 데이터를 읽어온다. (select문일 경우, ResultSet)

6) ResultSet close();
7) PreparedStatement close
8) DMBS 접속 close

⛳ DBUtil 클래스로 DB접근하기

public class DBUtil {
    public static Connection getConnection() throws SQLException {
        Connection conn =
                DriverManager.getConnection("jdbc:mysql://localhost:3306/classicmodels","root","gkseogh1!23");
        return conn;
    }

    // insert, update, delete - Connection, PreparedStatement
    public static void close(PreparedStatement ps, Connection conn) throws SQLException{
            ps.close();
            conn.close();
    }
    // select - Connection, PreparedStatement, ResultSet

    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException{
        rs.close();
        close(ps,conn);
    }
}

👕 DBUtil 클래스를 이용해 테이블 조회하기


package com.example.jdbcexam01;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConnectionTest02 {
    public static void main(String[] args) {

        try {
            Connection conn = DBUtil.getConnection();
            String sql = "select firstname, lastname from employees";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                String firstname = rs.getString("firstname");
                System.out.println(firstname);
            }
                DBUtil.close(rs, ps, conn);
            }catch(SQLException ex){
                System.out.println("DB error:" + ex.getMessage());
            }
        }
}

🧿 MYSQL과 java를 이용해 간단한 게시판을 만들어 보자.

CREATE TABLE board(
	id INT(11) AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    read_count INT(11) NOT NULL default 0,
    created DATETIME default now(),
    CONSTRAINT board_PK PRIMARY KEY(id)
);

글을 조회할때 read_count가 1씩 증가하는 쿼리문

SELECT id, name, title, count, read_count, created from where id = 1;
update board
set read_count = read_count + 1
where id = 1;

위와 같은 쿼리문을 실행시키면 read_count가 1씩 증가한다.

🔍 board 클래스만들기

위와 같은 타입의 board클래스를 만들어보자.

변수선언하기

package com.example.jdbcexam01;

import java.sql.Date;

public class Board {
    private long id;
    private String name;
    private String title;
    private String content;
    private long readCount;
    private Date created;
}

getter, setter, 생성자 만들기

package com.example.jdbcexam01;

import java.sql.Date;

public class Board {
    private long id;
    private String name;
    private String title;
    private String content;


    public Board(){

    }

    public Board(String name, String title, String content) {
        this.name = name;
        this.title = title;
        this.content = content;
    }

    public Board(long id, String name, String title, String content, long readCount, Date created) {
        this.id = id;
        this.name = name;
        this.title = title;
        this.content = content;
        this.readCount = readCount;
        this.created = created;
    }

    private long readCount;

    public long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    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 long getReadCount() {
        return readCount;
    }

    public void setReadCount(long readCount) {
        this.readCount = readCount;
    }

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    private Date created;
}

DAO클래스 만들기

DAO클래스의 getBoards 메소드

public class BoardDao   List<Board> getBoards(int start, int size){
            List<Board> list = new ArrayList<>();

            try{

            Connection conn = DBUtil.getConnection(); // DB연결
            String sql = "select id, name, title, read_count, created from board order by created desc limit ?,?"; // 정해져있지 않은 값.
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2,size);
            ResultSet rs = ps.executeQuery(); // SQL실행(select문)

            while(rs.next()){
                // 한건의 데이터 로우를 읽어온다.
                long id = rs.getLong("id");
                String name = rs.getString("name");
                String title = rs.getString("title");
                int readcount = rs.getInt("read_count");
                Date created = rs.getDate("created");

                Board board = new Board(id, name, title, null, readcount, created);
                list.add(board);
            }

            DBUtil.close(rs, ps , conn);
        }catch (SQLException ex){

            System.out.println("db error message"+ex.getMessage());

        }
        return list;
    }

DAO클래스의 addBoards 메소드

  • 저장할때는 데이터를 읽어오는 코드가 필요가 없다.
    -> ResultSet을 삭제한다.
  public int addBoard(Board board){
        int updateCount = 0;
        try{
            Connection conn = DBUtil.getConnection(); // DB연결
            String sql = "insert into board(name, title, content) values(?,?,?)"; // 정해져있지 않은 값.
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, board.getName());
            ps.setString(2,board.getTitle());
            ps.setString(3,board.getContent());

            updateCount = ps.executeUpdate(); //executeU

            DBUtil.close(ps , conn);

        }catch (SQLException ex){

            System.out.println("db error message"+ex.getMessage());
        }
        return updateCount;
    }

DAO클래스의 getBoards 메소드

  public Board getBoard(long id){
            Board board = null;
            Connection conn = null;
            try{
             conn = DBUtil.getConnection();
            conn.setAutoCommit(false);

            String sql = "select name, title, content, read_count, created from board where id = ?"; //
            PreparedStatement ps = conn.prepareStatement(sql);  //
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery(); // SQL(select)

            while(rs.next()){
                String name = rs.getString("name");
                String title = rs.getString("title");
                String content = rs.getString("content");
                int readcount = rs.getInt("read_count");
                Date created = rs.getDate("created");
                board = new Board(id, name, title, content, readcount, created);
            }

            ps.close();

            String sql2 = "update board\n" +
                    "set read_count = read_count+ 1\n" +
                    "where id = ?";

            ps = conn.prepareStatement(sql2);
            ps.setLong(1,id);
            ps.executeUpdate();
            conn.commit();
            DBUtil.close(rs, ps , conn);
        }catch (SQLException ex){
                try {
                    conn.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                System.out.println("db error message"+ex.getMessage());
        }
        return board;
    }
  • conn.setAutoCommit(false)로 지정한 후, conn.commit()을 해주어야 한다.

위의 클래스들을 실행시켜보자

조회하기

package com.example.jdbcexam01;

import java.util.List;

public class BoardTest {
    public static void main(String[] args) {
        BoardDao boardDao = new BoardDao();
        List<Board> boards = boardDao.getBoards(0, 30);
        for(Board board : boards){
            System.out.println(board);
        }

    }
}

id로 검색하기

package com.example.jdbcexam01;

public class BoardTest3 {
    public static void main(String[] args) {

        BoardDao boardDao = new BoardDao();
        Board board = boardDao.getBoard(3);
        System.out.println(board);

    }
}

늘어나는 조회수

package com.example.jdbcexam01;

public class BoardTest3 {
    public static void main(String[] args) {

        BoardDao boardDao = new BoardDao();
        Board board = boardDao.getBoard(3);
        System.out.println(board);

    }
}


profile
그래야만 한다

0개의 댓글