springboot multi jdbc database 설정, mybatis jpa gradle 방법, H2 RDBMS 사용방법

springboot 에서 여러가지 이유로 다중으로 데이터베이스를 설정해야할경우가 있습니다.

read(query), write(command) 분리하는 목적으로 진행할수도 있고

여러대의 RDBMS를 연결해야할때 사용할수도 있고 

프로젝트 진행하는데 DB 스키마가 안나와서 H2로 임시작업 해야할경우가 있습니다.

 

세팅할때마다 쉽지가 않기때문에 

모든 상황에 맞춰서 성공한다는 보장은 없습니다. 

 

여러가지 이유로인해서 잘안될수 있기때문에 어느정도 기본적인부분은 따라서 진행하시고

안되는부분은 천천히 서버 로그를 보면서 찾아보셔야합니다.

 

h2 사용방법

h2는 자바로 작성된 관계형 데이터베이스 관리 시스템입니다.

h2는 remote, in-memory, embedded 3가지 설정을 할수 있습니다. 

https://www.h2database.com/html/main.html

H2 Database Engine

최종버전은 2.1.214 이며 intellij에서 2.1.10 까지만 확인되기때문에 마지막버전은 사이트에서 다운로드하셔서 

h2생성시 드라이브 버전 설정을 맞추셔야합니다.

 

유의점 : h2 드라이브 설정이 중요한이유는 생성된 파일버전에 따라 접근되고 안되고 하기때문에 꼭 사용하시는 개발Tool과 gradle 버전모두 동일하게 세팅하셔야합니다.

 

1.intellij 의 경우 오른쪽 슬라이드탭에서 확인가능하며 h2 설정시 드라이브 버전세팅을 할수 있습니다.

버전 설정할때 꼭 intellij tool 그리고 gradle 맞춰주셔야합니다.

안그럼 겁나 삽질할수가 있어요

    // h2
    runtimeOnly 'mysql:mysql-connector-java'
    implementation('com.h2database:h2:2.1.214')

 

 

접근방법 

1.intellij tool을 이용해서 접근하는방법

Tool이 있다면 접근해서 DDL,DML 모두 가능합니다.

2.web console이용하는방법

application.yml 에 아래처럼 h2console 설정하시면

  h2:
    console:
      path: /h2console
      enabled: true

springboot 기동시 오픈할 포트 기준으로 실행가능합니다.

9001 포트를 기본으로했다면 해당 URL 뒤로 application.yml에 설정한 정보를 붙이면 나오게됩니다.

 

주의사항 : 회원 같은 스프링 시큐리티를 이용하는경우 url이 모두 막힐수 있으니 그럴경우 시큐리티는 별도로 접근가능한 세팅을 해주셔야 가능합니다. 참고하세요!!

 

h2는 임베디드 형태로 구성했기때문에 Local에 파일이 생성되고 저장됩니다.

jdbc:h2:~/h2db/cdp

~의경우 mac에서는 Users/{계정명}

~/h2db/cdp

---

springboot, jpa, h2, gradle
Read, Write 2개 분리해서 사용하는 설정

application.yml

server:
  port: 9090

spring:
  datasource:
    write:
      driverClassName: org.h2.Driver
      jdbcUrl: jdbc:h2:~/test
      username: sa
      password:
      maximumPoolSize: 30
      minimumIdle: 5
      poolName: writedbidus
      readOnly: false
    read:
      driverClassName: org.h2.Driver
      jdbcUrl: jdbc:h2:~/test
      username: sa
      password:
      maximumPoolSize: 30
      minimumIdle: 5
      poolName: readdbidus
      readOnly: true

  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: create-drop
    properties:
      hibernate:
        show_sql: fase
        use_sql_comments: true
        format_sql: true
        dialect: org.hibernate.dialect.H2Dialect
  h2:
    console:
      path: /h2
      enabled: true


jwt:
  secret: c2lsdmVybmluZS10ZWNoLXNwcmluZy1ib290LWp3dC10dX

application.yml 파일

 

driverClassName: org.h2.Driver : 드라이브 h2 설정시 사용됩니다.
jdbcUrl: jdbc:h2:~/test : mac 기준이며 ~/test 설정하시면 Mac 저장되는 경로는 "/Users/{계정명}/test" 하위에 2개의 h2관련 파일이 생성됩니다.

 

package com.idus.core.infrastructure.config;

import com.idus.core.infrastructure.type.DatabaseType;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.jpa.HibernatePersistenceProvider;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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.jdbc.datasource.LazyConnectionDataSourceProxy;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

@Slf4j
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = {
                "com.idus.domain.**.repository"
        },
        entityManagerFactoryRef = "idusEntityManagerFactory",
        transactionManagerRef = "idusTransactionManager"
)
public class IdusDataSourceConfig {
    @Value("${spring.jpa.hibernate.ddl-auto}")
    private String ddlAuto;

    @Value("${spring.jpa.properties.hibernate.dialect}")
    private String dialect;

    @Value("${spring.jpa.properties.hibernate.format_sql}")
    private String formatSql;

    @Value("${spring.jpa.properties.hibernate.show_sql}")
    private String showSql;

    @Value("${spring.jpa.properties.hibernate.use_sql_comments}")
    private String useSqlComments;

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.write")
    public DataSource idusWriteDataSource() {
        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.read")
    public DataSource idusReadDataSource() {
        return DataSourceBuilder.create()
                .type(HikariDataSource.class)
                .build();
    }

    public DataSource idusRoutingDataSource() {
        ReplicationRoutingDataSource routingDataSource = new ReplicationRoutingDataSource();

        Map<Object, Object> dataSourceMap = new HashMap<>();

        dataSourceMap.put(DatabaseType.WRITE, idusWriteDataSource());
        dataSourceMap.put(DatabaseType.READ, idusReadDataSource());

        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(idusWriteDataSource());
        routingDataSource.afterPropertiesSet();

        return routingDataSource;
    }

    @Bean(name = "idusEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean idusEentityManagerFactory() {
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(Boolean.TRUE);

        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();

        entityManagerFactoryBean.setPersistenceProvider(new HibernatePersistenceProvider());
        entityManagerFactoryBean.setPackagesToScan("com.idus.domain.**.entity");
        entityManagerFactoryBean.setDataSource(new LazyConnectionDataSourceProxy(idusRoutingDataSource()));
        entityManagerFactoryBean.setJpaProperties(hibernateProperties());
        entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
        entityManagerFactoryBean.setPersistenceUnitName("idusEntityManagerUnit");
        entityManagerFactoryBean.afterPropertiesSet();

        return entityManagerFactoryBean;
    }

    @Bean(name = "idusTransactionManager")
    public PlatformTransactionManager idusTransactionManager(
            @Qualifier("idusEntityManagerFactory") EntityManagerFactory idusEntityManagerFactory) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(idusEntityManagerFactory);

        return transactionManager;
    }

    private Properties hibernateProperties() {
        Properties properties = new Properties();

        properties.setProperty("hibernate.hbm2ddl.auto", ddlAuto);
        properties.setProperty("hibernate.dialect", dialect);
        properties.setProperty("hibernate.format_sql", formatSql);
        properties.setProperty("hibernate.show_sql", showSql);
        properties.setProperty("hibernate.use_sql_comments", useSqlComments);

        return properties;
    }

}

build.gradle 파일

plugins {
    id 'org.springframework.boot' version '2.5.6'
    id 'io.spring.dependency-management' version '1.0.11.RELEASE'
    id 'java'
}

group = 'com.idus'
version = '0.0.1'
sourceCompatibility = '11'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

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

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

    implementation('com.h2database:h2:1.4.196')
    runtimeOnly 'mysql:mysql-connector-java'

    implementation 'io.jsonwebtoken:jjwt-api:0.11.2'
    implementation 'io.jsonwebtoken:jjwt-impl:0.11.2'
    implementation 'io.jsonwebtoken:jjwt-jackson:0.11.2'

    testImplementation 'org.springframework.security:spring-security-test'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'

    implementation "io.springfox:springfox-boot-starter:3.0.0"
    implementation "io.springfox:springfox-swagger-ui:3.0.0"
}

test {
    useJUnitPlatform()
}

https://github.com/lswteen/idus-security

jpa h2 read,write 연결했던 코드 공유드립니다. 

---

springboot, mybatis, h2, gradle
first, second 분리하기

이번엔 h2 로 mybatis 연결하기 입니다.

 

주의사항 : 같은 버전일경우는 문제가없는데 mssql, mysql 등 드라이브 버전이 다를경우에는 멀티모듈로 격리시켜서 세팅하셔야 정상동작합니다. 이번장은  다루지않겠습니다. 귀찮아서 ㅎㅎ 꼭 참고하세여

spring:
  # JKDB 설정
  datasource-first:
    mybatis:
      mapper-locations: classpath:sql/first/**/*.xml
      config-location: classpath:config/mybatis-config.xml
    hikari:
      name: first
      pool-name: cdp-first-pool
      driver-class-name: org.h2.Driver
      jdbc-url: jdbc:h2:~/h2db/first;AUTO_SERVER=true
      username: sa
      password:
      connection-timeout: 3000
      validation-timeout: 3000
      minimum-idle: 1
      max-lifetime: 240000
      maximum-pool-size: 30
      connection-test-query: select 1

  datasource-second:
    mybatis:
      mapper-locations: classpath:sql/second/**/*.xml
      config-location: classpath:config/mybatis-config.xml
    hikari:
      name: second
      pool-name: cdp-second-pool
      driver-class-name: org.h2.Driver
      jdbc-url: jdbc:h2:~/h2db/second;AUTO_SERVER=true
      username: sa
      password:
      connection-timeout: 3000
      validation-timeout: 3000
      minimum-idle: 1
      max-lifetime: 240000
      maximum-pool-size: 30
      connection-test-query: select 1

  devtools:
    livereload:
      enabled: false

  h2:
    console:
      path: /h2console
      enabled: true

  main:
    allow-circular-references: true

hikari 로 설정을 했고 주의사항으로

 

아래 mapper 경로를 분리

mapper-locations: classpath:sql/first/**/*.xml

name, pool-name도 꼭 충돌안나게 만들어주시기 바랍니다.

name: first
pool-name: first-pool

 

first database config

주의사항 : bean 이름 생성시 second와 충돌발생하지않게 중복을 확인하시고 경로등이 정확한지 확인해주시기 바랍니다.

그리고 먼저 읽게 되는 database설정에는 @Primary를 꼭 붙여주시고 second 에는 빼주시기 바랍니다.

우선순위에 대한 기능이라고 아시면되고 자세한 사항은 찾아보시기 바랍니다.

@Slf4j
@Configuration
@MapperScan(
        basePackages = {"com.test"},
        annotationClass = Mapper.class,
        sqlSessionFactoryRef = "firstSqlSessionFactory"
)
public class FirstDatabaseConfig {

    /*
        [Hikari Connection Pool Configuration]
    */
    @Bean("firstHikariConfig")
    @ConfigurationProperties(prefix = "spring.datasource-first.hikari")
    public HikariConfig firstHikariConfig() {
        return new HikariConfig();
    }

    @Bean(name = "firstDataSource")
    @Primary
    public DataSource h2DataSource(@Qualifier("firstHikariConfig") HikariConfig hikariConfig) {
        return new HikariDataSource(hikariConfig);
    }

    /*
	 [MyBatis SQL Mapping XML Location pattern]
	 */
    @Value("${spring.datasource-first.mybatis.mapper-locations}")
    private String firstMyBatisMapperLocation;

    /*
        [MyBatis Config Location pattern]
     */
    @Value("${spring.datasource-first.mybatis.config-location}")
    private String firstMyBatisConfigLocation;

    @Bean("firstSqlSessionFactory")
    @Primary
    public SqlSessionFactory firstSqlSessionFactory(
            SqlSessionFactoryBeanProvider factoryBeanProvider,
            @Qualifier("firstDataSource") DataSource dataSource,
            ApplicationContext applicationContext) throws Exception {
        return createSqlSessionFactory(factoryBeanProvider, dataSource, applicationContext, firstMyBatisMapperLocation, firstMyBatisConfigLocation);
    }

    /**
     * SqlSessionFactory 를 생성하여 반환합니다.
     *
     * @param factoryBeanProvider SQL Session Factory Bean Provider
     * @param dataSource javax.sql.DataSource
     * @param applicationContext org.springframework.context.ApplicationContext
     * @param resourceLocation location of mybatis mapping xml file
     * @return SQL Session Factory
     * @throws Exception Factory Bean Exception
     */
    private SqlSessionFactory createSqlSessionFactory(SqlSessionFactoryBeanProvider factoryBeanProvider,
                                                      DataSource dataSource,
                                                      ApplicationContext applicationContext,
                                                      String resourceLocation,
                                                      String configLocation) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = factoryBeanProvider.create();
        sqlSessionFactoryBean.setDataSource(dataSource);
        var resources = applicationContext.getResources(resourceLocation);
        log.debug("[DB-CONFIG] {} = {} ", resourceLocation, resources);
        sqlSessionFactoryBean.setMapperLocations(resources);
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource(configLocation));
        return sqlSessionFactoryBean.getObject();

    }

    @Primary
    @Bean("firstTransactionManager")
    public DataSourceTransactionManager firstTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

second database config

@Slf4j
@Configuration
@MapperScan(
        basePackages = {"com.test"},
        annotationClass = Mapper.class,
        sqlSessionFactoryRef = "secondSqlSessionFactory"
)
public class FirstDatabaseConfig {

    /*
        [Hikari Connection Pool Configuration]
    */
    @Bean("secondHikariConfig")
    @ConfigurationProperties(prefix = "spring.datasource-second.hikari")
    public HikariConfig firstHikariConfig() {
        return new HikariConfig();
    }

    @Bean(name = "secondDataSource")
    public DataSource h2DataSource(@Qualifier("secondHikariConfig") HikariConfig hikariConfig) {
        return new HikariDataSource(hikariConfig);
    }

    /*
	 [MyBatis SQL Mapping XML Location pattern]
	 */
    @Value("${spring.datasource-second.mybatis.mapper-locations}")
    private String secondMyBatisMapperLocation;

    /*
        [MyBatis Config Location pattern]
     */
    @Value("${spring.datasource-second.mybatis.config-location}")
    private String secondMyBatisConfigLocation;

    @Bean("secondSqlSessionFactory")
    @Primary
    public SqlSessionFactory secondSqlSessionFactory(
            SqlSessionFactoryBeanProvider factoryBeanProvider,
            @Qualifier("secondDataSource") DataSource dataSource,
            ApplicationContext applicationContext) throws Exception {
        return createSqlSessionFactory(factoryBeanProvider, dataSource, applicationContext, secondMyBatisMapperLocation, secondMyBatisConfigLocation);
    }

    /**
     * SqlSessionFactory 를 생성하여 반환합니다.
     *
     * @param factoryBeanProvider SQL Session Factory Bean Provider
     * @param dataSource javax.sql.DataSource
     * @param applicationContext org.springframework.context.ApplicationContext
     * @param resourceLocation location of mybatis mapping xml file
     * @return SQL Session Factory
     * @throws Exception Factory Bean Exception
     */
    private SqlSessionFactory createSqlSessionFactory(SqlSessionFactoryBeanProvider factoryBeanProvider,
                                                      DataSource dataSource,
                                                      ApplicationContext applicationContext,
                                                      String resourceLocation,
                                                      String configLocation) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = factoryBeanProvider.create();
        sqlSessionFactoryBean.setDataSource(dataSource);
        var resources = applicationContext.getResources(resourceLocation);
        log.debug("[DB-CONFIG] {} = {} ", resourceLocation, resources);
        sqlSessionFactoryBean.setMapperLocations(resources);
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource(configLocation));
        return sqlSessionFactoryBean.getObject();

    }


    @Bean("secondTransactionManager")
    public DataSourceTransactionManager secondTransactionManager(@Qualifier("secondDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

부가기능

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="defaultEnumTypeHandler" value="jobkorea.careerpath.common.type.DefaultEnumTypeHandler"/>
    	<setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

해당 사항은 github에 추후 샘플작업 완료되면 올려보도록 하겠습니다.

멀티 db설정 삽질할때 참고하세요:)