본문으로 바로가기

Mybatis + Oracle or Postgresql 에서 프로시저(Procedure) 호출 시 파라메터 자동 매핑 및 Cursor로 리턴 받는 기능 추가

category JAVA/Spring Framework 2023. 12. 15. 15:02

기존 레거시 프로그램의 DB 호출 방식이 모두 프로시저 호출이다 보니 자동 맵핑 기능을 활용하여 쉽게 쉽게 프로시저 를 호출 하곤 했습니다.

특히 오라클을 사용 하였기 때문에 리턴 타입을 Ref Cursor 로 받아 List Map 으로 변환하여 사용 했습니다.

(프로시저가 자주 수정되기도 하고 특히, 한 프로시저에서 파라메터에 따라 다양한 테이블 구조로 반환을 하기 때문에 Dto를 사용하기는 어려웠습니다)

Postgresql 를 마이그레이션 하면서 이번에도 동일하게 같은 방법으로 구현하고자 합니다.

<Oracle>

1. 프로시저 명을 넘겨 파라메터 정보(Name, Position, In/Out, Type)을 받아 옵니다.

SELECT OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT
		  FROM ALL_ARGUMENTS
		 WHERE OBJECT_NAME = #{SP_NAME}
	  ORDER BY POSITION ASC

2. 리턴 받은 파라메터 정보를 가지고 프로시저 호출 구문을 생성(Java) 후 호출 합니다.

public HashMap<String, Object> callProcedureAutoMaching(String spName, HashMap<String, Object> DBParamMap){
		List<HashMap<String, Object>> procedureInfoList = new ArrayList<HashMap<String, Object>>();
		HashMap<String, Object>       procedureParamMap = new HashMap<String, Object>();

		StringBuilder sb = new StringBuilder();
		HashMap<String, Object> procedureInfoMap = new HashMap<String, Object>();
		
		try
		{
			
			procedureInfoMap.put("SP_NAME", spName);
			//System.out.println("step 1 start");
			procedureInfoList = this.selectQry("OraCommon.procedureParamGet", procedureInfoMap);
			//System.out.println(procedureInfoList);
			//System.out.println("step 1 end");
			
			sb.append("(");
			for(int i=0;i<procedureInfoList.size();i++)
			{
				String argumentName = procedureInfoList.get(i).get("ARGUMENT_NAME").toString().toUpperCase();
				String inOutType = procedureInfoList.get(i).get("IN_OUT").toString();
				String DataType = procedureInfoList.get(i).get("DATA_TYPE").toString();
				
				switch(DataType)
				{
					//sb.append("#{" + argumentName + ", mode=" + inOutType + ",jdbcType=" + DataType + "}");
					case "REF CURSOR" :
						sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=CURSOR, resultMap=customResultMap}");
						procedureInfoMap.put(argumentName, DBParamMap.get(argumentName));
						break;
					case "VARCHAR":
                    case "VARCHAR2":
                    case "NVARCHAR":
                    case "NVARCHAR2":
                    case "CHAR":
                    case "NCHAR":
						if(inOutType.equals("OUT"))
						{
							sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=VARCHAR, javaType=String}");
							procedureInfoMap.put(argumentName, null );
						}
						else
						{
							if(DBParamMap.get(argumentName) != null)
							{
								sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=VARCHAR, javaType=String}");
								procedureInfoMap.put(argumentName, String.valueOf(DBParamMap.get(argumentName)) );
							}
							else
							{
								sb.append("null");
								procedureInfoMap.put(argumentName, null );
							}						
						}
						break;
					case "NUMBER":
						if(inOutType.equals("OUT"))
						{
							sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=NUMERIC, javaType=long}");
							procedureInfoMap.put(argumentName, null );
						}
						else
						{
							if(DBParamMap.get(argumentName) != null && !DBParamMap.get(argumentName).equals(""))
							{
								sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=NUMERIC, javaType=long}");
								procedureInfoMap.put(argumentName, Long.parseLong(String.valueOf(DBParamMap.get(argumentName))) );
							}
							else
							{
								sb.append("null");
								procedureInfoMap.put(argumentName, null );	
							}
						}
						
						break;
                    case "BLOB":
                        sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=BLOB, javaType=String}");
                        procedureInfoMap.put(argumentName, String.valueOf(DBParamMap.get(argumentName)) );
                        break;
                    case "CLOB":
                        sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=CLOB, javaType=String}");
                        procedureInfoMap.put(argumentName, String.valueOf(DBParamMap.get(argumentName)) );
                        break;
					case "NCLOB":
						sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=NCLOB, javaType=String}");
						procedureInfoMap.put(argumentName, String.valueOf(DBParamMap.get(argumentName)) );
						break;
					default :
						//무조건 varchar로 넘길경우 아래처럼 바로 값만 넣어도 가능
						if(DBParamMap.get(argumentName) != null)
						{
							sb.append("'" + DBParamMap.get(argumentName).toString().replaceAll("\'", "\''")+ "'");	
						}
						else
						{
							sb.append("null");
						}
						//위 보다 좀 더 상세한 코드
						//sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=VARCHAR, javaType=String}");
						procedureInfoMap.put(argumentName, DBParamMap.get(argumentName));
						break;
				}

				if(i < procedureInfoList.size() - 1)
				{
					sb.append(",");
				}
			}
			sb.append(")");
			
		}
		catch(NullPointerException e)
		{
			Method method = new Object(){}.getClass().getEnclosingMethod();
			ExceptionMgr.getInstance().WriteLog(CommonUtil.exceptionToString(getClass().getName(), method, e));
		}
		catch(Exception e)
		{
			Method method = new Object(){}.getClass().getEnclosingMethod();
			ExceptionMgr.getInstance().WriteLog(CommonUtil.exceptionToString(getClass().getName(), method, e));
		}
		
		procedureParamMap.put("SP_NAME", spName);
		procedureParamMap.put("SP_PARAMS", sb.toString());
		procedureInfoMap.put("SP_PARAMS", sb.toString());
		
		//System.out.println("step 2 start");
		
		//System.out.println("Procedure Param : \n" + sb.toString());
		//System.out.println("Procedure Param : \n" + sb.toString());
		
		//System.out.println("before call procedure");
		//System.out.println(procedureParamMap);
		this.callProcedure("OraCommon.procedureCall", procedureInfoMap);
		//System.out.println("step 2 end");
		
		//System.out.println("after call procedure");
		//System.out.println(procedureInfoMap);
		procedureInfoMap.remove("SP_PARAMS");
		procedureInfoMap.remove("SP_NAME");
		//System.out.println("before check");
		CommonUtil.recusiveHashMapInnerClassCheck(procedureInfoMap);
		//System.out.println("after check");
		return procedureInfoMap;
	}
<select id="procedureParamGet" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		SELECT OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT
		  FROM ALL_ARGUMENTS
		 WHERE OBJECT_NAME = #{SP_NAME}
	  ORDER BY POSITION ASC 
	</select>
	
<resultMap id="customResultMap" type="java.util.HashMap" ></resultMap>
<select id="procedureCall" statementType="CALLABLE" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    { call ${SP_NAME} ${SP_PARAMS} }
</select>

3.호출 결과(Out RefCursor)를 Dto에 담아 반환합니다.

오라클,MS-SQL,MariaDB 의 경우 Mybatis에서는 자동으로 매핑되어 HashMap 형태로 반환하여 전달 합니다.(Good)

<Postgresql >

위 과정과 동일하지만 몇가지 이슈가 있습니다.

Postgresql 은 Mybatis 에서 jdbcType을 Cursor 설정하더라도 인식하지 못하여 에러를 발생 합니다.

이런 경우 OTHER 타입으로 설정하여, ResultSet 으로 변환 후 다시 List Map 으로 변환을 진행 하여야 합니다.

그러면 기존과 동일하게 사용 할 수 있습니다.

CREATE OR REPLACE PROCEDURE public.usp_apprline_temp_simple_check4(INOUT cv_1 refcursor DEFAULT 'cv_1'::refcursor)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
   v_temp SMALLINT = 0;

BEGIN
   OPEN cv_1 FOR
   SELECT *
     FROM test_table_2 tt ;
END;
$procedure$
;
public HashMap<String, Object> callProcedureAutoMaching_new(String spName, HashMap<String, Object> DBParamMap){
		
		StringBuilder sb = new StringBuilder();
		HashMap<String, Object> procedureInfoMap = new HashMap<String, Object>();
		procedureInfoMap.put("SP_NAME", spName);
		
		try
		{
			List<HashMap<String, Object>> procedureInfoList = new ArrayList<HashMap<String, Object>>();
			procedureInfoList = this.upperKeySet(this.selectQry("postgresql.procedureParamGet", procedureInfoMap));
			DBParamMap = (HashMap<String, Object>) this.upperKeySet(DBParamMap);
			sb.append("(");
			for(int i=0;i<procedureInfoList.size();i++)
			{
				String argumentName = procedureInfoList.get(i).get("ARGUMENT_NAME").toString().toUpperCase();
				String inOutType = procedureInfoList.get(i).get("IN_OUT").toString().toUpperCase();
				String DataType = procedureInfoList.get(i).get("DATA_TYPE").toString().toUpperCase();
				//System.out.println("argumentName : " + argumentName + " inOutType : " + inOutType + " DataType : " + DataType);
				/**
				 * postgresql 에서 지원하는 타입이 다양해서 indexof 로 판단 하는게 좋아 보임(2023-12-15 장원웅)
				 */
				if(DataType.indexOf("REFCURSOR") > -1){	// == CURSOR
					sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=OTHER, resultMap=customResultMap}");
					procedureInfoMap.put(argumentName, DBParamMap.get(argumentName));
				} else if(DataType.indexOf("CHARACTER") > -1) {
					if(inOutType.equals("OUT") || inOutType.equals("INOUT"))
					{
						sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=VARCHAR, javaType=String}");
						procedureInfoMap.put(argumentName, null );
					}
					else
					{
						if(DBParamMap.get(argumentName) != null)
						{
							sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=VARCHAR, javaType=String}");
							procedureInfoMap.put(argumentName, String.valueOf(DBParamMap.get(argumentName)) );
						}
						else
						{
							sb.append("null");
							procedureInfoMap.put(argumentName, null );
						}						
					}
				} else if(DataType.indexOf("INTEGER") > -1) {
					if(inOutType.equals("OUT"))
					{
						sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=NUMERIC, javaType=long}");
						procedureInfoMap.put(argumentName, null );
					}
					else
					{
						if(DBParamMap.get(argumentName) != null && !DBParamMap.get(argumentName).equals(""))
						{
							sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=NUMERIC, javaType=long}");
							procedureInfoMap.put(argumentName, Long.parseLong(String.valueOf(DBParamMap.get(argumentName))) );
						}
						else
						{
							sb.append("null");
							procedureInfoMap.put(argumentName, null );	
						}
					}
				} else {
					//무조건 varchar로 넘길경우 아래처럼 바로 값만 넣어도 가능
					if(DBParamMap.get(argumentName) != null)
					{
						sb.append("'" + DBParamMap.get(argumentName).toString().replaceAll("\'", "\''")+ "'");	
					}
					else
					{
						sb.append("null");
					}
					//위 보다 좀 더 상세한 코드
					//sb.append("#{" + argumentName + ", mode=" + inOutType + " ,jdbcType=VARCHAR, javaType=String}");
					procedureInfoMap.put(argumentName, DBParamMap.get(argumentName));
				}

				if(i < procedureInfoList.size() - 1)
				{
					sb.append(",");
				}
			}
			sb.append(")");
			
			procedureInfoMap.put("SP_PARAMS", sb.toString());
			this.callProcedure("postgresql.procedureCall", procedureInfoMap);
			
			if(procedureInfoMap.get("CV_1") != null) {
				PgResultSet result = (PgResultSet) procedureInfoMap.get("CV_1");
				procedureInfoMap.put("CV_1", resultSetToList(result));
			}
		    
			CommonUtil.recusiveHashMapInnerClassCheck(procedureInfoMap);
		}
		catch(NullPointerException e){
			e.printStackTrace();
		}
		catch(Exception e) {
			e.printStackTrace();
		}
		
		return procedureInfoMap;
	}
    
    private List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException {
	    ResultSetMetaData md = rs.getMetaData();
	    int columns = md.getColumnCount();
	    List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
	    while (rs.next()){
	        Map<String, Object> row = new HashMap<String, Object>(columns);
	        for(int i = 1; i <= columns; ++i){
	            row.put(md.getColumnName(i), rs.getObject(i));
	        }
	        rows.add(row);
	    }
	    return rows;
	}
<resultMap id="customResultMap" type="java.util.HashMap" ></resultMap>
<select id="postgresql.procedureCall" statementType="CALLABLE" parameterType="java.util.HashMap" resultType="java.util.HashMap">
	<![CDATA[
		call ${SP_NAME} ${SP_PARAMS}
	]]>
</select>
	
<select id="postgresql.procedureParamGet" parameterType="java.util.HashMap" resultType="java.util.HashMap">
    <![CDATA[
        select p.proname AS OBJECT_NAME,
           t.column_name AS ARGUMENT_NAME,
           CASE WHEN t.arg_type IS NOT NULL THEN  t.arg_type::regtype::TEXT 
           ELSE t.coly_type::regtype::TEXT 
           END AS DATA_TYPE,
           CASE t.arg_mode 
                   WHEN 'o' THEN 'OUT'
                   WHEN 'b' THEN 'INOUT'
                   WHEN 'i' THEN 'IN'
                   WHEN 't' THEN 'TABLE'
           ELSE 'IN'
           END AS IN_OUT
           --	   ,t2.typname
            --	   ,prokind
            --	   ,t.arg_mode
    from pg_proc p
      cross join unnest(proargnames, proargmodes, proallargtypes, proargtypes) with ordinality as t(column_name, arg_mode, arg_type,coly_type, col_num)
      join pg_type t2 
        on t2.oid = p.prorettype
       AND COALESCE(t.arg_mode,'') <> 't'
    where p.proname = #{SP_NAME}
    order by t.col_num
    ]]>
</select>
저작자표시 비영리 변경금지

'JAVA > Spring Framework' 카테고리의 다른 글

Jwt 인증 시 The Token can't be used before 메세지  (0) 2024.02.14
그룹웨어 운영한지 벌써 3년..잠재적 문제(백엔드)  (0) 2023.09.19
JPA를 알아보다가 나온 R2DBC(메모)  (0) 2023.08.23
Spring 비동기,Reactive 통신을 위한 자료들  (0) 2023.08.23
비동기 프로그래밍 정리된 글[펌]  (0) 2023.07.13
댓글 , 엮인글
Woong's 의 좌우충돌 프로그램 개발
블로그 이미지 뽀록이야 님의 블로그
MENU
CATEGORY
VISITOR 오늘 / 전체

티스토리툴바