본문으로 바로가기

springboot multiple db 연동하는 방법 + JPA

category 카테고리 없음 2023. 6. 23. 00:32

 

***** 삽질하지 말자.

H2 데이터베이스의 1.4.198 버전부터는 보안 이슈로 데이터베이스를 자동으로 생성해주지 않기 때문에 해당 데이터베이스가 없기 때문이라고 한다.

 

 

plugins {
	id 'java'
	id 'org.springframework.boot' version '3.1.0'
	id 'io.spring.dependency-management' version '1.1.0'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '17'

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter'
	
	  // H2 Database
    implementation 'com.h2database:h2:1.4.200'

    // Oracle Database
    implementation 'com.oracle.database.jdbc:ojdbc8:19.8.0.0'

    // Spring Boot Web
    implementation 'org.springframework.boot:spring-boot-starter-web'

    // MyBatis Dependencies
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.2.0'
    implementation 'org.mybatis:mybatis:3.4.6'
    implementation 'org.mybatis:mybatis-spring:1.3.2'
    
        // Spring Boot Starter Test
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    
    // JPA
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	
	implementation 'org.projectlombok:lombok'
}

tasks.named('test') {
	useJUnitPlatform()
}
package com.example.demo.h2.repository;

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

import com.example.demo.h2.entity.UserInfo;

public interface UserInfoRepository extends JpaRepository<UserInfo, Long>{

}​
package com.example.demo.h2.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity(name="userInfo")
public class UserInfo {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private String userId;
	
	private String password;
	
	private String name;
	
	
}​
package com.example.demo.h2.mapper;

import org.apache.ibatis.annotations.Mapper;

import com.example.demo.h2.entity.UserInfo;

@Mapper
public interface demoUserInfo {
	
	UserInfo selectUserInfo();
}
package com.example.demo.h2.config;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.h2.tools.Server;

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.zaxxer.hikari.HikariDataSource;

import jakarta.persistence.EntityManagerFactory;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "h2EntityManagerFactory",
        transactionManagerRef = "h2TransactionManager",
        basePackages = {"com.example.demo.repository"})
public class H2DatabaseConfig {
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Primary
    @Bean(name = "h2DataSourceProperties")
    @ConfigurationProperties("spring.h2.datasource")
    public DataSourceProperties h2DataSourceProperties() throws SQLException {
		
		Server server = Server.createTcpServer("-tcp","-tcpAllowOthers","-ifNotExists","-tcpPort", 9922+"").start();
		
		if(server.isRunning(true)) {
			logger.info("########################################Running....Tcp Server ");
		}
		
		
        return new DataSourceProperties();
    }
	
	@Primary
    @Bean(name = "h2DataSource")
    @ConfigurationProperties("spring.h2.datasource")
    public DataSource h2DataSource(@Qualifier("h2DataSourceProperties") DataSourceProperties h2DataSourceProperties) {
        return h2DataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
	
	
	@Primary
    @Bean(name = "h2EntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean h2EntityManagerFactory(
            EntityManagerFactoryBuilder h2EntityManagerFactoryBuilder, @Qualifier("h2DataSource") DataSource h2DataSource) {

        Map<String, String> h2JpaProperties = new HashMap<>();
        h2JpaProperties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
        h2JpaProperties.put("hibernate.hbm2ddl.auto", "create");

        return h2EntityManagerFactoryBuilder
                .dataSource(h2DataSource)
                .packages("com.example.demo.h2.entity")
                .persistenceUnit("h2DataSource")
                .properties(h2JpaProperties)
                .build();
    }
	
	
		@Primary
	    @Bean(name = "h2TransactionManager")
	    public PlatformTransactionManager h2TransactionManager(
	            @Qualifier("h2EntityManagerFactory") EntityManagerFactory h2EntityManagerFactory) {

	        return new JpaTransactionManager(h2EntityManagerFactory);
	    }
	 

}
# H2 Database
spring.h2.datasource.url=jdbc:h2:tcp://localhost:9922/./internal/spfdb
spring.h2.datasource.driver-class-name=org.h2.Driver
spring.h2.datasource.username=sa
spring.h2.datasource.password=

# Oracle Database
spring.oracle.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.oracle.datasource.username=your-username
spring.oracle.datasource.password=your-password
spring.oracle.datasource.driver-class-name=oracle.jdbc.OracleDriver
#jndi 기술?

 

H2가 정상적으로 연결 됐다면, 이제는 ORACLE DB 를 연동해보자.

package com.example.demo.oracle.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.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;

import com.zaxxer.hikari.HikariDataSource;

import jakarta.persistence.EntityManagerFactory;

//basePackages로 지정한 곳에 존재하는 @Mapper로 명시된 interface를 스캔한다.

@Configuration
@MapperScan(basePackages = {"com.example.demo.oracle.mapper"}, sqlSessionFactoryRef = "db2SqlSessionFactory")
@EnableTransactionManagement
public class OracleDatabaseConfig {
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	
//    @Bean(name = "oracleDataSourceProperties")
//    @ConfigurationProperties("spring.oracle.datasource")
//    public DataSourceProperties oracleDataSourceProperties() throws SQLException {
//        return new DataSourceProperties();
//    }
	
	
    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.oracle.datasource")
    public DataSource db2DataSource() {
        return DataSourceBuilder.create().build();
    }
	
	@Bean(name="db2SqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("db2DataSource") DataSource db2DataSource, ApplicationContext applicationContext) throws Exception
	{
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(db2DataSource);
		//sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.oracle.models.entities");
		
		sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:mybatis/mybatis-config.xml"));
		
		//resources 아래의 mapper 폴더 내의 *-mapper.xml 파일들을 SQL Map XML파일로 설정
		sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mapper/*-mapper.xml"));
		return sqlSessionFactoryBean.getObject();
	}	
	 
	@Bean(name="db2SqlSessionTemplate")
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory db2sqlSessionFactory) throws Exception
	{
		return new SqlSessionTemplate(db2sqlSessionFactory);
	}

}

 

!! 갑자기 해당 에러에 직면했다. 버전 이슈로 인한 오류였으니 역시 구굴링은...개발자에겐 필수다...

Mybatis - org/springframework/core/NestedIOException Error

 

master : MyBatis 3.5+, MyBatis-Spring 3.0, Java 17+ and Spring Boot 3.0
2.3.x : MyBatis 3.5+, MyBatis-Spring 2.1, Java 8+ and Spring Boot 2.5-2.7
2.1.x : MyBatis 3.5+, MyBatis-Spring 2.0+(2.0.6+ recommended), Java 8+ and Spring Boot 2.1-2.4 2.4

내 부트버전은 3.0.2
자바버전은 17
오.. 내 mybatis-spring-boot-starter 버전은 2.2.2..

결국 스타터 버전을 3.0.1로 올리고 해결되었지만 아직도 공부는 끝나지 않았다.
왜 컨트롤러로 호출하면 정상이었는지,
spring-boot-starter-test에 딱히 걸린 의존성도 보이지 않는데 테스트를 돌릴때만 문제가 되었는지..
공부할게 너무 많다 ㅠㅠ

 

이런 내용이 있었지만, 

 

// MyBatis Dependencies

implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:3.0.1'

implementation 'org.mybatis:mybatis:3.5.10'

implementation 'org.mybatis:mybatis-spring:2.0.7'

 

나는 이렇게 3개를 implementation 했었고, 이중 첫번째와 마지막은 동일한 내용으로 보여 마지막꺼를 제거하였더니 잘 동작하였다...

# Oracle Database
spring.oracle.datasource.jdbc-url=jdbc:oracle:thin:@localhost:1521:orcl
spring.oracle.datasource.username=c##demo
spring.oracle.datasource.password=demo
spring.oracle.datasource.driver-class-name=oracle.jdbc.OracleDriver

url 로 쓰면 에러가 발생한다...jdbc-url 로 입력하자

 

oracle dataconfig 최종 코드 

package com.example.demo.oracle.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.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;

import com.zaxxer.hikari.HikariDataSource;

import jakarta.persistence.EntityManagerFactory;

//basePackages로 지정한 곳에 존재하는 @Mapper로 명시된 interface를 스캔한다.

@Configuration
@MapperScan(basePackages = {"com.example.demo.oracle.mapper"}, sqlSessionFactoryRef = "db2SqlSessionFactory")
@EnableTransactionManagement
public class OracleDatabaseConfig {
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	
//    @Bean(name = "oracleDataSourceProperties")
//    @ConfigurationProperties("spring.oracle.datasource")
//    public DataSourceProperties oracleDataSourceProperties() throws SQLException {
//        return new DataSourceProperties();
//    }
	
	
    @Bean(name = "db2DataSource")
    @ConfigurationProperties(prefix = "spring.oracle.datasource")
    public DataSource db2DataSource() {
        return DataSourceBuilder.create().build();
    }
	
	@Bean(name="db2SqlSessionFactory")
	public SqlSessionFactory sqlSessionFactory(@Qualifier("db2DataSource") DataSource db2DataSource, ApplicationContext applicationContext) throws Exception
	{
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(db2DataSource);
		//sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.oracle.models.entities");
		
		sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:mybatis/mybatis-config.xml"));
		
		//resources 아래의 mapper 폴더 내의 *-mapper.xml 파일들을 SQL Map XML파일로 설정
		sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:mapper/*-mapper.xml"));
		return sqlSessionFactoryBean.getObject();
	}	
	 
	@Bean(name="db2SqlSessionTemplate")
	public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory db2sqlSessionFactory) throws Exception
	{
		return new SqlSessionTemplate(db2sqlSessionFactory);
	}

}
package com.example.demo.oracle.mapper;

import java.util.Optional;

import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.stereotype.Component;

import com.example.demo.oracle.models.entities.OraUserInfo;

@Mapper
public interface UserMapper {
	public OraUserInfo findById(String id);
}
package com.example.demo.oracle.models.entities;

import lombok.Data;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Data
public class OraUserInfo {
	private String userId;
	private String passWord;
}
package com.example.demo.oracle.service;

import java.util.Optional;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.demo.h2.entity.UserInfo;
import com.example.demo.oracle.mapper.UserMapper;
import com.example.demo.oracle.models.entities.OraUserInfo;

@Service
public class UserMapperService{
	
	@Autowired
	private UserMapper userMapper;
	
	public OraUserInfo findById(String id)
	{
		return userMapper.findById(id);
	}

}
<?xml version="1.0" encoding="UTF-8" ?>
<!-- mapper DTD 선언 -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="com.example.demo.oracle.mapper.UserMapper">
	
	<select id="findById" resultType="com.example.demo.oracle.models.entities.OraUserInfo">
		select user_id as userId,
		password as password
		from USER_INFO
	</select>
</mapper>
<?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>
        <!-- 카멜 케이스 VO 매핑 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 쿼리 결과 필드가 null인 경우, 누락이 되서 나오지 않게 설정-->
        <setting name="callSettersOnNulls" value="true"/>
        <!-- 쿼리에 보내는 파라미터가 null인 경우, 오류가 발생하는 것 방지 -->
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>
</configuration>
댓글 , 엮인글
민(民)덕(德)
블로그 이미지 민덕 님의 블로그
MENU
CATEGORY
VISITOR 오늘 / 전체

티스토리툴바