개발 흔적 남기기

insert 쿼리를 날리고 해당 쿼리의 Primary key값을 가져오고 싶은 경우가 있는데, 불필요하게 select를 날릴 필요없이 mybatis옵션을 통해 방금 insert한 값의 pk값을 가져올 수 있다.

useruseGeneratedKeys, keyProperty 속성을 추가하면 처리한 paramter값에 넣어주는 것을 볼 수 있다.

<insert id="inserTableData" parameterType="java.util.HashMap" useGeneratedKeys="true" keyProperty="id">
	INSERT INTO table(
      column1,
      ...
    ) VALUES (
      #{column1},
      ...
	)
	<selectKey keyProperty="lastPk" resultType="Integer" order="AFTER">
		SELECT LAST_INSERT_ID()
	</selectKey>
</insert>

hashmap parameter를 통해 insert를 처리하였고, insert dao가 끝난 후 hashmap데이터를 확인해보면 키값으로 지정한 <selectKey>를 통해 지정한 lastPk에 값이 들어간것을 볼 수 있었다.

기본적으로 하이디와 같은 툴에서 Mysql, Maria DB를 접근하여 쿼리를 날릴 때, 다중으로 delete, update, insert등을 처리 할 수 있다. mysql의 경우에는 구분자 ;(세미콜론)을 추가하여 DML 쿼리를 여러번 처리 할 수 있는데, 당연히 mybatis에서도 처리가 될 것이라고 생각하여 테스트 해봤지만 계속해서 문법 에러가 발생하였다.

확인을 해보니 Mybatis에 mysql연결을 할 때 옵션 설정이 필요하였다.

allowMultiQueries=true

allowMultiQueries 을 true처리해주고 DB를 연결해주면 다중으로 날려도 정상적으로 처리가 된다.

jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true

*추가적으로 적용할 옵션은 & 추가하여 처리

-Mybatis

<delete id="testDelete" parameterType="java.util.Map">
	DELETE FROM A where id = #{id};
	DELETE FROM B where id = #{id};
</delete>

자바 소켓 프로그램 + 스케줄러로 구성된 프로세스를 실서버 운용중에 있었는데, 주말간 문제가 없었는지 확인해보니 주말사이에 스케줄러가 동작할때마다 에러를 뿜어내고 있었습니다.

[2020-07-18 11:00:00] ERROR (utils.SqlSessionUtils - selectList:92) - Exception
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 86,387,538 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at kr.or.kisa.ktoaInterlock.utils.SqlSessionUtils.selectList(SqlSessionUtils.java:90)
	at kr.or.kisa.ktoaInterlock.dao.InterlockDao.selectYesterdayReportList(InterlockDao.java:95)
	at kr.or.kisa.ktoaInterlock.scheduler.InterLockScheduler.execute(InterLockScheduler.java:40)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

와 같은 에러를 출력중이였는데...

DB는 mysql을 사용중이며 해당 스케줄러는 하루에 한번 돌도록 구성되어 있습니다.

에러 내용을 가지고 검색을 해보니, 너무 오랫동안 mysql을 사용하지 않고 있다가 갑자기 사용하려고 하면 발생하는 에러로 mysql에서 오랫동안 사용을 하지 않으니 강제로 종료해버리는 이슈라고 합니다.

POOLED타입의 옵션을 수정할 필요가 있었고, 옵션들을 살펴봤습니다.

https://mybatis.org/mybatis-3/ko/configuration.html

공식문서를 참고하였고, 다른 포스팅 글들과 함께 참고하여 아래의 3옵션을 확인하였습니다.

poolPingQuery - 커넥션이 작업하기 좋은상태인지 요청을 받을 준비가 되었는지 등 체크하기 위해 DB에 핑쿼리를 날리는 문구를 지정합니다.

poolPingEnabled - 핑쿼리를 사용할지 안할지 설정합니다. default - false

poolPingConnectionsNotUsedFor - 핑쿼리를 얼마나 자주 사용할지 지정합니다. 핑쿼리를 사용할 경우에만 해당 옵션이 동작합니다.(ms)

mybatis 설정쪽에 의미없는 쿼리로 핑을 날리듯이 처리를 할 수 있다고하여 옵션을 추가하였고, 지켜보기로 하였습니다.

문제가 해결된다면 해당 포스팅은 여기서 종료하겠습니다.

설정 수정 옵션

<property name="poolPingQuery" value="select 1"/> <!-- 핑쿼리 추가 -->
<property name="poolPingEnabled" value="true"/> <!-- 핑쿼리 추가 -->
<property name="poolPingConnectionsNotUsedFor" value="7200000"/> <!-- 2시간마다 -->

위 내용처럼 ping처리를 할 수 있는 의미없는 쿼리와 사용하겠다는 옵션을 추가하였습니다.

모든 옵션

<environments default="development">
	<environment id="development">
		<transactionManager type="JDBC"/>
		<dataSource type="POOLED">
			<property name="driver" value="${driver}"/>
			<property name="url" value="${url}"/>
			<property name="username" value="${username}"/>
			<property name="password" value="${password}"/>
			<property name="poolPingQuery" value="select 1"/> <!-- 핑쿼리 추가 -->
			<property name="poolPingEnabled" value="true"/> <!-- 핑쿼리 추가 -->
			<property name="poolPingConnectionsNotUsedFor" value="7200000"/> <!-- 핑쿼리 추가 -->
			<property name="poolMaximumActiveConnections" value="100"/> <!-- 주어진 시간에 존재할수 있는 활성화된 커넥션 수 -->
			<property name="poolMaximumIdleConnections" value="100"/>	<!-- 주어진 시간에 존재할 수 있는 유휴 커넥션의 수 -->
			<property name="poolTimeToWait" value="20000"/>				<!-- 풀이 로그 상태를 출력하고 비정상적으로 긴경우 커넥션을 다시얻을려고 시도하는 로우 레벨 셋팅 --> 			
		</dataSource>
	</environment>
</environments>

Mybatis 구성을 하다보면 if문 또는 choose when을 통해 쿼리를 동적으로 구성하고 결과를 받는 경우가 종종 있는데, 보통 NULL체크용도로만 사용해서 크게 불편함 없이 사용했지만 최근에 동적으로 비교에 따른 쿼리 작성으로 숫자나 문자열을 비교하면서== equals비교 등이 헷갈리게 되어 많은 에러를 뱉게 되었다. 이번 기회에 각 케이스별로 실행해보고 결과를 정리해보고자 한다.

문자열 비교

* 어떤 비교를 하던지 null체크를 먼저 합니다.

1. 'param.equals()' --------- 정상 동작

<if test="name != null and 'name.equals(비교할 문자열)'">
	column = #{name}
</if>

<if test='name != null and "name.equals(비교할 문자열)"'>
	column = #{name}
</if>

2. ' 비교대상=="" ' 싱글쿼터로 감싸고 더블쿼터로 비교 --------- 정상 동작

<if test='name != null and name=="비교할 문자열"'>
	column = #{name}
</if>

3. " 비교대상=='' " 더블쿼터로 감싸고 싱글쿼터로 비교 --------- 정상 동작

<if test="name != null and name=='비교할 문자열'">
	column = #{name}
</if>

4. Null이 아니거나 공백이 아닌지 체크 (" =='' ",  ' =="" ' 둘다 가능하다)

<if test="name != null and name! = ''">
	column = #{name}
</if>

예전 기억으로는 문자열 비교도 정상비교가 안되는 케이스가 존재했던것으로 기억하는데 한글 비교시에도 문자열은 어느방식이든 문제가 없는것을 확인하였다.

문자(char) 비교

문자 단어의 길이가 한개로 지정한 경우입니다.

Map<Object, Object> params = new HashMap<Object, Object>();

params.put("type", "1");

1. ' 비교대상.equals("") ' --------- 정상 동작

<if test= 'type != null and type.equals("1")'> <!-- 성공 -->
	d_type = #{type}
</if>

2. " 비교대상.equals('') " --------- 문법에러는 없지만 비교 안됨

<if test= "type != null and type.equals('1')"> <!-- 비교 안됨 -->
	d_type = #{type}
</if>

3. " 비교대상=='' " --------- 문법에러는 없지만 비교 안됨

<if test= "type != null and type == '1'"> <!-- 비교 안됨 -->
	d_type = #{type}
</if>

4. ' 비교대상=="" ' --------- 정상 동작

<if test= 'type != null and type == "1"'> <!-- 성공 -->
	d_type = #{type}
</if>

5. Null이 아니거나 공백이 아닌지 체크 (" =='' ", ' =="" ' 둘다 가능하다)

<if test= "type != null and type != ''">
	d_type = #{type}
</if>

주의 사항으로 자바단에서 데이터 셋을 하는부분에서 한개의 워드를 싱글쿼드('')로 감싸게 되면 위의 기능이 정상적으로 동작하지 않는다. ex)params.put("type", '1');

숫자 비교

<if test= "idx != null and idx == 2">
	d_idx = #{idx}
</if>

DAO부분에서Mybatis사용시 발생하는 에러로 sqlsession.selectList("NameSpace.select", param)형태의 return List로 받아야 할 값을 Map이나 String, Integer와 같은 형이 맞지 않는 형태로 return할때 발생하는 에러이다.

resultType="hashMap"

mapper의 리턴타입을 hashMap으로 변경한다. 

<mapper namespace="NameSpace">
	<select id="select" parameterType="hashMap" resultType="hashMap">
		...
	</select>
</mapper>

resultMap="resultMap의 ID"

또는 VO형태로 받아야 하는 경우 상단에 resultMap을 정의하고 return 처리한다.

<mapper namespace="NameSpace">
  <resultMap type="com.psw.web.resultVO" id="resultVo">
    <result property="id"             column="id"/>
    <result property="timestamp"      column="timestamp"/>
    <result property="account"        column="account"/>
    <result property="result_date"    column="result_date"/>
    <result property="filename"       column="filename"/>
  </resultMap>

  <select id="selectTable" parameterType="java.util.Map" resultMap="resultVo">
      ...
  </select>
</mapper>

* mysql 기준으로 작성하였습니다. 타 DB는 조금씩 사용법이 다를 수 있습니다.

where절 조건문

<where>
    <if test="param1 != null and param1 !=''">
    	column1 = #{param1}
    </if>
    <if test="param2 != null and param2 !=''">
    	and column2 = #{param2}
    </if>
    <if test="param3 != null and param3 !=''">
    	and column3 = #{param3}
    </if>
</where>

where절에서 if문이 무조건 들어가지 않는 경우 1=1과 같은 편법으로 대처하는 경우가 보이는데

<where></where>을 사용하시면 조건 안에서 조건문이 있는 경우에만 검색해줍니다.

맨 위 param1의 값이 없고 param2의 값이 있는 경우 앞에 있는 and를 자연스럽게 지우고 쿼리를 작성하게되어

쿼리 문법에 문제가 되지 않도록 해줍니다.

즉, 아래와 같이 변경해줍니다.

where
    column2 = 'hello'
    and column3 = 100

자연스럽게 column2 앞에 텍스트로 박아놓은 AND문이 사라진걸 볼 수 있습니다.

IN절 검색하기

where column1 IN
  <if test="param1 != null and param1.size > 0">
    <foreach collection="param1" item="item"  open="(" close=")" separator=",">
    	#{item}
    </foreach>
  </if>

IN절의 검색은 해당 컬럼에 포함된 모든 데이터를 검색할때 보통 사용하는데,

(column1='1' or column2='2'...길게 나열하지 않기 위해)

$문법의 경우에는 배열을 넘겨주면 아주 쉽게 해결되지만, 취약점에 노출될 수 있으므로 #{}처리하는 방법으로

LIST형태로 param1값을 넘겨주고 해당값의 널체크와 사이즈체크를 한 후 ( ) 구분자, 처리를 통해

자연스럽게 IN (1, 2, 3)의 형태를 만들어 줄 수 있습니다.

ex 자바단)

String codeList = request.getParameter("codeList"); //파라미터값을 받고 //codeList = "1,2,3,4"
if(codeList != null && !codeList.equals("")){ //널 체크후
    String[] arr = codeList.split(",");  // ,으로 split처리
    param.put("codeList", Arrays.asList(arr)); //LIST에 파싱하여 파라미터값을 넘긴다.
    int res = service.func(param); //서비스 함수에 사용한다.
}

1,2,3 와 같은 형태로 넘어오는 파라미터 자체를 ,로 스플릿하고 LIST에 파싱하는 예제입니다.

like절 검색하기

$ 표기법

where
	column like '% ${검색값} %'

# 표기법 - mysql, maria db

where
	column like concat('%', #{검색값} ,'%')

$방식은 sql Injection 취약점에 노출되므로 #방식을 추천합니다.