헤르메스 LIFE

[Spring Boot] HikariCP를 이용한 Multi Database Connection 샘플 본문

Spring Framework

[Spring Boot] HikariCP를 이용한 Multi Database Connection 샘플

헤르메스의날개 2021. 7. 19. 10:29
728x90

개발환경

IDE : IntelliJ

Framework : Spring 2.5.2 + MyBatis

Database : Docker 에 DB 설치
- primary - PostgreSQL 13.3
- secondary - Maria DB 10.6.3

lombok

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.2</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.multi.db</groupId>
    <artifactId>MultiDatabase</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>MultiDatabase</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>
package com.db.multidatabase;

import com.db.multidatabase.user.service.MyUserService;
import com.db.multidatabase.user.service.PostUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

@SpringBootApplication
@Slf4j
public class MultiDatabaseApplication implements CommandLineRunner {

    @Autowired
    PostUserService postUserService;

    @Autowired
    MyUserService myUserService;

    public static void main( String[] args ) {
        SpringApplication.run( MultiDatabaseApplication.class, args );

        //ApplicationContext applicationContext = SpringApplication.run( MultiDatabaseApplication.class, args );

        //for( String name : applicationContext.getBeanDefinitionNames() ) {
        //    log.debug( "ApplicationContext Beans Names :: {}", name );
        //}
    }

    @Override
    public void run( String... args ) throws Exception {
        log.info( "result -> {}", postUserService.selectUserById( 1 ) );
        log.info( "result -> {}", myUserService.selectUserById( 1 ) );
    }
}
package com.db.multidatabase.config;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class MultipleMariaDataSourceConfiguration {
    @Bean
    @Primary
    @Qualifier("primaryHikariConfig")
    @ConfigurationProperties(prefix="spring.datasource.hikari.postgres")
    public HikariConfig primaryHikariConfig() {
        return new HikariConfig();
    }

    @Bean
    @Primary
    @Qualifier("primaryDataSource")
    public DataSource primaryDataSource() throws Exception {
        return new HikariDataSource( primaryHikariConfig());
    }
    @Bean
    @Qualifier("secondaryHikariConfig")
    @ConfigurationProperties(prefix="spring.datasource.hikari.maria")
    public HikariConfig secondaryHikariConfig() {
        return new HikariConfig();
    }

    @Bean
    @Qualifier("secondaryDataSource")
    public DataSource secondaryDataSource() throws Exception {
        return new HikariDataSource(secondaryHikariConfig());
    }
}
package com.db.multidatabase.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

@Configuration
public class PrimaryMyBatisConfiguration {
    @Primary
    @Bean(name = "primarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
        @Qualifier("primaryDataSource") DataSource dataSource, ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:mybatis/mybatis-config.xml"));
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mybatis/mapper/postgres/**/*.xml"));

        return sqlSessionFactoryBean.getObject();
    }

    @Primary
    @Bean(name = "primarySqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(
        @Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
package com.db.multidatabase.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class SecondaryMyBatisConfiguration {
    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
        @Qualifier("secondaryDataSource") DataSource dataSource, ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:mybatis/mybatis-config.xml"));
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mybatis/mapper/maria/**/*.xml"));

        return sqlSessionFactoryBean.getObject();
    }

    @Bean(name = "secondarySqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(
        @Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
server.port=9090

spring.datasource.hikari.postgres.jdbc-url=jdbc:postgresql://localhost:5432/springboot
spring.datasource.hikari.postgres.username=hermeswing
spring.datasource.hikari.postgres.password=pass
spring.datasource.hikari.postgres.maximum-pool-size=4

#spring.datasource.hikari.maria.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.hikari.maria.jdbc-url=jdbc:mysql://localhost:3306/springboot
spring.datasource.hikari.maria.username=hermeswing
spring.datasource.hikari.maria.password=pass
spring.datasource.hikari.maria.maximum-pool-size=4
package com.db.multidatabase.user.service;

import com.db.multidatabase.dto.UserDto;

public interface PostUserService {
    UserDto selectUserById( int id);
}
package com.db.multidatabase.user.service.impl;

import com.db.multidatabase.dto.UserDto;
import com.db.multidatabase.user.dao.PostUserDao;
import com.db.multidatabase.user.service.PostUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service( "postUserService" )
public class PostUserServiceImpl implements PostUserService {
    @Autowired
    PostUserDao userDao;

    public UserDto selectUserById( int id ) {
        return userDao.selectUserById( id );
    }
}
package com.db.multidatabase.user.dao;

import com.db.multidatabase.dto.UserDto;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

@Component
public class PostUserDao {
    private final SqlSession sqlSession;

    public PostUserDao( @Qualifier("primarySqlSessionTemplate") SqlSession sqlSession) {
        this.sqlSession = sqlSession;
    }

    public UserDto selectUserById( int id){
        return this.sqlSession.selectOne("com.db.multidatabase.user.dao.PostUserDao.selectUserById", id);
    }
}
package com.db.multidatabase.user.service;

import com.db.multidatabase.dto.UserDto;

public interface MyUserService {
    UserDto selectUserById( int id);
}
package com.db.multidatabase.user.service.impl;

import com.db.multidatabase.dto.UserDto;
import com.db.multidatabase.user.dao.MyUserDao;
import com.db.multidatabase.user.service.MyUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service( "myUserService" )
public class MyUserServiceImpl implements MyUserService {
    @Autowired
    MyUserDao userDao;

    public UserDto selectUserById( int id ) {
        return userDao.selectUserById( id );
    }
}
package com.db.multidatabase.user.dao;

import com.db.multidatabase.dto.UserDto;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

@Component
public class MyUserDao {
    private final SqlSession sqlSession;

    public MyUserDao(@Qualifier("secondarySqlSessionTemplate") SqlSession sqlSession) {
        this.sqlSession = sqlSession;
    }

    public UserDto selectUserById( int id){
        return this.sqlSession.selectOne("com.db.multidatabase.user.dao.MyUserDao.selectUserById", id);
    }
}
package com.db.multidatabase.dto;

import lombok.Data;

@Data
public class UserDto {
    String id;
    String name;
}
<?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="true"/> default value -->
		<!-- <setting name="cacheEnabled" value="false"/> -->
		<setting name="lazyLoadingEnabled" value="false"/>
		<!-- <setting name="multipleResultSetsEnabled" value="true"/> default value -->
		<!-- <setting name="useColumnLabel" value="true"/> default value -->
		<!-- <setting name="useGeneratedKeys" value="false"/> default value -->
		<!-- <setting name="defaultExecutorType" value="SIMPLE"/> default value -->
		<setting name="defaultStatementTimeout" value="25000"/>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<setting name="localCacheScope" value="STATEMENT"/>
		<setting name="jdbcTypeForNull" value="CHAR"/>
	</settings>
</configuration>
<?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">

<!-- /mybatis/mapper/postgres -->
<mapper namespace="com.db.multidatabase.user.dao.PostUserDao">
    <select id="selectUserById" parameterType="int" resultType="com.db.multidatabase.dto.UserDto">
        SELECT *
        FROM ACCOUNT
        WHERE id = #{value}
    </select>
</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">

<!-- /mybatis/mapper/maria -->
<mapper namespace="com.db.multidatabase.user.dao.MyUserDao">
    <select id="selectUserById" parameterType="int" resultType="com.db.multidatabase.dto.UserDto">
        SELECT *
        FROM USER
        WHERE id = #{value}
    </select>
</mapper>

실행 결과 - Select 만 했기 때문에 테이블 생성과 데이터의 Insert 는 직접해야 합니다.

더보기

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v2.5.2)

2021-07-19 10:15:53.574  INFO 10432 --- [           main] c.d.m.MultiDatabaseApplication           : Starting MultiDatabaseApplication using Java 11 on DESKTOP-HMJ6ST0 with PID 10432 (C:\JetBrains\IdeaProjects\MultiDatabase\target\classes started by hermeswing in C:\JetBrains\IdeaProjects\MultiDatabase)
2021-07-19 10:15:53.577  INFO 10432 --- [           main] c.d.m.MultiDatabaseApplication           : No active profile set, falling back to default profiles: default
2021-07-19 10:15:54.774  WARN 10432 --- [           main] o.m.s.mapper.ClassPathMapperScanner      : No MyBatis mapper was found in '[com.db.multidatabase]' package. Please check your configuration.
2021-07-19 10:15:55.534  INFO 10432 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 9090 (http)
2021-07-19 10:15:55.554  INFO 10432 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2021-07-19 10:15:55.555  INFO 10432 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.48]
2021-07-19 10:15:55.762  INFO 10432 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-07-19 10:15:55.763  INFO 10432 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2094 ms
2021-07-19 10:15:55.920  INFO 10432 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-07-19 10:15:56.073  INFO 10432 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2021-07-19 10:15:56.438  INFO 10432 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2021-07-19 10:15:56.498  INFO 10432 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2021-07-19 10:15:57.116  INFO 10432 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 9090 (http) with context path ''
2021-07-19 10:15:57.131  INFO 10432 --- [           main] c.d.m.MultiDatabaseApplication           : Started MultiDatabaseApplication in 4.866 seconds (JVM running for 9.53)
2021-07-19 10:15:57.192  INFO 10432 --- [           main] c.d.m.MultiDatabaseApplication           : result -> UserDto(id=1, name=postgresql)
2021-07-19 10:15:57.212  INFO 10432 --- [           main] c.d.m.MultiDatabaseApplication           : result -> UserDto(id=1, name=maria)

참조 : 백기선님의 스프링 부트 개념과 활용

https://mdwgti16.github.io/spring%20boot/spring_boot_mybatis_multi/#

 

Spring Boot - Mybatis Multiple Datasource 연동

Spring Boot - Mybatis Multiple Datasource 연동 기본적인 MyBatis 연동 방법은 Spring Boot - Mybatis 연동 참고

mdwgti16.github.io

 

728x90

'Spring Framework' 카테고리의 다른 글

[QueryDSL] STS에서 Gradle 사용 시 QueryDSL QClass 생성 설정  (0) 2022.03.27
[Hadoop] VirtualBox 에 HDP Sandbox 환경 구축  (0) 2021.12.02
[SpringBoot] H2 Database 연결하기  (0) 2021.07.17
[Logging] Log4j, Logback, Log4j2 로 MyBatis SQL 쿼리 남기기  (0) 2021.06.17
[Spring] Ajax data를 Controller에서 받는 두 가지 방법 : Vo / Map  (0) 2021.06.11
'Spring Framework' Related Articles more