SPRING PROCEDURE 사용하기 1

안녕하세요.

 

참고 프로시저

CREATE OR REPLACE PROCEDURE pro_testTableSel(
varIdCol IN OUT NUMBER,
varNameCol OUT VARCHAR2,
varValueCol OUT VARCHAR2)
IS
 
BEGIN
    SELECT idCol, nameCol, valueCol
    INTO varIdCol, varNameCol, varValueCol
    FROM testTable
    WHERE idCOl = varIdCol;
 
END pro_testTableSel;

 

home.jsp

프로시저 실행 <br/>
<form name="procedureForm" id="procedureForm" method="POST" action="./springProcedure.do">
    <input type="text" value="2" id="idCol" name="idCol"/>
     <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("IDCOL", Integer.parseInt(request.getParameter("idCol")));
        
        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) {
        homeMapper.testDbProcedure(map);
        
        logger.info("map Check ServiceImpl : "+map.toString());
    }

HomeMapper.java

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

testMapper.xml

<!-- 프로시저 (NUMBER -> NUMERIC 문자열(VARCHAR)) -->
    <select id="testDbProcedure" parameterType="java.util.Map" statementType="CALLABLE">
        CALL pro_testTableSel(
        #{IDCOL, mode=INOUT, jdbcType=NUMERIC, javaType=Integer},
        #{NAMECOL, mode=OUT, jdbcType=VARCHAR, javaType=String},
        #{VALUECOL, mode=OUT, jdbcType=VARCHAR, javaType=String}
        )
    </select>

 

결과

감사합니다.