AWS Back Day 70. "Spring Boot를 이용한 Entity 변수명 변경 및 객체 간 JOIN 활용"

이강용·2023년 4월 12일
Springboot
0

Spring Boot

목록 보기
5/20

Workbench

변수명 변경




entity 변수명 변경

search > file

객체와 객체간의 관계를 이용해서 JOIN 만들기

검색하기

course.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.web.study.repository.CourseRepository">

	<resultMap type="com.web.study.domain.entity.Lecture" id="lecture">
		<result column="ltm_id" property="ltm_id"/>
		<result column="ltm_name" property="ltm_name"/>
		<result column="ltm_price" property="ltm_price"/>
		<result column="itm_id" property="itm_id"/>
		<collection property="instructor" resultMap="instructor"></collection>
	</resultMap>
	
	<resultMap type="com.web.study.domain.entity.Instructor" id="instructor">
		<result column="itm_id" property="itm_id"/>
		<result column="itm_name" property="itm_name"/>
		<result column="itm_birth" property="itm_birth"/>
	</resultMap>
	
	<resultMap type="com.web.study.domain.entity.Student" id="student">
		<result column="sdm_id" property="sdm_id"/>
		<result column="sdm_name" property="sdm_name"/>
		<result column="sdm_birth" property="sdm_birth"/>
	</resultMap>
	
	<resultMap type="com.web.study.domain.entity.Course" id="course">
		<result column="csm_id" property="csm_id"/>
		<result column="ltm_id" property="ltm_id"/>
		<result column="sdm_id" property="sdm_id"/>
		<result column="registe_date" property="registe_date"/>
		<collection property="lecture" resultMap="lecture"></collection>
		<collection property="student" resultMap="student"></collection>
	</resultMap>
  
  <insert id="registe" parameterType="Course"> 
  	insert into course_mst
  	values (0, #{ltm_id}, #{sdm_id}, #{registe_date})
  </insert>
  
  <select id="getCourseAll" resultMap="course">
  	select
    	cm.csm_id,
	    cm.ltm_id,
	    cm.sdm_id,
	    cm.registe_date,
	    lm.ltm_id,
	    lm.ltm_name,
	    lm.ltm_price,
	    lm.itm_id,
	    im.itm_id,
	    im.itm_name,
	    im.itm_birth,
	    sm.sdm_id,
	    sm.sdm_name,
	    sm.sdm_birth
  	from
	    course_mst cm
	    left outer join lecture_mst lm on (lm.ltm_id = cm.ltm_id)
	    left outer join instructor_mst im on (im.itm_id = lm.itm_id)
	    left outer join student_mst sm on (sm.sdm_id = cm.sdm_id)
</select>

<select id="searchCourse" parameterType="String" resultMap="course">
  	select
    	cm.csm_id,
	    cm.ltm_id,
	    cm.sdm_id,
	    cm.registe_date,
	    lm.ltm_id,
	    lm.ltm_name,
	    lm.ltm_price,
	    lm.itm_id,
	    im.itm_id,
	    im.itm_name,
	    im.itm_birth,
	    sm.sdm_id,
	    sm.sdm_name,
	    sm.sdm_birth
  	from
	    course_mst cm
	    left outer join lecture_mst lm on (lm.ltm_id = cm.ltm_id)
	    left outer join instructor_mst im on (im.itm_id = lm.itm_id)
	    left outer join student_mst sm on (sm.sdm_id = cm.sdm_id)
    where
    	lm.ltm_name like concat('%',#{searchValue},'%') 
   	or  im.itm_name like concat('%',#{searchValue},'%')
   	or  sm.sdm_name like concat('%',#{searchValue},'%') 
</select>
  
 
</mapper>
 where
    	lm.ltm_name like concat('%',#{searchValue},'%') 
   	or  im.itm_name like concat('%',#{searchValue},'%')
   	or  sm.sdm_name like concat('%',#{searchValue},'%')

CourseController

package com.web.study.controller.lecture;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.web.study.dto.DataResponseDto;
import com.web.study.dto.ResponseDto;
import com.web.study.dto.request.course.CourseReqDto;
import com.web.study.service.CourseService;

import lombok.RequiredArgsConstructor;

@RestController
@RequiredArgsConstructor
public class CourseController {
	
	private final CourseService courseService;
	//Create
	@PostMapping("/course")
	public ResponseEntity<? extends ResponseDto> register(@RequestBody CourseReqDto CourseReqDto) {
		courseService.registeCourse(CourseReqDto);
		return ResponseEntity.ok().body(ResponseDto.ofDefault());
	}
	
	@GetMapping("/courses")
	public ResponseEntity<? extends ResponseDto> getCurseAll() {
		return ResponseEntity.ok().body(DataResponseDto.of(courseService.getCourseAll()));
	}
	
	@GetMapping("/search/courses")
	public ResponseEntity<? extends ResponseDto> searchCourse(String searchValue) {
		return ResponseEntity.ok().body(DataResponseDto.of(courseService.searchCourse(searchValue)));
	}
}

domain.entity

package com.web.study.domain.entity;

import java.time.LocalDate;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;


@Builder
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class Course {
	private int csm_id;
	private int ltm_id;
	private int sdm_id;
	private LocalDate registe_date;
	private Lecture lecture;
	private Student student;

}

CourseRepository

package com.web.study.repository;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.web.study.domain.entity.Course;



@Mapper
public interface CourseRepository {
	
	// insert, update, delete 리턴 자료형 없음
	public int registe(Course course );
	public List<Course> getCourseAll();
	public List<Course> searchCourse(String serachValue);


}

CourseService

package com.web.study.service;

import java.util.List;

import com.web.study.domain.entity.Course;
import com.web.study.dto.request.course.CourseReqDto;

public interface CourseService {
	
	public void registeCourse(CourseReqDto courseReqDto);
	public List<Course> getCourseAll();
	public List<Course> searchCourse(String serachValue);

}

CourseServiceImpl

package com.web.study.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.web.study.domain.entity.Course;
import com.web.study.dto.request.course.CourseReqDto;
import com.web.study.repository.CourseRepository;

import lombok.RequiredArgsConstructor;

@Service
@RequiredArgsConstructor
public class CourseServiceImpl implements CourseService {
	private final CourseRepository courseRepository;

	

	@Override
	public void registeCourse(CourseReqDto courseReqDto) {
		courseRepository.registe(courseReqDto.toEntity());
		
	}

	@Override
	public List<Course> getCourseAll() {
		return courseRepository.getCourseAll();
	}

	@Override
	public List<Course> searchCourse(String serachValue) {
		return courseRepository.searchCourse(serachValue);
	}
}

이름, 강사명 검색 옵션 추가하기

CourseService

package com.web.study.service;

import java.util.List;

import com.web.study.domain.entity.Course;
import com.web.study.dto.request.course.CourseReqDto;

public interface CourseService {
	
	public void registeCourse(CourseReqDto courseReqDto);
	public List<Course> getCourseAll();
	public List<Course> searchCourse(int type, String serachValue);

}

CourseServiceImpl

@Override
	public List<Course> searchCourse(int type, String searchValue) {
		
		Map<String, Object> parameterMap = new HashMap<>();
		parameterMap.put("type", type);
		parameterMap.put("searchValue", searchValue);
		return courseRepository.searchCourse(parameterMap);
	}
}

CourseController

@GetMapping("/search/courses")
	public ResponseEntity<? extends ResponseDto> searchCourse(int type, String searchValue) {
		return ResponseEntity.ok().body(DataResponseDto.of(courseService.searchCourse(type,searchValue)));
	}

CourseRepository

@Mapper
public interface CourseRepository {
	
	// insert, update, delete 리턴 자료형 없음
	public int registe(Course course );
	public List<Course> getCourseAll();
	public List<Course> searchCourse(Map<String, Object> parameterMap);


}

course.xml

where
     1 = 1
    <if test="type == 1"> 
    and	lm.ltm_name like concat('%',#{searchValue},'%')
    </if>
    <if test="type == 2"> 
    and	im.itm_name like concat('%',#{searchValue},'%')
    </if>
    <if test="type == 3"> 
    and	sm.sdm_name like concat('%',#{searchValue},'%')
    </if>

Course 클래스 구현 및 DTO 변환 메서드

response > CourseRespDto

package com.web.study.dto.response;

import java.time.LocalDate;

import lombok.Builder;
import lombok.Getter;

@Builder
@Getter
public class CourseRespDto {
	private int courseId;
	private LocalDate registeDate;
	private String lectureName;
	private int lecturePrice;
	private String instructorName;
	private String studentName;
}

domain.entity > Course

package com.web.study.domain.entity;

import java.time.LocalDate;

import com.web.study.dto.response.CourseRespDto;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;


@Builder
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class Course {
	private int csm_id;
	private int ltm_id;
	private int sdm_id;
	private LocalDate registe_date;
	private Lecture lecture;
	private Student student;

	public CourseRespDto toDto() {
		
		String lectureName = null;
		int lecturePrice = 0;
		String instructorName = null;
		String studentName = null;
		
		if(lecture != null) {
			lectureName = lecture.getLtm_name();
			lecturePrice = lecture.getLtm_price();
			if(lecture.getInstructor() != null) {
				instructorName = lecture.getInstructor().getItm_name();
			}
		}
		
		if(student != null) {
			studentName = student.getSdm_name();
		}
		
		return CourseRespDto.builder()
				.courseId(csm_id)
				.registeDate(registe_date)
				.lectureName(lectureName)
				.lecturePrice(lecturePrice)
				.instructorName(instructorName)
				.studentName(studentName)
				.build();
	}
}

Lecture - 강좌, 강사 검색

LectureController

//추가
@GetMapping("/search/lectures")
	public ResponseEntity<? extends ResponseDto> searchLecture(int type, String searchValue) {
		return ResponseEntity.ok().body(DataResponseDto.of(lectureService.searchLecture(type, searchValue)));
	}
  • (int type, String searchValue) - > 객체로 전달 하는 방법이 있음

LectureService

public interface LectureService {
	
	public void registeLecture(LectureReqDto lectureReqDto);
    
    //추가
	public List<LectureRespDto> searchLecture(int type, String serachValue);

}

lecture.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.web.study.repository.LectureRepository">
  
  	<resultMap type="com.web.study.domain.entity.Lecture" id="lecture">
		<result column="ltm_id" property="ltm_id"/>
		<result column="ltm_name" property="ltm_name"/>
		<result column="ltm_price" property="ltm_price"/>
		<result column="itm_id" property="itm_id"/>
		<collection property="instructor" resultMap="instructor"></collection>
	</resultMap>
	
	<resultMap type="com.web.study.domain.entity.Instructor" id="instructor">
		<result column="itm_id" property="itm_id"/>
		<result column="itm_name" property="itm_name"/>
		<result column="itm_birth" property="itm_birth"/>
	</resultMap>
	
	
  <insert id="registe" parameterType="Lecture"> 
  	insert into lecture_mst
  	values (0, #{ltm_name}, #{ltm_price},  #{itm_id})
  </insert>
  
  <select id="searchLecture" parameterType="hashMap" resultMap="lecture">
  	select
	    lm.ltm_id,
	    lm.ltm_name,
	    lm.ltm_price,
	    lm.itm_id,
	    im.itm_id,
	    im.itm_name,
	    im.itm_birth
	    
  	from
  		lecture_mst lm
	    left outer join instructor_mst im on (im.itm_id = lm.itm_id)
    where
     1 = 1
    <if test="type == 1"> 
    and	lm.ltm_name like concat('%',#{searchValue},'%')
    </if>
    <if test="type == 2"> 
    and	im.itm_name like concat('%',#{searchValue},'%')
    </if>
 </select>
  
  
</mapper>

  • <mapper> : MyBatis의 매퍼 파일임을 나타내는 태그
  • <resultMap> : 조회 결과 매핑 정보를 지정하는 태그
    • type 속성은 매핑할 클래스를 지정
    • id 속성은 매핑 정보의 고유한 아이디를 지정
  • <result> : 쿼리 결과 컬럼과 매핑할 클래스의 필드를 지정
    • column 속성은 쿼리 결과 컬럼명을 지정
    • property 속성은 매핑할 클래스의 필드명을 지정
  • <collection> : 클래스 내부에 컬렉션 타입 필드가 있는 경우, 해당 컬렉션 타입 필드와 연결된 매핑 정보를 추가로 지정할 수 있음
    • property 속성은 클래스 내부의 컬렉션 타입 필드명을 지정
    • resultMap 속성은 컬렉션 타입 필드에 매핑될 매핑 정보의 아이디 를 지정
  • <insert> : 데이터를 추가하는 쿼리문을 작성
    • id 속성은 매핑 정보의 고유한 아이디
    • parameterType 속성은 매핑할 클래스 타입
    • values DB에 삽입할 값을 지정
  • <select> : 데이터를 조회하는 쿼리문을 작성
    • id 속성은 매핑 정보의 고유한 아이디
    • parameterType 속성은 쿼리문에서 사용할 파라미터 타입
    • resultMap 속성은 조회 결과를 매핑할 매핑 정보의 아이디
    • where 구문은 조회 조건을 작성
    • if 조건에 따라 동적으로 쿼리를 생성

Lecture

//추가
public LectureRespDto toDto() {
		
		String instructorName = null;

		
		if(instructor != null) {
			instructorName = instructor.getItm_name();
		}
		
		return LectureRespDto.builder()
				.lectureId(ltm_id)
				.lecutreName(ltm_name)
				.lecturePrice(ltm_price)
				.instructorName(instructorName)
				.build();
	}

LectureRespDto

package com.web.study.dto.response;

import lombok.Builder;
import lombok.Getter;

@Builder
@Getter
public class LectureRespDto {
	
	private int lectureId;
	private String lecutreName;
	private int lecturePrice;
### 	private String instructorName;
}

LectureRepository

//추가
public List<Lecture> searchLecture(Map<String, Object> parameterMap);

LectureServiceImpl

//추가
@Override
	public List<LectureRespDto> searchLecture(int type, String searchValue) {
		Map<String, Object> parameterMap = new HashMap<>();
		parameterMap.put("type", type);
		parameterMap.put("searchValue", searchValue);
		List<LectureRespDto> dtos = new ArrayList<>();
		lectureRepository.searchLecture(parameterMap).forEach(entity -> {
			dtos.add(entity.toDto());
		});
		return dtos;
	}
profile
이강용
HW + SW = 1
이전 포스트

AWS Back Day 69. "Spring Boot와 MyBatis를 활용한 수강신청 웹 애플리케이션 구현"

다음 포스트

AWS Back Day 71. "Spring Boot에서 AOP를 이용한 예외처리와 유효성 검사"

0개의 댓글