[Spring Boot] (1) DB 사용을 위한 셋팅 구성

프로젝트를 시작할 때 가장 먼저 하는 일이라고하면 DB를 연결하기 위해 셋팅을 구성하는 일 일것이다.

 

DB를 하나만 사용한다고하면 application.yml에 프로퍼티를 설정하면 많은 것이 자동으로 구성되지만 그렇지 않은 설정들도 있다.

 

DB를 셋팅하면서 겪었던 일들을 트러블슈팅하고자 한다.

 

우선 내가 설정하기 전 조건들은 이러하다.

 

1. 다중 DB를 사용한다. (2개 이상)

 

2. JPA와 MyBatis를 사용한다.

 

3. QueryDSL을 사용한다.

 

 

DB가 1개일 경우, application.yml에 설정하면 자동으로 연결되지만 2개 이상일 경우 별도로 설정을 해주어야한다.

우선 공통으로 JPA와 MyBatis를 사용할 것이기 때문에 관련 설정 부분을 application.yml에서 가져와 셋팅하는 부분을 만들어 주었다.

 

build.gradle

// queryDSL version 정보
buildscript {
	ext {
		queryDslVersion = "5.0.0"
	}
}

plugins {
	id 'org.springframework.boot' version '2.6.5'
	id 'io.spring.dependency-management' version '1.0.11.RELEASE'
	id 'com.ewerk.gradle.plugins.querydsl' version '1.0.10'     // queryDSL plugins 추가
	id 'java'
}

group = 'com.springboot2'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.springframework.boot:spring-boot-starter-validation'

	// DB 관련
	implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.5.8'
	runtimeOnly 'org.mariadb.jdbc:mariadb-java-client:2.7.4'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.0'

	/* queryDSL */
	implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
	implementation "com.querydsl:querydsl-apt:${queryDslVersion}"
	implementation "com.querydsl:querydsl-core:${queryDslVersion}"

	developmentOnly 'org.springframework.boot:spring-boot-devtools'

	compileOnly 'org.projectlombok:lombok'
	annotationProcessor 'org.projectlombok:lombok'

	testCompileOnly 'org.projectlombok:lombok'      // 테스트 시 lombok 의존성 주입
	testCompileOnly 'org.mybatis.spring.boot:mybatis-spring-boot-starter-test'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

tasks.named('test') {
	useJUnitPlatform()
}

/* queryDSL 설정 추가 */
// queryDSL에서 사용할 경로 설정
def querydslDir = "$buildDir/generated/querydsl"

// JPA 사용 여부와 사용할 경로를 설정
querydsl {
	jpa = true
	querydslSourcesDir = querydslDir
}

// build 시 사용할 sourceSet 추가
sourceSets {
	main.java.srcDir querydslDir
}

// queryDSL 컴파일 시 사용할 옵션 설정
compileQuerydsl {
	options.annotationProcessorPath = configurations.querydsl
}

// queryDSL이 compileClassPath를 상속하도록 설정
configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
	querydsl.extendsFrom compileClasspath
}

 

DBConfig.java (공통 Config)

package com.springboot2.legacy.config;

import com.zaxxer.hikari.HikariConfig;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.Map;

@Configuration
public class DBConfig {

  @Autowired
  private HibernateProperties hibernateProperties;

  @Autowired
  private JpaProperties jpaProperties;

  protected void setConfigEntityManagerFactory(LocalContainerEntityManagerFactoryBean factory) {
    JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    Map<String, Object> properties = hibernateProperties.determineHibernateProperties(
      jpaProperties.getProperties(), new HibernateSettings()
    );

    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setJpaPropertyMap(properties);
    factory.afterPropertiesSet();
  }

  protected void setConfigSqlSessionFactory(SqlSessionFactoryBean sessionFactoryBean, DataSource dataSource) throws IOException {
    sessionFactoryBean.setDataSource(dataSource);
    sessionFactoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:/mybatis/config/mybatis-config.xml"));
    sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mybatis/mapper/*.xml"));
  }
}

 

 

 

이 공통 설정을 상속받아 첫 번째 / 두 번째 DB 설정을 구성한다.

 

FirstDBConfig.java (첫 번째 DB)

package com.springboot2.legacy.config;

import com.zaxxer.hikari.HikariDataSource;
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.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
  basePackages = "com.springboot2.legacy.repository.first",
  entityManagerFactoryRef = "firstEntityManagerFactory",
  transactionManagerRef = "firstTransactionManager"
)
@MapperScan(value = "com.springboot2.legacy.mapper.first", sqlSessionFactoryRef = "firstSessionFactory")
public class FirstDBConfig extends DBConfig {

  private final String FIRST_DATA_SOURCE = "firstDataSource";

  private final String FIRST_MANAGER_FACTORY = "firstEntityManagerFactory";

  private final String FIRST_TRANSACTION_MANAGER = "firstTransactionManager";

  private final String FIRST_SESSION_FACTORY = "firstSessionFactory";

  private final String FIRST_SESSION_TEMPLATE = "firstSessionTemplate";

  @ConfigurationProperties(prefix = "spring.first-db.datasource")
  @Bean(name = FIRST_DATA_SOURCE)
  @Primary
  public DataSource dataSource() {
  
    return DataSourceBuilder.create()
      .type(HikariDataSource.class)
      .build();
  }

  @Bean(name = FIRST_MANAGER_FACTORY)
  @Primary
  public EntityManagerFactory entityManagerFactory(@Qualifier(FIRST_DATA_SOURCE) DataSource dataSource) {
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setDataSource(dataSource);
    factory.setPackagesToScan("com.springboot2.legacy.entity.first");
    factory.setPersistenceUnitName("firstEntityManager");
    setConfigEntityManagerFactory(factory);

    return factory.getObject();
  }

  @Bean(name = FIRST_TRANSACTION_MANAGER)
  @Primary
  public PlatformTransactionManager transactionManager(@Qualifier(FIRST_MANAGER_FACTORY) EntityManagerFactory entityManagerFactory) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(entityManagerFactory);

    return transactionManager;
  }

  @Bean(name = FIRST_SESSION_FACTORY)
  @Primary
  public SqlSessionFactory sqlSessionFactory(@Qualifier(FIRST_DATA_SOURCE) DataSource dataSource) throws Exception {
    SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
    setConfigSqlSessionFactory(sessionFactoryBean, dataSource);

    return sessionFactoryBean.getObject();
  }

  @Bean(name = FIRST_SESSION_TEMPLATE)
  @Primary
  public SqlSessionTemplate sqlSessionTemplate(@Qualifier(FIRST_SESSION_FACTORY) SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

 

SecondDBConfig.java (두 번째 DB)

package com.springboot2.legacy.config;

import com.zaxxer.hikari.HikariDataSource;
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.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableJpaRepositories(
  basePackages = "com.springboot2.legacy.repository.second",
  entityManagerFactoryRef = "secondEntityManagerFactory",
  transactionManagerRef = "secondTransactionManager"
)
@MapperScan(value = "com.springboot2.legacy.mapper.second", sqlSessionFactoryRef = "secondSessionFactory")
public class SecondDBConfig extends DBConfig {

  private final String SECOND_DATA_SOURCE = "secondDataSource";

  private final String SECOND_MANAGER_FACTORY = "secondEntityManagerFactory";

  private final String SECOND_TRANSACTION_MANAGER = "secondTransactionManager";

  private final String SECOND_SESSION_FACTORY = "secondSessionFactory";

  private final String SECOND_SESSION_TEMPLATE = "secondSessionTemplate";

  @ConfigurationProperties(prefix = "spring.second-db.datasource")
  @Bean(name = SECOND_DATA_SOURCE)
  public DataSource dataSource() {
  
    return DataSourceBuilder.create()
      .type(HikariDataSource.class)
      .build();
  }

  @Bean(name = SECOND_MANAGER_FACTORY)
  public EntityManagerFactory entityManagerFactory(@Qualifier(SECOND_DATA_SOURCE) DataSource dataSource) {
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setDataSource(dataSource);
    factory.setPackagesToScan("com.springboot2.legacy.entity.second");
    factory.setPersistenceUnitName("secondEntityManager");
    setConfigEntityManagerFactory(factory);

    return factory.getObject();
  }

  @Bean(name = SECOND_TRANSACTION_MANAGER)
  public PlatformTransactionManager transactionManager(@Qualifier(SECOND_MANAGER_FACTORY) EntityManagerFactory entityManagerFactory) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(entityManagerFactory);

    return transactionManager;
  }

  @Bean(name = SECOND_SESSION_FACTORY)
  public SqlSessionFactory sqlSessionFactory(@Qualifier(SECOND_DATA_SOURCE) DataSource dataSource) throws Exception {
    SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
    setConfigSqlSessionFactory(sessionFactoryBean, dataSource);
    return sessionFactoryBean.getObject();
  }

  @Bean(name = SECOND_SESSION_TEMPLATE)
  public SqlSessionTemplate sqlSessionTemplate(@Qualifier(SECOND_SESSION_FACTORY) SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

 

application.yml

server:
  port: 9191

spring:
  # DB 설정
  first-db:
    datasource:
      driver-class-name: org.mariadb.jdbc.Driver
      username: ${FIR_DB_USER}
      password: ${FIR_DB_PW}
      jdbc-url: jdbc:mariadb://${FIR_DB_URL}

  second-db:
    datasource:
      driver-class-name: org.mariadb.jdbc.Driver
      username: ${SEC_DB_USER}
      password: ${SEC_DB_PW}
      jdbc-url: jdbc:mariadb://${SEC_DB_URL}

  # JPA 설정
  jpa:
    show-sql: true
    open-in-view: true
    properties:
      hibernate:
        physical_naming_strategy: org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy
        implicit_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
        dialect: org.hibernate.dialect.MariaDBDialect
        show_sql: true
        format_sql: true    # 권장되지 X
        use_sql_comments: true

# Mybatis 설정
mybatis:
  configuration:
    map-underscore-to-camel-case: true
    call-setters-on-nulls: true
  mapper-locations: classpath:/mybatis/mapper/*.xml
  config-location: classpath:/mybatis/config/mybatis-config.xml

 

 

 

MyBatis 설정을 위해 resources 아래에 mybatis 패키지를 만들고, 해당 패키지 아래에 mapper와 config 정보를 넣어둔다.

 

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>
    <typeAliases>
        <typeAlias type="com.springboot2.legacy.dto.first.PokemonDto" alias="PokemonDto"/>
        <typeAlias type="com.springboot2.legacy.dto.second.ItemDto" alias="ItemDto"/>
    </typeAliases>
</configuration>

config 파일에는 dto를 alias로 사용하기 위해 typeAlias를 지정해준다.

이렇게 지정해주지 않으면 mapper에서 파라미터 혹은 반환값으로 객체 이용 시, 경로를 전부 적어야하는 번거로움이 있다.

 

 

예시

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.springboot2.legacy.mapper.first.PokemonMapper">

    <!-- alias를 지정해주었기 때문에 resultType에 경로를 생략하고 alias만 적으면 된다. -->
    <select id="getPokemons" resultType="PokemonDto">
        <![CDATA[
            SELECT
                *
            FROM
                pokemon
        ]]>
    </select>

    <insert id="addPokemon" parameterType="PokemonDto">
        INSERT INTO pokemon (
            name,
            type
        ) VALUES (
            #{name},
            #{type}
        )
    </insert>
</mapper>

 

 

해당 설정 이후, JPA와 MyBatis를 테스트 해보면 잘 되는 것 같지만 여기에는 문제점이 있다.

이후 해당 문제점들을 살펴보고 해결해보고자 한다.