'spring boot 여러 db 설정'에 해당되는 글 1건

0.1 시작 전 Gradle 설정 내용 확인

https://minaminaworld.tistory.com/218

0.2 JPA 설정편 확인: JPA에서 생성한 데이터를 읽어오는 테스트 코드 작성함

https://minaminaworld.tistory.com/219

1. 테스트 DB, 스키마, 테이블 정보

 - 테스트 DB명: db1

 - 테스트 스키마: jpa

 - 테스트 테이블: userjpa (Jpa Option: create를 통한 자동 생성함)

2. application.yml 과 application-local.yml

 - application.yml

spring:
  profiles:
    active: local

- application-local.yml

spring:
  config:
    activate:
      on-profile: local
  datasource:
    jpa:
      #     jdbc-url: jdbc:postgresql://localhost:5432/db1
      url: jdbc:postgresql://localhost:5432/db1
      username: postgres
      password: postgres
      driver-class-name: org.postgresql.Driver
    mybatis:
      #      jdbcUrl: jdbc:postgresql://localhost:5432/db1
      url: jdbc:postgresql://localhost:5432/db1
      username: postgres
      password: postgres
      driver-class-name: org.postgresql.Driver
server:
  port: 8090 # 서버 포트 변경

2. MybatisDbProperties.java

package com.example.multidb.database.config;

import lombok.Getter;
import lombok.Setter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties(prefix = "spring.datasource.mybatis") // application-local.yml 참고
@Getter
@Setter
public class MybatisDbProperties {
    String url;
    String username;
    String password;
    String driverClassName;
}

3. mybatis-config.xml 작성 (* resources 폴더 생성)

<?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" />
        <setting name="useColumnLabel" value="true" />
        <setting name="useGeneratedKeys" value="false" />
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <setting name="defaultStatementTimeout" value="25000" />
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>
    <typeAliases>
        <!-- 오탈자 조심 -->
        <package name="com.example.multidb.domain.mybatis"/>
    </typeAliases>
</configuration>

4. UserMybatis.java: 테이블 읽어올 항목 작성(VO 생성)

package com.example.multidb.domain.mybatis;

import javax.persistence.Column;

public class UserMybatis {
    private Long id;
    private String name;
    private String email;
    private int age;

    @Override
    public String toString() {
        return "UserMybatis{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                '}';
    }
}

5. UserMybatisMapper.java와 UserMybatisMapper.xml 작성

- UserMybatisMapper.java

package com.example.multidb.persistence.mybatis.mapper;

import com.example.multidb.domain.mybatis.UserMybatis;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Map;

@Repository
public interface UserMybatisMapper {
    public ArrayList<UserMybatis> getUsers() throws Exception;
}

- UserMybatisMapper.xml : namespace 부분 확인하시면서 하세요. 오탈자 조심

<?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.example.multidb.persistence.mybatis.mapper.UserMybatisMapper">

    <select id="getUsers" resultType="com.example.multidb.domain.mybatis.UserMybatis">
            SELECT * FROM jpa.userjpa;
    </select>

</mapper>

6. MybatisDb.java

package com.example.multidb.database.config;

import lombok.AllArgsConstructor;
import lombok.RequiredArgsConstructor;
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.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@RequiredArgsConstructor
@MapperScan(
        basePackages = {"com.example.multidb.persistence.mybatis.mapper"}
)
public class MybatisDb {

    private final MybatisDbProperties mybatisDbProperties;

    @Bean(name = "mybatisDataSource")
    public DataSource dataSource() {
        // 빌더 dataSource 생성
        // 프로퍼티 정보
        System.out.println("------------------------------------------");
        String url = mybatisDbProperties.getUrl();
        String username = mybatisDbProperties.getUsername();
        String password = mybatisDbProperties.getPassword();
        String driverClassName = mybatisDbProperties.getDriverClassName();

        System.out.println("url = " + url);
        System.out.println("username = " + username);
        System.out.println("password = " + password);
        System.out.println("driverClassName = " + driverClassName);
        System.out.println("------------------------------------------");

        return  DataSourceBuilder.create()
                .url(mybatisDbProperties.getUrl())
                .username(mybatisDbProperties.getUsername())
                .password(mybatisDbProperties.getPassword())
                .driverClassName(mybatisDbProperties.getDriverClassName())
                .build();
    }

    @Bean(name = "mybatisSessionFactory")
//    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mybatisDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setTypeAliasesPackage("com.example.multidb.domain.jpa");
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactoryBean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
        sessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mybatis/mapper/**/*.xml"));
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "mybatisSqlSessionTemplate")
    public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("mybatisSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

6. MybatisService.java 및 테스트 코드 작성

- MybatisService.java

package com.example.multidb.service;

import com.example.multidb.domain.jpa.UserJpa;
import com.example.multidb.domain.mybatis.UserMybatis;
import com.example.multidb.persistence.jpa.repository.UserJpaRepository;
import com.example.multidb.persistence.mybatis.mapper.UserMybatisMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;

@Service
@RequiredArgsConstructor
public class MybatisService {
    private final UserMybatisMapper userMybatisMapper;

    @Transactional(readOnly = true)
    public void readUser() {
        try {
            ArrayList<UserMybatis> users = userMybatisMapper.getUsers();
            for (UserMybatis user : users) {
                System.out.println("user = " + user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

- MybatisServiceTest.java

package com.example.multidb.service;

import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
class mybatisServiceTest {

    @Autowired
    private MybatisService mybatisService;

    @Test
    @DisplayName("JPA에서 생성한 유저 읽어오기")
    void readUser() {
        mybatisService.readUser();
    }
}

7.  결과화면