SPRING PROCEDURE 사용하기 2

안녕하세요

PROCEDURE CURSOR 사용입니다.

ORACLE 프로시저구문

create or replace PROCEDURE pro_testTableSel(
varNameCol IN VARCHAR2,
c_result OUT SYS_REFCURSOR)
IS
 
BEGIN
OPEN c_result FOR
    SELECT idCol, nameCol, valueCol
    FROM testTable
    WHERE nameCol = varNameCol;
 
END pro_testTableSel;

home.jsp

프로시저 실행 <br/>
<form name="procedureForm" id="procedureForm" method="POST" action="./springProcedure.do">
    <input type="text" value="글쓴이" id="nameCol" name="nameCol"/>
     <input type="submit" value="프로시저 호출" />
</form>

HomeController.java

@RequestMapping(value = "/springProcedure.do", method = RequestMethod.POST)
    public void springProcedure(HttpServletRequest request){
        
        //request Map은 수정이 불가능해서 따로 담아서 Procedure 처리
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("NAMECOL", request.getParameter("nameCol"));
        
        logger.info("map check : "+map.toString());
        
        homeService.getProcedureSel(map);
    }

HomeService.java

public void getProcedureSel(Map<String, Object> map);

HomeServiceImpl.java

@Override
    public void getProcedureSel(Map<String, Object> map) {
        map.put("resultList", new ArrayList<HashMap<?,?>>());
        homeMapper.testDbProcedure(map);
        
        //가져온 사이즈 체크
        int size = map.get("resultList").toString().length();
        
        //가져온값
        List<?> list = (List<?>) map.get("resultList");
        
        //출력
        for(int i = 0; i < size - 1; i++){
            logger.info("cursor list Check ServiceImpl : "+list.get(i));
        }
        
    }

HomeMapper.java

public void testDbProcedure(Map<String, Object> map);

testMapper.xml

<!-- cursor 값 내용물 -->
<resultMap type="java.util.Map" id="resultMap">
    <result property="idCol" column="idCol" javaType="integer"/>
    <result property="nameCol" column="nameCol" javaType="string"/>
    <result property="valueCol" column="valueCol" javaType="string"/>
</resultMap>
 
<!-- 프로시저 (NUMBER -> NUMERIC 문자열(VARCHAR)), resultMap = 상위 id -->
    <select id="testDbProcedure" parameterType="java.util.Map" statementType="CALLABLE">
        CALL pro_testTableSel(
        #{NAMECOL, mode=IN, jdbcType=VARCHAR},
        #{resultList, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=resultMap}
        )
    </select>

결과

감사합니다.