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를 이용할 경우엔 아래 같이 하는 것도 괜찮지 않을까요?

 

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

 

반응형
Comments