데이터베이스 1개 연결
SpringBoot에서 제공하는 형식에 따라 spring 하위에 yaml 파일을 설정하기 때문에 별도 Configuration 설정을 불필요하다.
spring
datasource:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://localhost:8080;databaseName={dbName};encrypt=true;trustServerCertificate=true
username : {ID}
password: {PW}
jpa:
mapping-resources: {native query path}
데이터베이스 2개 연결
nativeQuery xml 파일은 spring에 설정해두고 multi라는 property 값을 설정하여 datasource를 나누었다.
위에서 1개 연결 설정시에는 SpringBoot에서 제공하는 형식에 맞추기 때문에 「url(또는 URL) : url 주소」로 설정하지만,
2개의 DB에 연결을 할때는 「jdbc-url : url 주소」 로 설정해주어야 한다고 한다.
이유는 그것이 HikariCP의 Database URL 설정이니까! (끄덕)
그냥 url로 했다가 url 값을 못찾는 오류가 발생했었으니 조심하자.
spring:
jpa:
mapping-resources: /query/nativeQuery.xml
multi:
datasource:
main:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url: jdbc:sqlserver://localhost:8080;databaseName={dbName1};encrypt=true;trustServerCertificate=true
username: {ID}
password: {PW}
base-package: {repository path ex)com.beige0905.test.repository}
jpa:
database-platform: org.hibernate.dialect.SQLServer2012Dialect
show-sql: true
format-sql: true
use-sql-comments: true
hibernate.ddl-auto: update
generate-ddl: false
second:
driver-class-name:
jdbc-url: jdbc:sqlserver://localhost:8080;databaseName={dbName2};encrypt=true;trustServerCertificate=true
username: {ID}
password: {PW}
base-package: {repository path ex)com.beige0905.test.repository}
jpa:
database-platform: org.hibernate.dialect.SQLServer2012Dialect
show-sql: true
format-sql: true
use-sql-comments: true
hibernate.ddl-auto: update
generate-ddl: false
MainDBConfiguration
package com.beige0905.test.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Properties;
@Configuration
@EnableJpaRepositories(basePackages = {"${multi.datasource.main.base-package}"},
entityManagerFactoryRef = "EntityManagerFactory",
transactionManagerRef = "TransactionManager")
public class MPMainDatabaseConfig {
@Value("${spring.jpa.mapping-resources}")
String namedQuery;
@Value("${multi.datasource.main.base-package}")
String basePackage;
@Value("${multi.datasource.main.jpa.database-platform}")
String dbPlatform;
@Primary
@Bean(name = "RepositoryDataSource")
@ConfigurationProperties(prefix = "multi.datasource.main")
public DataSource getDataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "EntityManagerFactory")
public LocalContainerEntityManagerFactoryBean masterEntityManager(@Qualifier("RepositoryDataSource") DataSource dataSource) {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan(basePackage);
em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
em.setMappingResources(namedQuery);
Properties jpaProperties = new Properties();
jpaProperties.put("hibernate.dialect", dbPlatform);
em.setJpaProperties(jpaProperties);
return em;
}
@Primary
@Bean(name = "TransactionManager")
public PlatformTransactionManager transactionManager(@Qualifier("EntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager(entityManagerFactory.getObject());
transactionManager.setNestedTransactionAllowed(true);
return transactionManager;
}
}
second Configuration main Configuration과 동일하지만 Bean name을 다르게 설정해야하고 @Primary annotation은 제외한다
package com.beige0905.test.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Properties;
@Configuration
@EnableJpaRepositories(basePackages = {"${multi.datasource.second.base-package}"},
entityManagerFactoryRef = "SubEntityManagerFactory",
transactionManagerRef = "SubTransactionManager")
public class MPEntryLogConfig {
@Value("${multi.datasource.second.base-package}")
String basePackage;
@Value("${multi.datasource.second.jpa.database-platform}")
String dbPlatform;
@Bean(name = "SubRepositoryDataSource")
@ConfigurationProperties(prefix="multi.datasource.second")
public DataSource getDataSource() { return DataSourceBuilder.create().build(); }
@Bean(name = "SubEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean masterEntityManager(@Qualifier("SubRepositoryDataSource") DataSource dataSource) {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan(basePackage);
em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
Properties jpaProperties = new Properties();
jpaProperties.put("hibernate.dialect", dbPlatform);
em.setJpaProperties(jpaProperties);
return em;
}
@Bean(name = "SubTransactionManager")
public PlatformTransactionManager transactionManager(@Qualifier("SubEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager(entityManagerFactory.getObject());
transactionManager.setNestedTransactionAllowed(true);
return transactionManager;
}
}
MainVo package com.beige0905.test.repository.vo;
import lombok.*;
import javax.persistence.*;
import java.sql.Timestamp;
@Getter
@Setter
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "product")
public class MainVo {
public enum STATUS {
AVAILABLE,
UNAVAILABLE
}
@Id
@Column(name = "product_no")
private int productNo;
@Column(name = "product_name")
private String productName;
@UpdateTimestamp
@Column(name = "update_date")
private Timestamp updateDate = Timestamp.valueOf(LocalDateTime.now());
@Column(name ="status")
@Enumerated(EnumType.STRING)
private STATUS status;
}
MainRepository
package com.beige0905.test.repository;
import com.beige0905.test.repository.vo.MainVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
public interface MainRepository extends JpaRepository<MainVo, String> {
@Transactional
@Modifying
@Query("update MainVo v set v.status = ?1 where v.no = ?2")
int updateStatus(MainVo.STATUS processing, int no);
}
SecondVo
package com.beige0905.test.second.repository.vo;
import lombok.*;
import javax.persistence.*;
import java.sql.Timestamp;
@Getter
@Setter
@Entity
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "user_info")
public class SecondVo {
@Id
@Column(name = "user_no")
private int userNo;
@Column(name = "user_name")
private String userName;
@Column(name = "join_date")
private Timestamp joinDate;
}
SecondRepository
package com.beige0905.test.secondDb.repository;
import com.beige0905.test.secondDb.repository.vo.SecondVo;
import org.springframework.data.jpa.repository.JpaRepository;
public interface SecondRepository extends JpaRepository<SecondVo, String> {
}
vo, repository 생성시 Main과 Second 별도 패키지를 사용해야한다.
참고자료
https://velog.io/@lehdqlsl/SpringBoot-JPA-Multiple-Databases-%EC%84%A4%EC%A0%95
SpringBoot JPA Multiple Databases 설정
다중 DB는 Spring boot 처럼 Auto Configuration되지 않음설정파일(application.yml 또는 application.properties) 값을 읽어와서 연동 할 DB 수 만큼 Datasource를 수동 설정해야함설정파일 대신 코드로 직접 설
velog.io
https://wiki.webnori.com/pages/viewpage.action?pageId=9798284
다중DB 환경설정 - WebFrameWork - WebNORI - IT개발문화 연구소
wiki.webnori.com
https://jojoldu.tistory.com/296
Spring Boot & HikariCP Datasource 연동하기
안녕하세요? 이번 시간엔 Spring Boot & Hikari Datasource 연동하기 예제를 진행해보려고 합니다. 모든 코드는 Github에 있기 때문에 함께 보시면 더 이해하기 쉬우실 것 같습니다. (공부한 내용을 정리하
jojoldu.tistory.com
