# Database

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

{% stepper %}
{% step %}

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

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

* 단일연결 (예시)

```yml
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
```

* 다중연결 (예시)

```yml
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 환경에서 분산 트렌젝션의 추적 |
| {% endstep %}  |                      |

{% step %}

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

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

```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 정의가 포함됩니다.)
{% endstep %}

{% step %}

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

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

```xml
<?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>
```

{% endstep %}

{% step %}

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

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

* MybatisEncryptInterceptor.java

```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

```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();
  }
}
```

{% endstep %}
{% endstepper %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://tech.x2bee.com/dev-guide/pjt-prepare/framework/database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.