> For the complete documentation index, see [llms.txt](https://tech.x2bee.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://tech.x2bee.com/dev-guide/developer-guide-en/pjt-prepare/framework-guide/database.md).

# Database

The X2BEE Framework uses MyBatis by default in Spring Boot for database control, and additionally uses JPA and QueryDSL. This document explains how to configure MyBatis, JPA, and QueryDSL for database usage.

{% stepper %}
{% step %}

### Database Connection, Multiple Database Connections — Configuration (application.yml)

Specify the server port number and database-related settings in the application.yml file as shown below.

* Single connection (example)

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

* Multiple connections (example)

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

Property description:

| Property Name  | Description                                           |
| -------------- | ----------------------------------------------------- |
| port           | Tomcat port                                           |
| url            | Database connection URL                               |
| username       | Database username                                     |
| password       | Database user password                                |
| driveClassName | Database driver class name                            |
| hikari         | Other                                                 |
| session        | Spring session configuration                          |
| zipkin         | Distributed transaction tracing in an MSA environment |
| {% endstep %}  |                                                       |

{% step %}

### Writing the DatabaseConfig.java File (e.g., DisplayReadWriteDatabaseConfig.java)

Below is an example configuration for multiple data sources (read-only / read-write) and a routing data source.

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

(The example above may omit some parts (...). The actual file includes additional Bean definitions such as SqlSessionFactory, EntityManagerFactory, TransactionManager, and QueryDSL configuration.)
{% endstep %}

{% step %}

### MyBatis Configuration File (mybatis-config.xml)

* Example of connecting encryption-related Java files and configuring the XSS prevention 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 %}

### Database Encryption and XssSanitizer Processing Java Files

(\* Data masking was previously handled by the MyBatis Interceptor, but has been changed to a MessageConverter response-value return processing module.) (Reference: 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 %}