Database

X2BEE Framework는 데이터베이스 제어를 위해 Spring Boot에서 기본적으로 MyBatis를 이용하며 부가적으로 Jpa 및 QueryDsl을 이용합니다. 데이터베이스 사용을 위한 MyBatis 및 Jpa, QueryDsl 설정 방법을 설명합니다.

데이터베이스 연결, 다중 데이터베이스 연결 — 설정 (application.yml)

아래와 같이 application.yml 파일에서 서버 port번호와 데이터베이스 관련 설정을 명시합니다.

단일연결 (예시)

server:
  port: 8080
spring:
  config:
    activate:
      on-profile: local
  devtools:
    livereload:
      port: 3${server.port}
  datasource:
    url: jdbc:log4jdbc:postgresql://xxxxx.xxxxxx.xxx:55005/{DatabaseName}?currentSchema={SchemaName}
    driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
    username: {userName}
    password: ******************
    hikari:
      maximum-pool-size: 3
      minimum-idle: 3
      connection-timeout: 30000
      validation-timeout: 5000
      max-lifetime: 1800000
      idle-timeout: 300000
  session:
    store-type: none
  zipkin:
    enabled: false

다중연결 (예시)

server:
  port: 8097
  servlet:
    context-path: /api/bo
spring:
  config:
    activate:
      on-profile: local
  zipkin:
    enabled: false
  devtools:
    livereload:
      port: 3${server.port}
  displayrodb:
    datasource:
      url: jdbc:log4jdbc:postgresql://xxxxx.xxxxxx.xxx:55005/{DatabaseName}?currentSchema={SchemaName}
      driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      username: {userName}
      password: ******************
      hikari:
        maximum-pool-size: 5
        minimum-idle: 3
        connection-timeout: 30000
        validation-timeout: 5000
        max-lifetime: 1800000
        idle-timeout: 300000
  displayrwdb:
    datasource:
      url: jdbc:log4jdbc:postgresql://xxxxx.xxxxxx.xxx:55005/{DatabaseName}?currentSchema={SchemaName}
      driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      username: {userName}
      password: ******************
      hikari:
        maximum-pool-size: 5
        minimum-idle: 3
        connection-timeout: 30000
        validation-timeout: 5000
        max-lifetime: 1800000
        idle-timeout: 300000

프로퍼티 설명:

프로퍼티명

설명

port

tomcat 포트

url

데이터베이스 접속 URL

username

데이터베이스 사용자 아이디

password

데이터베이스 사용자 비밀번호

driveClassName

데이터베이스 드라이버 클래스 명

hikari

기타

session

스프링 session 설정

zipkin

MSA 환경에서 분산 트렌젝션의 추적

DatabaseConfig.java 파일 작성 (예: DisplayReadWriteDatabaseConfig.java)

아래는 다중 데이터소스(읽기 전용/읽기-쓰기) 및 라우팅 데이터소스 구성 예제입니다.

package com.x2bee.api.bo.base.config;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.condition.ConditionalOnBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Profile;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.x2bee.common.base.mybatis.RefreshableSqlSessionFactoryBean;
import com.x2bee.common.base.routingdatasource.RoutingDataSourceRouter;
import com.x2bee.common.base.routingdatasource.RoutingDatabase;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import lombok.extern.slf4j.Slf4j;

/**
 * display Read, Write DB 설정
 */
@Configuration
@MapperScan(value = {"com.x2bee.api.bo.app.repository.displayrodb", "com.x2bee.api.bo.app.repository.displayrwdb"}, sqlSessionFactoryRef="displayRwdbSqlSessionFactory")
@EnableJpaRepositories(
  basePackages = {"com.x2bee.api.bo.app.repository.displayrodb", "com.x2bee.api.bo.app.repository.displayrwdb"},
  entityManagerFactoryRef = "displayRwdbEntityManagerFactory",
  transactionManagerRef = "displayRwdbTxManager"
)
/* -----------------DataSource 설정------------------------------------- */

@Bean(name = "displayRodbDataSourceProperties")
@ConfigurationProperties("spring.datasource.displayrodb")
public DataSourceProperties displayRodbDataSourceProperties() {
  return new DataSourceProperties();
}

@Bean(name = "displayRwdbDataSourceProperties")
@ConfigurationProperties("spring.datasource.displayrwdb")
public DataSourceProperties displayRwdbDataSourceProperties() {
  return new DataSourceProperties();
}

@Bean(name = "displayRodbDataSource")
@ConfigurationProperties("spring.datasource.displayrodb.hikari")
public HikariDataSource displayRodbDataSource(@Qualifier("displayRodbDataSourceProperties") DataSourceProperties displayRodbDataSourceProperties) {
  HikariDataSource rodbDataSource = displayRodbDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
  rodbDataSource.setReadOnly(true);
  return rodbDataSource;
}

@Bean(name = "displayRwdbDataSource")
@ConfigurationProperties("spring.datasource.displayrwdb.hikari")
public HikariDataSource displayRwdbDataSource(@Qualifier("displayRwdbDataSourceProperties") DataSourceProperties displayRwdbDataSourceProperties) {
  return displayRwdbDataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
}

@Primary
@Bean(name = "displayRouteDataSource")
@ConditionalOnBean(name = {"displayRodbDataSource", "displayRwdbDataSource"})
public DataSource displayRouteDataSource(@Qualifier("displayRodbDataSource") DataSource displayRodbDataSource, @Qualifier("displayRwdbDataSource") DataSource displayRwdbDataSource) {
  Map<Object, Object> targetDataSources = new HashMap<>();
  targetDataSources.put(RoutingDatabase.READONLY, displayRodbDataSource);
  targetDataSources.put(RoutingDatabase.READWRITE, displayRwdbDataSource);
  RoutingDataSourceRouter clientRoutingDatasource = new RoutingDataSourceRouter();
  clientRoutingDatasource.setTargetDataSources(targetDataSources);
  clientRoutingDatasource.setDefaultTargetDataSource(displayRwdbDataSource);
  return clientRoutingDatasource;
}

(위 예제는 일부 생략된 부분(...)이 있을 수 있습니다. 실제 파일에는 SqlSessionFactory, EntityManagerFactory, TransactionManager, Querydsl 설정 등 추가 Bean 정의가 포함됩니다.)

MyBatis 설정 파일 (mybatis-config.xml)

암호화 관련 java 파일 연결, XSS 방지처리 interceptor 설정 예:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <settings>
    <setting name="cacheEnabled" value="false" />
    <setting name="mapUnderscoreToCamelCase" value="true" />
    <setting name="defaultStatementTimeout" value="30" />
  </settings>

  <plugins>
    <plugin interceptor="com.x2bee.common.base.encrypt.MybatisEncryptInterceptor"/>
    <plugin interceptor="com.x2bee.common.base.xss.XssInterceptor"/>
  </plugins>
</configuration>

데이터베이스 암호화 및 XssSanitizer 처리 Java 파일

(※ 데이터 마스킹 처리는 기존에 Mybatis Interceptor에서 처리하였으나 MessageConverter 응답값 반환 처리 모듈로 변경되었습니다.) (참고: Masking처리 — https://x2bee-tech.atlassian.net/wiki/spaces/TG/pages/91127937/Masking)

MybatisEncryptInterceptor.java

package com.x2bee.common.base.encrypt;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Objects;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.mapping.MappedStatement;
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 com.x2bee.common.base.util.CryptoUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * Mybatis 암호화, 복호화 Interceptor
 * Encrypt Custom 어노테이션가 있는 필드에만 작동
 */
@Slf4j
@Intercepts({
  @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),
  @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class })
})
public class MybatisEncryptInterceptor implements Interceptor {
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    String method = invocation.getMethod().getName();
    if ("update".equals(method)) {
      return processUpdate(invocation);
    } else if ("handleResultSets".equals(method)) {
      return processQuery(invocation);
    } else {
      return invocation.proceed();
    }
  }

  private Object processUpdate(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
    Object[] args = invocation.getArgs();
    Object param = args[1];
    if (param != null) {
      Field[] fields = param.getClass().getDeclaredFields();
      for (Field field : fields) {
        Encrypt annotation = field.getAnnotation(Encrypt.class);
        if(annotation!=null && field.getType() == String.class) {
          try {
            String data = BeanUtils.getProperty(param, field.getName());
            String value = CryptoUtil.getInstance().encodeAes(data);
            BeanUtils.setProperty(param, field.getName(), value);
          } catch (Exception e) {
            log.warn(e.getMessage(), e);
          }
        }
      }
    }
    return invocation.proceed();
  }

  private Object processQuery(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
    Object result = invocation.proceed();
    if (Objects.isNull(result)){
      return null;
    }
    if (result instanceof ArrayList) {
      ArrayList<?> resultList = (ArrayList<?>) result;
      for (int i = 0; i < resultList.size(); i++) {
        if (resultList.get(i) == null) {
          continue; // null 오류방어
        }
        Field[] fields = resultList.get(i).getClass().getDeclaredFields();
        for (Field field : fields) {
          Encrypt annotation = field.getAnnotation(Encrypt.class);
          if(annotation!=null && field.getType() == String.class) {
            try {
              String data = BeanUtils.getProperty(resultList.get(i), field.getName());
              String val = CryptoUtil.getInstance().decodeAes(data);
              BeanUtils.setProperty(resultList.get(i), field.getName(), val);
            } catch (Exception e) {
              log.warn("", e);
            }
          }
        }
      }
    } else {
      Field[] fields = result.getClass().getDeclaredFields();
      for (Field field : fields) {
        Encrypt annotation = field.getAnnotation(Encrypt.class);
        if(annotation!=null && field.getType() == String.class) {
          try {
            String val = CryptoUtil.getInstance().decodeAes(BeanUtils.getProperty(result, field.getName())+"");
            BeanUtils.setProperty(result, field.getName(), val);
          }catch (Exception e) {
            log.warn("", e);
          }
        }
      }
    }
    return result;
  }
}

XssInterceptor.java

package com.x2bee.common.base.xss;

import jakarta.servlet.http.HttpServletRequest;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.mapping.MappedStatement;
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.springframework.util.Assert;
import org.springframework.util.ObjectUtils;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * Xss Filtering Interceptor
 * @XssSanitizer Custom 어노테이션가 있는 필드에만 작동
 */
@Slf4j
@Intercepts({
  @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
  @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class})
})
public class XssInterceptor implements Interceptor {
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    String method = invocation.getMethod().getName();
    if ("update".equals(method)) {
      // request
      return processUpdate(invocation);
    } else {
      return invocation.proceed();
    }
  }

  private Object processUpdate(Invocation invocation) throws InvocationTargetException, IllegalAccessException {
    Object[] args = invocation.getArgs();
    Object param = args[1];
    if (!ObjectUtils.isEmpty(param)) {
      Field[] fields = param.getClass().getDeclaredFields();
      for (Field field : fields) {
        XssSanitizer annotation = field.getAnnotation(XssSanitizer.class);
        if (annotation!=null && field.getType() == String.class) {
          try {
            RequestAttributes requestAttributes = RequestContextHolder.getRequestAttributes();
            Assert.notNull(requestAttributes, "Could not find current request via RequestAttributes");
            ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            Assert.notNull(attributes, "Could not find current request via HttpServletRequest");
            HttpServletRequest request = attributes.getRequest();
            String requestUri = request.getRequestURI();
            String data = BeanUtils.getProperty(param, field.getName());
            String value = XssProtectUtils.getInstance().htmlRequestSanitize(data, requestUri);
            BeanUtils.setProperty(param, field.getName(), value);
          } catch (Exception e) {
            log.warn(e.getMessage(), e);
          }
        }
      }
    }
    return invocation.proceed();
  }
}

이전API Server 구조도 다음Data Validation

마지막 업데이트 2개월 전

hashtag데이터베이스 연결, 다중 데이터베이스 연결 — 설정 (application.yml)

hashtagDatabaseConfig.java 파일 작성 (예: DisplayReadWriteDatabaseConfig.java)

hashtagMyBatis 설정 파일 (mybatis-config.xml)

hashtag데이터베이스 암호화 및 XssSanitizer 처리 Java 파일

데이터베이스 연결, 다중 데이터베이스 연결 — 설정 (application.yml)

DatabaseConfig.java 파일 작성 (예: DisplayReadWriteDatabaseConfig.java)

MyBatis 설정 파일 (mybatis-config.xml)

데이터베이스 암호화 및 XssSanitizer 처리 Java 파일