JAVA 자료구조 MyBatis 에서 procedure 처리

MyBatis 에서 procedure 처리

Oracle 기준
 

CREATE OR REPLACE PROCEDURE POST.TEST_PROC_VALUE 
(
    pi_CD IN varchar2,
    po_VALUE OUT varchar2
)
IS
BEGIN

    SELECT MAX(CD) INTO po_VALUE FROM CODE WHERE MSTR_CD = pi_CD;

   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
       
END TEST_PROC_VALUE;

<procedure>

	<parameterMap id="paramMap" type="hashmap">
		<parameter property="cd" mode="IN" jdbcType="VARCHAR" javaType="java.lang.String" />
		<parameter property="result" mode="OUT" jdbcType="VARCHAR" javaType="string"/>
	</parameterMap>
	
	<select id="getCode" statementType="CALLABLE" parameterMap="paramMap">
		{ call TEST_PROC_VALUE(?,?) }
	</select>
	
	<select id="getCode2" statementType="CALLABLE" parameterType="hashmap">
	<!-- { 와 call 사이에 스페이스만 허용 tab 은 허용하지 않음 -->
		{ call TEST_PROC_VALUE
			(
				#{cd, mode=IN, jdbcType=VARCHAR, javaType=string}, 
				#{result, mode=OUT, jdbcType=VARCHAR, javaType=string} 
			)
		}
	</select>

<sqlmap.xml>

 

CREATE OR REPLACE PACKAGE TestPackage AS
    
  type cursorType is ref cursor; 

  PROCEDURE getCodes(pi_cd IN VARCHAR2, pi_cursor OUT cursorType);

END TestPackage;

/


CREATE OR REPLACE PACKAGE BODY TestPackage AS

  PROCEDURE getCodes(pi_cd IN VARCHAR2, pi_cursor OUT cursorType) IS
  BEGIN
    open pi_cursor for select MSTR_CD, CD_TYPE, CD, CD_NM from CODE WHERE MSTR_CD = pi_cd;
  END;

END TestPackage;

/

<package>

	<resultMap type="kr.lancerme.webapp.dao.bookinfo.CodeBean" id="codeBeanResult">
		<result property="mstrCd" column="MSTR_CD" javaType="string"/>
		<result property="cdType" column="CD_TYPE" javaType="string"/>
		<result property="cd" column="CD" javaType="string"/>
		<result property="cdNm" column="CD_NM" javaType="string"/>
	</resultMap>
	
	<parameterMap id="paramMap2" type="hashmap">
		<parameter property="cd" mode="IN" jdbcType="VARCHAR" javaType="string"/>
		<parameter property="result" mode="OUT" jdbcType="CURSOR" resultMap="codeBeanResult"/>
	</parameterMap>
	
	<select id="getCode3" statementType="CALLABLE" parameterMap="paramMap2">
		{ call TESTPACKAGE.getCodes(?, ?)}
	</select>
	
	<select id="getCode4" statementType="CALLABLE" parameterType="hashmap">
		{ call TESTPACKAGE.getCodes
			(
				#{cd, mode=IN, jdbcType=VARCHAR, javaType=string},
				#{result, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=codeBeanResult}
			)
		}
	</select>

<sqlmap.xml>
 

	public String getCode (HashMap<String, Object> map);
	
	public String getCode2 (HashMap<String, Object> map);
	
	public String getCode3 (HashMap<String, Object> map);
	
	public String getCode4 (HashMap<String, Object> map);

<dao.java>

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

	@Autowired
    private BookInfoDao bookInfoDao;
	
	public String getCode(HashMap<String, Object> map) {
		return bookInfoDao.getCode(map);
	}
	
	public String getCode2(HashMap<String, Object> map) {
		return bookInfoDao.getCode2(map);
	}
	
	public String getCode3(HashMap<String, Object> map) {
		return bookInfoDao.getCode3(map);
	}

	public String getCode4(HashMap<String, Object> map) {
		return bookInfoDao.getCode4(map);
	}

<service.java>
 

	@Autowired
	private BookInfoService bookInfoService;
	
	public void getCode(String cd) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("cd", cd);
		
		bookInfoService.getCode(map);
		
		System.out.println("getCode = " + map.get("result"));
	}
	
	public void getCode2(String cd) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("cd", cd);
		
		bookInfoService.getCode2(map);
		
		System.out.println("getCode2 = " + map.get("result"));
	}
	
	@SuppressWarnings("unchecked")
	public void getCode3(String cd) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("cd", cd);
		
		bookInfoService.getCode3(map);
		
		ArrayList<CodeBean> list = (ArrayList<CodeBean>) map.get("result");
		Iterator<CodeBean> it = list.iterator();
		while (it.hasNext()) {
			System.out.println(it.next());
		}
	}

	@SuppressWarnings("unchecked")
	public void getCode4(String cd) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("cd", cd);
		
		bookInfoService.getCode4(map);
		
		ArrayList<CodeBean> list = (ArrayList<CodeBean>) map.get("result");
		Iterator<CodeBean> it = list.iterator();
		while (it.hasNext()) {
			System.out.println(it.next());
		}
	}

<controller.java>

[출처] https://lancerme.tistory.com/30