07/07 #2

ONLYS2545·2023년 7월 10일

DBCONNECTION 만들기

package db;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
    public static Connection getInstance(){
        // MySQL 연결 정보
        String url = "jdbc:mysql://localhost:3306/metadb";
        String username = "root";
        String password = "2--------";

        // JDBC 드라이버 로드
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(url, username, password);
            System.out.println("디버그 : DB연결 성공");
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

Model 만들기

Board, User 만들기

DTO 만들기

BoardDetailDTO, UserBoardDTO, UserDTO 만들기

DAO 만들기

BoardDAO 만들기

package model;

import dto.BoardDetailDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BoardDAO {
    // C - create (insert)
    // R - read (select)
    // U - update (update)
    // D - delete (delete)

    private Connection connection;

    public BoardDAO(Connection connection) {
        this.connection = connection;
    }

    public BoardDetailDTO findByIdWithUser(Integer id){
        BoardDetailDTO boardDetailDTO = null;
        String sql = "select bt.*, ut.u_username, ut.u_password, ut.u_email\n" +
                "from board_tb bt\n" +
                "left outer join user_tb ut on bt.u_id = ut.u_id\n" +
                "where b_id = ?";

        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                // 오브젝트 매핑 (테이블 데이터 -> 자바 오브젝트)
                boardDetailDTO = new BoardDetailDTO(
                        rs.getInt("b_id"),
                        rs.getInt("u_id"),
                        rs.getString("u_username"),
                        rs.getString("b_title"),
                        rs.getString("b_content")
                );

            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return boardDetailDTO;
    }

    public void insert(Board board){
        String sql = "insert into board_tb(b_title, b_content, u_id) values(?, ?, ?)";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, board.getBTitle());
            pstmt.setString(2, board.getBContent());
            pstmt.setInt(3, board.getUId());
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void delete(Integer id){
        String sql = "delete from board_tb where b_id = ?";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void update(Board board){}

    public List<Board> findAll(){
        List<Board> boardList = new ArrayList<>();
        String sql = "select * from board_tb order by b_id desc";

        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                // 오브젝트 매핑 (테이블 데이터 -> 자바 오브젝트)
                Board board = new Board(
                        rs.getInt("b_id"),
                        rs.getString("b_title"),
                        rs.getString("b_content"),
                        rs.getInt("u_id")
                );
                boardList.add(board);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return boardList;
    }
    public Board findById(Integer id){
        Board board = null;
        String sql = "select * from board_tb where b_id = ?";

        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                // 오브젝트 매핑 (테이블 데이터 -> 자바 오브젝트)
                board = new Board(
                        rs.getInt("b_id"),
                        rs.getString("b_title"),
                        rs.getString("b_content"),
                        rs.getInt("u_id")
                );
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return board;
    }
}

User DAO 만들기

package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    private Connection connection;

    public UserDAO(Connection connection) {
        this.connection = connection;
    }

    public void insert(User user){
        String sql = "insert into user_tb(u_username, u_password, u_email)  values(?, ?, ?)";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, user.getUUsername());
            pstmt.setString(2, user.getUPassword());
            pstmt.setString(3, user.getUEmail());
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void delete(Integer id){
        String sql = "delete from user_tb where u_id = ?";
        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    public void update(User user){}

    public List<User> findAll(){
        List<User> userList = new ArrayList<>();
        String sql = "select * from user_tb order by u_id desc";

        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                // 오브젝트 매핑 (테이블 데이터 -> 자바 오브젝트)
                User user = new User(
                        rs.getInt("u_id"),
                        rs.getString("u_username"),
                        rs.getString("u_password"),
                        rs.getString("u_email")
                );
                userList.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return userList;
    }
    public User findById(Integer id){
        User user = null;
        String sql = "select * from user_tb where u_id = ?";

        try {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                // 오브젝트 매핑 (테이블 데이터 -> 자바 오브젝트)
                user = new User(
                        rs.getInt("u_id"),
                        rs.getString("u_username"),
                        rs.getString("u_password"),
                        rs.getString("u_email")
                );
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return user;
    }
}

BlogApp 만들기

import db.DBConnection;
import dto.BoardDetailDTO;
import dto.UserBoardDTO;
import dto.UserDTO;
import model.Board;
import model.BoardDAO;
import model.User;
import model.UserDAO;

import java.sql.Connection;
import java.util.List;

public class BlogApp {
    public static void main(String[] args) {
        Connection connection = DBConnection.getInstance();

        UserDAO userDAO = new UserDAO(connection);
        BoardDAO boardDAO = new BoardDAO(connection);



        BoardDetailDTO boardDetailDTO = boardDAO.findByIdWithUser(1);
        System.out.println(boardDetailDTO);




//        User user = new User(null, "love", "1234", "love@nate.com");
//        userDAO.insert(user);

//        Board board = boardDAO.findById(1);
//        System.out.println(board.toString());
    }
}