> 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/dev-start/images-and-media/development-environment-database.md).

# Development Environment Database

Explains database connections and multiple database connections in the X2BEE development environment.

{% stepper %}
{% step %}

### Adding datasource-related Settings in the application.yml File

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

Example: writing data sources by RO/RW

application.yml ... spring: datasource: {dbname}rodb {dbname}rwdb ...

You can enable multiple connections by additionally writing {databasename}rodb / {databasename}rwdb.
{% endstep %}

{% step %}

### {dbname}ro / {dbname}rw Database Connection

Below is an example application.yml (partially omitted).

{% code title="application.yml (example)" %}

```yaml
spring:
  config:
    activate:
      on-profile: local
  mvc:
    log-request-details: true
  zipkin:
    enabled: false
  devtools:
    livereload:
      port: 3${server.port}
    restart:
      exclude: mapper/**
  datasource:
    displayrodb:
      url: jdbc:log4jdbc:postgresql://ec2-43-201-119-5.ap-northeast-2.compute.amazonaws.com:5432/x2bee_main?currentSchema=x2bee_main
      driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      username: 
      password:  #ENC(yGUobhgkk3gx5KFEq2XEOv8TMMY6Nm8N58S8VJG/qnnYZEMdMy3GClbWHxJKJSxu)
      hikari:
        maximum-pool-size: 5
        minimum-idle: 3
        connection-timeout: 30000
        validation-timeout: 5000
        max-lifetime: 1800000
        idle-timeout: 300000
        pool-name: HikariPool-apibo-displayrodb

    displayrwdb:
      url: jdbc:log4jdbc:postgresql://ec2-43-200-110-112.ap-northeast-2.compute.amazonaws.com:5432/x2bee_main?currentSchema=x2bee_main
      driver-class-name: net.sf.log4jdbc.sql.jdbcapi.DriverSpy
      username: x2bee_main
      password: x2beemain@11 #ENC(yGUobhgkk3gx5KFEq2XEOv8TMMY6Nm8N58S8VJG/qnnYZEMdMy3GClbWHxJKJSxu)
      hikari:
        maximum-pool-size: 5
        minimum-idle: 3
        connection-timeout: 30000
        validation-timeout: 5000
        max-lifetime: 1800000
        idle-timeout: 300000
        pool-name: HikariPool-apibo-displayrwdb
```

{% endcode %}
{% endstep %}

{% step %}

### Database-related Configuration Information

Properties and descriptions:

* port: Tomcat port
* url: Database connection URL
* username: Database username
* password: Database user password
* driveClassName: Database driver class name
* hikari: Other HikariCP-related settings
* session: Spring session configuration
* zipkin: Distributed transaction tracing in an MSA environment
  {% endstep %}

{% step %}

### Example of Writing the DatabaseConfig.java File

Below is an example of DisplayRodbDatabaseConfig.java.

{% code title="DisplayRodbDatabaseConfig.java" %}

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

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.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@MapperScan(value="com.x2bee.api.bo.app.repository.displayrodb", sqlSessionFactoryRef="displayRodbSqlSessionFactory")
public class DisplayRodbDatabaseConfig {

    @Bean(name = "displayRodbDataSource")
    @ConfigurationProperties(prefix = "spring.displayrodb.datasource")
    public DataSource displayRodbDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "displayRodbSqlSessionFactory")
    public SqlSessionFactory displayRodbSqlSessionFactory(@Qualifier("displayRodbDataSource") DataSource displayRodbDataSource,
                                                         ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(displayRodbDataSource);
        sqlSessionFactoryBean.setTypeAliasesPackage("com.x2bee.api.bo.app");
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mapper/displayrodb/**/*.xml"));
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:mapper/mybatis-config.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "displayRodbSqlSessionTemplate")
    public SqlSessionTemplate displayRodbSqlSessionTemplate(SqlSessionFactory displayRodbSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(displayRodbSqlSessionFactory);
    }

    // ...
}
```

{% endcode %}
{% endstep %}

{% step %}

### Writing the mybatis-config.xml File

This is an example of connecting data masking and encryption-related Java files as MyBatis plugins.

{% code title="mybatis-config.xml" %}

```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="mapUnderscoreToCamelCase" value="true" />
  </settings>
  <plugins>
    <plugin interceptor="com.x2bee.api.bo.base.masking.MybatisMaskingInterceptor"/>
    <plugin interceptor="com.x2bee.common.base.encrypt.MybatisEncryptInterceptor"/>
  </plugins>
</configuration>
```

{% endcode %}
{% endstep %}

{% step %}

### Writing Database Data Masking and Encryption-related Java Files

Below are examples of interceptors that handle data masking and encryption/decryption during MyBatis results/updates.

MybatisMaskingInterceptor.java

{% code title="MybatisMaskingInterceptor.java" %}

```java
package com.x2bee.api.bo.base.masking;

import java.lang.reflect.Field;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Objects;
import java.util.Properties;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import com.x2bee.common.base.context.ApplicationContextWrapper;
import com.x2bee.common.base.masking.MaskString;

/**
 * Result interceptor
 */
@Intercepts(@Signature(type = ResultSetHandler.class, method = "handleResultSets", args = { Statement.class }))
public class MybatisMaskingInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        BoApiMaskingUtils maskingUtils = (BoApiMaskingUtils)ApplicationContextWrapper.getBean("boApiMaskingUtils");
        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++) {
                Field[] fields = resultList.get(i).getClass().getDeclaredFields();
                for (Field field : fields) {
                    MaskString annotation = field.getAnnotation(MaskString.class);
                    if(annotation!=null && field.getType() == String.class) {
                        field.setAccessible(true);
                        String val = maskingUtils.getValue(field.get(resultList.get(i))+"", annotation.type());
                        try {
                            field.set(resultList.get(i), val);
                        } catch (IllegalAccessException e) {
                            System.out.println(e.getMessage());
                        }
                    }
                }
            }
        } else {
            Field[] fields = result.getClass().getDeclaredFields();
            for (Field field : fields) {
                MaskString annotation = field.getAnnotation(MaskString.class);
                if(annotation!=null && field.getType() == String.class) {
                    field.setAccessible(true);
                    String val = maskingUtils.getValue(field.get(result)+"", annotation.type());
                    try {
                        field.set(result, val);
                    } catch (IllegalAccessException e) {
                        System.out.println(e.getMessage());
                    }
                }
            }
        }
        return result;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }
}
```

{% endcode %}

MybatisEncryptInterceptor.java

{% code title="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.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 lombok.extern.slf4j.Slf4j;

/**
 * @author choiyh44
 * @version 1.0
 * @since 2021. 12. 6.
 */
@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) {
                    field.setAccessible(true);
                    try {
                        String val = EncryptUtils.getEncryptValue(field.get(param)+"", annotation.type());
                        log.info("EncryptValue: {}: {}", val.length(), val);
                        field.set(param, val);
                    } 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++) {
                Field[] fields = resultList.get(i).getClass().getDeclaredFields();
                for (Field field : fields) {
                    Encrypt annotation = field.getAnnotation(Encrypt.class);
                    if(annotation!=null && field.getType() == String.class) {
                        field.setAccessible(true);
                        try {
                            String val = EncryptUtils.getDecryptValue(field.get(resultList.get(i))+"", annotation.type());
                            field.set(resultList.get(i), val);
                        } catch (Exception e) {
                            System.out.println(e.getMessage());
                        }
                    }
                }
            }
        } else {
            Field[] fields = result.getClass().getDeclaredFields();
            for (Field field : fields) {
                Encrypt annotation = field.getAnnotation(Encrypt.class);
                if(annotation!=null && field.getType() == String.class) {
                    field.setAccessible(true);
                    try {
                        String val = EncryptUtils.getDecryptValue(field.get(result)+"", annotation.type());
                        field.set(result, val);
                    } catch (Exception e) {
                        System.out.println(e.getMessage());
                    }
                }
            }
        }
        return result;
    }
}
```

{% endcode %}
{% endstep %}
{% endstepper %}

##