ibatis/Mybatis - 동적 쿼리에서 발생하는 java.sql.SQLException : 부적한 열 이름 오류 원인과 해결 방법

개발 환경

• Java 8
• Spring 2.x
• Maven
• Oracle
• Mybatis
• IntelliJ

 

개요

Mybatis, ibatis에서는 동적으로 쿼리를 작성할 수 있는 Dynamic Tag 기능을 제공한다.

동적 쿼리란 조건에 따라 쿼리를 동적으로 생성하는 것을 말한다. 이를 통해 하나의 쿼리로 다양한 조건을 처리할 수 있다.

<select id="findUsers" resultType="User">
    SELECT * FROM users
    WHERE 1 = 1
    <if test="name != null">
        AND name = #{name}
    </if>
    <if test="age != null">
        AND age = #{age}
    </if>
</select>

 

위 쿼리는 조건에 따라 아래와 같은 쿼리들을 생성할 수 있다.

SELECT * FROM users

SELECT * FROM users
WHERE 1 = 1
AND name = '김찬빈'

SELECT * FROM users
WHERE 1 = 1
AND age = 30

SELECT * FROM users
WHERE 1 = 1
AND name = '김찬빈'
AND age = 30

 

본 글에서 다룰 에러 케이스는 SELECT 절에 동적 쿼리를 사용했을 때 발생했다.

아래 쿼리는 에러가 발생했던 쿼리이다.

SELECT id
<isEqual property="isOlderPerson" compareValue="true">
    , CASE WHEN age >= 30 THEN 'Y' ELSE 'N' END AS older_person_yn
</isEqual>
FROM users

 

isOlderPerson 값이 true이면 CASE WHEN 표현식이 포함되어 해당 유저가 아저씨 여부를 나타내는 older_person_yn 컬럼이 생성된다. isOlderPerson 값이 null이거나 false라면 CASE WHEN 표현식은 쿼리에 포함되지 않는다. 당연히 older_person_yn 컬럼도 생성되지 않는다.

 

에러가 발생했던 실행 흐름은 다음과 같다.

• isOlderPerson = true로 프로세스 실행
  • id, older_person_yn 컬럼값이 포함되어 쿼리가 잘 실행되었다.

SELECT id, CASE WHEN age >= 30 THEN 'Y' ELSE 'N' END AS older_person_yn
FROM users

 

• isOlderPerson = false로 프로세스 실행
  • java.sql.SQLException : 부적합한 열 이름 에러 발생

SELECT id
FROM users

 

원인

[ERROR] [webtob-hth0-19] [j.sqlonly] 2. ResultSet.getObject(older_person_yn)
java.sql.SQLException: 부적합한 열 이름
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3724) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2799) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at oracle.jdbc.driver.OracleResultSet.getObject(OracleResultSet.java:462) ~[ojdbc6-11.2.0.4.jar:11.2.0.4.0]
at net.sf.log4jdbc.sql.jdbcapi.ResultSetSpy.getObject(ResultSetSpy.java:1947) [log4jdbc-log4j2-jdbc4.1-1.16.jar:?]
at org.apache.commons.dbcp2.DelegatingResultSet.getObject(DelegatingResultSet.java:763) [commons-dbcp2-2.9.0.jar:2.9.0]
at org.apache.commons.dbcp2.DelegatingResultSet.getObject(DelegatingResultSet.java:763) [commons-dbcp2-2.9.0.jar:2.9.0]
at com.ibatis.sqlmap.engine.type.ObjectTypeHandler.getResult(ObjectTypeHandler.java:35) [ibatis-sqlmap-2.3.4.726.jar:?]
...

ResultSet.getObject(columnName) : ResultSet.getObject를 통해 DB 컬럼의 행값을 Java 객체로 가져온다. 

 

에러 로그를 확인해보면 older_person_yn 컬럼을 참조하려고 했지만 존재하지 않아서 SQLException이 발생한 것을 확인할 수 있다. 

문제는 해당 쿼리에서는 older_person_yn에 대한 내용이 쿼리에 포함되지 않도록 isOlderPerson = false로 실행된 프로세스이기 때문에 더 의아했다.

 

혹시나 쿼리에 포함되었을 수 있기 때문에 로그에서 실행된 쿼리문을 확인했다. 

SELECT id
FROM users

쿼리는 의도한대로 실행되었고 CASE WHEN 절도 제외되었고 older_person_yn 별칭조차 쿼리에 포함되지 않았는데, ResultSet.getObject(older_person_yn)가 실행된 이유가 도대체 뭘까?

 

ibatis는 성능 개선을 위해 쿼리 결과를 메모리에 저장하여, 동일한 쿼리에 대해 데이터베이스를 다시 조회하지 않고 메모리에서 데이터를 반환한다. 바로 이 부분때문에 발생한 문제였다.

 

동적 쿼리를 사용하게 되면 쿼리의 조건이나 반환되는 컬럼이 달라질 수 있다. 하지만 캐시를 사용하게 되면 이전 쿼리를 사용하기 때문에 변

경된 쿼리를 인식하지 못하고 이전에 캐시된 쿼리 결과를 반환하게되면서 발생한 문제였던 것이다.

 

해결 

이 문제를 해결하는 방법은 간단하다. sql 옵션 중 remapResult 값을 true로 설정하면된다. 

ibatis 공식 문서(25페이지)를 보면 쿼리에 동적 컬럼이 사용되면  remapResult 옵션을 true로 설정하라고 되어있다. (SELECT절, FROM절에 동적 쿼리가 사용되면 사용하면 될듯하다.) 

The remapResults attribute should be set to true when a query has a variable set of return columns.
For example consider the following queries:

SELECT $fieldList$
FROM table

In the former example the list of column is dynamic, even though the table is always the same.
SELECT *
FROM $sometable$

 

이렇게되면 캐시된 쿼리 결과를 사용하지 않게된다. 단, 캐시를 사용하지 않는 쿼리가 많아질수록 오버헤드가 발생할 수 있기때문에 동적 쿼리에만 적용해주는 것이 바람직하다. 

<select id="findUsers" parameterClass="java.util.HashMap" resultClass="java.util.HashMap" remapResults="true">
    SELECT id
    <isEqual property="isOlderPerson" compareValue="true">
        , CASE WHEN age >= 30 THEN 'Y' ELSE 'N' END AS older_person_yn
    </isEqual>
    FROM users
</select>

 

참조

https://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf

[ibatis] 동적으로 column명을 사용할때 java.sql.SQLException: 부적합한 열 이름 에러 해결

 

iBatis/Mybatis 동적쿼리 요류해결 - 캐시 비활성화