[개인 프로젝트] 게시판 만들기 2 - DTO, DAO(User, Post, LoginInfo) 작성

DTO, DAO 작성

sql로 테이블 설계를 완료했으니, 이제 java 코드에서 데이터를 다루기 위해 DTO와 DAO를 작성하겠습니다.

 

1. DTO(Data Transfer Object)

DTO는 데이터베이스에서 가져온 데이터를 담아 전달하는 객체입니다.

- 역할: Controller ↔ Service ↔ DAO 계층 간에 데이터를 주고받을 때 사용.

- 특징: 비즈니스 로직은 없고, 순수하게 데이터만 보관.

User DTO

package com.example.board.dto;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

import java.time.LocalDateTime;

@Setter
@Getter
@NoArgsConstructor
@ToString
public class User {
    private int userId;
    private String name;
    private String email;
    private String password;
    private String profileImage;
    private LocalDateTime createdAt;
    private LocalDateTime lastLogin;
}

 

Post DTO

package com.example.board.dto;


import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

import java.time.LocalDateTime;

@Setter
@Getter
@NoArgsConstructor
@ToString
public class Post {
    private int postId;
    private int userId;
    private String title;
    private String content;
    private int viewCount;
    private boolean isPublic;
    private LocalDateTime createdAt;
    private LocalDateTime updatedAt;
}

 

2. DAO (Data Access Object)

DAO는 실제 데이터베이스와 연결되어 SQL을 실행하는 계층입니다.

- 역할: DB와 직접 통신 → DTO 객체로 변환해 반환.

- 특징: SQL 쿼리를 작성하고 실행하는 부분을 한 곳에 모아 관리.

 

User DAO

package com.example.board.dao;

import com.example.board.dto.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcInsertOperations;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

@Repository
public class UserDao {
    private final NamedParameterJdbcTemplate jdbcTemplate;
    private final SimpleJdbcInsertOperations insertUser;

    public UserDao(DataSource dataSource) {
        jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        insertUser = new SimpleJdbcInsert(dataSource).withTableName("user").usingGeneratedKeyColumns("userId");
    }

    @Transactional
    public User addUser(String name, String email, String password) {
        User user = new User();
        user.setName(name);
        user.setEmail(email);
        user.setPassword(password);

        SqlParameterSource params = new BeanPropertySqlParameterSource(user);
        Number number = insertUser.executeAndReturnKey(params);

        int userId = number.intValue();
        user.setUserId(userId);

        return user;
    }

    @Transactional
    public void mappingUserRole(int userId) {
        String sql = "insert into user_role(userId, roleId) values (:userId, 1);";
        SqlParameterSource params = new MapSqlParameterSource("userId", userId);
        jdbcTemplate.update(sql, params);
    }

    @Transactional
    public User getUser(String email) {
        try {
            String sql = "select userId, name, email, password, profileImage, createdAt, lastLogin from user where email = :email";
            SqlParameterSource params = new MapSqlParameterSource("email", email);
            RowMapper<User> rowMapper = BeanPropertyRowMapper.newInstance(User.class);
            User user = jdbcTemplate.queryForObject(sql, params, rowMapper);
            return user;
        } catch (Exception ex) {
            return null;
        }
    }

    @Transactional
    public List<String> getRoles(int userId) {
        String sql = "select r.name from user_role ur, role r where ur.roleId = r.roleId and ur.userId = :userId";
        List<String> roles = jdbcTemplate.query(sql, Map.of("userId", userId), (rs, rowNum) -> {
            return rs.getString(1);
        });

        return roles;
    }
}

 

Post DAO

package com.example.board.dao;

import com.example.board.dto.Post;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcInsertOperations;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Repository
public class PostDao {
    private final NamedParameterJdbcTemplate jdbcTemplate;
    private final SimpleJdbcInsertOperations insertPost;

    public PostDao(DataSource dataSource) {
        jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        insertPost = new SimpleJdbcInsert(dataSource).withTableName("post").usingGeneratedKeyColumns("postId");
    }

    @Transactional
    public void addPost(int userId, String title, String content, boolean isPublic) {
        Post post = new Post();
        post.setUserId(userId);
        post.setTitle(title);
        post.setContent(content);
        post.setPublic(isPublic);
        post.setCreatedAt(LocalDateTime.now());
        post.setUpdatedAt(LocalDateTime.now());

        SqlParameterSource params = new BeanPropertySqlParameterSource(post);
        insertPost.execute(params);
    }

    @Transactional (readOnly = true)
    public int getTotalCount() {
        String sql = "select count(*) as totalCount from post";
        Integer totalCount = jdbcTemplate.queryForObject(sql, Map.of(), Integer.class);
        return totalCount.intValue();
    }

    @Transactional (readOnly = true)
    public List<Post> getPosts(int page) {
        int start = (page - 1) * 10;
        String sql = "select p.userId, p.postId, p.title, p.createdAt, p.updatedAt, p.viewCount, p.isPublic, u.name from post p, user u where p.userId = u.userId order by postId desc limit :start, 10";
        RowMapper<Post> rowMapper = BeanPropertyRowMapper.newInstance(Post.class);
        List<Post> list = jdbcTemplate.query(sql, Map.of("start", start), rowMapper);
        return list;
    }

    @Transactional (readOnly = true)
    public Post getPost(int postId) {
        String sql = "select p.userId, p.postId, p.title, p.createdAt, p.updatedAt, p.viewCount, p.isPublic, u.name, p.content from post p, user u where p.userId = u.userId and p.postId = :postId";
        RowMapper<Post> rowMapper = BeanPropertyRowMapper.newInstance(Post.class);
        Post post = jdbcTemplate.queryForObject(sql, Map.of("postId", postId), rowMapper);
        return post;
    }

    @Transactional
    public void updateViewCount(int postId) {
        String sql = "update post set viewCount = viewCount + 1 where postId = :postId";
        jdbcTemplate.update(sql, Map.of("postId", postId));
    }

    @Transactional
    public void deletePost(int postId) {
        String sql = "delete from post where postId = :postId";
        jdbcTemplate.update(sql, Map.of("postId", postId));
    }

    @Transactional
    public void updatePost(int postId, String title, String content, boolean isPublic) {
        String sql = "update post set title = :title, content = :content, isPublic = :isPublic where postId = :postId";

        Post post = new Post();
        post.setPostId(postId);
        post.setTitle(title);
        post.setContent(content);
        post.setPublic(isPublic);
        SqlParameterSource params = new BeanPropertySqlParameterSource(post);
        jdbcTemplate.update(sql, params);
    }
}