64일: SQL 복습, MyBatis와 SQL

2022.02.15.Tue.

✍ 복습

SQL 복습

-- CRUD
-- select, insert, delet, update
    -- insert의 경우 테이블의 모든 컬럼에 순서대로 값을 입력하면 컬럼 이름 생략 가능
    desc emp;
    insert into emp values(2000, 'spring', 'manager', null, sysdate, 2000, null, 10);
    insert into emp(empno, ename, job, sal, deptno) values(2000, 'spring', 'manager', 2000, 10);
    -- mybatis의 경우에는 기본적으로 null 입력이 막혀있다 → 필수입력만 입력받아라
    -- null(선택입력) 입력을 받으려면? #{컬럼명, mybatis jdbctype}
        -- insert into emp(empno, sal, job) values(#{empno}, #{sal,JdbcType.INTEGER}, #{job,JdbcType.VARCHAR});
        
    -- update
    update emp set ename='summer', sal=1000 where empno=3333;
    -- delete
    delete from emp where emp=3333;
    
    -- 게시판에서 글을 변경하는 경우 - 글쓴이 확인이 필요
    -- 하나의 작업에 둘 이상의 sql이 필요한 경우 → transaction
    select * from board where bno=10;
        -- 글쓴이와 로그인한 아이디 비교
    update board set title='제목입니다', content='내용입니다' where bno=10;
    
-- 테이블 설계 : 정규화 한다(정규화를 하면 테이블이 분리) → 출력할 때 정보를 합쳐야한다(조인)
    -- 조인을 하려면 조건을 지정할 공통 컬럼이 있어야 한다.(조건없이 조인하면 cross join)
    -- emp(사번,급여,부서번호...), dept(부서번호...), salgrade(급여등급, 최저급여, 최고급여)
    -- inner join - deptno가 두번 나온다.
        -- emp, dept 테이블이 있다 → Dao에 Emp, Dept 클래스가 있다
        -- 조인한 결과는 Emp나 Dept로 받을 수가 없다.
    select * from emp e inner join dept d on e.deptno=d.deptno;
    
    -- inner join을 하면 조건을 만족하는 값만 출력된다.
        -- dept에는 40번 부서가 있지만 사원 중에는 40번 부서에 근무하는 사원이 없다
        -- 연결 고리가 e.deptno=d.deptno → ? = 40 → 40번 부서는 출력되지 않는다
        
    -- 조건이 성립하지 않는 행도 출력해라 → outer join
    select * from emp e right outer join dept d on e.deptno=d.depno;
    select * from dept d full outer join emp e on d.deptno=e.deptno;
    
    -- 내부조인 중에 관계가 =인 조인을 동등조인, 동일조인, 등가조인, equi조인
    select * from emp e inner join salgrade s on e.sal between s.losal and hisal;
    

MyBatis와 SQL

pom.xml에 log4jdbc dependency 코드 추가

	<dependency>
		<groupId>org.bgee.log4jdbc-log4j2</groupId>
		<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
		<version>1.16</version>
	</dependency>
    

application.properties 코드 추가

server.port=8081

spring.datasource.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=scott
spring.datasource.password=1234

## com.example.demo.entity.Emp를 emp란 별칭으로 부르겠다
mybatis.type-aliases-package = com.example.demo.entity
## sql을 작성한 xml 파일의 경로
mybatis.mapper-locations = mapper/*.xml

log4jdbc.log4j2.properties, logback-spring.xml 파일 추가

empMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.EmpDao">
	<select resultType="emp" id="findAll">
	</select>
	
	<select resultType="emp" id="findById">
		select * from emp where empno=#{empno} and rownum=1
	</select>
	
	<!-- CDATA 영역은 xml이 아니라 그냥 글자야...라고 지정하는 영역 -->
	<select resultType="string" id="findEnameBySalLessThan">
		<![CDATA[
			select ename from emp where sal<=#{sal}
		]]>
	</select>
	
	<select resultType="emp" id="findBy2Condition1">
		<![CDATA[
			select * from emp where sal<=#{sal} and deptno=#{deptno}
		]]>
	</select>
	
	<select resultType="emp" id="findBy2Condition2">
		<![CDATA[
			select * from emp where sal<=#{sal} and deptno=#{deptno}
		]]>
	</select>
	
	<insert id="insert">
		insert into emp(empno, ename, sal) values(#{empno}, #{ename}, #{sal})
	</insert>
	
	<update id="update">
		update emp set ename=#{ename}, sal=#{sal} where empno=#{empno}
	</update>
	
	<delete id="delete">
		delete from emp where empno=#{empno}
	</delete>
	
	<!-- 이름이 존재하는가? SMITH를 주면 스미스라 사람이 있는가? -->
	<!-- 결과가 true/false로 나오는 select 문을 작성하고 싶다 -->
	<!-- db에는 boolean이 없다. 1이 참, 0이 거짓 -->
	<!-- count()는 조건을 만족하는 개수를 센다 -->
	<!-- count(empno)라고 적은 이유는 count는 null이면 안센다. 그래서 null값을 가질 수 없는 기본키를 세는 것이 안전하다 -->
	<select id="existsByUsername" resultType="boolean">
		select count(empno) from emp where ename=#{ename}
	</select>
	
	<!--
		조인을 하면 resultType에 엔티티를 사용할 수 없다
		해결책 1. Map을 사용
		해결책 2. 값을 담을 클래스를 만든다(DTO) -> DTO가 너무 많이 필요하다
			class Join1 { int empno; String ename; Double sal, Integer grade}
	 -->
	<select id="join1" resultType="map">
		select empno, ename, sal, grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal
	</select>
	
	<select id="join2" resultType="map">
		select empno, ename, sal, dname from emp e inner join dept d on e.empno=d.deptno
	</select>
	
	<select id="join3" resultType="map">
		select empno, ename, sal, dname, grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal
		inner join dept d on e.empno=d.deptno
	</select>
</mapper>

EmpDao.java

package com.example.demo.dao;

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

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import com.example.demo.entity.Emp;

// 설정 파일에는 xml 파일 위치와 엔티티 별칭을 만들 패키지의 위치를 지정한다
//		mapper/*.xml, com.example.demo.entity
// 즉 DAO에 대한 설정은 존재하지 않는다 

// 마이바티스가 xml을 읽어서 문서의 namespace로 DAO의 이름을 찾는다
//		<mapper namespace="com.example.demo.dao.EmpDao">
// xml에 작성한 sql문은 id를 가져야만 되고 그 id가 DAO의 메소드 이름이다

// xml에 작성한 select문은 반드시 resultType을 지정해야 한다
// select의 실행 결과는 1개 또는 여러개 -> 이 정보는 xml에는 나타나지 않는다(지금 같은 경우 xml은 그냥 emp)
// 결과가 1개/여러개는 DAO의 메소드 리턴으로 표현한다
@Mapper
public interface EmpDao {
	public List<Emp> findAll();							
	
	public Emp findById(Integer empno);
	
	public List<String> findEnameBySalLessThan(Integer sal);
	
	// select * from emp where sal<=#{sal} and deptno=#{deptno}
	// 아래의 경우 s, d란 이름이 아닌 @Param의 이름과 sql의 파라미터 이름이 일치해야 했다
	// 어느 순간부터 @Param을 지정안하고 메소드 파라미터와 sql 파라미터 이름을 맞추면 되더라
	public List<Emp> findBy2Condition1(@Param("sal") Integer s, @Param("deptno") Integer d);
	public List<Emp> findBy2Condition2(Integer sal, Integer deptno);
	
	public Integer insert(Emp emp);
	
	public Integer update(Emp emp);
	
	public Integer delete(Integer empno);
	
	public Boolean existsByUsername(String username);
	
	public List<Map<String,Object>> join1();
	
	public List<Map<String,Object>> join2();
	
	public List<Map<String,Object>> join3();
}

EmpDaoTest.java

package com.example.demo;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;

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

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

import com.example.demo.dao.EmpDao;
import com.example.demo.entity.Emp;

// 테스트를 하라면 설정 파일을 읽어들여 ApplicationContet를 생성
@SpringBootTest
public class EmpDaoTest {
	@Autowired
	private EmpDao empDao;
	
	//@Test
	public void initTest() {
		assertNotNull(empDao);
	}
	
	// mapper의 namespace를 com.example.demo.dao.XXX로 지정
		// application.properties의 설정에 따라 xml을 찾고 xml에 따라 dao와 메소드 이름을 찾는다
		// 못 찾으면 invalid bound statement(not found) : com.example.demo.dao.EmpDao.findAll
		// xml과 dao를 연결하는 방식 : xml에서 dao를 찾는다
		// 30번째 줄에서 empDao.findAll()을 실행 -> 마이바티스에서 연결된 sql을 찾는다 -> 못 찾았다
	
	// Mapped Statements collection already contains value for com.example.demo.dao.EmpDao.findAll
	// 오타 있다
	
	// invalid character : xml sql에 ;을 찍었다
	//@Test
	public void findAllTest() {
		assertEquals(15, empDao.findAll().size());
	}
	
	//@Test
	public void findByIdTest() {
		assertEquals(7369, empDao.findById(7369).getEmpno());
	}
	
	//@Test
	public void findEnameTest() {
		List<String> list = empDao.findEnameBySalLessThan(3000);
		list.forEach(e->System.out.println(e));
		/*
			for(String e:list)
				System.out.println(e);
		*/
	}
	
	//@Test
	public void findBy2Condition() {
		empDao.findBy2Condition1(3000, 10);
	}
	
	// 테스트할 때 변경 작업 후 작업 취소
	@Transactional
	//@Test
	public void CrUDTest() {
		Emp emp = Emp.builder().empno(3333).ename("summer").sal(5000.0).build();
		empDao.insert(emp);
		emp.setSal(7000.0);
		empDao.update(emp);
		empDao.delete(3333);
	}
	
	//@Test
	public void existsByUsernameTest() {
		System.out.println(empDao.existsByUsername("SMITH"));
		System.out.println(empDao.existsByUsername("HELL"));
	}
	
	//@Test
	public void mapTest() {
		// js에서 설계도(클래스)없이 바로 이름과 값의 pair으로 객체를 구성 -> 자바에서는 Map
		// const saram = {id:'spring', nai:20}; 
		
		// List 참조변수 만들고 객체는 전부 ArrayList인 것 처럼, Map 참조변수의 객체는 전부 HashMap
		Map<String,Object> map = new HashMap<>();
		map.put("id", "spring");
		map.put("nai", 20);
		
		System.out.println(map.get("id"));
		System.out.println(map.get("nai"));
		
		Emp emp = new Emp();
		//emp.setEmPno(1369);				// 오타를 내면 에러가 난다 -> 클래스는 검증이 된다
		
		System.out.println(map.get("Id"));	// map은 오타 검증이 없다
	}
	
	@Test
	public void join1Test() {
		empDao.join1();
		
		empDao.join1().forEach(map->System.out.println(map.get("empno")));
		
		// 마이바티스에서 resultType으로 맵을 사용하면 이름이 무조건 대문자로 들어간다
		empDao.join1().forEach(map->System.out.println(map.get("EMPNO")));
	}
}