Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
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 |
Tags
- Spring
- 공정능력
- JavaScript
- hadoop
- MSSQL
- Android
- Java
- IntelliJ
- react
- SSL
- vaadin
- mapreduce
- NPM
- mybatis
- window
- R
- Express
- Kotlin
- tomcat
- Sqoop
- Python
- xPlatform
- SQL
- es6
- plugin
- table
- 보조정렬
- SPC
- GIT
- Eclipse
Archives
- Today
- Total
DBILITY
mybatis typehandler를 이용한 oracle procedure에 list<map> parameter 넘기기 본문
java/mybatis
mybatis typehandler를 이용한 oracle procedure에 list<map> parameter 넘기기
DBILITY 2017. 7. 15. 10:44반응형
Oracle EMP 사원정보에 사원입력하는 예제.
mybatis의 foreach를 이용해 insert,insert all,또는 anonymous pl/sql block 이용해도 되겠습니다만,
다량의 row를 이용할 경우엔 아래 같이 하는 것도 괜찮지 않을까요?
- Oracle Object 및 Collection Type 생성
DROP TYPE SCOTT.EMP_LIST; DROP TYPE SCOTT.EMP_MAP CREATE OR REPLACE TYPE SCOTT.EMP_MAP AS OBJECT ( EMPNO NUMBER(4), ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ); CREATE OR REPLACE TYPE SCOTT.EMP_LIST AS TABLE OF SCOTT.EMP_MAP;
- mybatis용 TypeHandler
package com.dbility.apps.mybatis; import java.sql.Array; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Map; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * * Description * * * @author hyperrookie@gmail.com * * @version 1.0.0 * @date 2017. 7. 10. */ public class ListMapTypeHandler implements TypeHandler<Object> { private static final Logger log = LoggerFactory.getLogger(ListMapTypeHandler.class); @SuppressWarnings({ "rawtypes", "unchecked" }) @Override public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException { List<Map> objects = (List<Map>) parameter; StructDescriptor structDescriptor = new StructDescriptor("EMP_MAP", ps.getConnection()); STRUCT[] structs = new STRUCT[objects.size()]; log.debug("List Size --------> {}",objects.size()); for (int index = 0; index < objects.size(); index++) { Map map = objects.get(index); Object[] params = new Object[map.keySet().size()]; Iterator<String> iterator = map.keySet().iterator(); int keyIndex = 0; while (iterator.hasNext()) { String key = (String)iterator.next(); params[keyIndex] = map.get(key); log.debug("{}:{}:{} -----> {}",index,keyIndex,key,map.get(key)); keyIndex++; } log.debug("params -----> {}, length -----> {}",params,params.length); STRUCT struct = new STRUCT(structDescriptor, ps.getConnection(), params); structs[index] = struct; } ArrayDescriptor desc = ArrayDescriptor.createDescriptor("EMP_LIST",ps.getConnection()); Array array = new ARRAY(desc, ps.getConnection(),structs); ps.setArray(i, array); } @Override public Object getResult(ResultSet rs, String columnName) throws SQLException { return null; } @Override public Object getResult(ResultSet rs, int columnIndex) throws SQLException { return null; } @Override public Object getResult(CallableStatement cs, int columnIndex) throws SQLException { if ( cs.wasNull() ) return null; else return cs.getString(columnIndex); } }
반응형 - Oracle Procedure
CREATE OR REPLACE PROCEDURE SCOTT.EMP_DATA_INSERT (P_ARRAY IN EMP_LIST,V_MSG OUT VARCHAR2) IS START_TIME NUMBER; END_TIME NUMBER; TX_CNT NUMBER; TX_INPROGRESS EXCEPTION; /****************************************************************************** NAME: EMP_DATA_INSERT PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2017/07/10 ROOKIE 1. Created this procedure. NOTES: ******************************************************************************/ BEGIN START_TIME := DBMS_UTILITY.GET_TIME; V_MSG :='EMP_DATA_INSERT'; FOR I IN 1..P_ARRAY.count LOOP INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT P_ARRAY(i).EMPNO, P_ARRAY(i).ENAME, P_ARRAY(i).JOB, P_ARRAY(i).MGR, P_ARRAY(i).HIREDATE, P_ARRAY(i).SAL, P_ARRAY(i).COMM,P_ARRAY(i).DEPTNO FROM DUAL T1 WHERE NOT EXISTS ( SELECT NULL FROM SCOTT.EMP WHERE EMPNO= P_ARRAY(i).EMPNO AND ENAME=P_ARRAY(i).ENAME AND JOB=P_ARRAY(i).JOB AND MGR=P_ARRAY(i).MGR AND HIREDATE=P_ARRAY(i).HIREDATE AND SAL=P_ARRAY(i).SAL AND COMM=P_ARRAY(i).COMM AND DEPTNO=P_ARRAY(i).DEPTNO ); END LOOP; V_MSG := V_MSG||' Rows affected : '||TO_CHAR(SQL%ROWCOUNT,'FM999,999,990'); COMMIT; END_TIME := DBMS_UTILITY.GET_TIME; V_MSG := V_MSG||' ELAPSED: '||TO_CHAR( (END_TIME-START_TIME)/100,'FM9,990.990'); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(' ERROR: '||SQLERRM); V_MSG := ' ERROR: '||SQLERRM; RAISE; END EMP_DATA_INSERT; /
- mybatis Mapper
<select id="EMP_DATE_INSERT" parameterType="hashmap" statementType="CALLABLE"> { call SCOTT.EMP_DATA_INSERT(#{dsInput,javaType=Object,jdbcType=ARRAY, jdbcTypeName=EMP_LIST, mode=IN, typeHandler=com.dbility.apps.mybatis.ListMapTypeHandler},#{result,mode=OUT,jdbcType=VARCHAR}) } </select>
- Dao Layer
@Resource(name="sqlSessionTemplate") private SqlSessionTemplate sqlSession; public void execProc(String sqlId,HashMap<String,Object> commandMap) throws SQLException { sqlSession.selectOne(sqlId,commandMap); }
- Service Layer
/** * @author hyperrookie@gmail.com * */ @Service("EMPService") public class EMPServiceImpl implements EMPService { private static final Logger logger = LoggerFactory.getLogger(EMPServiceImpl.class); @Autowired private Dao<Map<String,Object>> dao; @SuppressWarnings("rawtypes") @Override public void saveEMP(HashMap<String,Object> dto) throws Exception { /*List paraList = (List) dto.get("dsInput"); HashMap paraMap = new HashMap(); logger.debug("paraList : {}", paraList); for( int i = 0; i < paraList.size() ; i++) { paraMap = (HashMap) paraList.get(i); logger.debug("paraMap : {}", paraMap); }*/ dao.execProc("com.dbility.apps.mapper.EMP_DATA_INSERT", dto); } }
반응형
'java > mybatis' 카테고리의 다른 글
mybatis foreach multiple like (0) | 2023.11.30 |
---|---|
mybatis result net.sourceforge.jtds.jdbc.ClobImpl@object 일 경우 (0) | 2023.01.09 |
mybatis 조건문에서 java static method 사용 예시 (0) | 2022.11.08 |
mybatis comparision usage in condition expression ( 부등호 조건 등 ) (0) | 2022.10.04 |
mybatis insert into table select시 ora-00933 (0) | 2017.07.01 |
Comments