[Spring Boot] Mybatis 쿼리 Interceptor 처리 테스트

MyBatisInterceptor.zip

0.04MB

MyBatis 개발환경에서 쿼리를 추출할 수 있는 방법을 찾아봤습니다.

소스를 오픈해주신 분이 계셔서, 쉽게 테스트 할 수 있었습니다.

 

소스 출처

https://velog.io/@yuna706/%EB%A7%88%EC%9D%B4%EB%B0%94%ED%8B%B0%EC%8A%A4-%ED%8C%8C%EB%9D%BC%EB%AF%B8%ED%84%B0-%EC%BD%98%EC%86%94-%EC%B6%9C%EB%A0%A5-%EA%B8%B0%EB%8A%A5-%EA%B5%AC%ED%98%84-spring-boot-interceptor

마이바티스 파라미터 콘솔 출력 기능 구현 - spring boot interceptor

 

---

개발환경

JDK : zulu-11

Spring Boot 2.7.18

STS 4.22.1.RELEASE

Maven

Mybatis

Postgres

---

MyBatis-Spring-Boot-Starter에는 다음 버전이 필요합니다.

MyBatis-Spring-Boot-Starter	MyBatis-Spring	스프링 부트	자바
3.0	3.0	3.0 - 3.1	17 이상
2.3	2.1	2.5 - 2.7	8 이상

https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

mybatis-spring-boot-autoconfigure – Introduction

---

1. DB 설치

https://hermeslog.tistory.com/541

[SpringBoot] Docker에 PostgreSQL 설치 / 연결하기

2. 개발환경 설치 / Source

- 첨부 참조

3. 테이블 생성

C:\Temp>docker exec -it postgres_boot bash

root@3deb323864bb:/# psql --username hermeswing --dbname springboot
psql (14.1 (Debian 14.1-1.pgdg110+1))
도움말을 보려면 "help"를 입력하십시오.

springboot=# \list
                                   데이터베이스 목록
    이름    |   소유주   | 인코딩 |  Collate   |   Ctype    |        액세스 권한
------------+------------+--------+------------+------------+---------------------------
 postgres   | hermeswing | UTF8   | ko_KR.utf8 | ko_KR.utf8 |
 springboot | hermeswing | UTF8   | ko_KR.utf8 | ko_KR.utf8 |
 template0  | hermeswing | UTF8   | ko_KR.utf8 | ko_KR.utf8 | =c/hermeswing            +
            |            |        |            |            | hermeswing=CTc/hermeswing
 template1  | hermeswing | UTF8   | ko_KR.utf8 | ko_KR.utf8 | =c/hermeswing            +
            |            |        |            |            | hermeswing=CTc/hermeswing
(4개 행)

springboot=# \dt
관련 릴레이션 찾을 수 없음.

springboot=# CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    position VARCHAR(100)
);

springboot=# \dt
         릴레이션(relation) 목록
 스키마 |   이름    |  종류  |   소유주
--------+-----------+--------+------------
 public | employees | 테이블 | hermeswing
(1개 행)

springboot=# INSERT INTO employees (name, age, position) VALUES
    ('홍길동', 35, 'Manager'),
    ('강감찬', 28, 'Engineer'),
    ('이순신', 40, 'Analyst'),
    ('허균', 33, 'Developer'),
    ('허난설헌', 45, 'Director'),
    ('신사임당', 30, 'Designer'),
    ('임꺽정', 38, 'Project Manager'),
    ('권율', 32, 'Software Engineer'),
    ('유관순', 36, 'Consultant'),
    ('안중근', 31, 'Administrator');
INSERT 0 10

springboot=# \dt
         릴레이션(relation) 목록
 스키마 |   이름    |  종류  |   소유주
--------+-----------+--------+------------
 public | employees | 테이블 | hermeswing
(1개 행)

springboot=# select *
springboot-#   from employees;
 employee_id |        name        | age |     position
-------------+--------------------+-----+-------------------
           1 | John Doe           |  35 | Manager
           2 | Jane Smith         |  28 | Engineer
           3 | Michael Johnson    |  40 | Analyst
           4 | Emily Davis        |  33 | Developer
           5 | Christopher Wilson |  45 | Director
           6 | Jessica Brown      |  30 | Designer
           7 | Daniel Taylor      |  38 | Project Manager
           8 | Sarah Martinez     |  32 | Software Engineer
           9 | Kevin Anderson     |  36 | Consultant
          10 | Amanda Thomas      |  31 | Administrator
(10개 행)

springboot=#

4. 결과

16:23:52.915 [Thread-0] DEBUG org.springframework.boot.devtools.restart.classloader.RestartClassLoader - Created RestartClassLoader org.springframework.boot.devtools.restart.classloader.RestartClassLoader@557ab8ad

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::               (v2.7.18)

2024-05-23 16:23:53.199  INFO 23984 --- [  restartedMain] octopus.MyBatisInterceptorApplication    : Starting MyBatisInterceptorApplication using Java 17.0.11 on DESKTOP-O29LTIL with PID 23984 (C:\DEV\workspace\MyBatisInterceptor\target\classes started by cspi.jypark in C:\DEV\workspace\MyBatisInterceptor)
2024-05-23 16:23:53.200  INFO 23984 --- [  restartedMain] octopus.MyBatisInterceptorApplication    : No active profile set, falling back to 1 default profile: "default"
2024-05-23 16:23:53.234  INFO 23984 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2024-05-23 16:23:53.234  INFO 23984 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2024-05-23 16:23:53.940  INFO 23984 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 9090 (http)
2024-05-23 16:23:53.949  INFO 23984 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2024-05-23 16:23:53.949  INFO 23984 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.83]
2024-05-23 16:23:54.002  INFO 23984 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2024-05-23 16:23:54.002  INFO 23984 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 767 ms
2024-05-23 16:23:54.210  INFO 23984 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-05-23 16:23:54.340  INFO 23984 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2024-05-23 16:23:54.373  INFO 23984 --- [  restartedMain] o.interceptor.MybatisLogInterceptor      : file [C:\DEV\workspace\MyBatisInterceptor\target\classes\mappers\postgres\SampleMapper.xml]
2024-05-23 16:23:54.373  INFO 23984 --- [  restartedMain] o.interceptor.MybatisLogInterceptor      : octopus.dao.SampleMapper.selectList
2024-05-23 16:23:54.373  INFO 23984 --- [  restartedMain] o.interceptor.MybatisLogInterceptor      : --------------------------------------sql: 
 SELECT employee_id
         , name
         , age
         , position
      FROM employees
2024-05-23 16:23:54.415  INFO 23984 --- [  restartedMain] octopus.controller.SampleController      : result :: [SampleDTO(employeeId=1, name=John Doe, age=35, position=Manager), SampleDTO(employeeId=2, name=Jane Smith, age=28, position=Engineer), SampleDTO(employeeId=3, name=Michael Johnson, age=40, position=Analyst), SampleDTO(employeeId=4, name=Emily Davis, age=33, position=Developer), SampleDTO(employeeId=5, name=Christopher Wilson, age=45, position=Director), SampleDTO(employeeId=6, name=Jessica Brown, age=30, position=Designer), SampleDTO(employeeId=7, name=Daniel Taylor, age=38, position=Project Manager), SampleDTO(employeeId=8, name=Sarah Martinez, age=32, position=Software Engineer), SampleDTO(employeeId=9, name=Kevin Anderson, age=36, position=Consultant), SampleDTO(employeeId=10, name=Amanda Thomas, age=31, position=Administrator)]
2024-05-23 16:23:54.684  INFO 23984 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2024-05-23 16:23:54.715  INFO 23984 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 9090 (http) with context path ''
2024-05-23 16:23:54.724  INFO 23984 --- [  restartedMain] octopus.MyBatisInterceptorApplication    : Started MyBatisInterceptorApplication in 1.801 seconds (JVM running for 2.29)

5. 주의점

interceptor를 설정하기 위해서는 mybatis-config.xml 을 Loading 해야 합니다.

Interceptor가 plugins 에 셋팅되어야 하기때문인데요.. application.yml 파일에 설정하는 방법은 찾지 못했습니다.

<!--
    <plugins>
        <plugin interceptor="octopus.interceptor.MybatisLogInterceptor"/>
    </plugins>
-->

위와 같이 Plugin 처리하지 않아도 됩니다.

@Component 를 추가하면 됩니다. ( 아래 소스 참조 )

---

mybatis-config.xml ( 참조 : https://mybatis.org/mybatis-3/ko/configuration.html )

<configuration>
    <settings>
        <setting name="cacheEnabled" value="false" />
        <setting name="useGeneratedKeys" value="true" />
        <setting name="defaultExecutorType" value="REUSE" />
        <setting name="callSettersOnNulls" value="true" />
        <setting name="jdbcTypeForNull" value="NULL" />
    </settings>
    <typeAliases>
        <typeAlias alias="hashMap" type="java.util.HashMap"/>
    </typeAliases>
    <typeHandlers>
        <typeHandler javaType="java.lang.String" jdbcType="CLOB" handler="org.apache.ibatis.type.ClobTypeHandler" />
    </typeHandlers>
    <plugins>
        <plugin interceptor="octopus.interceptor.MybatisLogInterceptor"/>
    </plugins>
</configuration>​

첨부된 예제에는  MybatisConfig.java 의 Configuration 으로 설정하는 방법입니다.

package octopus.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisConfig {
    @Autowired
    ApplicationContext applicationContext;

    // 데이터소스 빈은 스프링부트에서 자동으로 만들어줌
    private final DataSource dataSource;

    public MyBatisConfig( DataSource dataSource ) {
        this.dataSource = dataSource;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate( SqlSessionFactory sqlSessionFactory ) {
        return new SqlSessionTemplate( sqlSessionFactory );
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource( dataSource );
        sessionFactory.setConfigLocation( applicationContext.getResource( "classpath:mybatis-config.xml" ) );
        sessionFactory.setMapperLocations( applicationContext.getResources( "classpath:mappers/**/*.xml" ) );

        return sessionFactory.getObject();
    }
}

추가적으로 application.yml 로 mybatis-config.xml 파일을 loading 하는 방법은 아래와 같습니다.

##############################################################
# Server port
server:
  port: 9090
  servlet:
    context-path: /
    encoding:
      enabled: true
      charset: UTF-8
      force: true
    session:
      timeout: 18000                              # 30분, Default 기본단위 : 초
      
  tomcat:
    uri-encoding: UTF-8                         # Spring Default : UTF-8
    
spring:
  application:
    name: MyBatisInterceptor
##############################################################
# Spring Database 처리
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/springboot
    username: hermeswing
    password: pass
    poolName: aip
    connection-timeout: 3000
    maximum-pool-size: 5
    registerMbeans: false
##############################################################
# Mybatis 설정
mybatis:
  # resources/mappers/ 에 있는 모든 xml 파일
  config-location: classpath:mybatis-config.xml
  mapper-locations: classpath:mappers/**/*.xml
  type-aliases-package: com.octopus.**.dto.*

---

역시 가장 중요한 소스는 역시 MybatisLogInterceptor 입니다.

아래 소스 출처를 확인 해주세요. 기타 설명도 자세히 나와 있습니다.

소스를 오픈해주셔서 감사드립니다.

import java.lang.reflect.Field;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.function.BiConsumer;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import org.springframework.util.ReflectionUtils;

import com.fasterxml.jackson.core.JsonProcessingException;

import lombok.extern.slf4j.Slf4j;

/**
 * MybatisLogInterceptor.java
 * query console 출력 클래스
 * @author yuna706
 * @since 2023.02.17
 */
@Component
@Slf4j
@Intercepts(value = {
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class } )})
public class MybatisLogInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object paramObj = invocation.getArgs()[1];
        MappedStatement statement = (MappedStatement)invocation.getArgs()[0];

        try {
            BoundSql boundSql = statement.getBoundSql(paramObj);
            String paramSql = getParamBindSQL(boundSql);
            log.info("{}", statement.getResource());
            log.info("{}", statement.getId());
            log.info("--------------------------------------sql: \n {}", paramSql);
            return invocation.proceed();
        }catch (NoSuchFieldException nsf){
            return invocation.proceed();
        }
    }

    // 파라미터 sql 바인딩 처리
    public String getParamBindSQL(BoundSql boundSql) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException, JsonProcessingException {

        Object parameterObject = boundSql.getParameterObject();
        StringBuilder sqlStringBuilder = new StringBuilder(boundSql.getSql());

        // stringBuilder 파라미터 replace 처리
        BiConsumer<StringBuilder, Object> sqlObjectReplace = (sqlSb, value) -> {

            int questionIdx = sqlSb.indexOf("?");

            if(questionIdx == -1) {
                return;
            }

            if(value == null) {
                sqlSb.replace(questionIdx, questionIdx + 1, "null /**P*/");
            } else if(value instanceof String || value instanceof LocalDate || value instanceof LocalDateTime || value instanceof Enum<?>) {
                sqlSb.replace(questionIdx, questionIdx + 1, "'" + (value != null ? value.toString() : "") + "' /**P*/");
            } else {
                sqlSb.replace(questionIdx, questionIdx + 1, value.toString() + " /**P*/");
            }
        };

        if(parameterObject == null) {
            sqlObjectReplace.accept(sqlStringBuilder, null);
        } else {
            if(parameterObject instanceof Integer || parameterObject instanceof Long || parameterObject instanceof Float || parameterObject instanceof Double || parameterObject instanceof String) {
                sqlObjectReplace.accept(sqlStringBuilder, parameterObject);
            } else if(parameterObject instanceof Map) {
                Map paramterObjectMap = (Map)parameterObject;
                List<ParameterMapping> paramMappings = boundSql.getParameterMappings();

                for (ParameterMapping parameterMapping : paramMappings) {
                    String propertyKey = parameterMapping.getProperty();
                    try {
                        Object paramValue = null;
                        if(boundSql.hasAdditionalParameter(propertyKey)) {
                            // 동적 SQL로 인해 __frch_item_0 같은 파라미터가 생성되어 적재됨, additionalParameter로 획득
                            paramValue = boundSql.getAdditionalParameter(propertyKey);
                        } else {
                            paramValue = paramterObjectMap.get(propertyKey);
                        }

                        sqlObjectReplace.accept(sqlStringBuilder, paramValue);
                    } catch (Exception e) {
                        sqlObjectReplace.accept(sqlStringBuilder, "[cannot binding : " + propertyKey+ "]");
                    }

                }
            } else {
                List<ParameterMapping> paramMappings = boundSql.getParameterMappings();
                Class< ? extends Object> paramClass = parameterObject.getClass();

                for (ParameterMapping parameterMapping : paramMappings) {
                    String propertyKey = parameterMapping.getProperty();

                    try {
                        Object paramValue = null;
                        if(boundSql.hasAdditionalParameter(propertyKey)) {
                            // 동적 SQL로 인해 __frch_item_0 같은 파라미터가 생성되어 적재됨, additionalParameter로 획득
                            paramValue = boundSql.getAdditionalParameter(propertyKey);
                        } else {
                            Field field = ReflectionUtils.findField(paramClass, propertyKey);
                            field.setAccessible(true);
                            paramValue = field.get(parameterObject);
                        }

                        sqlObjectReplace.accept(sqlStringBuilder, paramValue);
                    } catch (Exception e) {
                        sqlObjectReplace.accept(sqlStringBuilder, "[cannot binding : " + propertyKey+ "]");
                    }
                }
            }
        }
        return sqlStringBuilder.toString().replaceAll("([\\r\\n\\s]){2,}([\\r\\n])+","\n");
    }
}

---

소스 출처

https://velog.io/@yuna706/%EB%A7%88%EC%9D%B4%EB%B0%94%ED%8B%B0%EC%8A%A4-%ED%8C%8C%EB%9D%BC%EB%AF%B8%ED%84%B0-%EC%BD%98%EC%86%94-%EC%B6%9C%EB%A0%A5-%EA%B8%B0%EB%8A%A5-%EA%B5%AC%ED%98%84-spring-boot-interceptor

마이바티스 파라미터 콘솔 출력 기능 구현 - spring boot interceptor