by: zepinosPosted on:

[Tistory] [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 mybatis parameter console output기존의 log4j를 걷어내고 spring boot + logback을 사용하게 되었는데 logback 설정으로 myBatis 쿼리를 콘솔에 찍는 기능이 있었다. 그러나 log4j의 sqlonly처럼 파라미터가 매 velog.io 개발환경 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 Introduction Translations Users can read about MyBatis-Spring-Boot-Starter in the following translations: What is MyBatis-Spring-Boot-Starter? The MyBatis-Spring-Boot-Starter help you build quickly MyBatis applications on top of the Spring Boot. By using t mybatis.org 1. DB 설치 https://hermeslog.tistory.com/541 [SpringBoot] Docker에 PostgreSQL 설치 / 연결하기 IDE : IntelliJJDK : OpenJDK 11Framework : Spring Boot 2.5.2Database : PostgreSQL 최신버전 ( 라이센스도 소스공개의무도 없음 )첨부파일이 MySqlRunner 로 되어있는데.. MySQL 접속테스트 중 소스만 바뀐거라.. ㅎㅎDocker hermeslog.tistory.com 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 파일에 설정하는 방법은 찾지 못했습니다. 위와 같이 Plugin 처리하지 않아도 됩니다. @Component 를 추가하면 됩니다. ( 아래 소스 참조 ) mybatis-config.xml ( 참조 : https://mybatis.org/mybatis-3/ko/configuration.html )












​ 첨부된 예제에는 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 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 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 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 mybatis parameter console output기존의 log4j를 걷어내고 spring boot + logback을 사용하게 되었는데 logback 설정으로 myBatis 쿼리를 콘솔에 찍는 기능이 있었다. 그러나 log4j의 sqlonly처럼 파라미터가 매 velog.io

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다