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

[Spring/MyBatis] JOIN 구문 연습 (inner join, outer join, group by)

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

SQL

1. 부모테이블 : spring_department TABLE 생성 및 INSERT

CREATE TABLE spring_department(
	deptno  NUMBER        PRIMARY KEY,
	dname   VARCHAR2(100) NOT NULL,
	loc     VARCHAR2(100) NOT NULL
)

INSERT INTO spring_department VALUES(10, '전략기획', '판교');
INSERT INTO spring_department VALUES(20, '해양수산', '부산');
INSERT INTO spring_department VALUES(30, '공공사업', '판교');

spring_department TABLE

2. 자식테이블 : spring_employmentTABLE 생성 및 INSERT

CREATE TABLE spring_employment(
    empno   number        PRIMARY KEY,
    ename   VARCHAR2(100) NOT NULL,
    sal     NUMBER        NOT NULL,
    deptno  NUMBER        NOT NULL,
    CONSTRAINT fk_spring_deptno FOREIGN KEY(deptno) REFERENCES spring_department(deptno)
)

-- 참조 무결성 제약조건 위배 예시
-- 부서테이블(부모테이블)에는 40번 부서가 없으므로 error
-- INSERT INTO spring_employment VALUES(1, '제임슨', 300, 40);

INSERT INTO spring_employment VALUES(1,'제임슨',300,10);
INSERT INTO spring_employment VALUES(2,'앨리사',500,10);
INSERT INTO spring_employment VALUES(3,'크림슨',400,20);

참조 무결성 (부모테이블에 존재하지 않는 정보를 등록할 수 없게끔! ) CONTRAINT를 통해 제약을 준다.

deptno는 FK(외래키, 참조키)로 지정해준다.

 

spring_employment TABLE

연습

* JOIN : 하나 이상의 테이블을 결합하여 조회

- Inner Join : 조인 조건에 일치하는 정보를 조회

- Outer Join : 조인 조건에 일치하지 않는 정보까지 모두 조회

   + Left Outer Join : 왼쪽 테이블 정보를 기준으로 결합한다. (왼쪽 테이블 정보가 조건에 부합하지 않아도 모두 결합)

- Self Join : 하나의 테이블을 자체적으로 조인

  1. Inner Join : 사원 번호를 통해 사원 정보와 사원의 부서정보를 반환
  2. Inner Join : 사원 정보가 있는 모든 부서정보와 사원정보를 리스트로 반환
  3. Outer Join : 사원 정보가 없는 모든 부서정보와 사원정보를 리스트로 반환 (사원정보가 없는 부서정보도 JOIN)
  4. Left Outer Join : 왼쪽 테이블(부서정보)를 기준으로 사원정보 테이블과 결합 - Outer Join 결과와 동일
    (모든 부서정보 나옴, 부서정보가 부모테이블이기 때문에 자식테이블인 사원정보도 모두 나옴)
  5. Right Outer Join : 오른쪽 테이블(사원정보)를 기준으로 부서정보 테이블과 결합 - Inner Join 결과와 동일
    (모든 사원정보 나옴,  부모테이블인 부서정보에 사원정보가 없는 경우, 모든 부서정보가 나오는 것은 아님)
  6. Group By : 부서 별 사원 수 리스트로 반환
  7. Group By + Join : 근무 지역(loc)  별 평균 급여 리스트로 반환 
    * 조인한 테이블에 Group By 적용 
  8. Inline View + Group By + Join : 부서 별 사원 수를 조회한 후, 부서 정보 별 사원수 리스트로 반환
    * Group By 적용한 테이블을 조인 (7번과 비교했을 때, 마지막에 조인을 한번 하는게 더 효율적이다!)
  9. Outer Join + NVL() + SubQuery(Inline View) + Group By

* Left /Right Outer Join 예시 (mapper.xml에 입력 시)

<!-- Left Outer Join -->
<select id="findAllEmpDeptList" resultType="map">
  SELECT e.empno, e.ename, d.deptno, d.dname, d.loc
  FROM   spring_department d 
  LEFT OUTER JOIN spring_employment e ON d.deptno = e.deptno
</select>

<!-- Right Outer Join -->
<select id="findAllEmpDeptList" resultType="map">
  SELECT e.empno, e.ename, d.deptno, d.dname, d.loc
  FROM   spring_department d 
  RIGHT OUTER JOIN spring_employment e ON d.deptno = e.deptno
</select>

Spring 설정

 

> spring-config.xml 설정

- bean 정의 : id=hrDAO, class=org.kosta.model.dao.HRDAOImpl으로 설정해준다.

<?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">
	
	<!-- DBCP 정의 -->
	<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>
	
	<!-- MyBatis와 Spring 연동 설정-->
	
	<!-- 1. sqlSessionFactoryBean -->
	<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- DBCP(datebase connection pool) 주입 -->
		<property name="dataSource" ref="dbcp"/>
		<!-- MyBatis에서 쓸 mapper loation(path) 주입 -->
		<property name="mapperLocations" value="classpath:/mybatis/config/*.xml"/>
		<!-- Package에 별칭주기 : 
		     org.kosta.model.vo 패키지 아래의 클래스는 
		     모두 자동으로 소문자로 시작하는 클래스명으로 별칭을 주도록 설정
		     ex) org.kosta.model.ProductVO 는 productVO로!  -->
		<property name="typeAliasesPackage" value="org.kosta.model.vo"/>
	</bean>
	
	<!-- 2. 개발 생산성을 위해 SqlSessionTemplate 클래스를 이용
		 SqlSessionTemplate은 선언적 방식의 트랜잭션 제어를 지원
		 (= AOP 기반 Transaction 제어) -->
	<bean id="SqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sqlSessionFactoryBean"/>
	</bean>

	<!-- 3. DAO 생성 
		: hrDAOImpl bean 생성
		- 생성 시에 생성자에 SqlSessionTemplate을 DI한다-->
	
	<bean id="hrDAO" class="org.kosta.model.dao.HRDAOImpl">
		<constructor-arg ref="SqlSessionTemplate"/>
	</bean>
	
</beans>

MyBatis 설정

 

/mybatis.config

 

- Inner Join에서는 조인 조건(WHERE d.deptno = e.deptno)에 의해서, 조건에 일치하는 사원과 부서정보만 조회한다. 

- 만약 사원이 존재하지 않는 부서정보(30번 부서)까지 모두 조회하고자 한다면 Outer Join이 필요하다.

 

- NVL(column, val) 함수 : null값을 val로 대체해서 표현

 

/hr.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="hr">
 	<select id="getTotalDepartmentCount" resultType="int">
 		SELECT COUNT(*)
 		FROM   spring_department
 	</select>
 	
 	<select id="getAllDeptListOrderByNoDesc" resultType="map">
 		SELECT   deptno, dname, loc
 		FROM     spring_department
 		ORDER BY deptno DESC
 	</select>
 	
 	<!-- resultType이 map일 때, 
 		 map의 key는 컬럼명을 대문자로 할당되고,
 		 map의 value는 반환되는 row의 key에 해당하는 컬럼의 값이 할당된다. -->
 	<select id="findEmployeeAndDeptByNo" resultType="map" parameterType="int">
 		SELECT  e.ename, d.dname, d.loc
		FROM    spring_department d, spring_employment e
		WHERE   d.deptno = e.deptno AND e.empno=#{value}
 	</select>
 	
 	<!-- Inner Join에서는 조인 조건 WHERE d.deptno = e.deptno 에 의해서
 		 조건에 일치하는 사원과 부서정보만 조회한다. -->
 	<select id="findAllEmpDeptList" resultType="map">
 		SELECT e.empno, e.ename, d.deptno, d.dname, d.loc
 		FROM   spring_department d, spring_employment e
 		WHERE  d.deptno = e.deptno
 	</select> 
 	
 	<!-- Outer Join은 조인 조건에 일치하지 않는 정보가 있는 테이블 측에
 	     연산자 (+)를 표기하면 된다. - 사원이 없으므로 사원 측에 (+)
 	     * DB의 관점으로 보기! -->
 	<select id="findAllEmpDeptListOuterJoin" resultType="map">
 		SELECT e.empno, e.ename, d.deptno, d.dname, d.loc
 		FROM   spring_department d, spring_employment e
 		WHERE  d.deptno = e.deptno(+)
 	</select>
 	
 	<!-- GROUP BY -->
 	<select id="getEmpGroupByDeptno" resultType="map">
 		SELECT   deptno, COUNT(*) AS EMP_COUNT
		FROM     spring_employment
		GROUP BY deptno
 	</select>

	<!-- GROUP BY + JOIN -->
 	<select id="getSalGroupByLoc" resultType="map">
	 	SELECT   d.loc, AVG(e.sal) AS AVG_SAL
		FROM     spring_department d, spring_employment e
		WHERE    d.deptno = e.deptno
		GROUP BY d.loc
 	</select>
 	
 	<!-- GROUP BY + JOIN 
 		 NVL(column, val) 함수 : null값을 val로 대체해서 표현	
 	-->
 	<select id="getEmpGroupByDeptno2" resultType="map">
		SELECT   d.deptno, d.dname, NVL(e.EMP_COUNT,0) AS EMP_COUNT
		FROM     (SELECT   COUNT(empno) AS EMP_COUNT, deptno
				  FROM     spring_employment 
				  GROUP BY deptno) e, spring_department d
		WHERE    d.deptno = e.deptno(+) 
 	</select>
 	
</mapper>

/org.kosta.model.dao

 

/HRDAO.java <<interface>>

package org.kosta.model.dao;

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

public interface HRDAO {

	int getTotalDepartmentCount();

	List<Map<String, Object>> getAllDeptListOrderByNoDesc();

	Map<String, Object> findEmployeeAndDeptByNo(int empno);

	List<Map<String, Object>> findAllEmpDeptList();
	
	List<Map<String, Object>> findAllEmpDeptListOuterJoin();

	List<Map<String, Object>> getEmpGroupByDeptno();

	List<Map<String, Object>> getSalGroupByLoc();

	List<Map<String, Object>> getEmpGroupByDeptno2();
	
}

 

/HRDAOImpl.java

package org.kosta.model.dao;

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

import org.mybatis.spring.SqlSessionTemplate;

public class HRDAOImpl implements HRDAO {
	//MyBatis Frame에서 제공해주는 Spring 연동을 위한 객체
	private SqlSessionTemplate template;

	//spring container로부터 SqlSessionTemplate 객체를 주입(DI)
	public HRDAOImpl(SqlSessionTemplate template) {
		super();
		this.template = template;
	}
	
	// 1. 전체 부서 수 반환
	@Override
	public int getTotalDepartmentCount() {
		return template.selectOne("hr.getTotalDepartmentCount");
	}

	// 2. 번호 별로 전체 부서 정보 리스트 반환
	@Override
	public List<Map<String, Object>> getAllDeptListOrderByNoDesc() {
		return template.selectList("hr.getAllDeptListOrderByNoDesc");
	}

	// 3. 사원 번호로 해당 사원정보와 부서정보 반환
	@Override
	public Map<String, Object> findEmployeeAndDeptByNo(int empno) {
		return template.selectOne("hr.findEmployeeAndDeptByNo", empno);
	}

	// 4. 사원정보가 있는 모든 부서정보와 사원정보를 리스트로 반환 (Inner Join)
	@Override
	public List<Map<String, Object>> findAllEmpDeptList() {
		return template.selectList("hr.findAllEmpDeptList");
	}
	
	// 5. 사원정보가 없는 모든 부서정보와 사원정보를 리스트로 반환 (Outer Join)
	@Override
	public List<Map<String, Object>> findAllEmpDeptListOuterJoin() {
		return template.selectList("hr.findAllEmpDeptListOuterJoin");
	}

	// 6. 부서 별 사원 수 리스트로 반환 (Group By)
	@Override
	public List<Map<String, Object>> getEmpGroupByDeptno() {
		return template.selectList("hr.getEmpGroupByDeptno");
	}
	
	// 7. 근무 지역(loc) 별 평균 연봉 리스트로 반환 (Group By + JOIN)
	@Override
	public List<Map<String, Object>> getSalGroupByLoc(){
		return template.selectList("hr.getSalGroupByLoc");
	}

	// 8. 부서 별 사원 수와 부서 이름 리스트로 반환 (Outer Join + NVL함수 + Inline View + Group By)
	@Override
	public List<Map<String, Object>> getEmpGroupByDeptno2() {
		return template.selectList("hr.getEmpGroupByDeptno2");
	};

}

/test

 

/TestHRDAO.java

package test;

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

import org.kosta.model.dao.HRDAO;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestHRDAO {
	public static void main(String[] args) {
		ClassPathXmlApplicationContext factory
			= new ClassPathXmlApplicationContext("spring-config.xml");
		
		HRDAO dao = (HRDAO) factory.getBean("hrDAO");
		
		// 1. 전체 부서 수 반환
		System.out.println("** 1. 전체 부서 수 반환 **");
		System.out.println(dao.getTotalDepartmentCount());
		
		// 2. 번호 별로 전체 부서 정보 리스트 반환
		System.out.println("\n** 2. 번호 별로 전체 부서 정보 리스트 반환 **");
		List<Map<String, Object>> list = dao.getAllDeptListOrderByNoDesc();
		for (Map<String, Object> map:list) {
			System.out.println(map.get("DEPTNO") + " " 
					+ map.get("DNAME") + " " + map.get("LOC"));
		}
		
		// 3. 사원 번호를 통해 사원 정보 반환 (Self Join)
		System.out.println("\n** 3. 사원 번호를 통해 사원 정보 반환 **");
		int empno = 1;
		Map<String, Object> map = dao.findEmployeeAndDeptByNo(empno);
		if (map != null)
			System.out.println(map.get("ENAME")+ " "+map.get("DNAME")+ " "+ map.get("LOC"));
		else
			System.out.println("사원번호 '" + empno + "'에 해당하는 사원 없음");
			//ex) 사원번호 '10' 에 해당하는 사원 없음
		
		
		// 4. 사원정보가 있는 모든 부서정보와 사원정보를 리스트로 반환 (Inner Join)
		System.out.println("\n** 4. 사원정보가 있는 모든 부서정보와 사원정보를 리스트로 반환 (Inner Join) **");
		List<Map<String, Object>> list6 = dao.findAllEmpDeptList();
		for(Map<String, Object> map1:list6) {
			System.out.println(map1.get("EMPNO") + " " +map1.get("ENAME") + " "
					+ map1.get("DEPTNO") + " " + map1.get("DNAME") + " "
					+ map1.get("LOC"));
		}
	
		// 5. 사원정보가 없는 모든 부서정보와 사원정보를 리스트로 반환 (Outer Join)
		System.out.println("\n** 5. 사원정보가 없는 모든 부서정보와 사원정보를 리스트로 반환 (Outer Join) **");
		List<Map<String, Object>> list2 = dao.findAllEmpDeptListOuterJoin();
		for(Map<String, Object> map2:list2) {
			System.out.println(map2.get("EMPNO") + " " + map2.get("ENAME") + " "
					+ map2.get("DEPTNO") + " " + map2.get("DNAME") + " "
					+ map2.get("LOC"));
		}
		
		// 6. 부서 별 사원 수 리스트로 반환 (Group By)
		System.out.println("\n** 6. 부서 별 사원 수 리스트로 반환 (Group By) **");
		List<Map<String, Object>> list3 = dao.getEmpGroupByDeptno();
		for (Map<String, Object> map3:list3) {
			System.out.println(map3.get("DEPTNO") + "번 부서 사원수: " 
						+ map3.get("EMP_COUNT") + "명");
		}
		
		// 7. 근무 지역(loc) 별 평균 연봉 리스트로 반환 (Group By + JOIN)
		System.out.println("\n** 7. 근무 지역(loc) 별 평균 연봉 리스트로 반환 (Group By + JOIN) **");
		List<Map<String, Object>> list4 = dao.getSalGroupByLoc();
		for (Map<String, Object> map4:list4) {
			System.out.println(map4.get("LOC") + " 지역: " 
					+ map4.get("AVG_SAL") + "원");
		}
		
		// 8. 부서 별 사원 수와 부서 이름 리스트로 반환 (Outer Join + NVL함수 + SubQuery(Inline View) + Group By)
		System.out.println("\n** 8. 부서 별 사원 수와 부서 이름 리스트로 반환 (Outer Join + NVL함수 + Inline View + Group By) **");
		List<Map<String, Object>> list5 = dao.getEmpGroupByDeptno2();
		for (Map<String, Object> map5:list5) {
			System.out.println(map5.get("DNAME") +"부(" +map5.get("DEPTNO")+ "번): "
					+ map5.get("EMP_COUNT") + "명");
		}
		
		factory.close();
	}
}

[ 결과 ]

728x90
반응형

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

[Spring/MyBatis] Spring MVC Pattern 적용해보기  (0) 2020.11.04
[Spring/MyBatis] has a (aggregation) 관계 시, 구현방법 연습  (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
[MyBatis] 마이바티스 적용 연습 2 ! (SELECT, INSERT, UPDATE + include & List, Map 타입 활용)  (1) 2020.10.30

관련글

티스토리툴바