[JPA] QueryDSL + multi datasource 설정

1. 접속 DB 1개인 경우

application.yml 에 datasource 정보만 기입하고 따로 config 파일 (class 또는 xml) 을 안 만들어도 spring boot 에서 자동구성해준다.

spring:
  datasource-default:
    hikari:
      jdbc-url: jdbc:h2:~/mytest
      driver-class-name: org.h2.Driver
      username: sa
      password:

 

2. 접속 DB 2개인 경우

테스트를 위한 테이블 및 데이터

create table member(
	id int not null auto_increment,
	name varchar(255) not null,
	email varchar(255) null,
	primary key(id)
)

insert into member (name, email) values ('kim', 'kim@naver.com');
insert into member (name, email) values ('park', 'park@gmail.com');
insert into member (name, email) values ('lee', 'lee@gmail.com');

create table department(
	id int not null auto_increment,
	name varchar(255) not null,
	primary key(id)
)

insert into department (name) values ('development');
insert into department (name) values ('sales');

default DB는 member 테이블을 조회

second DB는 department 테이블을 조회 할 것이다.

controller 파일, entity 파일, repository 파일을 각각 아래 패키지에 작성하였다.

com.example.myjpa <--- member 관련 controller, entity, repository 파일들

com.example.myjpa2 <---department 관련 controller, entity, repository 파일들

패키지 구조

 

(1) application.yml 작성

spring:
  datasource-default:
    hikari:
      jdbc-url: jdbc:h2:~/mytest
      driver-class-name: org.h2.Driver
      username: sa
      password: 
  datasource-second:
    hikari:
      jdbc-url: jdbc:h2:~/mytest2
      driver-class-name: org.h2.Driver
      username: sa
      password:

 

(2) datasource config 파일 작성

DB 별로 config 파일을 작성해준다.

package com.example.myjpa.config;

import java.util.HashMap;

import javax.sql.DataSource;

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;

@Configuration
@EnableJpaRepositories(
	    basePackages = "com.example.myjpa.repository",   //repository 경로
	    entityManagerFactoryRef = "defaultEntityManagerFactory", 
	    transactionManagerRef = "defaultTransactionManager"
	)
public class DefaultDatasourceConfig {
	
	@Primary
	@Bean
	@ConfigurationProperties(prefix="spring.datasource-default.hikari")
	public DataSource defaultDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean
	@Primary
	public LocalContainerEntityManagerFactoryBean defaultEntityManagerFactory() {
		LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
		em.setDataSource(defaultDataSource());
        em.setPackagesToScan(new String[] { "com.example.myjpa.entity" }); //entity 경로
        em.setPersistenceUnitName("defaultEntityManager");
	
		HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
        em.setJpaPropertyMap(properties);		
		em.setJpaVendorAdapter(vendorAdapter);
		return em;
	}
	
	@Primary
	@Bean
	public PlatformTransactionManager defaultTransactionManager() {	
		JpaTransactionManager transactionManager = new JpaTransactionManager();
		transactionManager.setEntityManagerFactory(defaultEntityManagerFactory().getObject());
		return transactionManager;
	}
}

defaultDatasource() : application.yml 에서 접속정보를 가져와 datasource 메소드를 작성

defaultEntityManagerFactory() : 여기서 중요한 부분은 entity 패키지 경로와 persistenceUnitName을 지정해주는 것이다.

persistenceUnitName은 JPAQueryFactory 의 entityManager 지정할때 이용된다.

TransactionManager() : 적용할 entityManager 메소드 지정

 

package com.example.myjpa.config;

import java.util.HashMap;

import javax.sql.DataSource;

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;

@Configuration
@EnableJpaRepositories(
	    basePackages = "com.example.myjpa2.repository",   //repository 경로
	    entityManagerFactoryRef = "secondEntityManagerFactory", 
	    transactionManagerRef = "secondTransactionManager"
	)
public class SecondDatasourceConfig {
	
	@Bean
	@ConfigurationProperties(prefix="spring.datasource-second.hikari")
	public DataSource secondDataSource() {
		return DataSourceBuilder.create().build();
	}
	
	@Bean
	public LocalContainerEntityManagerFactoryBean secondEntityManagerFactory() {
		LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
		em.setDataSource(secondDataSource());
        em.setPackagesToScan(new String[] { "com.example.myjpa2.entity" }); //entity 경로
        em.setPersistenceUnitName("secondEntityManager");
        
		HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
		
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", "update");
        properties.put("hibernate.dialect", "org.hibernate.dialect.H2Dialect");
        em.setJpaPropertyMap(properties);		
		em.setJpaVendorAdapter(vendorAdapter);
		return em;
	}
	
	@Bean
	public PlatformTransactionManager secondTransactionManager() {	
		JpaTransactionManager transactionManager = new JpaTransactionManager();
		transactionManager.setEntityManagerFactory(secondEntityManagerFactory().getObject());
		return transactionManager;
	}
}

 

(3) JPAQueryFactory config 작성

@Configuration
public class QuerydslConfig {

    @PersistenceContext(unitName = "defaultEntityManager")
    private EntityManager defaultEntityManager;
    
    @PersistenceContext(unitName = "secondEntityManager")
    private EntityManager secondEntityManager;

    @Bean
    public JPAQueryFactory defaultJpaQueryFactory() {
        return new JPAQueryFactory(defaultEntityManager);
    }
    
    @Bean
    public JPAQueryFactory secondJpaQueryFactory() {
    	return new JPAQueryFactory(secondEntityManager);
    }
}

defaultJpaQueryFactory, secondJpaQueryFactory 는 queryDSL 쿼리 조회시 이용하게 된다.

 

(4) repository 작성

public interface MemberRepository extends JpaRepository<Member, Integer>, MemberRepositoryCustom {

}

public interface MemberRepositoryCustom {

	List<Member> findByNameOrEmail(String type, String keyword);
}

public class MemberRepositoryCustomImpl implements MemberRepositoryCustom {

	@Autowired
	private JPAQueryFactory defaultJpaQueryFactory;
	private QMember qMember = QMember.member;
	
	@Override
	public List<Member> findByNameOrEmail(String type, String keyword) {
		
		List<Member> content = defaultJpaQueryFactory
				.select(qMember)
				.from(qMember)
				.where(qMember.email.contains(keyword))
				.fetch();
		return content;
	}
}

defaultJpaQueryFactory 을 이용하여 조회하고 있다.

 

public interface DepartmentRepository extends JpaRepository<Department, Integer>, DepartmentRepositoryCustom {

}

public interface DepartmentRepositoryCustom {

	List<Department> findByName(String keyword);
}

public class DepartmentRepositoryCustomImpl implements DepartmentRepositoryCustom {

	@Autowired
	private JPAQueryFactory secondJpaQueryFactory;
	private QDepartment qDepartment = QDepartment.department;
	
	@Override
	public List<Department> findByName(String keyword) {
		
		List<Department> content = secondJpaQueryFactory
				.select(qDepartment)
				.from(qDepartment)
				.where(qDepartment.name.contains(keyword))
				.fetch();
		return content;
	}
}

secondJpaQueryFactory 을 이용하여 조회하고 있다.

 

 

테스트

@RestController
@AllArgsConstructor
public class MemberController {

	private MemberRepository memberRepository;
	
	@GetMapping("members")
	public ResponseEntity<List<Member>> selectMember(){
		List<Member> list = memberRepository.findAll();
		return ResponseEntity.ok(list);
	}
	
	@GetMapping("members/search")
	public ResponseEntity<List<Member>> selectMemberByNameOrEmail(
			@RequestParam String type, 
			@RequestParam String keyword){
		List<Member> list = memberRepository.findByNameOrEmail(type, keyword);
		return ResponseEntity.ok(list);
	}
}

호출

http://localhost:8080/members/search?type=1&keyword=gmail

결과

[{"id":2,"name":"park","email":"park@gmail.com"},{"id":3,"name":"lee","email":"lee@gmail.com"}]

 

@RestController
@AllArgsConstructor
public class DepartmentController {

	private DepartmentRepository departmentRepository;
	
	@GetMapping("departments")
	public ResponseEntity<List<Department>> selectDepartment(){
		List<Department> list = departmentRepository.findAll();
		return ResponseEntity.ok(list);
	}
	
	@GetMapping("departments/search")
	public ResponseEntity<List<Department>> selectDepartmentByName(@RequestParam String keyword){
		List<Department> list = departmentRepository.findByName(keyword);
		return ResponseEntity.ok(list);
	}
}

호출

http://localhost:8080/departments/search?keyword=sales

결과

[{"id":2,"name":"sales"}]

 

소스

parkjongyoon/MyJPA-multi-datasource (github.com)