This is a sample code of a web application that handles multiple databases using Spring Boot.
environment
reference
It is assumed that there are three databases handled by the application, one (test_dev3) is the database common to the application, and the other two (test_dev1, test_dev2) are treated as multi-tenant. The schemas of the test_dev1 and test_dev2 databases are the same, but the data to be stored is different.
In this sample application, the value of the request parameter switches between the test_dev1 and test_dev2 databases. Also, since it is a sample application, the table to be handled has no particular meaning (appropriate). In addition, JpaTransactionManager does not seem to be able to execute transactions that span multiple databases (data sources).
Set the data source in application.yml as follows.
application.yml
spring:
datasource:
dev1:
url: jdbc:postgresql://localhost:5432/test_dev1
username: user_dev1
password: pass1
driverClassName: org.postgresql.Driver
defaultAutoCommit: false
defaultReadOnly: false
validationQuery: SELECT 'dev1'
timeBetweenEvictionRunsMillis: 3600000
dev2:
url: jdbc:postgresql://localhost:5432/test_dev2
username: user_dev2
password: pass2
driverClassName: org.postgresql.Driver
defaultAutoCommit: false
defaultReadOnly: false
validationQuery: SELECT 'dev2'
timeBetweenEvictionRunsMillis: 3600000
dev3:
url: jdbc:postgresql://localhost:5432/test_dev3
username: user_dev3
password: pass3
driverClassName: org.postgresql.Driver
defaultAutoCommit: false
defaultReadOnly: false
validationQuery: SELECT 'dev3'
timeBetweenEvictionRunsMillis: 3600000
jpa:
showSql: true
formatSql: true
logging:
level:
root: INFO
org.springframework: INFO
# org.springframework.orm.jpa.JpaTransactionManager: DEBUG
# org.springframework.transaction: TRACE
org.hibernate: DEBUG
org.hibernate.SQL: DEBUG
org.hibernate.type.descriptor.sql.BasicBinder: TRACE
# org.hibernate.transaction: DEBUG
# org.hibernate.jpa.internal: DEBUG
org.hibernate.event.internal: DEBUG
org.hibernate.engine.transaction.internal: DEBUG
org.hibernate.internal.util: DEBUG
test_dev1
Data source settings for tenant A. The implementation is just to build the data source from the settings in the property file. Entity manager and transaction manager are implemented in different classes.
Dev1DataSourceConfigure
package com.example.datasource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class Dev1DataSourceConfigure {
public static final String DEV1_DATASOURCE = "dev1DataSource";
@ConfigurationProperties(prefix = "spring.datasource.dev1")
@Bean(DEV1_DATASOURCE)
public DataSource dataSource() {
DataSource dev1 = DataSourceBuilder.create().build();
return dev1;
}
}
test_dev2
Data source settings for tenant B. It is the same implementation as tenant A.
Dev2DataSourceConfigure
package com.example.datasource;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class Dev2DataSourceConfigure {
public static final String DEV2_DATASOURCE = "dev2DataSource";
@ConfigurationProperties(prefix = "spring.datasource.dev2")
@Bean(DEV2_DATASOURCE)
public DataSource dataSource() {
DataSource dev2 = DataSourceBuilder.create().build();
return dev2;
}
}
Implement entity manager and transaction manager that refer to the data source for each tenant implemented above. The point of implementation is
MultiDataSourceConfigure
package com.example.datasource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import static com.example.datasource.Dev1DataSourceConfigure.DEV1_DATASOURCE;
import static com.example.datasource.Dev2DataSourceConfigure.DEV2_DATASOURCE;
import static com.example.datasource.MultiDataSourceConfigure.ENTITY_MANAGER;
import static com.example.datasource.MultiDataSourceConfigure.REPOSITORY_PACKAGE;
import static com.example.datasource.MultiDataSourceConfigure.TRANSACTION_MANAGER;
@Configuration
@EnableTransactionManagement(order = 100) // (1)
@EnableJpaRepositories(
basePackages = {REPOSITORY_PACKAGE},
entityManagerFactoryRef = ENTITY_MANAGER,
transactionManagerRef = TRANSACTION_MANAGER
)
public class MultiDataSourceConfigure {
public static final String REPOSITORY_PACKAGE = "com.example.repository.tenantds";
public static final String ENTITY_PACKAGES = "com.example.entity.tenantds";
public static final String ENTITY_MANAGER = "multiEntityManagerFactory";
public static final String TRANSACTION_MANAGER = "multiTransactionManager";
public static final String MULTI_DATASOURCE_PU = "multiDataSourcePersistenceUnit";
public static final String MULTI_DATASOURCE = "multiDataSource";
@Autowired
@Qualifier(DEV1_DATASOURCE)
private DataSource dev1;
@Autowired
@Qualifier(DEV2_DATASOURCE)
private DataSource dev2;
@Bean(MULTI_DATASOURCE)
public RoutingDataSourceResolver multiDataSource() { // (2)
RoutingDataSourceResolver resolver = new RoutingDataSourceResolver();
//Set the data source to switch
Map<Object, Object> dataSources = new HashMap<Object,Object>();
dataSources.put(DEV1_DATASOURCE, dev1);
dataSources.put(DEV2_DATASOURCE, dev2);
resolver.setTargetDataSources(dataSources);
resolver.setDefaultTargetDataSource(dev1);
return resolver;
}
@Bean(ENTITY_MANAGER)
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean emf = builder
.dataSource(multiDataSource())
.persistenceUnit(MULTI_DATASOURCE_PU)
.packages(ENTITY_PACKAGES)
.build();
return emf;
}
@Bean(TRANSACTION_MANAGER)
public PlatformTransactionManager transactionManager(
@Autowired @Qualifier(ENTITY_MANAGER) EntityManagerFactory entityManagerFactory) {
JpaTransactionManager jtm = new JpaTransactionManager();
jtm.setEntityManagerFactory(entityManagerFactory);
//jtm.setDataSource(multiDataSource());
return jtm;
}
}
Implement the process of inheriting and switching the AbstractRoutingDataSource of Spring Framework.
RoutingDataSourceResolver
package com.example.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import static com.example.datasource.Dev1DataSourceConfigure.DEV1_DATASOURCE;
import static com.example.datasource.Dev2DataSourceConfigure.DEV2_DATASOURCE;
public class RoutingDataSourceResolver extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
if (MultiDataSourceContextHolder.getTenantType() == null) {
return DEV1_DATASOURCE;
}
//Tenant and data source mapping
switch (MultiDataSourceContextHolder.getTenantType()) {
case TENANT_A:
return DEV1_DATASOURCE;
case TENANT_B:
return DEV2_DATASOURCE;
default:
throw new RuntimeException("unknown tenant");
}
}
}
MultiDataSourceContextHolder
package com.example.datasource;
public class MultiDataSourceContextHolder {
private static ThreadLocal<TenantType> contextHolder = new ThreadLocal<>();
public static void setTenantType(TenantType tenantType) {
if (tenantType == null) {
throw new NullPointerException();
}
contextHolder.set(tenantType);
}
public static TenantType getTenantType() {
return contextHolder.get();
}
public static void clearTenantType() {
contextHolder.remove();
}
}
Enum that manages the type of tenant.
TenantType
package com.example.datasource;
import java.util.Arrays;
import java.util.Optional;
public enum TenantType {
/* test_dev1 */
TENANT_A("aaa"),
/* test_dev2 */
TENANT_B("bbb")
;
private final String tenantName;
TenantType(String tenantName) {
this.tenantName = tenantName;
}
public String getTenantName() {
return this.tenantName;
}
public static TenantType byName(final String tenantName) {
Optional<TenantType> tenantType = Arrays.stream(TenantType.values())
.filter(t -> t.getTenantName().equals(tenantName))
.findFirst();
if (!tenantType.isPresent()) {
throw new IllegalStateException("unknown tenant name : [" + tenantName + "]");
}
return tenantType.get();
}
}
There are several ways to switch data sources, but this sample application uses the Spring AOP feature. Set for the method with the argument "tenant" of the class with the following annotation. The data source is switched by the value of this argument called tenant.
SwitchingDataSource
package com.example.aop;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.TYPE})
public @interface SwitchingDataSource {
}
Adjust the Order annotation so that the data source is switched before the transaction. The point of implementation is
SwitchingDataSourceAop
package com.example.aop;
import com.example.datasource.MultiDataSourceContextHolder;
import com.example.datasource.TenantType;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
@Order(99) // (1)
@Aspect
@Component
public class SwitchingDataSourceAop {
@Around("@annotation(swds) && args(tenant,..)") // (2)
public Object switchingForMethod(ProceedingJoinPoint pjp, SwitchingDataSource swds, String tenant) throws Throwable {
try {
switching(tenant);
Object result = pjp.proceed();
return result;
} finally {
clear();
}
}
@Around("@within(swds) && args(tenant,..)") // (3)
public Object switchingForClass(ProceedingJoinPoint pjp, SwitchingDataSource swds, String tenant) throws Throwable {
try {
switching(tenant);
Object result = pjp.proceed();
return result;
} finally {
clear();
}
}
private void switching(String tenant) {
TenantType tenantType = TenantType.byName(tenant);
MultiDataSourceContextHolder.setTenantType(tenantType);
}
private void clear() {
MultiDataSourceContextHolder.clearTenantType();
}
}
Since there is no need to switch data sources common to applications, this class even implements entity manager and transaction manager. By the way, the Primary annotation is added so that this setting becomes the default.
SingleDataSourceConfigure
package com.example.datasource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import java.util.Properties;
import static com.example.datasource.SingleDataSourceConfigure.ENTITY_MANAGER;
import static com.example.datasource.SingleDataSourceConfigure.REPOSITORY_PACKAGES;
import static com.example.datasource.SingleDataSourceConfigure.TRANSACTION_MANAGER;
@Configuration
@EnableTransactionManagement(order = 101)
@EnableJpaRepositories(
basePackages = {REPOSITORY_PACKAGES},
entityManagerFactoryRef = ENTITY_MANAGER,
transactionManagerRef = TRANSACTION_MANAGER
)
public class SingleDataSourceConfigure {
public static final String REPOSITORY_PACKAGES = "com.example.repository.appds";
public static final String ENTITY_PACKAGES = "com.example.entity.appds";
public static final String ENTITY_MANAGER = "entityManagerFactory";
public static final String TRANSACTION_MANAGER = "transactionManager";
public static final String SINGLE_DATASOURCE_PU = "singleDataSourcePersistenceUnit";
public static final String SINGLE_DATASOURCE = "singleDataSource";
@Primary
@ConfigurationProperties(prefix = "spring.datasource.dev3")
@Bean(SINGLE_DATASOURCE)
public DataSource singleDataSource() {
DataSource dataSource = DataSourceBuilder.create().build();
return dataSource;
}
@Primary
@Bean(ENTITY_MANAGER)
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder) {
LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
emf.setDataSource(singleDataSource());
emf.setPersistenceUnitName(SINGLE_DATASOURCE_PU);
emf.setPackagesToScan(ENTITY_PACKAGES);
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
emf.setJpaVendorAdapter(vendorAdapter);
emf.setJpaProperties(additionalProperties());
return emf;
}
@Primary
@Bean(TRANSACTION_MANAGER)
public PlatformTransactionManager transactionManager(
@Autowired @Qualifier(ENTITY_MANAGER) EntityManagerFactory entityManagerFactory) {
JpaTransactionManager jtm = new JpaTransactionManager();
jtm.setEntityManagerFactory(entityManagerFactory);
//jtm.setDataSource(singleDataSource());
return jtm;
}
@Bean
public PersistenceExceptionTranslationPostProcessor exceptionTranslation(){
return new PersistenceExceptionTranslationPostProcessor();
}
private Properties additionalProperties() {
Properties properties = new Properties();
properties.setProperty("hibernate.show_sql", "true");
properties.setProperty("hibernate.format_sql", "true");
properties.setProperty("hibernate.use_sql_comments", "true");
properties.setProperty("hibernate.generate_statistics", "false");
return properties;
}
}
As shown below, the tenant and application common packages are managed separately. Note that service classes higher than entities and repositories do not need to be separated because it is not necessary to separate packages.
com.example
|
+--- entity
| |
| +--- tenantds //tenant datasource
| | |
| | +--- Todo
| |
| +--- appds //application datasource
| |
| +--- Memo
|
+--- repository
| |
| +--- tenantds
| | |
| | +--- TodoRepository
| |
| +--- appds
| |
| +--- MemoRepository
|
+--- service
|
+--- impl
| |
| +--- TodoService
| +--- MemoService
|
+--- TodoServiceImpl
+--- MemoServiceImpl
There is nothing special about the implementation, just separate the packages.
Todo
package com.example.entity.tenantds;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import java.util.Date;
@Entity
@Table(name="todo")
@SequenceGenerator(name = "todo_id_gen", sequenceName = "todo_id_seq", allocationSize = 1)
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Todo {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name="title")
private String title;
@Column(name="done")
private Boolean done;
@Column(name="updated")
@Temporal(TemporalType.TIMESTAMP)
private Date updated;
}
Memo
package com.example.entity.appds;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import java.util.Date;
@Entity
@Table(name="memo")
@SequenceGenerator(name = "memo_id_gen", sequenceName = "memo_id_seq", allocationSize = 1)
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Memo {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name="title")
private String title;
@Column(name="description")
private String description;
@Column(name="done")
private Boolean done;
@Column(name="updated")
@Temporal(TemporalType.TIMESTAMP)
private Date updated;
}
TodoRepository
package com.example.repository.tenantds;
import com.example.entity.multids.Todo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import javax.persistence.LockModeType;
public interface TodoRepository extends JpaRepository<Todo, Long> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
Todo findById(Long id);
}
MemoRepository
package com.example.repository.appds;
import com.example.entity.singleds.Memo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import javax.persistence.LockModeType;
public interface MemoRepository extends JpaRepository<Memo, Long> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
Memo findById(Long id);
}
Switch the data source with the value of the request parameter "tenant".
Example
/todo/list?tenant=<Tenant code>
TodoController
package com.example.controller;
import com.example.entity.tenantds.Todo;
import com.example.service.TodoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.Date;
import java.util.List;
@RequestMapping(path = "todo")
@RestController
public class TodoController {
@Autowired
private TodoService service;
@GetMapping(path = "list", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
@ResponseBody
public List<Todo> list(
@RequestParam(value = "tenant", required = true) String tenant) throws Exception {
List<Todo> lists = service.list(tenant);
return lists;
}
@GetMapping(path = "update", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
@ResponseBody
public Todo update(
@RequestParam(value = "tenant", required = true) String tenant,
@RequestParam(value = "id", required = true) Long id,
@RequestParam(value = "title") String title,
@RequestParam(value = "done", defaultValue = "FALSE") Boolean done,
@RequestParam(value = "wt", defaultValue = "30") Long waittime) throws Exception {
Todo todo = service.lockAndUpdate(tenant, id, title, done, new Date(), waittime);
return todo;
}
@GetMapping(path = "insert", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
@ResponseBody
public Todo insert(
@RequestParam(value = "tenant", required = true) String tenant,
@RequestParam(name = "title", required = true) String title) throws Exception {
Todo todo = service.insert(tenant, title, Boolean.FALSE, new Date());
return todo;
}
}
TodoService
package com.example.service;
import com.example.entity.tenantds.Todo;
import java.util.Date;
import java.util.List;
public interface TodoService {
List<Todo> list(String tenant);
Todo lockAndUpdate(String tenant, Long id, String title, Boolean done, Date updated, Long waittime) throws Exception;
Todo insert(String tenant, String title, Boolean done, Date updated) throws Exception;
}
There are some points in the implementation.
(1) and (3) are the implementations required to switch data sources in AOP.
TodoServiceImpl
package com.example.service.impl;
import com.example.aop.SwitchingDataSource;
import com.example.entity.tenantds.Todo;
import com.example.repository.tenantds.TodoRepository;
import com.example.service.TodoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.Date;
import java.util.List;
import java.util.concurrent.TimeUnit;
import static com.example.datasource.MultiDataSourceConfigure.TRANSACTION_MANAGER;
@Service
@SwitchingDataSource // (1)
@Transactional(readOnly = true, timeout = 10, transactionManager = TRANSACTION_MANAGER) // (2)
public class TodoServiceImpl implements TodoService {
@Autowired
private TodoRepository repository;
@Override
public List<Todo> list(String tenant) { // (3)
Sort sort = new Sort(new Order(Sort.Direction.DESC, "updated"), new Order(Sort.Direction.DESC, "id"));
List<Todo> lists = repository.findAll(sort);
return lists;
}
@Transactional(readOnly = false, timeout = 120, rollbackFor = Exception.class, transactionManager = TRANSACTION_MANAGER)
@Override
public Todo lockAndUpdate(String tenant, Long id, String title, Boolean done, Date updated, Long waittime) throws Exception {
Todo todo = repository.findById(id);
todo.setTitle(title);
todo.setDone(done);
todo.setUpdated(updated);
//Time-out test code
try {
TimeUnit.SECONDS.sleep(waittime);
} catch (InterruptedException e) {
System.out.println("timeout:" + e.getMessage());
throw new Exception("timeout");
}
repository.save(todo);
return todo;
}
@Transactional(readOnly = false, rollbackFor = Exception.class, transactionManager = TRANSACTION_MANAGER)
@Override
public Todo insert(String tenant, String title, Boolean done, Date updated) throws Exception {
Todo todo = Todo.builder().title(title).done(done).updated(updated).build();
repository.save(todo);
return todo;
}
}
Recommended Posts