mybatis typehandler를 이용한 oracle procedure에 list<map> parameter 넘기기

Oracle EMP 사원정보에 사원입력하는 예제.

 

mybatis의 foreach를 이용해 insert,insert all,또는 anonymous pl/sql block 이용해도 되겠습니다만,

다량의 row를 이용할 경우엔 아래 같이 하는 것도 괜찮지 않을까요?

 

1. 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;

2. 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);
   	}
   
   }

   
   
   반응형
   
   
   
3. 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;
   /

4. 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>

   
   
   
   
5. Dao Layer

   @Resource(name="sqlSessionTemplate")
   private SqlSessionTemplate sqlSession;
    
   public void execProc(String sqlId,HashMap<String,Object> commandMap) throws SQLException {
       sqlSession.selectOne(sqlId,commandMap);
   }

6. 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);
   	}
   }