개발환경 데이터베이스

X2BEE 개발 환경에서 데이터 베이스 연결과 다중 데이터베이스 연결에 대해 설명합니다.

1

application.yml 파일 내 datasource 관련 설정 추가

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

예시: RO/RW 별로 데이터 소스 작성

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

{databasename}rodb / {databasename}rwdb 추가 작성하여 다중연결 가능합니다.

2

{dbname}ro / {dbname}rw 데이터베이스 연결

아래는 application.yml 예시(일부 생략된 형태)입니다.

application.yml (예시)

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

3

데이터베이스 관련 설정 정보

프로퍼티와 설명:

• port: tomcat 포트

• url: 데이터베이스 접속 URL

• username: 데이터베이스 사용자 아이디

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

• driveClassName: 데이터베이스 드라이버 클래스 명

• hikari: 기타 HikariCP 관련 설정

• session: 스프링 session 설정

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

4

DatabaseConfig.java 파일 작성 예시

아래는 DisplayRodbDatabaseConfig.java의 예시입니다.

DisplayRodbDatabaseConfig.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);
    }

    // ...
}

5

mybatis-config.xml 파일 작성

데이터 마스킹, 암호화 관련 Java 파일을 MyBatis 플러그인으로 연결하는 예시입니다.

mybatis-config.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>

6

데이터베이스 데이터 마스킹, 암호화 관련 Java 파일 작성

아래는 MyBatis 결과/업데이트 시 데이터 마스킹 및 암복호화를 처리하는 인터셉터 예시들입니다.

MybatisMaskingInterceptor.java

MybatisMaskingInterceptor.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) {
    }
}

MybatisEncryptInterceptor.java

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