Spring Boot multi DB (Oracle, MongoDB)

Spring Boot에서 다중 DB를 만들어 보자.

기존에 MyBatis를 써서 @Mapper로만 사용했었는데 이걸 JPARepository를 확장해서 사용한다는 것이 일반적인것 같다.

• 기존 코드

@Mapper
public interface BoardMapper {
	
	@Select("SELECT bd_hit FROM TP01BOARD WHERE bd_no = #{bd_no}")
	public int selectBoardHit(int bd_no);
	
	@Update("UPDATE TP01BOARD SET bd_hit = #{bd_hit} WHERE bd_no = #{bd_no}")
	public void updateBoardHit(BoardDTO boardDTO);
	
	@Select("SELECT COUNT(*) bd_no FROM TP01BOARD")
	public int getBoardCount();
	
	@Select("SELECT B.bd_no, B.ur_no, B.bd_name, M.ur_name, B.bd_type, B.bd_hit, B.bd_reg_date "
			+ "FROM TP01BOARD B JOIN TP01MEMBER M ON M.ur_no = B.ur_no")
	public List<BoardDTO> getBoards();
	
	@Select("SELECT B.bd_no, B.bd_name, M.ur_no, M.ur_name, B.bd_type ,B.bd_info, B.bd_reg_date, B.bd_hit "
			+ "FROM TP01BOARD B JOIN TP01MEMBER M ON B.ur_no = M.ur_no WHERE bd_no = #{bd_no}")
	public List<BoardDTO> getBoard(int bd_no);
	
	@Insert("INSERT INTO TP01BOARD (ur_no, ur_name, bd_name, bd_info, bd_type) VALUES (#{ur_no}, #{ur_name},#{bd_name}, #{bd_info}, #{bd_type})")
	public void insertBoard(BoardDTO boardDTO);
	
	@Update("UPDATE TP01BOARD SET ur_name = #{ur_name}, bd_name = #{bd_name}, bd_info = #{bd_info}, bd_type = #{bd_type} WHERE bd_no = #{bd_no}")
	public void updateBoard(BoardDTO boardDTO);
	
	@Delete("DELETE FROM TP01BOARD WHERE bd_no = #{bd_no} AND ur_no = #{ur_no}")
	public void deleteBoard(BoardDTO boardDTO);
}

@Mapper는 MyBatis, @Repository는 JPA와 관련이 있다.
ToyProject의 목표는 다중 DB가 있기 때문에 JPA로 확장하자...

Maven 설정

<!-- JPA를 위한 설정 -->
		<!-- oraclexe -->
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc11</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!-- mongodb -->
		<dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-data-mongodb</artifactId>
		</dependency>
		<!-- JPA를 위한 설정 -->
		<dependency>
    		<groupId>jakarta.persistence</groupId>
    		<artifactId>jakarta.persistence-api</artifactId>
    	</dependency>
		<dependency>
    		<groupId>org.springframework.data</groupId>
    		<artifactId>spring-data-jpa</artifactId>
    	</dependency>
    	<dependency>
    		<groupId>org.hibernate</groupId>
    		<artifactId>hibernate-core</artifactId>
    		<version>6.4.1.Final</version>
    	</dependency>

hibernate, jakarta(JPA)는 JPA 쓰기위해서 필요함

yml 설정

• As-Is (자동으로 물음)

datasource:
    url: jdbc:oracle:thin:@localhost:1521:xe
    username: dbUser
    password: 0000

• To-Be (수동으로 명시해서 써야함)

  datasource:
    hikari: 
      jdbc-url: jdbc:oracle:thin:@localhost:1521:xe
      username: c##user001
      password: 1234
  data:
    mongodb:
      uri: mongodb://localhost:27017/test
 

hikari는 jdbc pool에서 쓰는것 중 하나며 다중 DB의 connection등을 담당함.
!! hikari를 명시하여 쓰면dbConfig class를 하나 만들어야 함

OracleXE11gConfig class

• basePackages는 Repository 경로
• ConfigurationProperties(prefix = "yml 경로")

@Configuration
@EnableJpaRepositories(
		basePackages = {"kr.spring.board.repository", "kr.spring.member.repository"},
		entityManagerFactoryRef = "primaryEntityManager",
		transactionManagerRef = "primaryTransactionManager"
)
public class OracleXE11gConfig {
	@Bean
	@Primary
	@ConfigurationProperties(prefix = "spring.datasource.hikari")
	public DataSource primaryDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean
	@Primary
	public LocalContainerEntityManagerFactoryBean primaryEntityManager() {
		LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
		em.setDataSource(primaryDataSource());
        em.setPackagesToScan(new String[] {"kr.spring.board.entity", "kr.spring.member.entity"});
        
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setShowSql(true);
        vendorAdapter.setGenerateDdl(true);
        em.setJpaVendorAdapter(vendorAdapter);
        
        HashMap<String, Object> prop = new HashMap<>();
        prop.put("hibernate.dialect", "org.hibernate.dialect.OracleDialect");
        prop.put("hibernate.hbm2ddl.auto", "update");
        prop.put("hibernate.format_sql", true);
        em.setJpaPropertyMap(prop);
        
        return em;
	}
	
	@Bean
    @Primary
    public PlatformTransactionManager primaryTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(primaryEntityManager().getObject());
        return transactionManager;
    }
}

BoardEntity class

근데 JPA는 snake case로 만드는걸 권고하지 않는다... BoardRepsitory interface를 확장할 때 _를 인식을 못하기 때문이다.

• snake case ex) bd_no (X)
• camel case ex) bdNo (O)

@Entity
@Table(name = "TP01BOARD")
public class BoardEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int bd_no;
    
    @Column
    private int ur_no;
    
    @Column
    private String ur_name;
    
    @Column
    private String bd_name;
    
    @Column
    private String bd_info;
    
    @Column
    private String bd_type;
    
    @Column
    private int bd_auth = 4;
    
    @Column
    private int bd_hit = 0;
    
    @Column
    private Date bd_reg_date;

    public BoardEntity() {
    }

    //Getters Setters... 생략
    
}

BoardRepsitory interface

기본적으로 제공되는 save, findById, findAll, count 같은 것들이 있음

package kr.spring.board.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import kr.spring.board.entity.BoardEntity;

@Repository
public interface BoardRepository extends JpaRepository <BoardEntity, Integer> {
}

BoardService class

BoardRepository를 간단하게 불러서 사용이 가능하다.

@Service
@Transactional
public class BoardService {

	@Autowired
	BoardRepository boardRepository;
    
    // 게시판 전체 개수 가져오기
	public int getBoardCount() {
		int cnt = (int) boardRepository.count();
		return cnt;
	}

	// 게시판 데이터 가져오기
	public List<BoardDTO> getAllBoards() {
		List<BoardEntity> entityList = boardRepository.findAll();
		List<BoardDTO> list = new ArrayList<>();
		for(BoardEntity boardEntity : entityList) {
			list.add(BoardConverter.EntityToDTO(boardEntity));
		}
		return list;
	}
    
    //.... 형태로 쓰면 된다.
}

MongoDBConfig 클래스

MongoDB 관련한 log용 DB 설정
class가 자꾸 생성되는데 그걸 막기 위한 설정

@Configuration
@EnableMongoAuditing
@EnableMongoRepositories(basePackages = "kr.spring.log.repository")
public class MongoDBConfig {
    
	@Autowired
	private MongoDatabaseFactory mongoDatabaseFactory;
	
	@Autowired
	private MongoMappingContext mongoMappingContext;

    @Bean
	public MappingMongoConverter mappingMongoConverter() {
		MappingMongoConverter converter = new MappingMongoConverter(
				new DefaultDbRefResolver(mongoDatabaseFactory), mongoMappingContext);
		converter.setTypeMapper(new DefaultMongoTypeMapper(null));
		return converter;
	}
	
}

LogEntity class

Getters Setters가 필요한 Entity

• @Id를 주면 자동으로 생성되고, NoSQL은 Document라고함
• collection은 Table임
• Column이 field임
• 특징이 정해진 틀이 아닌 집어넣는 형식이기 때문에 중간에막바꿔도 되서 확장성이 좋음

@Document(collection = "users")
public class LogEntity {

	@Id
	private String id;

	@Field(name = "ur_id")
	private String urId;
	
	@Field(name = "type")
	private String type;

	@Field(name = "content")
	private String content;

	@Field(name = "reg_time")
	private Date regTime;

	// Getters
	public String getId() {
		return id;
	}

	public String getUrId() {
		return urId;
	}
	
	public String getType() {
		return type;
	}

	public String getContent() {
		return content;
	}

	public Date getRegTime() {
		return regTime;
	}

	// Setters
	public void setId(String id) {
		this.id = id;
	}

	public void setUrId(String urId) {
		this.urId = urId;
	}
	
	public void setType(String type) {
		this.type = type;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public void setRegTime(Date regTime) {
		this.regTime = regTime;
	}
}

LogRepository class

JPA 처럼 쓰는건데 Maven에서 물어준 MongoDB 관련 어노테이션을 써서 확장 시킴

public interface LogRepository extends MongoRepository<LogEntity,String>{

}

BoardLogService class

@Service
public class BoardLogService {
	@Autowired
	LogRepository logRepository;
	
	//게시판 클릭
	public boolean createBoardClickLog (BoardDTO boardDTO, String ur_id) {
		LogEntity memberLogEntity = new LogEntity();
		Date currentTime = new Date();
		
		memberLogEntity.setUrId(ur_id);
		memberLogEntity.setType("Board");
		memberLogEntity.setRegTime(currentTime);
		memberLogEntity.setContent(ur_id+" 님이 게시판 번호"+boardDTO.getBd_no()+"를 조회하셨습니다.");
		
		logRepository.save(memberLogEntity);
		return true;
	}
	
	//게시판 작성
	public boolean createBoardWriteLog (BoardDTO boardDTO, String ur_id) {
		LogEntity memberLogEntity = new LogEntity();
		Date currentTime = new Date();
		
		memberLogEntity.setUrId(ur_id);
		memberLogEntity.setType("Board");
		memberLogEntity.setRegTime(currentTime);
		memberLogEntity.setContent(ur_id+" 님이 게시판 번호"+boardDTO.getBd_no()+"를 작성하셨습니다.");
		
		logRepository.save(memberLogEntity);
		return true;
	}
	
	//게시판 수정
	public boolean createBoardModifyLog (BoardDTO boardDTO, String ur_id) {
		LogEntity memberLogEntity = new LogEntity();
		Date currentTime = new Date();
		
		memberLogEntity.setUrId(ur_id);
		memberLogEntity.setType("Board");
		memberLogEntity.setRegTime(currentTime);
		memberLogEntity.setContent(ur_id+" 님이 게시판 번호"+boardDTO.getBd_no()+"를 수정하셨습니다.");
		
		logRepository.save(memberLogEntity);
		return true;
	}
	
	//게시판 삭제
	public boolean createBoardDeleteLog (BoardDTO boardDTO, String ur_id) {
		LogEntity memberLogEntity = new LogEntity();
		Date currentTime = new Date();
		
		memberLogEntity.setUrId(ur_id);
		memberLogEntity.setType("Board");
		memberLogEntity.setRegTime(currentTime);
		memberLogEntity.setContent(ur_id+" 님이 게시판 번호"+boardDTO.getBd_no()+"를 삭제하셨습니다.");
		
		logRepository.save(memberLogEntity);
		return true;
	}
}

기존 Controller에 Service 끼워넣기

@RestController
public class BoardRestController {
	// 토큰값에서 가져온거 쓰기
	private int ur_no;
	private String ur_id;
	private String ur_name;

	private void init(HttpServletResponse response) throws IOException {
		Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
		if (authentication != null && authentication.getPrincipal() instanceof MemberDetails) {
			MemberDetails userDetails = (MemberDetails) authentication.getPrincipal();
			this.ur_no = userDetails.getUr_no();
			this.ur_id = userDetails.getUsername();
			this.ur_name = userDetails.getUr_name();
		} else {
			response.sendRedirect("/member/login");
		}
	}

	@Autowired
	private BoardService boardService;
	
	@Autowired
	private BoardLogService boardLogService;
	
	// 게시판 개수 가져오기
	@GetMapping("/api/board/countBoard")
	public int getCountBoard() {
		int cnt = boardService.getBoardCount();
		return cnt;
	}

	// 데이터 하나 가져오기
	@GetMapping("/api/board/{bd_no}")
	public Map<String, Object> getBoard(@PathVariable("bd_no") int bd_no,
			HttpServletResponse response) throws IOException {
		init(response);
		Map<String, Object> mapJson = new HashMap<>();
		List<BoardDTO> list = boardService.getBoard(bd_no);
		if(!list.isEmpty()) {
			int curHit = boardService.getCurrentBoardHit(bd_no);
			BoardDTO boardDTO = new BoardDTO();
			int increasedHit = curHit + 1;
			boardDTO.setBd_hit(increasedHit);
			boardDTO.setBd_no(bd_no);
			boardService.upHit(boardDTO);
			if(boardLogService.createBoardClickLog(boardDTO, ur_id)) {
				mapJson.put("log", "success");
			}
		} else { //게시글 없을 때
			mapJson.put("result", "fail");
			mapJson.put("message", "잘못된 접근입니다.");
			mapJson.put("redirectUrl", "/board/main");
			return mapJson;
		}
		mapJson.put("result", "success");
		mapJson.put("count", list.size());
		mapJson.put("list", list);
		return mapJson;
	}

	// 게시판 작성
	@PostMapping("/api/board")
	public Map<String, Object> writeBoard(@RequestBody BoardDTO boardDTO, HttpServletResponse response)
			throws IOException {
		Map<String, Object> mapJson = new HashMap<>();
		init(response);
		boardDTO.setUr_no(ur_no);
		boardDTO.setUr_name(ur_name);
		if (boardService.upsertBoard(boardDTO)) {
			mapJson.put("result", "success");
			mapJson.put("message", "글이 등록 되었습니다.");
			mapJson.put("redirectUrl", "/board/main");
			if(boardLogService.createBoardWriteLog(boardDTO, ur_id)) {
				mapJson.put("log", "success");
			}
		} else {
			mapJson.put("result", "fail");
			mapJson.put("message", "다시 시도하세요.");
		}

		return mapJson;
	}

	// 게시판 수정
	@PutMapping("/api/board/{bd_no}")
	public Map<String, Object> modifyBoard(@PathVariable("bd_no") int bd_no, @RequestBody BoardDTO boardDTO,
			HttpServletResponse response) throws IOException {
		Map<String, Object> mapJson = new HashMap<>();
		init(response);
		boardDTO.setBd_no(bd_no);
		boardDTO.setUr_no(ur_no);
		boardDTO.setUr_name(ur_name);
		if (boardService.upsertBoard(boardDTO)) {
			mapJson.put("result", "success");
			mapJson.put("message", "글이 수정 되었습니다.");
			mapJson.put("redirectUrl", "/board/detail?bd_no=" + bd_no);
			if(boardLogService.createBoardModifyLog(boardDTO, ur_id)) {
				mapJson.put("log", "success");
			}
		} else {
			mapJson.put("result", "fail");
			mapJson.put("message", "다시 시도하세요.");
		}
		return mapJson;
	}

	@DeleteMapping("/api/board/{bd_no}")
	public Map<String, Object> deleteBoard(@PathVariable("bd_no") int bd_no, HttpServletResponse response)
			throws IOException {
		init(response);
		Map<String, Object> mapJson = new HashMap<>();
		BoardDTO boardDTO = new BoardDTO();
		boardDTO.setBd_no(bd_no);
		boardDTO.setUr_no(ur_no);

		if (boardService.deleteBoard(boardDTO)) {
			mapJson.put("result", "success");
			mapJson.put("message", "글이 삭제되었습니다.");
			mapJson.put("redirectUrl", "/board/main");
			if(boardLogService.createBoardDeleteLog(boardDTO, ur_id)) {
				mapJson.put("log", "success"); 
			}
		} else {
			mapJson.put("result", "fail");
			mapJson.put("message", "다시 시도하세요.");
		}
		return mapJson;
	}

	// 데이터 가져오기
	@GetMapping("/api/board")
	public Map<String, Object> getBoards() {
		Map<String, Object> mapJson = new HashMap<>();
		// JWT 토큰 없으면 뱉기
		List<BoardDTO> list = boardService.getAllBoards();

		mapJson.put("result", "success");
		mapJson.put("count", boardService.getBoardCount());
		mapJson.put("list", list);

		return mapJson;
	}

}