20220914 [Spring Boot, Oracle, MyBatis]

📖 SQL의 종류

SQL = Structured Query Language

• 데이터 정의(DDL)
• 데이터 조작(DML)
• 데이터 제어(DCL)
• 트랜잭션 제어어(TCL)

📌 DCL(데이터 제어어)

DCL(데이터 제어어) = Data Control Language
: 사용자에게 권한 생성 혹은 권한 삭제 명령어

• CREATE : 계정 생성
• DROP : 계정 삭제
• GRANT : 권한 생성
• REVOKE : 권한 삭제

📌 DDL(데이터 정의어)

DDL(데이터 정의어) = Data Definition Language
: 데이터의 구조를 정의하기 위한 테이블 생성, 수정, 삭제 명령어

• CREATE : 테이블 생성
• DROP : 테이블 삭제
• ALTER : 테이블 수정
• TRUNCATE : 테이블에 있는 모든 데이터 삭제
• RENAME : 테이블명 변경

📌 DML(데이터 조작어)

DML(데이터 조작어) = Data Manipulation Language
: 데이터 추가, 조회, 수정 및 삭제를 위한 명령어
➡️ commit 또는 rollback 사용가능

• SELECT : 데이터 조회
• INSERT : 데이터 입력
• UPDATE : 데이터 수정
• DELETE : 데이터 삭제

📌 TCL(트랜잭션 제어어)

TCL(트랜잭션 제어어) = Transaction Control Language

• COMMIT : 적용하기
• ROLLBACK : 되돌리기
• SAVEPOINT : 저장점을 정의

---

📃 게시물 댓글

게시판 댓글작성

• 게시글 하나에 여러개의 댓글 = 이미지와 같음
• 댓글 테이블, 시퀀스 ⇒ 오라클에서 생성후 BOARDREPLYMAPPER 생성, 나머지는 SPRING에서 작성
• DTO 없이 MAP으로 반환하여 생성해보기
• 댓글 여러개 등록시 map을 list에 넣어주면 여러개 댓글 등록 가능하다!

ORACLE

• 댓글 작성용 테이블 생성
• 댓글 번호용 시퀀스 생성
• 시퀀스 함수 생성

--댓글 작성용 테이블 생성
CREATE TABLE BOARDREPLYTBL(
    NO NUMBER CONSTRAINT PL_BOARDREPLY_NO PRIMARY KEY,
    BRDNO NUMBER CONSTRAINT FK_BOARDREPLY_BRDNO REFERENCES BOARDTBL(NO),
    CONTENT CLOB,
    WRITER VARCHAR2(100),
    REGDATE TIMESTAMP DEFAULT CURRENT_DATE
);

--댓글 번호용 시퀀스 생성
CREATE SEQUENCE SEQ_BOARDREPLY_NO START WITH 1001
    INCREMENT BY 1 NOMAXVALUE NOCACHE;

--시퀀스 함수 생성
CREATE OR REPLACE FUNCTION FUNC_REPLYSEQ RETURN NUMBER
IS
BEGIN
    RETURN SEQ_BOARDREPLY_NO.NEXTVAL;
END;
/

📙 댓글 추가하기(1개)

ORACLE

INSERT ALL
    INTO BOARDREPLYTBL 
        VALUES(FUNC_REPLYSEQ, 71, '댓글내용', '작성자1', CURRENT_DATE)
    INTO BOARDREPLYTBL 
        VALUES(FUNC_REPLYSEQ, 71, '댓글내용', '작성자1', CURRENT_DATE)
    INTO BOARDREPLYTBL 
        VALUES(FUNC_REPLYSEQ, 71, '댓글내용', '작성자1', CURRENT_DATE)
SELECT * FROM DUAL;

SELECT BR.* FROM BOARDREPLYTBL BR;

BoardReplyMapper.java

    @Insert({
        " INSERT INTO BOARDREPLYTBL(NO, BRDNO, CONTENT, WRITER) ",
        " VALUES(SEQ_BOARDREPLY_NO.NEXTVAL, #{map.brdno}, ",
        " #{map.content}, #{map.writer} )"
    })
    public int insertReply(@Param("map") Map<String, Object> map);

BoardReplyTest.java

    @Test
    void insertTest(){
        Map<String, Object> map = new HashMap<>();
        map.put("brdno", 71L);
        map.put("content", "댓글내용");
        map.put("writer","댓글작성자");
        int ret = brMapper.insertReply(map);
        log.info(format, ret);
    }

📙 게시물 댓글 목록

ORACLE

BoardReplyMapper.java

BoardReplyTest.java

📙 댓글 수정

ORACLE

BoardReplyMapper.java

BoardReplyTest.java

📙 댓글 삭제

ORACLE

BoardReplyMapper.java

BoardReplyTest.java

📙 게시물 + 답글 inner join

ORACLE

BoardReplyMapper.java

BoardReplyTest.java

---

💻 Mybatis Mapper XML

공식홈페이지도 xml기준의 메뉴얼이 작성되어 있으며 쿼리문+자바문법을 이용하여 SQL문을 작성하는것보다 전반적으로 XML형식의 SQL문을 더 많이 사용한다

📁 ItemDTO.java

@Getter
@Setter
@ToString
@NoArgsConstructor
public class ItemDTO {
    Long no;
    String name;
    String content;
    Long price;
    Long quantity;
    Date regdate;
}

📙 아이템 등록(1개)

📁 ItemMapper.java

• ItemMapper.java 에서는 틀만 만들어 주고,
  쿼리문은 itemMapper.xml에서 넘어온다고 생각하면 된다

import org.apache.ibatis.annotations.Mapper;

import com.example.vo.ItemDTO;

@Mapper
public interface ItemMapper {

    // @param을 정의할 필요가 없음
    public int insertItem( ItemDTO item );
    
}

📁 resources / mappers / itemMapper.xml

• xml파일은 바로 호출이 불가하며 JAVA 파일 하나 있어야 테스트도 가능하다!
  ➡️ 생성한 itemMapper.xml을 사용하기 위해 ItemMapper.java에 쿼리문을 넘겨준다
• XML mapper 사용하면 2개 이상의 파라미터 전달 불가!
  ➡️ @Param 사용 불가하기 때문에 하나만 전달 할 수 있다 = @Param을 정의할 필요가 없다
• int가 자동으로 넘어가니 result없어도 된다

<?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.mapper.ItemMapper">
    <insert id="insertItem" parameterType="com.example.vo.ItemDTO">
        INSERT INTO ITEMTBL(NO, NAME, CONTENT, PRICE, QUANTITY, REGDATE)
        VALUES(SEQ_ITEM_NO.NEXTVAL, #{name}, #{content}, #{price}, #{quantity}, CURRENT_DATE)
    </insert>

    <select id="selectListItem" resultType="com.example.vo.ItemDTO">
        SELECT * FROM ITEM
    </select>
</mapper>

📁 ItemTest.java

@SpringBootTest
@Slf4j
public class ItemTest {
    final String format = "ITEMTBL => {}";
    
    @Autowired
    ItemMapper iMapper;

    // 아이템 등록
    @Test
    void insert(){
        ItemDTO item = new ItemDTO();
        item.setName("test물품");
        item.setContent("test내용");
        item.setPrice(1212L);
        item.setQuantity(12L);
        int ret = iMapper.insertItem(item);
        log.info(format, ret);
    }

📙 아이템 조회

📁 ItemMapper.java

// xml에서 자동으로 찾아서 실행됨
    public List<ItemDTO> selectListItem();

📁 resources / mappers / itemMapper.xml

    <select id="selectListItem" resultType="com.example.vo.ItemDTO">
        SELECT * FROM ITEM
    </select>

📁 ItemTest.java

    // 아이템 조회
    @Test
    void selectList(){
        List<ItemDTO> list = iMapper.selectListItem();
        for(ItemDTO item : list){
            log.info(format, item.toString());
        }
    }

📃 아이템 조회(ResultMap 사용)

• ResultMap이란?
  복잡한 결과 매핑을 간편하게 만들어주기 위해 만들어진 태그
  공식문서참고
  ➡️ DB컬럼명과 DTO의 변수명이 다른경우 setter가 동작이 되지 않는경우 대안방안코드로 사용된다

<result property="no" column="NO" jdbcType="NUMERIC" javaType="Long" />

• property = itemDTO의 값 / column = sql데이터의 값
  보이지 않지만 자동적으로 result로 변환이 되며, apptest에서 결과값을 받은 이후 결과값의 타입의 변환이 필요하다면 ResultMap 사용한다
• 결과를 출력했을때 읽을 수 없는 형태이거나, 원하는 형태로 출력이 되지 않았을 경우에 result를 명시하여 원하는 타입으로 변경하여 사용해준다

📁 itemMapper.xml

<resultMap id="resultMap1" type="com.example.vo.ItemDTO">
	<result property="no" column="NO" jdbcType="NUMERIC" javaType="Long" />
</resultMap>

	<select id="selectListItem" resultMap="resultMap1">
        SELECT I.* FROM ITEMTBL I
    </select>

📙 아이템 1개 조회

📁 ItemMapper.java

public ItemDTO selectOneItem(Long no);

📁 resources / mappers / itemMapper.xml

    <select id="selectOneItem" parameterType="Long" resultType="com.example.vo.ItemDTO">
        SELECT I.* FROM ITEMTBL I WHERE I.NO = #{no}
    </select>

📁 ItemTest.java

    @Test
    void selectOneItem(){
        ItemDTO item = iMapper.selectOneItem(1008L);
        log.info(format, item.toString());
    }

📙 재고수량이 n개 미만인것 조회

📁 ItemMapper.java

public List<ItemDTO> selectQuantityList(Long quantity);

📁 resources / mappers / itemMapper.xml

    <select id="selectQuantityList" parameterType="Long" resultType="com.example.vo.ItemDTO">
        SELECT I.* FROM ITEMTBL I WHERE I.QUANTITY <![CDATA[ > ]]> #{quantity}
    </select>

📁 ItemTest.java

    @Test
    void selectQuantityList(){
        List<ItemDTO> item = iMapper.selectQuantityList(50L);
        log.info(format, item.toString());
    }

🤯 오류

Mybatis 쿼리문에서 부등호 기호를 사용하는 경우 오류가 발생한다
<![CDATA[ ]]>의 [ ]안에 원하는 연산자를 입력하면 된다
🌎참고링크

위와같이 사용하니 오류없이 작동한다

📙 특정날짜에 등록된 항목만 조회

9월 14일에 등록된 항목만 조회하기

📁 ItemMapper.java

public List<ItemDTO> selectRegdateList(String regdate);

📁 resources / mappers / itemMapper.xml

    <select id="selectRegdateList" parameterType="String" resultType="com.example.vo.ItemDTO">
        SELECT I.* FROM ITEMTBL I WHERE TO_CHAR(REGDATE, 'yy-mm-dd') = #{regdate}
    </select>

📁 ItemTest.java

    @Test
    void selectRegdateList(){
        List<ItemDTO> item = iMapper.selectRegdateList("22-09-14");
        log.info(format, item.toString());
    }

📙 특정날짜의 금액합계, 금액평균, 수량합계 조회

9월 14일 등록된 물품 금액합계, 금액평균, 수량합계 조회

📁 ItemMapper.java

public List<Map<String, Object>> selectRegdateGroup();

📁 resources / mappers / itemMapper.xml

DTO에 없는 변수를 새로 지정하는 경우 ➡️ 그냥 변수명 적어주면 생성된 map에 데이터가 들어간다

• REGDATE = 새로 지정된 변수명
• #{REGDATE} = 값을 넘겨주는 변수명

    <select id="selectRegdateGroup" resultType="Map">
        SELECT 
            TO_CHAR(REGDATE, 'yy-mm-dd') REGDATE, SUM(I.PRICE) SUMPRICE, AVG(I.PRICE) AVGPRICE, SUM(I.QUANTITY) SUMQAN, COUNT(*) CNT
        FROM
            ITEMTBL I 
        WHERE
            TO_CHAR(REGDATE, 'yy-mm-dd') = '22-09-14'
        GROUP BY
            TO_CHAR(REGDATE, 'yy-mm-dd')
    </select>

📁 ItemTest.java

    @Test
    void selectRegdateGroup(){
        List<Map<String, Object>> list = iMapper.selectRegdateGroup();
        log.info(format, list.toString());
    }