54일차 insert all(seq문제), 트리거(프로시저), vscode에서 일괄 등록

oracle

54일차 insert all(seq문제), 트리거(프로시저), vscode에서 일괄 등록

비루블 2022. 9. 13. 12:54

요약정리

목요일 평가

vscode에서 sql문 쓸때 script 쓰는 이유는 반복문 돌릴때 오류나서 넣은 것이라고 함,

일괄 등록 insert all 쓸때 시퀀스 문제 때문에, insert all을 사용하기 위한 함수 FUNC_BOARDSEQ를 만듬(함수 생성)

프로시저 매크로
함수 간단하게 쓰고 데이터 리턴
트리거도 매크로 같은 절차(행동이 일어날때 감지, 프로시저로 만듬)

트리거만듬(데이터를 백업해주는 용도 BOARDTBL_BACK)

감지하여 BACK에도 데이터를 넣어줌

프로시저(트리거) 만들어 놓으니
자동적으로 BOARDTBL_BACK에 자동 COMMIT 되는 중

일괄 처리의 중요성

넣어줄꺼면 한번에 넣어주고 아니면 false 처리

하나씩 넣어주다보면 롤백 처리가 굉장히 힘듬.

DTO나 VO나 똑같은 말임.

오전일과

-- 22_09_13 화요일
SET SERVEROUTPUT ON;

-- 내장함수 CURRENT_DATE 현재 시간
SELECT CURRENT_DATE FROM DUAL;

-- 내장 함수 TO_CHAR 문자로 변환
SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

-- 반올림
SELECT ROUND(1.2345, 2) FROM DUAL;

-- 문자를 대문자로 변경
SELECT UPPER('abCDeFg') FROM DUAL;

-- 오른쪽 10자리 중에서 빈곳을 *로 채움
SELECT RPAD('asdf', 10, '*') FROM DUAL;

-- 시작위치, 개수
SELECT SUBSTR('abcde', 1, 3) FROM DUAL;


-- 함수만들기 오늘 날짜를 특정 포멧으로 변경해서 반환하는 함수
CREATE OR REPLACE FUNCTION FUNC_TODAY RETURN VARCHAR2
IS
    V_DATE VARCHAR2(50); -- 반환될 임시변수
BEGIN
    SELECT TO_CHAR(CURRENT_DATE, 'YYYY"년" MM"월" DD"일"') INTO V_DATE
    FROM DUAL;
    RETURN V_DATE;
END;
/

SELECT FUNC_TODAY FROM DUAL;

SELECT B.* FROM BOARDTBL B;

-- SEQ_BOARDTBL_NO의 시퀀스를 호출해서 값을 반환함수
CREATE OR REPLACE FUNCTION FUNC_BOARDSEQ RETURN NUMBER
IS
BEGIN
    RETURN SEQ_BOARDTBL_NO.NEXTVAL;
END;
/

INSERT ALL
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
SELECT * FROM DUAL;


-- BOARDTBL에서 조회수가 가장높은 글번호 찾는 함수
CREATE OR REPLACE FUNCTION FUNC_BOARD_HIT RETURN NUMBER
IS
    V_NO NUMBER; -- 임시변수
BEGIN
    SELECT B.NO INTO V_NO FROM (
        SELECT B.HIT, B.NO, RANK() OVER (ORDER BY B.HIT DESC) RNK FROM BOARDTBL B
    ) B WHERE B.RNK=1;
    RETURN V_NO;
END;
/

SELECT FUNC_BOARD_HIT FROM DUAL;
SELECT B.* FROM BOARDTBL B;


-- BOARDTBL에 데이터가 추가될때 자동으로 수행되는 프로시저(트리거)
CREATE OR REPLACE TRIGGER TRI_BOARDTBL_INSERT
    AFTER INSERT ON BOARDTBL 
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('insert 트리거');
END;
/

-- BOARDTBL의 데이터를 BOARDTBL_BACK에 보관하는 트리거 작성
SELECT B.* FROM BOARDTBL B ORDER BY NO DESC;

-- 테이블 복사 -> DDL테이블 + DML데이터
CREATE TABLE BOARDTBL_BACK AS 
    SELECT B.* FROM BOARDTBL B ORDER BY NO DESC;

-- 복사한 테이블 내용 확인
SELECT BK.* FROM BOARDTBL_BACK BK ORDER BY NO DESC;

-- COMMIT은 자동으로 됨. 
CREATE OR REPLACE TRIGGER TRI_BOARD_BACK
    AFTER INSERT ON BOARDTBL 
    FOR EACH ROW
BEGIN
    INSERT INTO BOARDTBL_BACK VALUES( :new.NO, :new.TITLE, :new.WRITER, :new.HIT, CURRENT_DATE, :new.CONTENT);
END;
/




CREATE OR REPLACE TRIGGER TRI_BOARD_BACK1
    AFTER INSERT OR UPDATE OR DELETE ON BOARDTBL 
    FOR EACH ROW
BEGIN
    IF inserting THEN
    INSERT INTO BOARDTBL_BACK VALUES(
    :new.NO, :new.TITLE, :new.WRITER, :new.HIT, CURRENT_DATE, :new.CONTENT);
    
    ELSIF deleting THEN
       DELETE BOARDTBL_BACK WHERE NO = :old.NO;
    ELSIF updating THEN
        UPDATE BOARDTBL_BACK SET TITLE=:new.TITLE
            WHERE NO = :old.NO;
    END IF;
END;
/

UPDATE BOARDTBL SET TITLE='변경제목', WRITER='변경작성자' WHERE NO=2;
DELETE FROM BOARDTBL WHERE NO=5;

SELECT * FROM BOARDTBL WHERE TITLE LIKE '%' || '1' || '%' ORDER BY NO DESC;
SELECT B.* FROM BOARDTBL B WHERE TITLE LIKE '%' || '1' || '%' ORDER BY NO DESC;
SELECT * FROM ( SELECT B.*, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN FROM BOARDTBL B) WHERE ROWN BETWEEN 1 AND 5;

SELECT
    COUNT(*)
FROM 
    (SELECT B.* FROM BOARDTBL B WHERE TITLE LIKE '%' || '1' || '%');
    
UPDATE BOARDTBL SET HIT=HIT+1 WHERE NO=3;

SELECT B.* FROM BOARDTBL B;


-- 이전글, 다음글
SELECT b.* FROM(
SELECT 
    NO,
    LAG(NO,1,-1) OVER(ORDER BY NO ASC) AS PREBNO,
    LEAD(NO,1,-1) OVER(ORDER BY NO ASC) AS NEXTBNO 
FROM BOARDTBL
) B
WHERE B.NO = 3;

-- 이전글
SELECT b.* FROM(
SELECT 
    NO,
    LAG(NO,1,-1) OVER(ORDER BY NO ASC) AS PREBNO
FROM BOARDTBL
) B
WHERE B.NO = 3;

-- 다음글
SELECT b.* FROM(
SELECT 
    NO,
    LEAD(NO,1,-1) OVER(ORDER BY NO ASC) AS NEXTBNO 
FROM BOARDTBL
) B
WHERE B.NO = 3;

서버온

SET SERVEROUTPUT ON;

-- 내장함수 CURRENT_DATE 현재 시간

SELECT CURRENT_DATE FROM DUAL;

-- 내장 함수 TO_CHAR 문자로 변환

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

-- 반올림

SELECT ROUND(1.2345, 2) FROM DUAL;

-- 문자를 대문자로 변경

SELECT UPPER('abCDeFg') FROM DUAL;

-- 오른쪽 10자리 중에서 빈곳을 *로 채움

SELECT RPAD('asdf', 10, '*') FROM DUAL;

-- 시작위치, 개수

SELECT SUBSTR('abcde', 1, 3) FROM DUAL;

함수만들기(함수탭에 생성됨)
FUNC_TODAY
년월일 한글 붙여주기

-- 함수만들기 오늘 날짜를 특정 포멧으로 변경해서 반환하는 함수

CREATE OR REPLACE FUNCTION FUNC_TODAY RETURN VARCHAR2
IS
    V_DATE VARCHAR2(50); -- 반환될 임시변수
BEGIN
    SELECT TO_CHAR(CURRENT_DATE, 'YYYY"년" MM"월" DD"일"') INTO V_DATE
    FROM DUAL;
    RETURN V_DATE;
END;
/

SELECT FUNC_TODAY FROM DUAL;

시퀀스가 있을때 INSERT ALL
일괄 등록
함수 만들어서 해결하기
-- SEQ_BOARDTBL_NO의 시퀀스를 호출해서 값을 반환함수

CREATE OR REPLACE FUNCTION FUNC_BOARDSEQ RETURN NUMBER
IS
BEGIN
    RETURN SEQ_BOARDTBL_NO.NEXTVAL;
END;
/

아래 일괄 등록이 사용 가능해짐.

INSERT ALL
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
SELECT * FROM DUAL;

-- BOARDTBL에서 조회수가 가장 높은 글번호 찾는 함수

CREATE OR REPLACE FUNCTION FUNC_BOARD_HIT RETURN NUMBER
IS
    V_NO NUMBER; -- 임시변수
BEGIN
    SELECT B.NO INTO V_NO FROM (
        SELECT B.HIT, B.NO, RANK() OVER (ORDER BY B.HIT DESC) RNK FROM BOARDTBL B
    ) B WHERE B.RNK=1;
    RETURN V_NO;
END;
/

위 코드는 조회수가 가장 높은 글이 2개(중복)가 있을때, 에러가 남.

그래서 아래 코드로 변경해줌

CREATE OR REPLACE FUNCTION FUNC_BOARD_HIT RETURN NUMBER
IS
    V_NO NUMBER; -- 임시변수
BEGIN
    SELECT B.NO INTO V_NO FROM (
        SELECT B.HIT, B.NO, ROW_NUMBER() OVER (ORDER BY B.HIT DESC) RNK FROM BOARDTBL B
    ) B WHERE B.RNK=1;
    RETURN V_NO;
END;
/

SELECT FUNC_BOARD_HIT FROM DUAL;

프로시저 매크로
함수 간단하게 쓰고 데이터 리턴
트리거도 매크로 같은 절차(행동이 일어날때 감지, 프로시저로 만듬)

지금은
트리거 해볼 것임. 사진참고
INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 자동적으로 수행되는 프로시저

-- BOARDTBL에 데이터가 추가될때 자동으로 수행되는 프로시저(트리거)

CREATE OR REPLACE TRIGGER TRI_BOARDTBL_INSERT
    AFTER INSERT ON BOARDTBL 
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('insert 트리거');
END;
/

insert되면 "insert 트리거가 출력"

SELECT B.* FROM BOARDTBL B ORDER BY NO DESC;

-- BOARDTBL의 데이터를 BOARDTBL_BACK에 보관하는 트리거 작성
-- 테이블 복사 -> DDL테이블 + DML데이터

CREATE TABLE BOARDTBL_BACK AS 
    SELECT B.* FROM BOARDTBL B ORDER BY NO DESC;

-- 복사한 테이블 내용 확인

SELECT BK.* FROM BOARDTBL_BACK BK ORDER BY NO DESC;

-- COMMIT은 자동으로 됨.

CREATE OR REPLACE TRIGGER TRI_BOARD_BACK
    AFTER INSERT ON BOARDTBL 
    FOR EACH ROW
BEGIN
    INSERT INTO BOARDTBL_BACK VALUES( :new.NO, :new.TITLE, :new.WRITER, :new.HIT, CURRENT_DATE, :new.CONTENT);
END;
/

프로시저 만들어 놓으니
자동적으로 BOARDTBL_BACK에 자동 COMMIT 되는 중

OR 사용으로 UPDATE, DELETE도 추가(new, old)

CREATE OR REPLACE TRIGGER TRI_BOARD_BACK1
    AFTER INSERT OR UPDATE OR DELETE ON BOARDTBL 
    FOR EACH ROW
BEGIN
    IF inserting THEN
    INSERT INTO BOARDTBL_BACK VALUES(
    :new.NO, :new.TITLE, :new.WRITER, :new.HIT, CURRENT_DATE, :new.CONTENT);
    
    ELSIF deleting THEN
       DELETE BOARDTBL_BACK WHERE NO = :old.NO;
    ELSIF updating THEN
        UPDATE BOARDTBL_BACK SET TITLE=:new.TITLE
            WHERE NO = :old.NO;
    END IF;
END;
/

DTO나 VO나 똑같은 말임.

지금 vscode로 이동하여
BoardDTO.java 작성중

package com.example.vo;

import java.util.Date;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter // mybatis Mapper 에서 사용함
@Setter // view에서 사용
@ToString // 현재 객체의 내용 확인용
@NoArgsConstructor // 생성자

// @Data
public class BoardDTO {
    private Long no;
    private String title = null; 
    private String writer = null; 
    private Long hit = 1L; 
    private Date regdate = null;
    private String content = null; 
}

@data는 아래 네개를 축약한 것임. 하지만 구체적인 명시를 못씀
ex) ToString(exclude = )같은 것 못씀

@Getter // mybatis Mapper 에서 사용함
@Setter // view에서 사용
@ToString // 현재 객체의 내용 확인용
@NoArgsConstructor // 생성자

insertBoardOne 작성

package com.example.mapper;

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

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;

import com.example.vo.BoardDTO;

@Mapper
public interface BoardMapper {

    // ;사용 불가. commit은 auto로 설정되어있음.

    // 1. 전체 글목록
    @Select({
        "<script>",
        " SELECT B.* FROM BOARDTBL B ",
        "</script>" 
    })
    public List<BoardDTO> selectBoardList();

    // 2. 게시글 1개 조회
    @Select({
        "<script>",
        " SELECT B.* FROM BOARDTBL B WHERE NO=#{no} ",
        "</script>" 
    })
    public BoardDTO selectBoardOne(@Param("no") Long no);

    // 3. 검색어 포함해서 글목록
    @Select({
        "<script>",
        " SELECT B.* FROM BOARDTBL B WHERE TITLE LIKE '%' || #{text} || '%' ORDER BY NO DESC ",
        "</script>" 
    })
    public List<BoardDTO> select3(@Param("text") String text);

    // 4. 검색어 페이지네이션 포함 글목록(제목)
    @Select({
        "<script>",
        " SELECT * FROM ( SELECT B.*, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN FROM BOARDTBL B) WHERE ROWN BETWEEN #{page1} AND #{page2} ",
        "</script>" 
    })
    public List<BoardDTO> select4(@Param("title") String title, @Param("page1") int page1, @Param("page2") int page2);

    // 5. 검색어 페이지네이션 포함 글개수(제목)
    @Select({
        "<script>",
        " SELECT COUNT(*) FROM (SELECT B.* FROM BOARDTBL B WHERE TITLE LIKE '%' || #{title} || '%') ",
        "</script>" 
    })
    public int select5(@Param("title") String title);

    // 6. 조회수 증가
    @Insert({
        " UPDATE BOARDTBL SET HIT=HIT+1 WHERE NO=#{no} "
    })
    public void updateHit(@Param("no") Long no);

    // 7. 이전글
    // 이건 좀 어렵네
    @Select({
        " SELECT b.* FROM(SELECT NO,LAG(NO,1,-1) OVER(ORDER BY NO ASC) AS PREBNO FROM BOARDTBL) B WHERE B.NO = #{no} "
    })
    public List<Map<String, Object>> prevno(@Param("no") Long no);

    // 8. 다음글
    @Select({
        " SELECT b.* FROM(SELECT NO, LEAD(NO,1,-1) OVER(ORDER BY NO ASC) AS NEXTBNO FROM BOARDTBL) B WHERE B.NO = #{no} "
    })
    public List<Map<String, Object>> nextno(@Param("no") Long no);

    
    // 게시판 글쓰기, INSERT, UPDATE, DELETE
    // INSERT INTO 테이블명(컬럼명들) VALUES(추가할 값들)
    @Insert({
        " INSERT INTO BOARDTBL(NO, TITLE, WRITER, HIT, REGDATE, CONTENT) ", 
        " VALUES(FUNC_BOARDSEQ, #{board.title}, #{board.writer}, #{board.hit}, CURRENT_DATE, #{board.content} ) "
    })
    public int insertBoardOne(@Param("board") BoardDTO board);

    
    @Insert({
        "{ call PROC_BOARD_UPSERT(", 
            "#{map.no, mode=IN, jdbcType=NUMERIC, javaType=Long }, ",
            "#{map.title, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.content, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.writer, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.ret, mode=OUT, jdbcType=NUMERIC, javaType=Integer }",
        ")}"
    })
    @Options(statementType = StatementType.CALLABLE)
    public void callProcedure(@Param("map") Map<String, Object> map);
    /* 
    테스트하는 곳에서
    Map<String, Object> map = new HashMap<>();
    map.put("no", 7981L);
    map.put("title", "제목");
    map.put("content", "내용");
    map.put("writer", "작성자");
    map.put("ret", 0);
    bMapper.callProcedure( map );
    System.out.print( map.get("ret") ); //0, 1, 2
    */
}

지금 boardMapper.java 에서 실습 문제중

문제 3번 검색어 찾을때 '' 이거 예외인듯

SELECT B.* FROM BOARDTBL B WHERE TITLE LIKE '%' || #{text} || '%' ORDER BY NO DESC

오후일과는 위 실습 문제 풀이 예정 오후에 한 것이 더 정확

MyBoardTest.java

package com.example.boot_20220827;

import java.util.HashMap;
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 com.example.mapper.BoardMapper;
import com.example.vo.BoardDTO;

import lombok.extern.slf4j.Slf4j;

@SpringBootTest
@Slf4j
public class MyBoardTest {
    final String format = "BOARDTBL => {}";

    @Autowired
    BoardMapper bMapper;

    // 문제 8. 다음글
    @Test
    void nextno(){
        Long no = 41L;
        log.info(format, bMapper.nextno(no));
    }

    // 문제 7. 이전글
    @Test
    void prevno(){
        Long no = 1L;
        log.info(format, bMapper.prevno(no));
    }



    // 문제 6. 조회수 증가
    @Test
    void select6(){
        Long no = 1L;
        bMapper.updateHit(no);
    }

    // 문제 5. 검색어 페이지네이션 포함 글개수(제목)
    @Test
    void select5(){
        String title = "1";
        log.info(format, bMapper.select5(title));
    }


    // 문제 4. 검색어 페이지네이션 포함 글목록(제목)
    @Test
    void select4(){
        int page = 2;
        int page1 = (page-1)*5+1;
        int page2 = page*5;
        String title = "1";
        log.info(format, bMapper.select4(title, page1, page2));
    }


    // 문제 3. 검색어 포함해서 글목록
    @Test
    void select3(){
        String text = "1";
        log.info(format, bMapper.select3(text));
    }


    // 문제 2. 게시글 1개 조회
    @Test
    void selectOne(){
        log.info(format, bMapper.selectBoardOne(1L));
    }


    // 문제 1. 전체 글목록
    @Test
    void selectBoard(){
        log.info(format, bMapper.selectBoardList());
    }

    @Test
    void insertTest(){
        BoardDTO board = new BoardDTO();
        board.setTitle("mapper테스트용");
        board.setContent("내용테스트용");
        board.setWriter("작성자테스트용");

        int ret = bMapper.insertBoardOne(board);
        log.info(format, ret);
    }



    @Test
    public void selectListTest(){
        log.info(format,"PROC TEST");
        Map<String, Object> map = new HashMap<>();
        map.put("no", 1L);
        map.put("title", "제목");
        map.put("content", "내용");
        map.put("writer", "작성자");
        map.put("ret", 0);
        bMapper.callProcedure( map );
        // System.out.println( "retretretretretretretretretretretretretret"+map.get("ret") );
        log.info(format,map.get("ret"));
    }
}

오후일과

실습문제 풀이

이전글 작은 것들 중에서 가장 큰수 max와 where no< 사용 + null일때 디폴트값 0 NVL(MIN(NO),0)

@Select({
    " SELECT NVL(MAX(NO),0) FROM BOARDTBL WHERE NO < #{no} "
})
public Long prevno(@Param("no") Long no);

다음글 큰것들 중에서 가장 작은수 min

@Select({
    " SELECT NVL(MIN(NO),0) FROM BOARDTBL WHERE NO > #{no} "
})
public Long nextno(@Param("no") Long no);

선생님 코드 BoardMapper.java

package com.example.mapper;

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

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;

import com.example.vo.BoardDTO;

@Mapper
public interface BoardMapper {

    // ;사용불가, commit은 auto로 설정되어있음.

    // 1. 전체 글목록 
    @Select({
        "<script>",
            " SELECT B.* FROM BOARDTBL B",
        "</script>"
    })
    public List<BoardDTO> selectBoardList();    

    // 2. 게시글 1개 조회
    @Select({
        "<script>",
            " SELECT B.* FROM BOARDTBL B WHERE B.NO=#{no}",
        "</script>"
    })
    public BoardDTO selectBoardOne(@Param("no") Long no);

    // 3. 검색어 포함해서 글목록
    @Select({
        "<script>",
            " SELECT B.* FROM BOARDTBL B WHERE B.TITLE LIKE '%' || #{text} || '%' ",
        "</script>"
    })
    public BoardDTO selectBoardListLike(@Param("text") String text);

    // 4. 검색어 페이지네이션 포함 글목록(제목)
    @Select({
        "<script>",
            " SELECT B.* FROM ( ",
                " SELECT B.*, ROW_NUMBER() OVER (ORDER BY NO DESC) ROWN ",
                " FROM BOARDTBL B WHERE B.TITLE LIKE '%' || #{text} || '%' ",
            ") B WHERE ROWN BETWEEN #{start} AND #{start}+9 ",
        "</script>"
    })
    public BoardDTO selectBoardListLikePagenation(
        @Param("text") String text, @Param("start") int start);


    // 5. 검색어 페이지네이션 포함 글개수(제목)
    @Select({
        "<script>",
            " SELECT COUNT(*) CNT FROM BOARDTBL B ",
            " WHERE B.TITLE LIKE '%' || #{text} || '%' ",
        "</script>"
    })
    public Long countBoardListLikePagenation(
        @Param("text") String text);

    // 6. 조회수 증가
    @Update({
        " UPDATE BOARDTBL SET HIT=HIT+1 WHERE NO=#{no} "
    })
    public int updateBoardHit(@Param("no") Long no);

    // 7. 이전글
    @Select({
        " SELECT NVL(MAX(NO),0) FROM BOARDTBL WHERE NO < #{no} "
    })
    public Long selectBoardPrev(@Param("no") Long no);

    // 8. 다음글 
    @Select({
        " SELECT NVL(MIN(NO),0) FROM BOARDTBL WHERE NO > #{no} "
    })
    public Long selectBoardNext(@Param("no") Long no);

    

    // 게시판글쓰기, INSERT ,UPDATE ,DELETE
    // INSERT INTO 테이블명(컬럼명들) VALUES(추가할값들)
    @Insert({
        " INSERT INTO BOARDTBL(NO, TITLE, WRITER, HIT, REGDATE, CONTENT) ", 
        " VALUES(FUNC_BOARDSEQ, #{board.title}, #{board.writer}, #{board.hit}, CURRENT_DATE, #{board.content} ) "
    })
    public int insertBoardOne(@Param("board") BoardDTO board);


    @Insert({
        "{ call PROC_BOARD_UPSERT(", 
            "#{map.no, mode=IN, jdbcType=NUMERIC, javaType=Long }, ",
            "#{map.title, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.content, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.writer, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.ret, mode=OUT, jdbcType=NUMERIC, javaType=Integer }",
        ")}"
    })
    @Options(statementType = StatementType.CALLABLE)
    public void callProcedure(@Param("map") Map<String, Object> map);

}

이미지 보관할 것임. (테이블, 시퀀스)

-- 이미지 번호용 시퀀스 생성

CREATE SEQUENCE SEQ_BOARDIMAGE_NO
START WITH 1 INCREMENT BY 1 NOCACHE NOMAXVALUE;

게시판에 이미지를 보관할 수 없음.
테이블을 새로만들고 외래키

-- 이미지 보관용 테이블

CREATE TABLE BOARDIMAGETBL(
    NO NUMBER CONSTRAINT PK_BOARDIMAGE_NO PRIMARY KEY,
    BRDNO NUMBER CONSTRAINT FK_BOARD_NO REFERENCES BOARDTBL(NO),
    IMAGENAME VARCHAR2(200),
    IMAGESIZE NUMBER,
    IMAGETYPE VARCHAR2(30),
    IMAGEDATA BLOB,
    REGDATE TIMESTAMP DEFAULT CURRENT_DATE
);

테스트용 insert 등록

INSERT INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGEDATA, IMAGETYPE, IMAGESIZE)
VALUES(SEQ_BOARDIMAGE_NO.NEXTVAL, 20, '테스트', null, 'image/jpeg', 0);

테스트 조회

SELECT BI.* FROM BOARDIMAGETBL BI;

테스트 삭제

DELETE FROM BOARDIMAGETBL WHERE NO=1;

테스트 업데이트

UPDATE BOARDIMAGETBL SET IMAGENAME = '테스트', IMAGEDATA=null, IMAGETYPE='image/jpeg', IMAGESIZE=0 WHERE NO=8;

부트에서 이미지 넣기
BoardImageDTO.java
BLOB == byte[]

package com.example.vo;

import java.util.Date;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
@NoArgsConstructor
public class BoardImageDTO {

    protected long no; // private안보이고 protected상속자만 볼 수 있고 public 다 볼 수 있음.
    long brdno;
    String imagename;
    int imagesize;
    String imagetype;
    byte[] imagedata; // BLOB == byte[]
    Date regdate;
}

BoardImageMapper.java

package com.example.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.type.JdbcType;

import com.example.vo.BoardImageDTO;

public interface BoardImageMapper {
    
    // 1. 이미지 삭제
    @Delete({
        " DELETE FROM BOARDIMAGETBL WHERE NO=#{no} "
    })
    public int DeleteBoardOne(@Param("no") long no);

    // 2. 이미지 수정
    @Update({
        // " INSERT INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGEDATA, IMAGETYPE, IMAGESIZE) ",
        // " VALUES(SEQ_BOARDIMAGE_NO.NEXTVAL, #{obj.brdno}, #{obj.imagename}, #{obj.imagedata, jdbcType=BLOB}, ",
        // " #{obj.imagetype}, #{obj.imagesize}) "

        " UPDATE BOARDIMAGETBL SET IMAGENAME = #{obj.imagename}, IMAGEDATA=#{obj.imagedata, jdbcType=BLOB}, ",
        " IMAGETYPE=#{obj.imagetype}, IMAGESIZE=#{obj.imagesize} WHERE NO=#{obj.no} "
    })
    public int updateBoardImageOne(@Param("obj") BoardImageDTO obj);


    // 3. 물품번호에 해당하는 전체 이미지 조회
    @Results({
        // @Result(property = "no1", column = "NO"), 변수명<다를때> 컬럼명
        @Result(property = "imagedata", column = "IMAGEDATA", jdbcType = JdbcType.BLOB, javaType = byte[].class)
    })
    @Select({
        " SELECT BI.* FROM BOARDIMAGETBL BI WHERE BRDNO=#{brdno} "
    })
    public List<BoardImageDTO> selectBoardAll(@Param("brdno") long brdno);



    // 4. 이미지 여러개 등록
    @Insert({
        "<script>",
            " INSERT ALL ",
                "<foreach collection='list' item='obj' separator=' '>",
                    " INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGESIZE, ",
                    " IMAGETYPE, IMAGEDATA) VALUES(FUNC_BOARDIMAGESEQ, ",
                    " #{obj.brdno}, #{obj.imagename}, #{obj.imagesize}, ",
                    " #{obj.imagetype}, #{obj.imagedata, jdbcType=BLOB}) ",
                "</foreach>",
            " SELECT * FROM DUAL ",
        "</script>"
    })
    public int insertImageBatch(
        @Param("list") List<BoardImageDTO> list);


    // 이미지 추가
    @Insert({
        " INSERT INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGEDATA, IMAGETYPE, IMAGESIZE) ",
        " VALUES(SEQ_BOARDIMAGE_NO.NEXTVAL, #{obj.brdno}, #{obj.imagename}, #{obj.imagedata, jdbcType=BLOB}, ",
        " #{obj.imagetype}, #{obj.imagesize}) "
    })
    public int insertBoardImageOne(@Param("obj") BoardImageDTO obj);

    //이미지는 타입이 다르기 때문에 컬럼명이 같더라도 result를 통해 변환작업 필요
    @Results({
        // @Result(property = "no1", column = "NO"), 변수명<다를때> 컬럼명
        @Result(property = "imagedata", column = "IMAGEDATA", jdbcType = JdbcType.BLOB, javaType = byte[].class)
    })
    @Select({
        " SELECT BI.* FROM BOARDIMAGETBL BI WHERE NO=#{no} "
    })
    public BoardImageDTO selectBoardOne(@Param("no") long no);
}

이미지는 타입이 다르기 때문에 컬럼명이 같더라도 result를 통해 변환작업 필요

@Results({
    // @Result(property = "no1", column = "NO"), 변수명<다를때> 컬럼명
    @Result(property = "imagedata", column = "IMAGEDATA", jdbcType = JdbcType.BLOB, javaType = byte[].class)
})
@Select({
    " SELECT BI.* FROM BOARDIMAGETBL BI WHERE NO=#{no} "
})
public BoardImageDTO selectBoardOne(@Param("no") long no);

이미지 실습

// 1. 이미지 삭제
@Delete({
    " DELETE FROM BOARDIMAGETBL WHERE NO=#{no} "
})
public int DeleteBoardOne(@Param("no") long no);

// 2. 이미지 수정
@Update({
    // " INSERT INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGEDATA, IMAGETYPE, IMAGESIZE) ",
    // " VALUES(SEQ_BOARDIMAGE_NO.NEXTVAL, #{obj.brdno}, #{obj.imagename}, #{obj.imagedata, jdbcType=BLOB}, ",
    // " #{obj.imagetype}, #{obj.imagesize}) "

    " UPDATE BOARDIMAGETBL SET IMAGENAME = #{obj.imagename}, IMAGEDATA=#{obj.imagedata, jdbcType=BLOB}, ",
    " IMAGETYPE=#{obj.imagetype}, IMAGESIZE=#{obj.imagesize} WHERE NO=#{obj.no} "
})
public int updateBoardImageOne(@Param("obj") BoardImageDTO obj);


// 3. 물품번호에 해당하는 전체 이미지 조회
@Results({
    // @Result(property = "no1", column = "NO"), 변수명<다를때> 컬럼명
    @Result(property = "imagedata", column = "IMAGEDATA", jdbcType = JdbcType.BLOB, javaType = byte[].class)
})
@Select({
    " SELECT BI.* FROM BOARDIMAGETBL BI WHERE BRDNO=#{brdno} "
})
public List<BoardImageDTO> selectBoardAll(@Param("brdno") long brdno);



// 4. 이미지 여러개 등록
@Insert({
    "<script>",
        " INSERT ALL ",
            "<foreach collection='list' item='obj' separator=' '>",
                " INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGESIZE, ",
                " IMAGETYPE, IMAGEDATA) VALUES(FUNC_BOARDIMAGESEQ, ",
                " #{obj.brdno}, #{obj.imagename}, #{obj.imagesize}, ",
                " #{obj.imagetype}, #{obj.imagedata, jdbcType=BLOB}) ",
            "</foreach>",
        " SELECT * FROM DUAL ",
    "</script>"
})
public int insertImageBatch(
    @Param("list") List<BoardImageDTO> list);

MyBoardimageTest.java

package com.example.boot_20220827;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ResourceLoader;

import com.example.mapper.BoardImageMapper;
import com.example.vo.BoardImageDTO;

import lombok.extern.slf4j.Slf4j;

@SpringBootTest
@Slf4j
public class MyBoardimageTest {

    final String format = "BOARDTBL => {}";

    @Autowired
    ResourceLoader resourceLoader;

    @Autowired
    BoardImageMapper biMapper;
    

    // 4. 이미지 여러개 등록
    @Test
    public void insertImageBatch() throws IOException{
        List<BoardImageDTO> list = new ArrayList<>();
        InputStream is = resourceLoader.getResource(
            "classpath:/static/image/noimage.png").getInputStream();

        BoardImageDTO obj = new BoardImageDTO();
        obj.setBrdno(22L);    // 외래키 !!
        obj.setImagedata(is.readAllBytes());
        obj.setImagesize(obj.getImagedata().length);
        obj.setImagetype("image/png");
        obj.setImagename("noimage.png");
        list.add(obj);

        BoardImageDTO obj1 = new BoardImageDTO();
        InputStream is1 = resourceLoader.getResource(
            "classpath:/static/image/1111.png").getInputStream();
        obj1.setBrdno(22L);    // 외래키 !!
        obj1.setImagedata(is1.readAllBytes());
        obj1.setImagesize(obj1.getImagedata().length);
        obj1.setImagetype("image/png");
        obj1.setImagename("1111.png");
        list.add(obj1);

        // System.out.println(list);


        int ret = biMapper.insertImageBatch(list);
        log.info(format, ret);
        // log.info(format, list);
    }

    // 3. 보드 번호 전체 이미지 가져오기
    @Test
    public void selectAllImage(){
        List<BoardImageDTO> ret = biMapper.selectBoardAll(20L);
        log.info(format, ret);
    }

    // 2. 이미지 하나 수정
    @Test
    public void updateImage() throws IOException{
        InputStream is = resourceLoader.getResource(
            "classpath:/static/image/1111.png").getInputStream();

        BoardImageDTO obj = new BoardImageDTO();
        obj.setNo(4L);    // 외래키 !!
        obj.setImagedata(is.readAllBytes());
        obj.setImagesize(obj.getImagedata().length);
        obj.setImagetype("image/png");
        obj.setImagename("1111.png");

        int ret = biMapper.updateBoardImageOne(obj);
        log.info(format, ret);
    }


    // 1. 이미지 하나 삭제
    @Test
    public void deleteOneImage(){
        int ret = biMapper.DeleteBoardOne(7L);
        log.info(format, ret);
    }

    // 이미지 하나만 조회
    @Test
    public void selectOneImage(){
        BoardImageDTO ret = biMapper.selectBoardOne(6);
        log.info(format, ret);
    }

    @Test
    public void insertImage() throws IOException{
        InputStream is = resourceLoader.getResource(
            "classpath:/static/image/noimage.png").getInputStream();

        BoardImageDTO obj = new BoardImageDTO();
        obj.setBrdno(22L);    // 외래키 !!
        obj.setImagedata(is.readAllBytes());
        obj.setImagesize(obj.getImagedata().length);
        obj.setImagetype("image/png");
        obj.setImagename("noimage.png");

        int ret = biMapper.insertBoardImageOne(obj);
        log.info(format, ret);
    }

}

'oracle' 카테고리의 다른 글

55일차 xml로 작성해보기 results로 대소문자변경(CLOB포함) (0)	2022.09.14
53일차 오라클 (0)	2022.09.08
50, 51일차 oracle 연결, 테이블, 그룹, 페이지 네이션 (0)	2022.09.07

현재글54일차 insert all(seq문제), 트리거(프로시저), vscode에서 일괄 등록

Today :
Yesterday :

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

마루쓰