[스프링부트] Jdbc Template 적용하기

YTT.erica·2024년 6월 10일
BEKCS스프링부트

config 설정

WebConfig

  • cors 설정
  • 이미지 주소 설정
package com.kcs.community.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebConfig implements WebMvcConfigurer {
  @Bean
  public WebMvcConfigurer corsConfigurer() {
    return new WebMvcConfigurer() {
      @Override
      public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**")
            .allowedOrigins("http://localhost:3000")
            .allowedMethods("GET", "POST", "PATCH", "DELETE", "OPTIONS")
            .allowedHeaders("*")
            .allowCredentials(true);
      }

      @Override
      public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/images/post/**")
            .addResourceLocations("file:src/main/resources/static/images/post/");
        registry.addResourceHandler("/images/profile/**")
            .addResourceLocations("file:src/main/resources/static/images/profile/");
      }
    };
  }
}

Entity, Controller, Service, Repository, DTO

Entity

BaseEntity: 날짜

컬럼

  • created_at
  • updated_at
  • 코드
    @MappedSuperclass
@Getter
@SuperBuilder
@EntityListeners(value={AuditingEntityListener.class})
@NoArgsConstructor
@AllArgsConstructor
public abstract class BaseEntity {
  @CreatedDate
  @Column(name="created_at", updatable = false)
  private Timestamp created_at;

  @LastModifiedDate
  @Column(name="updated_at")
  private Timestamp updated_at;
}

User: 유저

컬럼

  • user_id: 유저 아이디
  • profile_image: 프로필 이미지
  • password: 비밀번호
  • nickname: 닉네임
  • username: (spring security에서 사용할 것, email과 동일 값)
  • email: 이메일
  • roles: 권한(spring security에서 사용)
  • 코드
    @Entity
@Data
@Getter
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class Member extends BaseEntity implements UserDetails {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long user_id;

  private String email;
  private String profile_image;
  private String password;
  private String nickname;
  private String roles;

  @Override
  public Collection<? extends GrantedAuthority> getAuthorities() {
    return Collections.singletonList(new SimpleGrantedAuthority(this.roles));
  }

  @Override
  public String getUsername() {
    return this.email;
  }

  @Override
  public boolean isAccountNonExpired() {
    return true;
  }

  @Override
  public boolean isAccountNonLocked() {
    return true;
  }

  @Override
  public boolean isCredentialsNonExpired() {
    return true;
  }

  @Override
  public boolean isEnabled() {
    return true;
  }
}

Post: 게시글

  • post_id: 게시글 id
  • post_image: 게시글 사진
  • title: 제목
  • content: 내용
  • type: 게시글 타입
  • view: 조회 수
  • comment_count: 댓글 수
  • 코드
    @Entity
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class Post extends BaseEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long post_id;

  private Long user_id;
  private String post_image;
  private String title;
  private String content;
  private String type;
  private Long view;
  private Long comment_count;

  @Transient
  private Member member;
}

Comment: 댓글

  • comment_id: 댓글 id
  • comment: 댓글 내용
  • user_id: 작성자
  • post_id: 게시글
  • 코드
    package com.kcs.community.entity;

import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.SuperBuilder;

@Entity
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class Comment extends BaseEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long comment_id;

  private String comment;
  private Long user_id;
  private Long post_id;

  @Transient
  private Member member;
}

Controller

MemberController

공통 url: /api/users

methodapi설명
GET/user로그인 한 유저 정보
GET/myWrite자신이 작성한 글 수, 댓글 수
POST/signup회원가입
POST/login로그인
POST/email/{email}중복된 이메일 체크
POST/signup/nickname/{nickname}회원가입시 중복 닉네임 체크
POST/nickname/{nickname}중복된 닉네임 체크
PATCH/user/profile프로필 정보 수정
PATCH/user/password비밀번호 수정
DELETE/user회원탈퇴
  • 코드
    @Log4j2
@RequestMapping("/api/users")
@RestController
public class MemberController {
  @Autowired
  private MemberService memberService;

  @Autowired
  JwtTokenProvider jwtTokenProvider;

  @GetMapping("/user")
  public ResponseEntity<Map<String, Object>> getUser(@RequestHeader(name = "Authorization") String token) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Member member = memberService.find(userIdFromToken);
    return ResponseUtil.createResponse(200, member, null);
  }

  @GetMapping("/myWrite")
  public ResponseEntity<Map<String, Object>> getUserWrite(@RequestHeader("Authorization") String token) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    CountDto count = memberService.findUserWrite(userIdFromToken);
    return ResponseUtil.createResponse(200, count, null);
  }

  @PostMapping("/signup")
  public ResponseEntity<Map<String, Object>> createUser(@RequestBody MemberDto dto) {
    Long created = memberService.create(dto);
    if (created == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(201, created, "sign_up_success");
  }

  @PostMapping("/logIn")
  public ResponseEntity<Map<String, Object>> logInUser(@RequestBody MemberDto dto) {
    JwtTokenDto logIn = memberService.findLogIn(dto);
    if (logIn == null) {
      return ResponseUtil.createResponse(404, null, "user_not_found");
    }

    Map<String, Object> data = new HashMap<>();
    data.put("token", logIn.getAccessToken());
    return ResponseUtil.createResponse(200, data, "log_in_success");
  }

  @PatchMapping("/user/profile")
  public ResponseEntity<Map<String, Object>> updateProfile(@RequestHeader("Authorization") String token, @RequestBody MemberDto dto) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Long updated = memberService.updateProfile(userIdFromToken, dto);
    if (updated == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(200, updated, "update_success");
  }

  @PatchMapping("/user/password")
  public ResponseEntity<Map<String, Object>> updatePassword(@RequestHeader("Authorization") String token, @RequestBody MemberDto dto) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Long updated = memberService.updatePassword(userIdFromToken, dto);
    if (updated == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(200, updated, "update_success");
  }

  @PostMapping("/email/{email}")
  public ResponseEntity<Map<String, Object>> getDuplicateEmail(@PathVariable String email) {
    Member check = memberService.findEmail(email);
    if (check != null) {
      return ResponseUtil.createResponse(400, null, "already_exist_email");
    }
    return ResponseUtil.createResponse(200, null, "available_email");
  }

  @PostMapping("/signup/nickname/{nickname}")
  public ResponseEntity<Map<String, Object>> getDuplicateNickname(@PathVariable String nickname) {
    Member check = memberService.findNickname(nickname);
    if (check != null) {
      return ResponseUtil.createResponse(400, null, "already_exist_email");
    }
    return ResponseUtil.createResponse(200, null, "available_nickname");
  }

  @PostMapping("/user/nickname/{nickname}")
  public ResponseEntity<Map<String, Object>> getDuplicateNicknameChange(@RequestHeader("Authorization") String token, @PathVariable(name="nickname") String nickname) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Member check = memberService.findNickname(nickname);

    if (check != null && !check.getUser_id().equals(userIdFromToken)) {
      return ResponseUtil.createResponse(400, null, "already_exist_email");
    }

    return ResponseUtil.createResponse(200, null, "available_nickname");
  }

  @DeleteMapping("/user")
  public ResponseEntity<Map<String, Object>> deleteUser(@RequestHeader("Authorization") String token) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Long deleted = memberService.erase(userIdFromToken);
    if (deleted == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(200, deleted, "user_delete_success");
  }
}

PostController

공통 url: /api/posts

methodapi설명
GET게시물 목록
GET/other자신이 작성한 글 수, 댓글 수
GET/coding코딩 타입 게시물
GET/myPosts본인이 작성한 게시물
GET/top10조회소 top 10 게시물
GET/{id}id에 해당한 게시글 + 조회수 업데이트
GET/{id}/updateid에 해당한 게시글
POST게시글 등록
POST/checkOwner게시글 작성자 체크
PATCH/{id}id 게시글 수정
DELETE/{id}id 게시글 삭제
  • 코드
    @Log4j2
@RequestMapping("/api/posts")
@RestController
public class PostController {
  @Autowired
  PostService postService;

  @Autowired
  JwtTokenProvider jwtTokenProvider;

  @GetMapping("")
  public ResponseEntity<Map<String, Object>> getPosts() {
    List<Post> posts = postService.findAll();
    return ResponseUtil.createResponse(200, posts, null);
  }

  @GetMapping("/other")
  public ResponseEntity<Map<String, Object>> getOtherPosts() {
    List<Post> posts = postService.findOtherAll();
    return ResponseUtil.createResponse(200, posts, null);
  }

  @GetMapping("/coding")
  public ResponseEntity<Map<String, Object>> getCodingPosts() {
    List<Post> posts = postService.findCodingAll();
    return ResponseUtil.createResponse(200, posts, null);
  }

  @GetMapping("/myPosts")
  public ResponseEntity<Map<String, Object>> getMyPosts(@RequestHeader("Authorization") String token) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    List<Post> posts = postService.findMyPosts(userIdFromToken);
    return ResponseUtil.createResponse(200, posts, null);
  }

  @GetMapping("/top10")
  public ResponseEntity<Map<String, Object>> getTopPosts() {
    List<Post> posts = postService.findTop();
    return ResponseUtil.createResponse(200, posts, null);
  }

  @GetMapping("/{id}")
  public ResponseEntity<Map<String, Object>> getPost(@PathVariable Long id) {
    Post post = postService.findByIdUpdate(id);
    if (post == null) {
      return ResponseUtil.createResponse(404, null, "post_not_found");
    }
    return ResponseUtil.createResponse(200, post, null);
  }

  @GetMapping("/{id}/update")
  public ResponseEntity<Map<String, Object>> getUpdatePost(@PathVariable Long id) {
    Post post = postService.findById(id);
    if (post == null) {
      return ResponseUtil.createResponse(404, null, "post_not_found");
    }
    return ResponseUtil.createResponse(200, post, null);
  }

  @PostMapping("")
  public ResponseEntity<Map<String, Object>> createPost(@RequestHeader("Authorization") String token, @RequestBody PostDto dto) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    log.info(userIdFromToken);
    Long created = postService.create(userIdFromToken, dto);
    if (created == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(201, created, null);
  }

  @PostMapping("/checkOwner")
  public ResponseEntity<Map<String, Object>> checkPostOwner(@RequestHeader("Authorization") String token, @RequestBody PostDto dto) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Long check = postService.checkUser(userIdFromToken, dto);
    if (check == null) {
      return ResponseUtil.createResponse(403, null, "unauthorized_user");
    }
    return ResponseUtil.createResponse(200, null, "authorized_user");
  }

  @PatchMapping("/{id}")
  public ResponseEntity<Map<String, Object>> updatePost(@PathVariable Long id, @RequestBody PostDto dto) {
    Long updated = postService.update(id, dto);
    if (updated == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(200, updated, null);
  }

  @DeleteMapping("/{id}")
  public ResponseEntity<Map<String, Object>> deletePost(@PathVariable Long id) {
    Long deleted = postService.erase(id);
    if (deleted == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }
    return ResponseUtil.createResponse(200, deleted, null);
  }
}

CommentController

공통 url: /api/posts/{pid}/comments

methodapi설명
GETpid 게시물의 전체 댓글
GET/{commentId}commentId 댓글
POST댓글 등록
POST/checkOwner댓글 작성자 체크
PATCH/{commentId}commentId 댓글 수정
DELETE/{commentId}commentId 댓글 삭제
  • 코드
    @RestController
@RequestMapping("/api/posts/{pid}/comments")
public class CommentController {

  @Autowired
  CommentService commentService;

  @Autowired
  PostService postService;

  @Autowired
  JwtTokenProvider jwtTokenProvider;

  @GetMapping("")
  public ResponseEntity<Map<String, Object>> getComments(@PathVariable(name = "pid") Long pid) {
    Post post = postService.findById(pid);
    if (post == null) {
      return ResponseUtil.createResponse(404, null, "posts_not_found");
    }

    List<Comment> comments = commentService.findAll(pid);
    if (comments == null) {
      return ResponseUtil.createResponse(404, null, "comments_not_found");
    }

    return ResponseUtil.createResponse(200, comments, null);
  }

  @GetMapping("/{commentId}")
  public ResponseEntity<Map<String, Object>> getComment(@PathVariable(name = "pid") Long pid, @PathVariable(name = "commentId") Long cid) {
    Post post = postService.findById(pid);
    if (post == null) {
      return ResponseUtil.createResponse(404, null, "posts_not_found");
    }

    Comment comment = commentService.findById(pid, cid);
    if (comment == null) {
      return ResponseUtil.createResponse(404, null, "comments_not_found");
    }

    return ResponseUtil.createResponse(200, comment, null);
  }

  @PostMapping("")
  public ResponseEntity<Map<String, Object>> createComment(@RequestHeader("Authorization") String token, @PathVariable(name = "pid") Long pid, @RequestBody CommentDto dto) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);
    Post post = postService.findById(pid);
    if (post == null) {
      return ResponseUtil.createResponse(404, null, "posts_not_found");
    }

    Long created = commentService.create(userIdFromToken, pid, dto);
    if (created == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }

    return ResponseUtil.createResponse(201, created, null);
  }

  @PostMapping("/checkOwner")
  public ResponseEntity<Map<String, Object>> checkCommentOwner(@RequestHeader(name = "Authorization") String token, @RequestBody CommentDto dto) {
    Long userIdFromToken = jwtTokenProvider.getUserId(token);

    Long check = commentService.checkUser(userIdFromToken, dto);
    if (check == null) {
      return ResponseUtil.createResponse(403, null, "unauthorized_user");
    }

    return ResponseUtil.createResponse(200, null, "authorized_user");
  }

  @PatchMapping("/{commentId}")
  public ResponseEntity<Map<String, Object>> updateComment(@PathVariable(name = "pid") Long pid, @PathVariable(name = "commentId") Long cid, @RequestBody CommentDto dto) {
    Comment comment = commentService.findById(pid, cid);
    if (comment == null) {
      return ResponseUtil.createResponse(404, null, "comments_not_found");
    }

    Long updated = commentService.update(cid, dto);
    if (updated == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }

    return ResponseUtil.createResponse(200, updated, null);
  }

  @DeleteMapping("/{commentId}")
  public ResponseEntity<Map<String, Object>> deleteComment(@PathVariable(name = "pid") Long pid, @PathVariable(name = "commentId") Long cid) {
    Long deleted = commentService.erase(pid, cid);
    if (deleted == null) {
      return ResponseUtil.createResponse(500, null, "internal_server_error");
    }

    return ResponseUtil.createResponse(200, deleted, null);
  }
}

Service: 비즈니스 로직

MemberService

인터페이스: MemberService

public interface MemberService {
	//id에 해당하는 유저 찾기
  Member find(Long id);

	//로그인 
  JwtTokenDto findLogIn(MemberDto dto);

	//이메일 중복 찾기
  Member findEmail(String email);

	//닉네임 중복 찾기
  Member findNickname(String email);

	//유저가 작성한 댓글 수, 게시글 수
  CountDto findUserWrite(Long id);

	//회원가입
  Long create(MemberDto dto);

	//프로필 수정
  Long updateProfile(Long id, MemberDto dto);

	//비밀번호 수정
  Long updatePassword(Long id, MemberDto dto);

	//회원탈퇴
  Long erase(Long id);
}
  • 구현체: MemberServiceImpl
    @Log4j2
@Service
public class MemberServiceImpl implements MemberService {
  @Autowired
  MemberRepository memberRepository;

  @Autowired
  AuthenticationManagerBuilder authenticationManagerBuilder;

  @Autowired
  JwtTokenProvider jwtTokenProvider;

  @Override
  public Member find(Long uid) {
    return memberRepository.show(uid);
  }

  @Override
  public JwtTokenDto findLogIn(MemberDto dto) {
    Long userId = memberRepository.logIn(dto);
    UsernamePasswordAuthenticationToken authenticationToken = new UsernamePasswordAuthenticationToken(dto.getEmail(), dto.getPassword());
    Authentication authentication = authenticationManagerBuilder.getObject().authenticate(authenticationToken);

    return jwtTokenProvider.generateToken(authentication, userId);
  }

  @Override
  public Member findEmail(String email) {
    return memberRepository.showEmail(email);
  }

  @Override
  public Member findNickname(String nickname) {
    return memberRepository.showNickname(nickname);
  }

  @Override
  public CountDto findUserWrite(Long id) {
    return memberRepository.showUserWrite(id);
  }

  @Override
  public Long create(MemberDto dto) {
    return memberRepository.save(dto);
  }

  @Override
  public Long updateProfile(Long id, MemberDto dto) {
    return memberRepository.modifyProfile(id, dto);
  }

  @Override
  public Long updatePassword(Long id, MemberDto dto) {
    return memberRepository.modifyPassword(id, dto);
  }

  @Override
  public Long erase(Long id) {
    return memberRepository.delete(id);
  }
}

PostService

인터페이스: PostService

public interface PostService {
  //게시글 전체
  List<Post> findAll();

	//id 게시글 조회
  Post findById(Long id);

	//고민 유형 게시글
  List<Post> findOtherAll();

	//코딩 유형 게시글
  List<Post> findCodingAll();

	//본인이 작성한 게시글
  List<Post> findMyPosts(Long uid);

	//top10 게시글
  List<Post> findTop();

	//update할 id 게시글
  Post findByIdUpdate(Long id);

	//게시글 작성
  Long create(Long uid, PostDto dto);

	//게시글 수정
  Long update(Long id, PostDto dto);
	
	//게시글 삭제
  Long erase(Long id);

	//게시글 작성자 확인
  Long checkUser(Long uid, PostDto dto);
}
  • 구현체: PostServiceImpl
    @Log4j2
@Service
public class PostServiceImpl implements PostService {
  @Autowired
  PostRepository postRepository;

  @Override
  public List<Post> findAll() {
    return postRepository.showAll();
  }

  @Override
  public Post findById(Long id) {
    return postRepository.show(id);
  }

  @Override
  public List<Post> findOtherAll() {
    return postRepository.showOtherAll();
  }

  @Override
  public List<Post> findCodingAll() {
    return postRepository.showCodingAll();
  }

  @Override
  public List<Post> findMyPosts(Long uid){
    return postRepository.showMyPosts(uid);
  }

  @Override
  public List<Post> findTop() {
    return postRepository.showTop();
  }

  @Override
  @Transactional
  public Post findByIdUpdate(Long id) {
    Long check = postRepository.addView(id);
    if(check == null) return null;
    return postRepository.show(check);
  }

  @Override
  public Long create(Long uid, PostDto dto) {
    return postRepository.save(uid, dto);
  }

  @Override
  public Long update(Long id, PostDto dto) {
    return postRepository.modify(id, dto);
  }

  @Override
  public Long erase(Long id) {
    return postRepository.delete(id);
  }

  @Override
  public Long checkUser(Long uid, PostDto dto) {
    return postRepository.check(uid, dto.getPost_id());
  }
}

CommentService

인터페이스: CommentService

public interface CommentService {
	//id 게시글에 달린 댓글
  List<Comment> findAll(Long id);
	
	//pid 게시글에 달린 cid 댓글
  Comment findById(Long pid, Long cid);

	//댓글 등록
  Long create(Long uid, Long pid, CommentDto dto);

	//댓글 수정
  Long update(Long cid, CommentDto dto);

	//댓글 삭제
  Long erase(Long pid, Long cid);

	//댓글 작성자 확인
  Long checkUser(Long uid, CommentDto dto);
}
  • 구현체: CommentServiceImpl
    @Service
public class CommentServiceImpl implements CommentService {
  @Autowired
  CommentRepository commentRepository;

  @Autowired
  PostRepository postRepository;

  @Override
  public List<Comment> findAll(Long pid) {
    return commentRepository.showAll(pid);
  }

  @Override
  @Transactional
  public Comment findById(Long pid, Long cid) {
    return commentRepository.show(pid, cid);
  }

  @Override
  @Transactional
  public Long create(Long uid, Long pid, CommentDto dto) {
    Long saved = commentRepository.save(uid, pid, dto);
    postRepository.increaseComment(pid);

    return saved;
  }

  @Override
  public Long update(Long id, CommentDto dto) {
    return commentRepository.modify(id, dto);
  }

  @Override
  public Long erase(Long pid, Long cid) {
    Long deleted = commentRepository.delete(cid);
    postRepository.decreaseComment(pid);

    return deleted;
  }

  @Override
  public Long checkUser(Long uid, CommentDto dto) {
    return commentRepository.check(uid, dto.getComment_id());
  }
}

Repository: DB 접근

MemberRepository

인터페이스: MemberRepository

public interface MemberRepository {
	//id에 해당하는 유저 보여주기
  Member show(Long id);
  
	//유저 회원가입
  Long save(MemberDto dto);
  
	//유저 로그인
  Long logIn(MemberDto dto);
  
	//유저 프로필 수정
  Long modifyProfile(Long id, MemberDto dto);
  
	//유저 비밀번호 수정
  Long modifyPassword(Long id, MemberDto dto);
  
	//회원탈퇴
  Long delete(Long id);
  
	//이메일 중복 체크
  Member showEmail(String email);
  
	//닉네임 중복 체크
  Member showNickname(String nickname);
  
	//id에 해당하는 유저가 작성한 게시글, 댓글 수 보여주기
  CountDto showUserWrite(Long id);
}
  • 구현체: MemberRepositoryImpl
    @Log4j2
@Repository
public class MemberRepositoryImpl implements MemberRepository {
  @Autowired
  private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

  private final RowMapper<Member> userRowMapper = new RowMapper<Member>() {
    @Override
    public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
      return Member.builder()
          .user_id(rs.getLong("user_id"))
          .email(rs.getString("email"))
          .profile_image(rs.getString("profile_image"))
          .password(rs.getString("password"))
          .nickname(rs.getString("nickname"))
          .roles(rs.getString("roles"))
          .build();
    }
  };

  @Override
  public Member show(Long id) {
    String sql = "SELECT * FROM users WHERE user_id = :userId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("userId", id);
      return namedParameterJdbcTemplate.queryForObject(sql, parameters, userRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public Long save(MemberDto dto) {
    String profileServerUrl = "";

    if (dto.getProfile_image() != null && !dto.getProfile_image().isEmpty()) {
      profileServerUrl = saveUserProfileImage(dto.getProfile_image());
    }

    String sql = "INSERT INTO users (email, profile_image, password, nickname, created_at) " +
        "VALUES (:email, :profilImage, :password, :nickname, :createdAt)";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("email", dto.getEmail())
          .addValue("profilImage", profileServerUrl)
          .addValue("password", dto.getPassword())
          .addValue("nickname", dto.getNickname())
          .addValue("createdAt", new Timestamp(System.currentTimeMillis()));

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return namedParameterJdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", new MapSqlParameterSource(), Long.class);
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long logIn(MemberDto dto) {
    String sql = "SELECT * FROM users WHERE email = :email and password = :password";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("email", dto.getEmail())
          .addValue("password", dto.getPassword());

      return namedParameterJdbcTemplate.queryForObject(sql, parameters, userRowMapper).getUser_id();
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public Long modifyProfile(Long id, MemberDto dto) {
    String profileServerUrl = "";

    if (dto.getProfile_image() != null && !dto.getProfile_image().isEmpty()) {
      profileServerUrl = saveUserProfileImage(dto.getProfile_image());
    }

    String sql = "UPDATE users SET nickname = :nickname, profile_image = :profileImage,  updated_at = :updatedAt WHERE user_id = :userId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("nickname", dto.getNickname())
          .addValue("profileImage", profileServerUrl)
          .addValue("updatedAt", new Timestamp(System.currentTimeMillis()))
          .addValue("userId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long modifyPassword(Long id, MemberDto dto) {
    String sql = "UPDATE users SET password = :password, updated_at = :updatedAt WHERE user_id = :userId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("password", dto.getPassword())
          .addValue("updatedAt", new Timestamp(System.currentTimeMillis()))
          .addValue("userId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long delete(Long id) {
    String sql = "DELETE FROM users WHERE user_id = :userId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("userId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Member showEmail(String email) {
    String sql = "SELECT * FROM users WHERE email = :email";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("email", email);

      return namedParameterJdbcTemplate.queryForObject(sql, parameters, userRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public Member showNickname(String nickname) {
    String sql = "SELECT * FROM users WHERE nickname = :nickname";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("nickname", nickname);

      return namedParameterJdbcTemplate.queryForObject(sql, parameters, userRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public CountDto showUserWrite(Long id) {
    String postCountSql = "SELECT COUNT(*) AS postCount FROM posts WHERE user_id = :userId";
    String commentCountSql = "SELECT COUNT(*) AS commentCount FROM comments WHERE user_id = :userId";

    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("userId", id);

      long postCount = namedParameterJdbcTemplate.queryForObject(postCountSql, parameters, Long.class);
      long commentCount = namedParameterJdbcTemplate.queryForObject(commentCountSql, parameters, Long.class);
      return new CountDto(postCount, commentCount);
    } catch (EmptyResultDataAccessException e) {
      return new CountDto(0L, 0L);
    }
  }

  public String saveUserProfileImage(String base64Image) {
    if(base64Image.contains("localhost")){
      return base64Image;
    }

    String[] parts = base64Image.split(",");
    if (parts.length != 2) {
      log.info("Wrong Image Type");
      return null;
    }

    String imageString = parts[1];
    byte[] imageBytes = Base64.getDecoder().decode(imageString);

    String imageName = "profile_image_" + System.currentTimeMillis() + ".png";
    Path imagePath = Paths.get("src/main/resources/static/images/profile", imageName);

    try {
      Files.createDirectories(imagePath.getParent());
      Files.write(imagePath, imageBytes);
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }

    return "http://localhost:8080/images/profile/" + imageName;
  }
}

PostRepository

인터페이스: PostRepository

public interface PostRepository {
	//전체 게시물 목록 보여주기
  List<Post> showAll();
	
	//기타 게시물 목록 보여주기
  List<Post> showOtherAll();

	//코딩 게시물 목록 보여주기
  List<Post> showCodingAll();

	//Top10 게시물 목록 보여주기
  List<Post> showTop();

	//본인이 작성한 게시물 목록 보여주기
  List<Post> showMyPosts(Long uid);

  //pno 게시물 반환
  Post show(Long pid);

  //게시물 작성 후 게시물 번호 반환
  Long save(Long uid, PostDto postDto);

  //게시물 수정
  Long modify(Long id,PostDto dto);

  //게시물 삭제
  Long delete(Long id);

  //게시물 조회수 변경
  Long addView(Long id);
	
	//id 댓글 수 증가
  Long increaseComment(Long id);

	//id 댓글 수 감소
  Long decreaseComment(Long id);

	//pid 게시물 작성자 확인
	Long check(Long uid, Long pid);
}
  • 구현체: PostRepositoryImpl
    @Log4j2
@Repository
public class PostRepositoryImpl implements PostRepository {

  @Autowired
  private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

  private final RowMapper<Post> postRowMapper = new RowMapper<Post>() {
    @Override
    public Post mapRow(ResultSet rs, int rowNum) throws SQLException {
      return Post.builder()
          .post_id(rs.getLong("post_id"))
          .post_image(rs.getString("post_image"))
          .title(rs.getString("title"))
          .content(rs.getString("content"))
          .type(rs.getString("type"))
          .comment_count(rs.getLong("comment_count"))
          .view(rs.getLong("view"))
          .user_id(rs.getLong("user_id"))
          .created_at(rs.getTimestamp("created_at"))
          .updated_at(rs.getTimestamp("updated_at"))
          .build();
    }
  };

  private final RowMapper<Post> postWithUserRowMapper = new RowMapper<Post>() {
    @Override
    public Post mapRow(ResultSet rs, int rowNum) throws SQLException {
      Post post = Post.builder()
          .post_id(rs.getLong("post_id"))
          .post_image(rs.getString("post_image"))
          .title(rs.getString("title"))
          .content(rs.getString("content"))
          .type(rs.getString("type"))
          .comment_count(rs.getLong("comment_count"))
          .view(rs.getLong("view"))
          .user_id(rs.getLong("user_id"))
          .created_at(rs.getTimestamp("created_at"))
          .updated_at(rs.getTimestamp("updated_at"))
          .build();
      Member member = Member.builder()
          .user_id(rs.getLong("user_id"))
          .profile_image(rs.getString("profile_image"))
          .nickname(rs.getString("nickname"))
          .email(rs.getString("email"))
          .roles(rs.getString("roles"))
          .build();

      post.setMember(member); // Post 객체에 User 객체를 설정합니다.

      return post;
    }
  };

  @Override
  public List<Post> showAll() {
    String sql = "SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id ORDER BY posts.created_at DESC;";
    try {
      return namedParameterJdbcTemplate.query(sql, postWithUserRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return Collections.emptyList();
    }
  }

  @Override
  public List<Post> showOtherAll() {
    String sql = "SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id WHERE posts.type = 'other' ORDER BY posts.created_at DESC;";
    try {
      return namedParameterJdbcTemplate.query(sql, postWithUserRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return Collections.emptyList();
    }
  }

  @Override
  public List<Post> showCodingAll() {
    String sql = "SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id WHERE posts.type = 'coding' ORDER BY posts.created_at DESC;";
    try {
      return namedParameterJdbcTemplate.query(sql, postWithUserRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return Collections.emptyList();
    }
  }

  @Override
  public List<Post> showMyPosts(Long uid) {
    String sql = "SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id WHERE users.user_id = :userId ORDER BY posts.created_at DESC;";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("userId", uid);
      return namedParameterJdbcTemplate.query(sql, parameters, postWithUserRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return Collections.emptyList();
    }
  }

  @Override
  public List<Post> showTop() {
    String sql = "SELECT * FROM posts ORDER BY view DESC LIMIT 10";
    try {
      return namedParameterJdbcTemplate.query(sql, postRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return Collections.emptyList();
    }
  }

  @Override
  public Post show(Long pid) {
    String sql = "SELECT * FROM posts posts INNER JOIN users ON posts.user_id = users.user_id WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", pid);
      return namedParameterJdbcTemplate.queryForObject(sql, parameters, postWithUserRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public Long save(Long uid, PostDto dto) {
    String postServerUrl = "";

    if (dto.getPost_image() != null && !dto.getPost_image().isEmpty()) {
      postServerUrl = savePostImage(dto.getPost_image());
    }
    String sql = "INSERT INTO posts (user_id, title, post_image, content, type, created_at) " +
        "VALUES (:userId, :title, :postImage, :content, :type, :createdAt)";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("userId", uid)
          .addValue("title", dto.getTitle())
          .addValue("postImage", postServerUrl)
          .addValue("content", dto.getContent())
          .addValue("type", dto.getType())
          .addValue("createdAt", new Timestamp(System.currentTimeMillis()));

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);
      log.info(rowsAffected);
      if (rowsAffected > 0) {
        Long index = namedParameterJdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", new MapSqlParameterSource(), Long.class);
        log.info(index);
        return index;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long modify(Long id, PostDto dto) {
    String postServerUrl = "";

    if (dto.getPost_image() != null && !dto.getPost_image().isEmpty()) {
      postServerUrl = savePostImage(dto.getPost_image());
    }

    String sql = "UPDATE posts SET title = :title, post_image = :postImage, content = :content, type = :type, updated_at = :updatedAt WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("title", dto.getTitle())
          .addValue("postImage", postServerUrl)
          .addValue("content", dto.getContent())
          .addValue("type", dto.getType())
          .addValue("updatedAt", new Timestamp(System.currentTimeMillis()))
          .addValue("postId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long delete(Long id) {
    String sql = "DELETE FROM posts WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long addView(Long id) {
    String sql = "UPDATE posts SET view = view + 1 WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long increaseComment(Long id) {
    String sql = "UPDATE posts SET comment_count = comment_count + 1 WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long decreaseComment(Long id) {
    String sql = "UPDATE posts SET comment_count = comment_count - 1 WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  public String savePostImage(String base64Image) {
    if (base64Image.contains("localhost")) {
      return base64Image;
    }

    String[] parts = base64Image.split(",");
    if (parts.length != 2) {
      log.info("Wrong Image Type");
      return null;
    }

    String imageString = parts[1];
    byte[] imageBytes = Base64.getDecoder().decode(imageString);

    String imageName = "post_image_" + System.currentTimeMillis() + ".png";
    Path imagePath = Paths.get("src/main/resources/static/images/post", imageName);

    try {
      Files.createDirectories(imagePath.getParent());
      Files.write(imagePath, imageBytes);
    } catch (IOException e) {
      e.printStackTrace();
      return null;
    }

    return "http://localhost:8080/images/post/" + imageName;
  }

  @Override
  public Long check(Long uid, Long pid) {
    String sql = "SELECT * FROM posts posts INNER JOIN users users ON posts.user_id = users.user_id WHERE post_id = :postId and users.user_id = :userId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", pid)
          .addValue("userId",uid);
      Post post =  namedParameterJdbcTemplate.queryForObject(sql, parameters, postWithUserRowMapper);
      return 1L;
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }
}

CommentRepository

인터페이스: CommentRepository

public interface CommentRepository {
  //id에 해당하는 댓글 목록 보여주기
  List<Comment> showAll(Long id);

  //pid 게시물에 달린 cid 댓글 보여주기
  Comment show(Long pid, Long cid);

  //댓글 작성
  Long save(Long uid, Long pid, CommentDto commentDto);

  //댓글 수정
  Long modify(Long id,CommentDto dto);

  //댓글삭제
  Long delete(Long id);

	//댓글 작성자 확인
  Long check(Long uid, Long cid);
}
  • 구현체: CommentrRepositoryImpl
    @Log4j2
@Repository
public class CommentRepositoryImpl implements CommentRepository{

  @Autowired
  private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

  private final RowMapper<Comment> commentRowMapper = new RowMapper<Comment>() {
    @Override
    public Comment mapRow(ResultSet rs, int rowNum) throws SQLException {
      Comment comment = Comment.builder()
          .comment_id(rs.getLong("comment_id"))
          .comment(rs.getString("comment"))
          .user_id(rs.getLong("user_id"))
          .post_id(rs.getLong("post_id"))
          .created_at(rs.getTimestamp("created_at"))
          .updated_at(rs.getTimestamp("updated_at"))
          .build();

      Member member = Member.builder()
          .user_id(rs.getLong("user_id"))
          .nickname(rs.getString("nickname"))
          .email(rs.getString("email"))
          .profile_image(rs.getString("profile_image"))
          .roles(rs.getString("roles"))
          .build();

      comment.setMember(member);
      return comment;
    };
  };

  public List<Comment> showAll(Long pid) {
    String sql = "SELECT * FROM comments comments INNER JOIN users users ON comments.user_id = users.user_id  WHERE post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", pid);
      return namedParameterJdbcTemplate.query(sql, parameters, commentRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public Comment show(Long pid, Long cid) {
    String sql = "SELECT * FROM comments WHERE comment_id = :commentId and post_id = :postId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("postId", pid).addValue("commentId", cid);
      return namedParameterJdbcTemplate.queryForObject(sql, parameters, commentRowMapper);
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }

  @Override
  public Long save(Long uid, Long pid, CommentDto dto) {
    String sql = "INSERT INTO comments (user_id, post_id, comment, created_at) " +
        "VALUES (:userId, :postId, :comment, :createdAt)";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("userId", uid)
          .addValue("postId", pid)
          .addValue("createdAt", new Timestamp(System.currentTimeMillis()))
          .addValue("comment", dto.getComment());

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return namedParameterJdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", new MapSqlParameterSource(), Long.class);
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long modify(Long id, CommentDto dto) {
    String sql = "UPDATE comments SET comment = :comment, updated_at = :updatedAt WHERE comment_id = :commentId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("comment", dto.getComment())
          .addValue("updatedAt", new Timestamp(System.currentTimeMillis()))
          .addValue("commentId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long delete(Long id) {
    String sql = "DELETE FROM comments WHERE comment_id = :commentId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("commentId", id);

      int rowsAffected = namedParameterJdbcTemplate.update(sql, parameters);

      if (rowsAffected > 0) {
        return id;
      } else {
        return null;
      }
    } catch (DataAccessException e) {
      return null;
    }
  }

  @Override
  public Long check(Long uid, Long cid) {
    String sql = "SELECT * FROM comments comments INNER JOIN users users ON comments.user_id = users.user_id WHERE comment_id = :commentId and users.user_id = :userId";
    try {
      MapSqlParameterSource parameters = new MapSqlParameterSource()
          .addValue("commentId", cid)
          .addValue("userId",uid);
      Comment comment =  namedParameterJdbcTemplate.queryForObject(sql, parameters, commentRowMapper);
      return 1L;
    } catch (EmptyResultDataAccessException e) {
      return null;
    }
  }
}

DTO

MemberDto

@Log4j2
@Data
@NoArgsConstructor
public class MemberDto {
  private Long user_id;

  private String email;
  private String profile_image;
  private String password;
  private String nickname;
  private String roles;
}

PostDto

@Log4j2
@Data
@NoArgsConstructor
public class PostDto {
  private Long post_id;

  private Long user_id;
  private String title;
  private String post_image;
  private String content;
  private String type;
  private Long view;
}

CommentDto

@Log4j2
@Data
@NoArgsConstructor
public class CommentDto {
  private Long comment_id;

  private String comment;
  private Long user_id;
  private Long post_id;
}
profile
YTT.erica
'◡'✿ 꿈을 찾아가보자고~ '◡'✿
이전 포스트

[Express] MySQL 연동 및 쿼리 수정

다음 포스트

[스프링부트] JPA, Querydsl 적용하기

0개의 댓글