본문 바로가기
Java Web Programming/6. Spring | MyBatis

[Spring/MyBatis] has a (aggregation) 관계 시, 구현방법 연습

by 파프리카_ 2020. 11. 3.
728x90
반응형

ERD (정규화)

* 3 정규화 : 일반 속성에 종속적인 속성을 분리 = 이행적 함수 종속 성을 분리

SQL

1. 부모테이블인 감독 테이블 생성

CREATE TABLE director(
    director_id    VARCHAR2(100) PRIMARY KEY,
    director_name  VARCHAR2(100) NOT NULL,
    intro          VARCHAR2(100) NOT NULL
);

 

2. 자식테이블인 영화 테이블 생성

-- 별도의 FOREIGN KEY 명시 없이 컬럼 바로 뒤에(컴마, 없이) 제약조건을 주어도 된다.

CREATE TABLE movie(
    movie_id    VARCHAR2(100) PRIMARY KEY,
    title       VARCHAR2(100) NOT NULL,
    genre       VARCHAR2(100) NOT NULL,
    attendance  NUMBER        DEFAULT 0,
    director_id VARCHAR2(100) NOT NULL
    CONSTRAINT fk_spring_director_id REFERENCES director(director_id)
)

Maven 설정

/pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>MyBatis-Spring-Study3</groupId>
	<artifactId>MyBatis-Spring-Study3</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<build>
		<sourceDirectory>src</sourceDirectory>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
	<dependencies>
		<!-- Spring Framework -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.3.14.RELEASE</version>
		</dependency>
		
		<!-- DBCP ver.2 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.1.1</version>
		</dependency>

		<!-- MyBatis Framework -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.4.0</version>
		</dependency>
		
		<!-- Spring과 MyBatis 연동 Framework -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.3.0</version>
		</dependency>

		<!-- JDBC(*Spring ver과 같아야한다) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.3.14.RELEASE</version>
		</dependency>
	</dependencies>
</project>

Spring 설정

/spring-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
	
	<bean id="dbcp" class="org.apache.commons.dbcp2.BasicDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
		<property name="username" value="scott"/>
		<property name="password" value="tiger"/>
	</bean>
	
	<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dbcp"/>
		<property name="mapperLocations" value="classpath:/mybatis/config/*.xml"/>
		<property name="typeAliasesPackage" value="org.kosta.model.vo"/>
		<!-- underScore 표기법을 Camel 표기법으로 mapping(변환)해주는 설정 -->
		<property name="configuration">
			<bean class="org.apache.ibatis.session.Configuration">
				<property name="mapUnderscoreToCamelCase" value="true"></property>
			</bean>
		</property>
	</bean>
	
	<bean id="SqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sqlSessionFactoryBean"/>
	</bean>

	<bean id="movieDAO" class="org.kosta.model.dao.MovieDAOImpl">
		<constructor-arg ref="SqlSessionTemplate"/>
	</bean>
	
</beans>

MyBatis 설정

/mybatis.config

 

★ insert 시, has a 관계 입력하기 ★
   : #{[vo객체] .[instance variable] }

 

★ select시, has a 관계 조회하기 ★

- MovieVO 생성 시, DirectorVO를 함꼐 생성하기 위해 resultMap사용 (resultMap은 상속이 가능하다)

* vo별칭1은 자식 VO(movieVO), vo별칭2는 부모 VO(directorVO)를 지정해준다. 

* 하나의 resultMap에 movieVO instance변수를 모두 넣어도 되지만, 성능을 위해 나누어서 생성하고, 

상속받아 사용한다.

<resultMap type="[vo별칭1]" id="[id1]">
    <result column="[SQL컬럼명]" property="[vo별칭2.인스턴스변수명]"/>
</resultMap>

<!-- resultMap(id=id1)을 상속 -->
<resultMap type="[vo별칭1]" id="[id3]" extends="[id1]">
    <result column="[SQL컬럼명2]" property="[vo별칭2.인스턴스변수명2]"/>
    <result column="[SQL컬럼명3]" property="[vo별칭2.인스턴스변수명3]"/>
</resultMap>

<select id="[id2]" resultMap="[id1]">
    <!--SQL 문-->
</select>

<select id="[id4]" resultMap="[id3]">
    <!--SQL문-->
</select>

 

/movie.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Sql Mapper -->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="movie">
 	<sql id="selectDirector">
 		SELECT director_id, director_name, intro
 		FROM   director
 	</sql>
 	
 	<insert id="registerDirector" parameterType="directorVO">
 		INSERT INTO director(director_id, director_name, intro) 
 		VALUES (#{directorId}, #{directorName}, #{intro})
 	</insert>
 	
 	<!-- java의 카멜표기법과 sql의 언더스코어표기법이 다르기에 AS(별칭)로 맞춰준다 -->
 	<select id="findDirectorById" resultType="directorVO">
		SELECT director_id AS directorId, director_name AS directorName, intro
		FROM   director
 		WHERE  director_id = #{value}
 	</select>

	<!-- spring-config.xml에 property에 추가해서, 카멜스코어와 언더스코어를 매핑처리해준다.
	     그래서 별도로 별칭표기를 해주지 않아도 된다 -->
 	<select id="findDirectorById2" resultType="directorVO">
		SELECT director_id, director_name, intro
		FROM   director
 		WHERE  director_id = #{value}
 	</select>
 	
 	<select id="getAllDirectorList" resultType="directorVO">
 		<include refid="selectDirector"/>
 	</select>
 	
 	<!-- insert 시, has a 관계 입력하기
 		 : #{directorVO.directorId}
 	 -->
 	<insert id="registerMovie" parameterType="movieVO">
 		INSERT INTO movie(movie_id, title, genre, attendance, director_id) 
 		VALUES (#{movieId}, #{title}, #{genre}, #{attendance}, #{directorVO.directorId})
 	</insert>
 	
 	<select id="getAllMovieAndDirectorList" resultType="map">
		SELECT m.title, m.genre, d.director_name 
		FROM   director d, movie m
		WHERE  d.director_id = m.director_id
 	</select>
 	
 	<!-- select 시, has a 관계 조회하기
 		 MovieVO 생성 시, DirectorVO를 함께 생성하기 위해
 		 resultMap을 사용
 		 : resultMap은 상속이 가능하다.
 	-->
 	
 	<!--  영화 리스트의 감독 정보 일부(이름만!) -->
 	<resultMap type="movieVO" id="movieListRM">
 		<result column="director_name" property="directorVO.directorName"/>
 	</resultMap>
 	
 	<select id="getAllMovieVOListAndDirectorVOList" resultMap="movieListRM">
 		SELECT m.title, d.director_name
		FROM   director d, movie m
		WHERE  d.director_id = m.director_id
 	</select>
 	
 	<!--  영화 상세 정보 (+감독 상세 정보)
 		  : movieListRM를 상속받아 추가 정보 넣음
 	 -->
 	<resultMap type="movieVO" id="detailMovieRM" extends="movieListRM">
 		<result column="director_id" property="directorVO.directorId"/>
 		<result column="intro" property="directorVO.intro"/>
 	</resultMap>
 	
 	<select id="findMovieAndDirectorVO" resultMap="detailMovieRM">
 		SELECT m.movie_id, m.title, m.genre, m.attendance, 
 			   d.director_id, d.director_name, d.intro
		FROM   director d, movie m
		WHERE  d.director_id = m.director_id AND movie_id=#{value}
 	</select>
 	
</mapper>

/org.kosta.model.vo

DirectorVO는 spring-config.xml에서 

<property name="typeAliasesPackage" value="org.kosta.model.vo"/> 로 설정하여, 

소문자로 시작하는 directorVO로 별칭이 정해진다.

 

/DirectorVO.java

package org.kosta.model.vo;

public class DirectorVO {
	private String directorId;
	private String directorName;
	private String intro;
	
	public DirectorVO() {
		super();
	}

	public DirectorVO(String directorId, String directorName, String intro) {
		super();
		this.directorId = directorId;
		this.directorName = directorName;
		this.intro = intro;
	}

	public String getDirectorId() {
		return directorId;
	}

	public void setDirectorId(String directorId) {
		this.directorId = directorId;
	}

	public String getDirectorName() {
		return directorName;
	}

	public void setDirectorName(String directorName) {
		this.directorName = directorName;
	}

	public String getIntro() {
		return intro;
	}

	public void setIntro(String intro) {
		this.intro = intro;
	}

	@Override
	public String toString() {
		return "DirectorVO [directorId=" + directorId + ", directorName=" + directorName + ", intro=" + intro + "]";
	}
	
}

 

/MovieVO.java

package org.kosta.model.vo;

public class MovieVO {
	private String movieId;
	private String title;
	private String genre;
	private int attendance;
	
	//has a 관계 (DirectorVO - MovieVO)
	private DirectorVO directorVO;

	public MovieVO() {
		super();
	}

	public MovieVO(String movieId, String title, String genre, int attendance, DirectorVO directorVO) {
		super();
		this.movieId = movieId;
		this.title = title;
		this.genre = genre;
		this.attendance = attendance;
		this.directorVO = directorVO;
	}

	public String getMovieId() {
		return movieId;
	}

	public void setMovieId(String movieId) {
		this.movieId = movieId;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getGenre() {
		return genre;
	}

	public void setGenre(String genre) {
		this.genre = genre;
	}

	public int getAttendance() {
		return attendance;
	}

	public void setAttendance(int attendance) {
		this.attendance = attendance;
	}

	public DirectorVO getDirectorVO() {
		return directorVO;
	}

	public void setDirectorVO(DirectorVO directorVO) {
		this.directorVO = directorVO;
	}

	@Override
	public String toString() {
		return "MovieVO [movieId=" + movieId + ", title=" + title + ", genre=" + genre + ", attendance=" + attendance
				+ ", directorVO=" + directorVO + "]";
	}
}

/org.kosta.model.dao

 

/MovieDAO.java <<interface>>

package org.kosta.model.dao;

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

import org.kosta.model.vo.DirectorVO;
import org.kosta.model.vo.MovieVO;

public interface MovieDAO {

   void registerDirector(DirectorVO directorVO);

   DirectorVO findDirectorById(String directorId);

   DirectorVO findDirectorById2(String directorId);

   List<DirectorVO> getAllDirectorList();

   void registerMovie(MovieVO mvo);

   List<Map<String, String>> getAllMovieAndDirectorList();

   List<MovieVO> getAllMovieVOListAndDirectorVOList();

   MovieVO findMovieAndDirectorVO(String movieId);

}

 

/MovieDAOImpl.java

package org.kosta.model.dao;

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

import org.kosta.model.vo.DirectorVO;
import org.kosta.model.vo.MovieVO;
import org.mybatis.spring.SqlSessionTemplate;

public class MovieDAOImpl implements MovieDAO {
   private SqlSessionTemplate template;
   
   public MovieDAOImpl(SqlSessionTemplate template) {
      super();
      this.template = template;
   }
   
   @Override
   public void registerDirector(DirectorVO directorVO) {
      template.insert("movie.registerDirector", directorVO);
   }

   @Override
   public DirectorVO findDirectorById(String directorId) {
      return template.selectOne("movie.findDirectorById", directorId);
   }

	@Override
	public DirectorVO findDirectorById2(String directorId) {
	  return template.selectOne("movie.findDirectorById2", directorId);
	}

	@Override
	public List<DirectorVO> getAllDirectorList() {
		return template.selectList("movie.getAllDirectorList");
	}

	@Override
	public void registerMovie(MovieVO mvo) {
		template.insert("movie.registerMovie", mvo);
	}

	@Override
	public List<Map<String, String>> getAllMovieAndDirectorList() {
		return template.selectList("movie.getAllMovieAndDirectorList");
	}

	@Override
	public List<MovieVO> getAllMovieVOListAndDirectorVOList() {
		return template.selectList("movie.getAllMovieVOListAndDirectorVOList");
	}

	@Override
	public MovieVO findMovieAndDirectorVO(String movieId) {
		return template.selectOne("movie.findMovieAndDirectorVO", movieId);
	}
   
}

/test

 

/TestMovieDAO.java

package test;

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

import org.kosta.model.dao.MovieDAO;
import org.kosta.model.vo.DirectorVO;
import org.kosta.model.vo.MovieVO;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestMovieDAO {
	public static void main(String[] args) {
		ClassPathXmlApplicationContext factory
			= new ClassPathXmlApplicationContext("spring-config.xml");
		//System.out.println(factory.getBean("SqlSessionTemplate"));
		
		MovieDAO dao = (MovieDAO) factory.getBean("movieDAO");
		
		dao.registerDirector(new DirectorVO("A", "봉준호", "칸 황금 종려상 수상"));
		dao.registerDirector(new DirectorVO("B", "데이빗 핀쳐", "소셜 네트워크 감독이다"));
		System.out.println("감독 정보 등록 완료");
		
		System.out.println("** 감독 아이디로 감독정보 출력**");
		String directorId = "A";
		System.out.println(dao.findDirectorById(directorId));
		System.out.println(dao.findDirectorById2(directorId)); 
		
		System.out.println("** 모든 감독정보 출력 **");
		List<DirectorVO> list = dao.getAllDirectorList();
		for (DirectorVO vo:list)
			System.out.println(vo);
		
		
		MovieVO mvo = new MovieVO();
		mvo.setMovieId("M2");
		mvo.setTitle("기생충");
		mvo.setGenre("스릴러");
		mvo.setAttendance(5000);
		DirectorVO dvo = new DirectorVO();
		dvo.setDirectorId("A");
		mvo.setDirectorVO(dvo);
		
		dao.registerMovie(mvo);
		System.out.println("영화 정보 등록 완료");
		
		// 영화와 감독 정보 반환받는 리스트 - map으로 반환받는 경우
		System.out.println("** 모든 영화와 감독 정보 리스트 (map반환) **");
		List<Map<String, String>> list2 = dao.getAllMovieAndDirectorList();
		for(Map<String, String> map1:list2) {
			System.out.println("영화명: " + map1.get("TITLE") + 
					" | 장르: " + map1.get("GENRE") +
					" | 감독명: " + map1.get("DIRECTOR_NAME"));
		}
		
		// 영화와 감독 정보 반환받는 리스트 - VO로 반환받는 경우
		System.out.println("** 모든 영화와 감독 정보 리스트 (VO반환) **");
		List<MovieVO> list3 = dao.getAllMovieVOListAndDirectorVOList();
		for(MovieVO mvo1:list3)
			System.out.println("영화명: " + mvo1.getTitle() +
					", 감독명: "+mvo1.getDirectorVO().getDirectorName());
		
		
		// 영화 상세 정보 (감독 상세정보 포함)
		System.out.println("** 영화 상세정보 **");
		String movieId = "M1";
		MovieVO m = dao.findMovieAndDirectorVO(movieId);
		System.out.println("영화아이디: " + m.getMovieId());
		System.out.println("타이틀: " + m.getTitle());
		System.out.println("장르: " + m.getGenre());
		System.out.println("관객수: " + m.getAttendance());
		System.out.println("감독 아이디: " + m.getDirectorVO().getDirectorId());
		System.out.println("감독 이름: " + m.getDirectorVO().getDirectorName());
		System.out.println("감독 이력: " + m.getDirectorVO().getIntro());
		
		
		factory.close();
	}
}

[ 결과 ]

728x90
반응형

'Java Web Programming > 6. Spring | MyBatis' 카테고리의 다른 글

[SpringMVC] xml 기반으로 SpringMVC 패턴 적용해보기  (0) 2020.11.04
[Spring/MyBatis] Spring MVC Pattern 적용해보기  (0) 2020.11.04
[Spring/MyBatis] JOIN 구문 연습 (inner join, outer join, group by)  (0) 2020.11.03
[Spring/MyBatis] Spring + MyBatis 적용 연습 2 ! (+CDATA, GROUP BY, LIKE, INSERT, Sequence 시퀀스)  (0) 2020.11.02
[MyBatis/Spring] 스프링에 마이바티스 적용해보기  (0) 2020.10.30

관련글

티스토리툴바