본문 바로가기
Back end/Spring

[Spring] 데이터베이스 연결(MyBatis)

by 더 이프 2023. 7. 25.
728x90

목차

    1. MyBatis

    ￭ MyBatis란?

    MyBatis는 객체 지향 언어인 자바의 관계형 데이터베이스 프로그래밍을 좀 더 쉽게 할 수 있게 도와 주는 개발 프레임워크로서 JDBC를 통해 데이터베이스에 연결하는 작업을 캡슐화하고 SQL 쿼리와 자바 오브젝트간에 자동 매핑을 지원합니다. MyBatis에서는 프로그램에 있는 SQL쿼리들을 한 구성파일에 구성하여 프로그램 코드와 SQL을 분리할 수 있습니다.

    ￭ MyBatis 설치

    1. MVN REPOSITORY에서 MyBatis 버전 2.2.2를 pom.xml에 추가

    2. resources 폴더 내에 mappers라는 폴더를 만들어 내부에 xml파일로 매핑

    ￭ MyBatipse 설치

    • MyBatipse는 Mapper내의 xml파일에서 namespace 내 변수의 패키지 경로를 포함해서 자동으로 완성
    • Marketplace에서 MyBatipse를 검색하여 설치


     

    2. 예시

    ￭ Controller

    a. LectureController

    • IoC 컨테이너에 등록된 LectureService를 가져와 요청온 강의 등록 정보를 제공
    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.lecture.LectureReqDto;
import com.web.study.service.LectureService;

import lombok.RequiredArgsConstructor;

@RestController
@RequiredArgsConstructor
public class LectureController {
	
	private final LectureService lectureService;

	// Create
	@PostMapping("/lecture")
	public ResponseEntity<? extends ResponseDto> register(@RequestBody LectureReqDto lectureReqDto) {
		lectureService.registeLecture(lectureReqDto);
		return ResponseEntity.ok().body(ResponseDto.ofDefault());
	}
	
	// Read
	@GetMapping("/search/lecture")
	public ResponseEntity<? extends ResponseDto> searchLecture(int type, String searchValue) {
		return ResponseEntity.ok().body(DataResponseDto.of(lectureService.searchLecture(type, searchValue)));
	}
	
	// Update
	public ResponseEntity<? extends ResponseDto> modify() {
		return ResponseEntity.ok().body(ResponseDto.ofDefault());
	}
	
	// Delete
	public ResponseEntity<? extends ResponseDto> remove() {
		return ResponseEntity.ok().body(ResponseDto.ofDefault());
	}
}

    b. StudentController

    package com.web.study.controller.lecture;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
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.student.StudentReqDto;
import com.web.study.service.StudentService;

import lombok.RequiredArgsConstructor;

@RestController
@RequiredArgsConstructor
public class StudentController {
	
	private final StudentService studentService;

	// @RequestBody => 클라이언트 -> 서버 데이터 전송(JSON)
	// JSON의 형태 -> 객체
	@PostMapping("/student")
	public ResponseEntity<? extends ResponseDto> registeStudent(@RequestBody StudentReqDto studentReqDto) {
//		System.out.println(studentReqDto);
		studentService.registeStudent(studentReqDto);
		return ResponseEntity.ok().body(ResponseDto.ofDefault());
	}
	
	@GetMapping("/students")
	public ResponseEntity<? extends ResponseDto> getStudents() {
		return ResponseEntity.ok().body(DataResponseDto.of(studentService.getStudentAll()));
	}
	
	@GetMapping("/student/{id}")
	public ResponseEntity<? extends ResponseDto> getStudent(@PathVariable int id) {
		return ResponseEntity.ok().body(DataResponseDto.of(studentService.findStudentById(id)));
	}
}

    c. InstructorController

    package com.web.study.controller.lecture;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
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.instructor.InstructorReqDto;
import com.web.study.service.InstructorService;

import lombok.RequiredArgsConstructor;

@RestController
@RequiredArgsConstructor
public class InstructorController {

	private final InstructorService instructorService;
	
	@PostMapping("/instructor")
	public ResponseEntity<? extends ResponseDto> register(@RequestBody InstructorReqDto instructorReqDto) {
		instructorService.registeInstructor(instructorReqDto);
		return ResponseEntity.ok().body(ResponseDto.ofDefault());
	}
	
	@GetMapping("/instructors")
	public ResponseEntity<? extends ResponseDto> getInstructors() {
		return ResponseEntity.ok().body(DataResponseDto.of(instructorService.getInstructorAll()));
	}
	
	@GetMapping("/instructor/{id}")
	public ResponseEntity<? extends ResponseDto> getInstructorById(@PathVariable int id) {
		return ResponseEntity.ok().body(DataResponseDto.of(instructorService.findInstructorById(id)));
	}
}

    d. 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;
	
	@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> getCourseAll() {
		return ResponseEntity.ok().body(DataResponseDto.of(courseService.getCourseAll()));
	}
	
	@GetMapping("/search/courses")
	public ResponseEntity<? extends ResponseDto> searchCourse(int type, String searchValue) {
		return ResponseEntity.ok().body(DataResponseDto.of(courseService.searchCourse(type, searchValue)));
	}
}

    ￭ Entity

    a. Lecture

    • Getter는 매핑된 SQL 쿼리에 값을 주기 위해 적용
    • Builder는 자바에서 요청된 값을 쉽게 전달하기 위하여 적용
    package com.web.study.domain.entity;

import com.web.study.dto.response.lecture.LectureRespDto;

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

@Builder
@Getter
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Lecture {
	private int ltm_id;
	private String ltm_name;
	private int ltm_price;
	private int itm_id;
	private Instructor instructor;
	
	public LectureRespDto toDto() {
		String instructorName = null;
		
		if(instructor != null) {
			instructorName = instructor.getItm_name();
		}
		
		return LectureRespDto.builder()
				.lectureId(itm_id)
				.lectureName(ltm_name)
				.lecturePrice(itm_id)
				.instructorName(instructorName)
				.build();
	}
}

    b. Student

    package com.web.study.domain.entity;

import java.time.LocalDate;

import org.apache.ibatis.type.Alias;

import com.web.study.dto.response.student.StudentRespDto;

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

@Builder
@Getter
@NoArgsConstructor
@AllArgsConstructor
//@Alias("S1")
public class Student {
	private int sdm_id;
	private String sdm_name;
	private LocalDate sdm_birth;
	
	public StudentRespDto toDto() {
		return StudentRespDto.builder()
				.id(sdm_id)
				.name(sdm_name)
				.birthDate(sdm_birth)
				.build();
	}
}

    c. Instructor

    package com.web.study.domain.entity;

import java.time.LocalDate;

import com.web.study.dto.response.instructor.InstructorRespDto;

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

@Builder
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class Instructor {
	private int itm_id;
	private String itm_name;
	private LocalDate itm_birth;
	
	public InstructorRespDto toDto() {
		return InstructorRespDto.builder()
				.id(itm_id)
				.name(itm_name)
				.birthDate(itm_birth)
				.build();
	}
}

    d. Course

    package com.web.study.domain.entity;

import java.time.LocalDate;

import com.web.study.dto.response.course.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();
	}
}

    ￭ Dto

    a. LectureReqDto

    • Getter를 사용하면 원본 정보에 접근할 수 있기 때문에 toEntity메소드를 사용하여 Dto 값을 Entity에 전달
    package com.web.study.dto.request.lecture;

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

import lombok.Setter;
import lombok.ToString;

@Setter
@ToString
public class LectureReqDto {
	private String lectureName;
	private int lecturePrice;
	private int instructorId;
	
	public Lecture toEntity() {
		return Lecture.builder()
				.ltm_name(lectureName)
				.ltm_price(lecturePrice)
				.itm_id(instructorId)
				.build();
	}
}

    b. LectureRespDto

    package com.web.study.dto.response.lecture;

import lombok.Builder;
import lombok.Getter;

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

    c. StudentReqDto

    package com.web.study.dto.request.student;

import java.time.LocalDate;

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

import lombok.Setter;
import lombok.ToString;

@Setter
@ToString
public class StudentReqDto {
	private String name;
	private LocalDate birthDate;
	
	public Student toEntity() {
		return Student.builder()
				.sdm_name(name)
				.sdm_birth(birthDate)
				.build();
	}
}

    d. StudentRespDto

    package com.web.study.dto.response.student;

import java.time.LocalDate;

import lombok.Builder;
import lombok.Getter;

@Builder
@Getter
public class StudentRespDto {
	private int id;
	private String name;
	private LocalDate birthDate;
}

    e. InstructorReqDto

    package com.web.study.dto.request.instructor;

import java.time.LocalDate;

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

import lombok.Setter;

@Setter
public class InstructorReqDto {
	private String name;
	private LocalDate birthDate;
	
	public Instructor toEntity() {
		return Instructor.builder()
				.itm_name(name)
				.itm_birth(birthDate)
				.build();
	}
}

    f. InstructorRespDto

    package com.web.study.dto.response.instructor;

import java.time.LocalDate;

import lombok.Builder;
import lombok.Getter;

@Builder
@Getter
public class InstructorRespDto {
	private int id;
	private String name;
	private LocalDate birthDate;
}

    g. CourseReqDto

    package com.web.study.dto.request.Course;

import java.time.LocalDate;

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

import lombok.Setter;

@Setter
public class CourseReqDto {
	private int lectureId;
	private int studentId;
	private LocalDate registeDate;
	
	public Course toEntity() {
		return Course.builder()
				.ltm_id(lectureId)
				.sdm_id(studentId)
				.registe_date(registeDate)
				.build();
	}
}

    h. CourseRespDto

    package com.web.study.dto.response.course;

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;
	
}

    ￭ Service

    a. LectureService

    package com.web.study.service;

import java.util.List;

import com.web.study.dto.request.lecture.LectureReqDto;
import com.web.study.dto.response.lecture.LectureRespDto;

public interface LectureService {
	public void registeLecture(LectureReqDto lectureReqDto);
	public List<LectureRespDto> searchLecture(int type, String searchValue);
}

    b. LectureServiceImpl

    • Dto는 클라이언트 쪽, Entity는 데이터베이스 쪽이라고 생각하면 됨
    • Dto를 Entity로 변환
    • Repository로 Entity를 전달
    package com.web.study.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.web.study.domain.entity.Lecture;
import com.web.study.dto.request.lecture.LectureReqDto;
import com.web.study.dto.response.lecture.LectureRespDto;
import com.web.study.repository.LectureRepository;

import lombok.RequiredArgsConstructor;

@Service
@RequiredArgsConstructor
public class LectureServiceImpl implements LectureService{
	
	// final => 상수: 초기화가 무조건 일어나야함.
	private final LectureRepository lectureRepository;

	@Override
	public void registeLecture(LectureReqDto lectureReqDto) {
		// DTO -> ENTITY로 변환
		//DTO는 클라이언트 쪽, ENTITY는 데이터베이스 쪽
		Lecture lecture = lectureReqDto.toEntity();
		System.out.println("변환: " + lecture);
		lectureRepository.registe(lecture);
	}

	@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;
	}
}

    c. StudentService

    package com.web.study.service;

import java.util.List;

import com.web.study.dto.request.student.StudentReqDto;
import com.web.study.dto.response.student.StudentRespDto;

public interface StudentService {

	public void registeStudent(StudentReqDto studentReqDto);
	public List<StudentRespDto> getStudentAll();
	public StudentRespDto findStudentById(int id);
}

    d. StudentServiceImpl

    package com.web.study.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Service;

import com.web.study.dto.request.student.StudentReqDto;
import com.web.study.dto.response.student.StudentRespDto;
import com.web.study.repository.StudentRepository;

import lombok.RequiredArgsConstructor;

@Service
@RequiredArgsConstructor
public class StudentServiceImpl implements StudentService{
	
	private final StudentRepository studentRepository;

	@Override
	public void registeStudent(StudentReqDto studentReqDto) {
//		System.out.println(studentReqDto);
		studentRepository.saveStudent(studentReqDto.toEntity());
	}

	@Override
	public List<StudentRespDto> getStudentAll() {
		List<StudentRespDto> dtos = new ArrayList<>();
		studentRepository.getStudentAll().forEach(entity -> {
			dtos.add(entity.toDto());
		});
		return dtos;
	}

	@Override
	public StudentRespDto findStudentById(int id) {
		return studentRepository.findStudentById(id).toDto();
	}

}

    e. InstructorService

    package com.web.study.service;

import java.util.List;

import com.web.study.dto.request.instructor.InstructorReqDto;
import com.web.study.dto.response.instructor.InstructorRespDto;

public interface InstructorService {
	public void registeInstructor(InstructorReqDto instructorReqDto);
	public List<InstructorRespDto> getInstructorAll();
	public InstructorRespDto findInstructorById(int id);
}

    f. InstructorServiceImpl

    package com.web.study.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Service;

import com.web.study.dto.request.instructor.InstructorReqDto;
import com.web.study.dto.response.instructor.InstructorRespDto;
import com.web.study.repository.InstructorRepository;

import lombok.RequiredArgsConstructor;

@Service
@RequiredArgsConstructor
public class InstructorServiceImpl implements InstructorService {

	private final InstructorRepository instructorRepository;

	@Override
	public void registeInstructor(InstructorReqDto instructorReqDto) {
		instructorRepository.registeInstructor(instructorReqDto.toEntity());
	}

	@Override
	public List<InstructorRespDto> getInstructorAll() {
		List<InstructorRespDto> dtos = new ArrayList<>();
		instructorRepository.getInstructorAll().forEach(entity -> {
			dtos.add(entity.toDto());
		});
		return dtos;
	}

	@Override
	public InstructorRespDto findInstructorById(int id) {
		return instructorRepository.findInstructorById(id).toDto();
	}

}

    g. CourseService

    package com.web.study.service;

import java.util.List;

import com.web.study.dto.request.Course.CourseReqDto;
import com.web.study.dto.response.course.CourseRespDto;

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

    h. CourseServiceImpl

    package com.web.study.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.web.study.dto.request.Course.CourseReqDto;
import com.web.study.dto.response.course.CourseRespDto;
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.saveCourse(courseReqDto.toEntity());
	}

	@Override
	public List<CourseRespDto> getCourseAll() {
		List<CourseRespDto> dtos = new ArrayList<>();
		courseRepository.getCourseAll().forEach(entity -> {
			dtos.add(entity.toDto());
		});
		return dtos;
	}

	@Override
	public List<CourseRespDto> searchCourse(int type, String searchValue) {
		Map<String, Object> parameterMap = new HashMap<>();
		parameterMap.put("type", type);
		parameterMap.put("searchValue", searchValue);
		
		List<CourseRespDto> dtos = new ArrayList<>();
		courseRepository.searchCourse(parameterMap).forEach(entity -> {
			dtos.add(entity.toDto());
		});
		return dtos;
	}

}

    ￭ Repository

    a. LectureRepository

    • @Mapper를 적용하여 Mappers에 있는 SQL쿼리에 접근
    • 쿼리문에서 insert, update, delete는 쿼리 성공횟수를 리턴하기 때문에 자료형을 int로 설정
    package com.web.study.repository;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;

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

@Mapper
public interface LectureRepository {
	public int registe(Lecture lecture);
	public List<Lecture> searchLecture(Map<String, Object> parameterMap);
}

    b. StudentRepository

    package com.web.study.repository;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

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

@Mapper
public interface StudentRepository {
	public int saveStudent(Student student);
	public List<Student> getStudentAll();
	public Student findStudentById(int id);
}

    c. InstructorRepository

    package com.web.study.repository;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

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

@Mapper
public interface InstructorRepository {
	public int registeInstructor(Instructor instructor);
	public List<Instructor> getInstructorAll();
	public Instructor findInstructorById(int id);
}

    d. CourseRepository

    package com.web.study.repository;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;

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

@Mapper
public interface CourseRepository {
	public int saveCourse(Course course);
	public List<Course> getCourseAll();
	public List<Course> searchCourse(Map<String, Object> parameterMap);
}

    ￭ Mapper

    a. Lecture

    • 내부에 insert 쿼리문을 사용
    • id에는 Repository에서 정한 메소드명을 사용
    • parameterType에는 전달될 파라미터의 패키지경로를 포함한 클래스명을 작성
    • namespace에는 @Mapper를 적용한 곳의 패키지경로 및 인터페이스, 클래스명을 작성
    • Mapper에서 값을 적용할 때는 #{변수명}을 사용
    • 값을 요청하면 데이터베이스에 저장
    • 데이터베이스에서 id는 auto increment를 설정해두었기 때문에 자동으로 설정
    <?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.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.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>
	
	<insert id="registe" parameterType="com.web.study.domain.entity.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>
    • Request(Insert)

    b. Student

    • application.yml의 mybatis아래에 type-aliases-package: com.web.study.domain.entity를 추가
    • 위의 경로가 저장되어 Student만 적어도 위 경로의 Student로 인식
    • @Alias로 이름을 따로 지정해서도 사용 가능
    <?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.StudentRepository">

	<insert id="saveStudent" parameterType="Student">
		insert into student_mst
		values (0, #{sdm_name}, #{sdm_birth})
	</insert>
	
	<select id="getStudentAll" resultType="Student">
		select
			sdm_id,
			sdm_name,
			sdm_birth
		from
			student_mst
	</select>
	
	<select id="findStudentById" parameterType="integer" resultType="Student">
		select
			sdm_id,
			sdm_name,
			sdm_birth
		from
			student_mst
		where
			sdm_id=#{id}
	</select>
	
</mapper>

    c. Instructor

    • parameterType에서 기본자료형일 경우 생략해도 똑같이 동작
    • 데이터 결과를 카멜 표기법으로 받아야 하기 때문에 toDto로 받을 ResponseDto 만들거나 mapper에서 alias로 카멜표기법으로 변경  
    <?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.InstructorRepository">
	
	<insert id="registeInstructor" parameterType="com.web.study.domain.entity.Instructor">
		insert into instructor_mst
		values (0, #{itm_name}, #{itm_birth})
	</insert>
	
	<select id="getInstructorAll" resultType="Instructor">
		select
			itm_id,
			itm_name,
			itm_birth
		from
			instructor_mst
	</select>
	
	<select id="findInstructorById" parameterType="integer" resultType="Instructor">
		select
			itm_id,
			itm_name,
			itm_birth
		from
			instructor_mst
		where
			itm_id = #{id}
	</select>
	
</mapper>
    • getInstructorAll의 Response
    • findInstructorById의 Response

    d. Course

    • join하는 방법은 객체와 객체를 연결하는 방법과 select한 column들을 모두 선언한 entity를 만드는 방법이 존재
    • join을 하기 위해 resultMap을 이용하여 각 객체를 지정하고 collection으로 해당 객체를 연결
    • mybatis에는 파라미터가 하나만 올 수 있기 때문에 여러 파라미터를 service에서 repository로 전달할 때는 Map형태로 변환하여 전달
    • if를 선언하면 test 항목이 있는데 이 항목에 조건 작성 가능
    • 모든 조건을 만족하지 않으면 where문 에 조건식이 없기 때문에 error가 발생하는데 이를 해결하기 위해 무조건 참인 1 = 1을 적용
    <?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">
	<!-- column은 DB의 컬럼명 property는 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="saveCourse" parameterType="Course">
		insert into course_mst
		values (0, #{ltm_id}, #{sdm_id}, #{registe_date})
	</insert>
	
	<!-- 	1. 데이터베이스의 이름을 명확하게 설정
			2. alias를 적용 -->
	<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="hashmap" 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
		<!-- test에 조건을 넣음 -->
			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>
	</select>
	
</mapper>
    • searchCourse의 Response

    관련글

    티스토리툴바