Mybatis에서 PostgreSQL 프로시저 Cursor 값 받기

Oracle to PostgreSql 로 DB를 컨버전 할 일이 생겼다...

PostgreSql컨버전을 하던 중 아니나 다를까 문제가 생겼다.

Oracle에서 Procedure의 Out parameter로 받는 Cursor를 PostgreSql에서는 mybatis에서 값을 받아오는데 문제가 생겼다,,, 하...

 

해당 내용에 관해 아무리 구글링을 해도 최근 버전에서야 Procedure를 도입한 Postgresql인지라 관련 자료도 없기에 미친 추론을 하여 겨우 감을 잡았다.

 

관련 내용을 찾던 중, jdbc postgreSql 공식문서를 보니  Statement를 이용해 Object로 값을 받아와 ResultSet으로 형변환 해주는 방식을 사용하고 있었다.

https://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resultset-setof

 

하지만, 현재 시스템에선 mybatis를 사용하고 있어서 최대한 이 규칙을 맞추고 싶었다.

삽질을 하며 찾아낸 돌파구를 기록하니 누군가에게 도움이 되었으면 한다.

 

일단, mybatis에서 oracle은 cursor를 jdbcType Cursor로 받아올 수 있다.

#{result, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=dto}

이런식으로 mybatis가 ResultSet을 dto에 맞춰서 알아서 형변환을 해주기 때문에 손쉽게 List로 받아올 수 있다.

 

하지만 Postgresql에서는 cursor를 CURSUR로 받지 못하며, OTHER 타입으로 받아야 한다.

ALL test(#{result, mode=OUT, jdbcType=OTHER})

 

당연히 OTHER타입으로 받으니 해당 Dto 변수는 Object로 타입을 지정 해놔야한다.

 

 

간략하게 예제 소스를 보면서 이해해보자.

 

test procedure

CREATE OR REPLACE PROCEDURE test(OUT result refcursor)
 LANGUAGE plpgsql
AS $procedure$
	BEGIN
		OPEN result FOR
		SELECT EMP_NO
		     , EMP_NAME
		  FROM EMPLOYEE ;

	END;
$procedure$
;

해당 테이블에 임시 데이터를 채워넣어 놨다.

 

 

 

Mapper.xml

<selectid="testSp"statementType="CALLABLE"resultType="Map"parameterType="Map">
	CALL test(#{result,mode=OUT,jdbcType=OTHER})
</select>

 

Mapper.class

@Mapper
public interface TestMapper{
	public void testSp(Map<String,Object>paramMap);
}

 

Service.class

@Service
@Slf4j
@Transactional
public class TestService{

    public void test(){
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("result", null);
        testMapper.testSp(paramMap);
        ResultSet rs = (ResultSet)paramMap.get("result");
        try{
            while (rs.next()){
                String empNo = rs.getString("emp_no");
                String empNm = rs.getString("emp_name");
                System.out.println(empNo);
                System.out.println(empNm);
            }
        }catch (Exception e ){
            e.getMessage();
        }

    }
}

여기서 주의할 점이 반드시 Service Class위에 *@Transactional 어노테이션을 추가* 해줘야 한다.

PostgreSql에서는 Autocommit false인 상태에서 트랜잭션을 태워야 Cursor값을 출력할 수 있다. 

트랜잭션을 태우지 않으면 <unnamed portal 1> 이라는 값으로 제대로 Cursor값을 출력할 수 없게 된다.

 

 

결과

 

 

 

음,, 이렇게 하면 각기 다른 개발자가 Service단에서 무한 rs.getString("컬럼명") 을 찍어야 한다.

테이블 컬럼이 많아질수록 도르마무 사태는 반복된다.

 

엄청난 트랙잭션이 발생하면서 빠른 퍼포먼스를 요구하지 않는 비즈니스 로직이라면 좀 더 통일되고 하드코딩이 없는 방식으로 ResultSet값을 추출하고 싶었다.

 

그래서 아래와 같은 클래스를 만들었다.

 

public class StrConverter {
    private StrConverter(){}
    public static String snakeToCamel(String str) {
        str = str.substring(0, 1).toUpperCase()
                + str.substring(1);

        StringBuilder sb
                = new StringBuilder(str);

        for (int i = 0; i < sb.length(); i++) {
            if (sb.charAt(i) == '_') {

                sb.deleteCharAt(i);
                sb.replace(
                        i, i + 1,
                        String.valueOf(
                                Character.toUpperCase(
                                        sb.charAt(i))));
            }
        }

        return sb.toString();
    }


    public static void convertMapToDto(Map<String,Object> map, Object dto) throws InvocationTargetException, IllegalAccessException {
        String keyAttribute = "";
        String setMethodString = "set";
        String methodString = "";
        StringBuilder sb = new StringBuilder();
        Iterator<String> itr = map.keySet().iterator();

        while(itr.hasNext()){
            keyAttribute = itr.next();

            sb.append(setMethodString);
            sb.append(keyAttribute);

            methodString = sb.toString();
            sb.delete(0, sb.length());

            Method[] methods = vo.getClass().getDeclaredMethods();

            for(int i=0;i<methods.length;i++){
                if(methodString.equals(methods[i].getName())){
                    methods[i].invoke(dto, map.get(keyAttribute));
                }
            }
        }
    }
}

 

ResultSetMetaData 를 이용해 ResultSet의 컬럼명을 불러오고 해당 컬럼을 Camel 케이스로 변환한 후, 자바 reflaction을 이용해 map에 담긴 데이터를 dto의 set메소드를 호출하여 값을 setting할 수 있는 메소드를 만들어 놨다.

 

dto 작성

@Data
public class TestDto {
    private String empNo;
    private String empName;
}

 

service 수정

public void test(){
        Map<String,Object> paramMap = new HashMap<>();
        paramMap.put("result", null);
        testMapper.testSp(paramMap);
        ResultSet rs = (ResultSet)paramMap.get("result");
        List<TestDto> resultList = new ArrayList<>();
        try{
            ResultSetMetaData rsmd = rs.getMetaData();
            int culmnCnt = rsmd.getColumnCount();
            while(rs.next()){
                Map<String,Object> resultMap = new HashMap<>();
                for(int i = 1; i <= culmnCnt; i++){
                    String columnName = rsmd.getColumnName(i);
                    resultMap.put(StrConverter.snakeToCamel(columnName), rs.getString(columnName));
                }

                TestDto dto = new TestDto();
                StrConverter.convertMapToVO(resultMap, dto);
                resultList.add(dto);
            }
        }catch (Exception e ){
            e.getMessage();
        }
        resultList.forEach(dto -> System.out.println(dto.toString()));

    }

 

위와 같이 수정하면, 모든 개발자가 dto만 수정하면 동일한 코드를 유지할 수 있어서, rs.getString의 도르마무 사태를 피할 수 있다. 그리고 Reflaction은 많은 비용을 요구하기 때문에 기피되긴 하지만, 취향에 따라 쓰면 될 것 같다. 참고로 컬럼 15개인 테이블의 데이터 2000건을 reflaction을 이용해서 변환했을때 0.1초가 안걸렸으므로 일단은 사용해보려고 한다.

 

누군가에게 도움이 되길 바라며 글을 마무리한다,,

끗!