oracle MyBatis에서 다중 Insert 하는 방법



mssql에서 oracle로 마이그레이션을 하는 도중 하나의 맵핑된 쿼리 안에서


여러개의 행을 insert하는 것에서 문제가 발생했다


mssql을 사용할떈 그냥 insert문을 여러개 쓰는것 만으로 한번에 여러 행을 입력 할 수 있었는데


오라클에선 구문오류가 발생하게 된다.


그렇게 방법을 찾다가 발견한 방법은


INSERT ALL 이다



사용법은 아주아주 굉장히 간단하다.


일단 MyBatis에서 여러 데이터를 맵형태로 보내 다이나믹 쿼리로 만드는 방법은


아래 링크를 참고하고

2015/07/09 - [Yame Programmer/전자정부프레임워크] - [ibatis] 동적쿼리 생성 및 outOfBoundsException 에러


ibatis와 mybatis의 사용방법이 상이하긴 하지만 맥락은 같으니 참고하면 좋을 것이다.




쿼리문은 아래와 같이 사용 하면 된다.


1
2
3
4
5
6
7
8
9
10
    <insert id="insertMtrlOrderList" parameterType="java.util.Map">
        <foreach collection="list" item="item" index="index"  open="INSERT ALL " separator=" " close="SELECT * FROM DUAL" > 
         into FM_MATERIALS_ORDER_LIST(MTRL_ID, MTRL_ORDER_ID, 
            MTRL_ENTERPRISE, UNIT_COST, MTRL_ORD_QUANTITY, APPLY_DATE)
        values
            (#{item.MTRL_ID}, #{item.MTRL_ORDER_ID}, 
        #{item.MTRL_ENTERPRISE}, #{item.UNIT_COST}, 
        #{item.MTRL_ORD_QUANTITY}, SYSDATE)
        </foreach>
    </insert>
cs



시작할떄 INSERT ALL 을 써주고 본 쿼리으 insert문은 insert를 제외한 into부터 시작해서 작성하면 끝


역시 사람은 아는게 많아야 손발이 고생을 안하는 것 같다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MSSQL] 프로시저 트리거 while 반복문 사용 방법, 변수를 컬럼명으로 사용 하는 방법  (0) 2017.05.17
[MSSQL]Insert실행시 자동증가되는 IDENTITY값 바로 가져오기  (0) 2017.04.26
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09

Oracle 부적합한 열유형 null 처리 jdbcType=VARCHAR를 사용하지 않고 해결하는 방법


mssql로 만들어진 프로젝트를 Oracle로 마이그레이션 작업을 하던 도중 어마어마한 노가다 작업에 봉착했다


Oracle 사용시 Null 허용컬럼에 값이 null이 들어가는 경우


부적합한 열 유형 이라는 에러 메세지가 뜨게 된다.


이때 이를 해결하기 위해서 


value1 = #{value1, jdbcType=VARCHAR},

value2 = #{value2, jdbcType=VARCHAR}


이런식으로 값 뒤에  , jdbcType=VARCHAR]  를 붙이게 되는데 


생각해보니 이게 양이 너무나도 많은것이다. 이걸 하나하나 언제 다 수정하고 앉아있을까


그래서 구글링해서 찾아낸 방법이 있다.


단 이것은 자신이 mybatis 설정파일을 변경할 권한이 있는 경우 사용하길 바란다.



1. mybatis-config.xml 파일을 연다. 만약 없는 경우

전자정부 프레임워크를 사용한다면 context-sqlMap.xml 파일을 열어 보면


1
2
3
4
5
6
7
8
9
10
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/egovframework/sqlmap/example/sql-map-config.xml" />
        <!-- <property name="mapperLocations" value="classpath:/egovframework/sqlmap/example/sample/PostgreSQL.xml" />
        
        <property name="configLocation" value="classpath:/mapper/mybatis-config.xml" /> -->
        <!-- Core File관련 Table SQL MAP 설정 위치 -->
        <!-- DB변동시 sql 맵퍼 패턴 수정 -->
        <property name="mapperLocations" value="classpath:/egovframework/sqlmap/example/mappers/${Globals.Mapping}"    /> 
    </bean>
cs



이런 부분이 있는데 여기서  프로퍼티 네임이 configLocation 인것을 찾는다


value값에 보면 컨피그로케이션의 경로가 있는데 저 경로에 찾아가면


아마 mybatis 설정하는 부분이 있거나 typeAlias를 선언해 주는 부분일텐데 일단 해당 파일을 연다




2. configuration 태그 아래 settings 이라는 태그가 있는지 확인


3. 없으면 직접 설정한다


4. 있던 없던 아래와 같은 셋팅 프로퍼티를 추가 한다.


1
2
3
4
5
6
<!--  oracle null 처리  -->
    <settings>
        <setting name="cacheEnabled" value="false" />
        <setting name="jdbcTypeForNull" value="NULL" />
    </settings>
 
cs




그럼 이제 저 귀찮음 많은 jdbcType=VARCHAR 선언들을 해주지 않아도 된다!


역시 개발자는 귀찮은거 싫어하고 머리를 굴려야 한다는걸 다시 깨닫게 된다.


저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[Oracle MyBatis] 오라클 마이바티스에서 다중 insert 하는 방법(insert all)  (0) 2017.01.16
[Mybatis] getter for property named 에러 해결방법  (3) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[oracle] 값이 있는지 확인 후 insert update 하기 [MERGE INTO]  (1) 2017.01.06
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27

Oracle MERGE INTO를 활용해 값이 있는지 확인후 insert , update , delete ,selete 하는 방법 


이전에 포스팅 했던 MSSQL의 EXISTS 기능을 오라클에서 사용하는 방법이다


2016/08/12 - [Yame Programmer/SQL] - [MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용



MSSQL을 사용했던 프로젝트를 Oracle로 마이그레이션 하게 되면서 다시 사용하게 되었다.


입사 초기에 Oracle를 사용하다 이후 쭉 MSSQL만 써서 기억이 가물가물 했다ㅋㅋ



1. 기본 포맷


1
2
3
4
5
6
MERGE INTO 테이블명  USING ( 서브쿼리 ) 
ON  ( 조건 )
WHEN MATCHED THEN 
조건에 값이 있는 경우 실행할 쿼리 
WHEN NOT MATCHED THEN 
조건에 값이 없는 경우 실행할 쿼리 
cs



2. 간단한 예제


1) 서브쿼리 없이 사용


1
2
3
4
5
6
7
8
9
10
11
12
13
MERGE INTO 
    TABLENAME  
USING DUAL 
    ON (PK_CD = 'PKCD001'  AND DATA_NAME = 'dataName')
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs




2) 서브쿼리 사용

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
MERGE INTO 
    TABLE_NAME  TARGETTB
USING(
    SELECT 
        CHECKTB.PK_CD
        ,CHECKTB.DATA_NAME
    FROM
        TABLE_NAME CHECKTB
     LEFT JOIN
        JOIN_TB JT
    ON
        JT.DATA = CHECKTB.DATA 
        ) CHECK
 
    ON (TARGETTB.PK_CD = CHECK.PK_CD    AND   TARGETTB.DATA_NAME = CHECK.DATA_NAME)
    WHEN MATCHED THEN     
        UPDATE SET     
                DATA_COL1 = '업데이트 내용 1',             
                DATA_COL2 = '업데이트 내용 2',             
                DATA_COL3 = '업데이트 내용 3',         
        
    WHEN NOT MATCHED THEN
        INSERT (PK_CD, DATA_NAME,  DATA_COL1, DATA_COL2, DATA_COL3)
        VALUES('PKCD001', 'dataName', '넣을 내용1', '넣을 내용3', '넣을 내용3')
cs

ㅇ

꼭 INSERT UPDATE만 넣을 수 있는게 아니라 SELECT DELETE를 넣어서 사용 할 수도 있다.




저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[MyBatis] Oracle 부적합한 열유형 null 처리 방법 jdbcType=VARCHAR 안쓰기  (2) 2017.01.11
[MSSQL] MSSQL 날짜 포맷 변환표  (0) 2017.01.09
[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05

Mssql 에서는 IF EXISTS 함수를 사용해 값이 존재하는지 확인 후


값이 있을때와 없을때 다른 쿼리를 사용 할 수 있다.


oracle에서는 MERGE 함수를 사용해 유사한 기능을 구현 할 수 있다.



1
2
3
4
5
6
7
8
9
10
11
12
13
 MERGE INTO 
     TBL_NAME -- table명
USING DUAL 
    ON (column1 = '존재하는지 확인할 값')
WHEN MATCHED THEN -- 존재하는 경우 실행   
    UPDATE SET  
        column2= 'update할 값1',
        column3= 'update할 값2'
WHEN NOT MATCHED THEN -- 존재하지 않는 경우 실행
    INSERT 
        (column1, column2, column3)
    VALUES
        ('존재하는지 확인할 값','insert할 값1', 'insert할 값2')
cs



위와 같은 쿼리문을 작성하면 된다


더 간단하게 나타내자면


1
2
3
4
5
6
7
8
 MERGE INTO 
     TBL_NAME -- table명
USING DUAL 
    ON (column1 = '존재하는지 확인할 값')
WHEN MATCHED THEN 
    -- 값이 존재하 경우 실행할 쿼리 
WHEN NOT MATCHED THEN 
    -- 값이 존재하지 않을 경우 실행할 쿼리
cs



이렇게 나타낼 수 도 있으며


좀더 응용하자면


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
merge into target_table tt
    using 
        ( -- 조인하는 테이블
            select
                te.te_cd tt_cd
                ,te.description tt_disp
                ,te.jt_no jt_no
                ,decode(te.is_flag, 'normal', 'Y', 'N') is_flag
            from
                temp_table te
            left join join_table jt
                on te.te_no=jt.jt_no
            where
                1=1
        ) so
    on (tt.tt_cd=so.tt_cd and tt.jt_no=so.jt_no) -- 조인한 테이블의 값 
     
    when matched then
        update set
            tt.is_flag=so.is_flag
            ,tt.tt_disp=so.tt_disp
 
 
    when not matched then
            insert (tt_no, tt_cd, tt_disp, jt_no, is_flag, reg_date)
            values(
                sq_target_table.nextval
                , so.tt_cd
                , so.tt_disp
                , so.jt_no
                , so.is_flag
                , sysdate
            );
cs

(코드 출처 :  http://yangtaeho.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-MERGE-%EC%82%AC%EC%9A%A9%ED%95%98%EA%B8%B0-update-insert-%EB%8F%99%EC%8B%9C%EC%97%90  


http://zinlee.tistory.com/185 에서 더 자세한 내용을 확인할 수 있습니다.

 )



이렇게 사용 할 수도 있다. 단순히 한 테이블에 대해서만 값이 존재하는지 확인 하는 것이 아니라


조인했을때의 값도 활용 할 수가 있다.

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[sql 튜닝] 이론 임시 내용[하드 파싱 소프트 파싱 캐시 블락]  (0) 2016.10.27
[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] java.sql.SQLException: ORA-00911: 문자가 부적합합니다.  (1) 2016.10.05
[MSSQL] mssql에서 dual과 같은 가상 테이블 사용 하기  (0) 2016.08.12
[MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용  (0) 2016.08.12

개발을 시작한지 초반에, 그리고 아직도 종종 자주 하는 실수중에 하나를 했을때 나타나는 메세지 이다.


java.sql.SQLException: ORA-00911: 문자가 부적합합니다.


쿼리문에 잘못된 문자를 사용했다는 의미 인데.


90%정도는 바로 쿼리문 마지막에  -> ';'  요 세미콜론을 붙였기 때문에 나타난다.


쿼리 내용을 넣는 xml문서에는 마지막에 세미콜론을 붙이지 않는다.


오라클 디벨로퍼에서 쿼리 테스트 해보고 그대로 복사한 후 소스에 붙여넣기 했을때 주로 나타나는 에러.


문자가 잘못되었다고 해서 아무리 들여다봐도 잘못된 부분이 없는데 마지막을 보면 저 세미콜론이 붙어 있다.


에러 로그는 요란스럽게 올라오지만 별거 아닌 문제니 천천히 쿼리를 살펴보도록 하자.




java.sql.SQLException: ORA-00911: 문자가 부적합합니다


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)

at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)

at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)

at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:380)

at org.springframework.orm.ibatis.SqlMapClientTemplate$9.doInSqlMapClient(SqlMapClientTemplate.java:1)

at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:200)

at org.springframework.orm.ibatis.SqlMapClientTemplate.update(SqlMapClientTemplate.java:378)

at egovframework.rte.psl.dataaccess.EgovAbstractDAO.update(EgovAbstractDAO.java:101)

at kr.co.narutec.pems2016.widget.service.impl.WidgetDAO.updateUserAutoSetting(WidgetDAO.java:142)

at kr.co.narutec.pems2016.widget.service.impl.WidgetServiceImpl.updateUserAutoSetting(WidgetServiceImpl.java:118)

at kr.co.narutec.pems2016.widget.web.WidgetController.updateUserWidgetSetting(WidgetController.java:170)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at egovframework.rte.ptl.mvc.filter.HTMLTagFilter.doFilter(HTMLTagFilter.java:51)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:423)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1079)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:318)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[sql튜닝] cmd 명령문 실행 계획  (0) 2016.10.27
[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05
[MSSQL] mssql에서 dual과 같은 가상 테이블 사용 하기  (0) 2016.08.12
[MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용  (0) 2016.08.12
[Mybatis] 비교연산자 부등호 >,< 안될때  (11) 2016.07.19

개발자가 DB에서 단순 데이터를 출력하기 위해


오라클에서는 DUAL 이라는 아래와 같이 임시의 가상 테이블을 사용한다.



1
2
3
4
/*Orecle*/

SELECT
    SYSDATE
FROM
    DUAL
cs

오라클에서 단순히 현재 시간데이터만을 출력하기 위한 쿼리이다.


그러나 MS SQL에서는 DUAL이 없다


그렇다면 MSSQL에서는 어떻게 출력해야 하나


답은 아주 간단하다


1
2
/*MS SQL*/

SELECT
    getdate() A
cs


위와같이 작성하면 A라는 컬럼명으로 현재 시간이 나타난다.


어차피 존재하지 않는 테이블이기 때문에 from 테이블명을 사용하지 않고


바로 출력하는 것 같다.


제 포스팅이 도움이 되었나요? 

그렇다면 공감하기 한번 눌러주세요 블로거에게 큰 힘이 됩니다


저작자표시 비영리

'Yame Programmer > SQL' 카테고리의 다른 글

[Oracle] 값이 존재하는지 확인 후 insert, update 하기  (0) 2016.10.05
[Oracle] java.sql.SQLException: ORA-00911: 문자가 부적합합니다.  (1) 2016.10.05
[MSSQL] 값이 존재하는지 확인 후 update insert [EXISTS] 활용  (0) 2016.08.12
[Mybatis] 비교연산자 부등호 >,< 안될때  (11) 2016.07.19
[mysql,MariaDB] 150 mysql foreign key constraint is incorrectly formed  (0) 2016.07.18

+ Recent posts

티스토리툴바