db 이중화 작업(springBoot,mybatis)

간단하게 db 이중화 작업을 알아보죠

환경 설정은 springBoot,mybaits,Thymeleaf

디비는 mysql,oracle로 진행 하였습니다

다른 설정으로는 Lombok , Spring Boot DevTools , Java 11 ,Boot 2.7.13

# MASTER DB
spring.master.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.master.datasource.jdbc-url=jdbc:mysql://localhost:3306/?useSSL=false&useUnicode=true&serverTimezone=Asia/Seoul&allowPublicKeyRetrieval=true
spring.master.datasource.username=cos
spring.master.datasource.password=cos1234


# SLAVE 1 DB
spring.slave-1.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.slave-1.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:xe
spring.slave-1.datasource.username=scott
spring.slave-1.datasource.password=tiger

마스터와 슬레이브 2개를 하였고 SLAVE1로 이름을 정한건 추후 SALVE2를 추가할수도 있을까봐 설정했습니다

꼭 확인 해야할께 2번째에 url을 꼭 jdbc-url으로 바꿔줘야함

package com.spring.boot.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.context.annotation.Primary;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@MapperScan(value="com.spring.boot.mapper.master", sqlSessionFactoryRef="masterSqlSessionFactory")
@EnableTransactionManagement
public class MasterDataBaseConfig {
	
	@Primary
	@Bean(name="masterDataSource")
	@ConfigurationProperties(prefix="spring.master.datasource")
	public DataSource masterDataSource() {
		//application.properties에서 정의한 DB 연결 정보를 빌드
		return DataSourceBuilder.create().build();
	}
	
	@Primary
	@Bean(name="masterSqlSessionFactory")
	public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource, ApplicationContext applicationContext) throws Exception{
		//세션 생성 시, 빌드된 DataSource를 세팅하고 SQL문을 관리할 mapper.xml의 경로를 알려준다.
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(masterDataSource);
		sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mybatis/mapperMaster/*.xml"));
		return sqlSessionFactoryBean.getObject();
	}
	
	@Primary
	@Bean(name="masterSqlSessionTemplate")
	public SqlSessionTemplate masterSqlSessionTemplate(SqlSessionFactory masterSqlSessionFactory) throws Exception{
		return new SqlSessionTemplate(masterSqlSessionFactory);
	}
	
}

위에는 master db에 관한 설정입니다 한번 흝어보고 수정이 필요한부분만 수정하시면 됩니다

package com.spring.boot.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.transaction.annotation.EnableTransactionManagement;

@Configuration
@MapperScan(value="com.spring.boot.mapper.slave1", sqlSessionFactoryRef="slave1SqlSessionFactory")
@EnableTransactionManagement
public class Slave1DataBaseConfig {
	
	@Bean(name="slave1DataSource")
	@ConfigurationProperties(prefix="spring.slave-1.datasource")
	public DataSource masterDataSource() {
		//application.properties에서 정의한 DB 연결 정보를 빌드
		return DataSourceBuilder.create().build();
	}
	
	@Bean(name="slave1SqlSessionFactory")
	public SqlSessionFactory slave1SqlSessionFactory(@Qualifier("slave1DataSource") DataSource slave1DataSource, ApplicationContext applicationContext) throws Exception{
		//세션 생성 시, 빌드된 DataSource를 세팅하고 SQL문을 관리할 mapper.xml의 경로를 알려준다.
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(slave1DataSource);
		sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mybatis/mapperSlave1/*.xml"));
		return sqlSessionFactoryBean.getObject();
	}
	
	@Bean(name="slave1SqlSessionTemplate")
	public SqlSessionTemplate slave1SqlSessionTemplate(SqlSessionFactory slave1SqlSessionFactory) throws Exception{
		return new SqlSessionTemplate(slave1SqlSessionFactory);
	}
	
}

slave에 관한 설정 이하동문입니다.

솔직히 이게 끝인거 같아요

위와의 차이점이 있다면 @Primary를 꼭 써줘야합니다 우선순위를 정해줘요

이 밑에는 데이터가 제대로 나오는지 확인하기 위한 작업이므로 귀찮으시면 패스하셔도 됩니다

package com.spring.boot.model;

import lombok.Builder;
import lombok.Data;
import lombok.NonNull;

@Builder
@Data
public class CourseModel {
	private int id;
	private String courseName;
	private int departmentId;
	private int unit;
	private int professorId;
	private String startDate;
}

package com.spring.boot.model;

import lombok.Builder;
import lombok.Data;
import lombok.NonNull;

@Builder @Data
public class DeptModel {

	private int deptno;
	private String dname;
	private String loc;
}

model입니당 마스터 : course 슬레이브 : dept

package com.spring.boot.mapper.master;

import java.util.List;

import com.spring.boot.model.CourseModel;

public interface MasterDataBaseMapper {
	public List<CourseModel> getCourse() throws Exception;
}

package com.spring.boot.mapper.slave1;

import java.util.List;

import com.spring.boot.model.DeptModel;

public interface Slave1DataBaseMapper {
	public List<DeptModel> getDept() throws Exception;
}

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.spring.boot.mapper.master.MasterDataBaseMapper">
	<select id="getCourse" resultType="com.spring.boot.model.CourseModel">
		select * from student1.course
	</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">

<mapper namespace="com.spring.boot.mapper.slave1.Slave1DataBaseMapper">
	<select id="getDept" resultType="com.spring.boot.model.DeptModel">
		SELECT * FROM dept
	</select>
</mapper>

sql을 작성하는 xml입니당

package com.spring.boot.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.spring.boot.mapper.master.MasterDataBaseMapper;
import com.spring.boot.model.CourseModel;

@Service
public class MasterDataBaseService {
	@Autowired
	MasterDataBaseMapper masterDataBaseMapper;
	
	public List<CourseModel> getCourse() throws Exception{
		return masterDataBaseMapper.getCourse();
	}
	
}

package com.spring.boot.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.spring.boot.mapper.slave1.Slave1DataBaseMapper;
import com.spring.boot.model.DeptModel;

@Service
public class Slave1DataBaseService {
	@Autowired
	Slave1DataBaseMapper slave1DataBaseMapper;
	
	public List<DeptModel> getDept() throws Exception {
		return slave1DataBaseMapper.getDept();
	}
	
}

서비스에요

package com.spring.boot.controller;

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.spring.boot.model.DeptModel;
import com.spring.boot.model.CourseModel;
import com.spring.boot.service.MasterDataBaseService;
import com.spring.boot.service.Slave1DataBaseService;

@Controller
public class HomeController {
	@Autowired
	private MasterDataBaseService masterDataBaseService;
	
	@Autowired
	private Slave1DataBaseService slave1DataBaseService;
	
	@RequestMapping(value = "/home", method = RequestMethod.GET)
	public ModelAndView goHome(HttpServletRequest request) throws Exception {
		
		ModelAndView mav = new ModelAndView();
		
		List<CourseModel> courseList = masterDataBaseService.getCourse();
		List<DeptModel> deptList = slave1DataBaseService.getDept();
		
		mav.addObject("courseList", courseList);
		mav.addObject("deptList",deptList);
		mav.setViewName("content/home.html");
		return mav;
	}
	
}

깐트롤입니다

<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org"
      xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout"
      layout:decorate="~{layout/default_layout}">

<!-- home.html -->
<h3>Master DB in Course Table</h3>
<table border="1">
    <tr>
        <th>id</th>
        <th>courseName</th>
        <th>departmentId</th>
        <th>unit</th>
        <th>professorId</th>
        <th>startDate</th>
    </tr>
    <th:block th:each="course : ${courseList}">
    <tr>
       <td th:text="${ course.id }"></td>
       <td th:text="${ course.courseName }"></td>
       <td th:text="${ course.departmentId }"></td>
       <td th:text="${ course.unit }"></td>
       <td th:text="${ course.professorId }"></td>
       <td th:text="${ course.startDate }"></td>
    </tr>
    </th:block>
</table> 
    <hr>

	<h3>Slave1 DB in Dept Table</h3>
    <table border="1">
    <tr>
        <th>dname</th>
        <th>dname</th>
        <th>loc</th>

    </tr>
    <th:block th:each="dept : ${deptList}">
    <tr>
        <td th:text="${ dept.deptno }"></td>
        <td th:text="${ dept.dname }"></td>
        <td th:text="${ dept.loc }"></td>
    </tr>
    </th:block>
</table>


</html>

타임리프로 제대로 작동이 되는지 확인 해보면됩니다 jsp로 하려면 따로 설정해줘야합니다

디비 이중화를 하는 이유는 ?

디비 이중화 통한 디비 역할 분리를 통한 디비에 대한 부담을 최소화

master DB에는 write , insert , update

slave DB에는 select

저작자표시 비영리 변경금지

'SPRING' 카테고리의 다른 글

Retry 와 Recover (0)	2024.05.30
ehcache (0)	2024.05.30
MVC 패턴 구현 순서 (1)	2023.11.24
스프링 설정에 관하여(web.xml,servlet-context.xml,root-context.xml) (1)	2023.11.24
Context란? (1)	2023.11.24

brilliant-growth

db 이중화 작업(springBoot,mybatis)

'SPRING' 카테고리의 다른 글

티스토리툴바

db 이중화 작업(springBoot,mybatis)

'SPRING' 카테고리의 다른 글

관련글

티스토리툴바