본문 바로가기
MySql

selectKey keyProperty 테이블의 특정 컬럼 값을 자동 증가시켜서 리턴

by 루에 2017. 5. 31.
반응형
1
2
3
<selectKey resultType="hashmap" keyProperty="DETAIL_ID" order="AFTER">
            SELECT #{seqVal} as DETAIL_ID
        </selectKey>
cs


selectKey는 해당 테이블의 keyProperty에 해당하는 값을 자동 증가한 값을 알려주는 것.

해당 컬럼이 현재 100까지 있다면,

#{seqVal}은 101이 될 것이다.


전체 쿼리는 이런 식으로 쓴다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<insert id="아이디" parameterType="hashmap"> 
        <selectKey resultType="hashmap" keyProperty="DETAIL_ID" order="AFTER">
            SELECT #{seqVal} as DETAIL_ID
        </selectKey>
        INSERT INTO FILE_DETAIL
        (
            FILE_DETAIL_ID,
            FILE_MASTER_ID,
            SEQ,
            FILE_DIR,
            ORIGINAL_FILE_NAME,
            RENAMED_FILE_NAME,
            FILE_SIZE,
            <if test="@Ognl@isNotEmpty(DELETE_DATE)">
            DELETED_BY,
            </if>
            <if test="@Ognl@isNotEmpty(DELETE_DATE)">
            DELETE_DATE,
            </if>
            CREATED_BY,
            CREATION_DATE,
            UPDATED_BY,
            UPDATE_DATE
        )
        SELECT
            #{seqVal},
            #{FILE_MASTER_ID},
            ifnull(MAX(SEQ),0+ 1,
            #{FILE_DIR},
            #{ORIGINAL_FILE_NAME},
            #{RENAMED_FILE_NAME},
            #{FILE_SIZE},
            <if test="@Ognl@isNotEmpty(DELETE_DATE)">
            #{my_user_id},
            </if>
            <if test="@Ognl@isNotEmpty(DELETE_DATE)">
            DATE_FORMAT(#{DELETE_DATE},'%Y-%m-%d %H:%i:%s'),
            </if>
            #{my_user_id},
            now(),
            #{my_user_id},
            now()
        FROM FILE_DETAIL
        WHERE 1=1
          AND FILE_MASTER_ID = #{FILE_MASTER_ID}
     </insert>
cs


반응형

댓글