Dynamically switch the database to connect to

I needed to search and update multiple databases in one request, so I investigated how to do it. 2018/05/20 ** I created an article using narayanaJTA here. ** **

0. Overview

Realization goal

--Connect to the database using the connection information prepared in advance --Connect to the database with additional information generated in the logic

environment

• Windows10 Professional
• Java 9.0.4
• PostgreSQL10.3-1 --Library used

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.3.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.tomcat</groupId>
			<artifactId>tomcat-jdbc</artifactId>
		</dependency>

1. Write the connection information in a yaml file and read it

Create DataSourceProperties.java and DataSourcePropertyDetail.java used to get connection information and read the information of ʻapplication.yml`

package com.example.demo.common;

import java.util.List;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Component
@ConfigurationProperties("demo.datasource")
public class DataSourceProperties {

	/**
 * Keep the initial value of connection information
	 */
	private DataSourcePropertyDetail base;

	/**
 * Keep a list of connection information. Insufficient information is set from the initial value
	 */
	private List<DataSourcePropertyDetail> list;

 / ** getter setter omitted * /

}

For the actual setting value, get the one defined in DataSourcePropertyDetail.java. This time, define the minimum four of driverClassName, url, username, password, and defaultAutoCommit. Also, prepare a constructor for deep copy in consideration of the case of adding additional information generated in the logic.

package com.example.demo.common;

import java.util.Objects;

public class DataSourcePropertyDetail {

	private String name;

	private String driverClassName;
	private String url;
	private String username;
	private String password;
	private Boolean defaultAutoCommit;

	public DataSourcePropertyDetail(){
	}

	public DataSourcePropertyDetail(DataSourcePropertyDetail prop){
		if (Objects.isNull(prop)) {
			return;
		}
		this.driverClassName = prop.driverClassName;
		this.url = prop.url;
		this.username = prop.username;
		this.password = prop.password;
		this.defaultAutoCommit = prop.defaultAutoCommit;
	}

 / ** getter setter omitted * /

}

Add the information for reading the DataSourceProperties andDataSourcePropertyDetail created above to ʻapplication.yml`.

demo:
  datasource:
    base:
      driver-class-name: org.postgresql.Driver
      url: jdbc:postgresql://localhost:5432/postgres
      username: postgres
      password: postgres
      default-auto-commit: false
    list:
    - name: postgres
    - name: user
      url: jdbc:postgresql://localhost:5432/user
      username: user
      password: user
    - name: database
      url: jdbc:postgresql://localhost:5432/database
      username: database
      password: database

2. Actually switch the data source

Two parts are required to switch the data source. Prepare a part that creates a data source from connection information and a part that determines the data source to be used when connecting.

2.1 Parts that create a data source from connection information

Basically, the necessary processing may be described in DataSourceConfig, but since there are some processing that you want to use repeatedly this time, DataSourceUtil is prepared to summarize the processing to be used in the future.

package com.example.demo.common;

import java.util.HashMap;

import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;

@Component
@Configuration
public class DataSourceConfig {

	@Autowired
	DataSourceProperties properties;

	@Bean
	@Primary
	public DynamicRoutingDataSourceResolver getRoutingdataSource() {
		DynamicRoutingDataSourceResolver resolver = new DynamicRoutingDataSourceResolver();
		DataSource defDataSource = DataSourceUtil.getDataSource(properties.getBase(), properties.getBase());
		Map<Object, Object> datasources = new HashMap<Object,Object>();
		datasources.put(properties.getBase().getName(), defDataSource);
		properties.getList().forEach(prop -> datasources.put(prop.getName(), getDataSource(prop)));

		resolver.setTargetDataSources(datasources);

		resolver.setDefaultTargetDataSource(defDataSource);

		return resolver;
	}

	private DataSource getDataSource(DataSourcePropertyDetail prop) {
		return DataSourceUtil.getDataSource(properties.getBase(), prop);
	}
}

package com.example.demo.common;

import java.util.Objects;
import java.util.function.Consumer;

import org.apache.tomcat.jdbc.pool.DataSource;

public class DataSourceUtil {

	public static DataSource getDataSource(DataSourcePropertyDetail base, DataSourcePropertyDetail prop) {
		DataSource datasource = new org.apache.tomcat.jdbc.pool.DataSource();
		skipIfNull(defaultIfEmpty(prop.getDriverClassName(), base.getDriverClassName()), datasource::setDriverClassName);
		skipIfNull(defaultIfEmpty(prop.getUrl(), base.getUrl()), datasource::setUrl);
		skipIfNull(defaultIfEmpty(prop.getUsername(), base.getUsername()), datasource::setUsername);
		skipIfNull(defaultIfEmpty(prop.getPassword(), base.getPassword()), datasource::setPassword);
		skipIfNull(defaultIfEmpty(prop.getDefaultAutoCommit(), base.getDefaultAutoCommit()), datasource::setDefaultAutoCommit);
		return datasource;
	}

	public static<T> T defaultIfEmpty(T obj, T def) {
		if (Objects.isNull(obj)) {
			return def;
		}

		return obj;
	}

	public static <T> void skipIfNull(T obj, Consumer<T> consumer) {
		if (Objects.isNull(obj)) {
			return;
		}
		consumer.accept(obj);
	}
}

2.2 Parts that determine the data source to use when connecting

Three to prepare ʻDynamicRoutingDataSourceResolver that inherits AbstractRoutingDataSource DataBaseSelectHolder that holds the selected connection information on the local thread DataBaseSelectInfo, which is a structure for connection information

package com.example.demo.common;

import java.util.Objects;
import java.util.Optional;
import java.util.function.Consumer;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicRoutingDataSourceResolver extends AbstractRoutingDataSource {

	@Autowired
	DataSourceProperties properties;

	@Override
	protected Object determineCurrentLookupKey() {
		DataBaseSelectInfo key = DataBaseSelectHolder.getDataBaseInstanceInfo();
		if (Objects.isNull(key)) {
			return null;
		}
		return key.getDataSourceName();
	}

	private DataBaseSelectInfo determineCurrentLookupKeyRaw() {
		return DataBaseSelectHolder.getDataBaseInstanceInfo();
	}

	@Override
	protected DataSource determineTargetDataSource() {
		DataBaseSelectInfo info = determineCurrentLookupKeyRaw();
 // If there is additional information, create a data source based on the additional information
 // Call the method of the inheriting class if there is no additional information
		if (info != null && info.isAdditional()) {
			return createNewDataSouce(info);
		}
		return super.determineTargetDataSource();
	}

 // Create a data source with additional information based on the specified properties
	private DataSource createNewDataSouce(DataBaseSelectInfo info) {
		Optional<DataSourcePropertyDetail> op = properties.getList()
			.stream()
			.filter(prop -> Objects.equals(prop.getName(), info.getDataSourceName()))
			.findFirst();
		if (op.isPresent()) {
			DataSourcePropertyDetail prop = new DataSourcePropertyDetail(op.get());

			DataSourceUtil.skipIfNull(info.getAdditionalUrl(),
				 (url) -> prop.setUrl(prop.getUrl() + url));
			DataSourceUtil.skipIfNull(info.getAdditionalUserName(),
				 (name) -> prop.setUsername(prop.getUsername() + name));
			DataSourceUtil.skipIfNull(info.getAdditionalPassword(),
				 (password) -> prop.setPassword(prop.getPassword() + password));

			return DataSourceUtil.getDataSource(properties.getBase(), prop);
		}
		return super.determineTargetDataSource();
	}
}

package com.example.demo.common;

import org.springframework.util.Assert;

public class DataBaseSelectHolder {
	 private static ThreadLocal<DataBaseSelectInfo> contextHolder = new ThreadLocal<DataBaseSelectInfo>();

	    public static void setDataBaseInstanceInfo(DataBaseSelectInfo datasource) {
	        Assert.notNull(datasource, "datasource cannot be null.");
	        contextHolder.set(datasource);
	    }

	    public static void setDataBaseInstanceInfo(String dataSourceName) {
	        Assert.notNull(dataSourceName, "dataSourceName cannot be null.");
	        contextHolder.set(new DataBaseSelectInfo(dataSourceName));
	    }

	    public static void setDataBaseInstanceInfo(String dataSourceName, String additionalUrl,
			String additionalUserName, String additionalPassword) {
	        Assert.notNull(dataSourceName, "dataSourceName cannot be null.");
	        contextHolder.set(new DataBaseSelectInfo(dataSourceName, additionalUrl,
			additionalUserName, additionalPassword));
	    }

	    public static DataBaseSelectInfo getDataBaseInstanceInfo() {
	        return contextHolder.get();
	    }

	    public static void clear() {
	        contextHolder.remove();
	    }
}

package com.example.demo.common;

import java.util.Objects;

public class DataBaseSelectInfo {

	private String dataSourceName;

	private String additionalUrl;

	private String additionalUserName;

	private String additionalPassword;

	public DataBaseSelectInfo(String dataSourceName, String additionalUrl
		, String additionalUserName, String additionalPassword) {
		this.dataSourceName = dataSourceName;
		this.additionalUrl = additionalUrl;
		this.additionalUserName = additionalUserName;
		this.additionalPassword = additionalPassword;
	}

 / ** getter setter omitted * /

	/**
 * Method to determine if there is additional information
	 */
	public boolean isAdditional() {
		return !(Objects.isNull(this.additionalUrl) 
			&& Objects.isNull(this.additionalUserName) 
			&& Objects.isNull(this.additionalPassword));
	}

}

3. Actually use

Create Controller, Service and DAO and actually operate them. Use Propagation.REQUIRES_NEW to suspend the current transaction and create another transaction in the Transactional property.

Confirm that it is rolled back at the time of exception with DemoController # exception Check that multiple DBs have been updated with DemoController # demo

package com.example.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;

import com.example.demo.common.DataBaseSelectHolder;
import com.example.demo.service.DemoService;

@Controller
@Transactional(propagation=Propagation.REQUIRES_NEW)
public class DemoController {

	@Autowired
	private DemoService service;

	@RequestMapping("exception")
	public String exception() {
		DataBaseSelectHolder.setDataBaseInstanceInfo("postgres");
		service.insertException();
		return "empty";
	}

	@RequestMapping("demo")
	public String demo() {
		DataBaseSelectHolder.setDataBaseInstanceInfo("postgres");
		service.insert();
		return "empty";
	}

}

I do not know whether it is the specification of Spring Transactional or the specification of AOP itself because I have not studied, but since the transaction is not performed at the time of method call in the same instance, DB switching within the transaction is as follows You are using the method. --Transactional is added to the DAO class method itself --Hold and call your own instance I think it would be better to prepare a class for each transaction, but this time I will not think about it due to various circumstances.

package com.example.demo.service;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.example.demo.common.DataBaseSelectHolder;
import com.example.demo.dao.DemoDAO;

@Service
@Transactional(propagation=Propagation.REQUIRES_NEW)
public class DemoService {

	@Autowired
	private DemoDAO demoDao;

	@Autowired
	private DemoService self;

	public void insertException() {
 // DB rolled back with key duplication error
		DataBaseSelectHolder.setDataBaseInstanceInfo("database");
		List<Map<String, Object>> list = demoDao.selectTransactional();
		list.stream().forEach(demoDao::insert);
		list.stream().forEach(demoDao::insert);
	}

	public void insert() {

		DataBaseSelectHolder.setDataBaseInstanceInfo("user", "001", null, null);
		List<Map<String, Object>> list1 = demoDao.selectTransactional();
		list1.stream().forEach(demoDao::insert);

		DataBaseSelectHolder.setDataBaseInstanceInfo("user", "002", null, null);
		List<Map<String, Object>> list2 = self.getList();
		list2.stream().forEach(demoDao::insert);

		DataBaseSelectHolder.setDataBaseInstanceInfo("database");
		List<Map<String, Object>> list3 = demoDao.select();
		self.insertOther(list3);
	}

	public List<Map<String, Object>> getList() {
		return demoDao.select();
	}

	public void insertOther(List<Map<String, Object>> list) {
		list.stream().forEach(demoDao::insert);
	}

}

package com.example.demo.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Mapper
public interface DemoDAO {

	@Select("SELECT key, value FROM demo")
	List<Map<String, Object>> select();

	@Select("SELECT key, value FROM demo")
	@Transactional(propagation=Propagation.REQUIRES_NEW)
	List<Map<String, Object>> selectTransactional();

	@Insert("INSERT INTO demo (key, value) VALUES ( #{map.key}, #{map.value})")
	int insert(@Param("map")Map<String, Object> map);

	@Update("UPDATE demo SET value = #{map.value} WHERE key = #{map.key}")
	int update(@Param("map")Map<String, Object> map);
}

3.1 DemoController#exception http://localhost:8080/exception ~~ INSERT the SELECT result of user01 into postgres, but an exception occurs in the second INSERT and it is rolled back. ~~ ** The test data is bad and no rollback is actually performed. ** **

Pre-implementation DB

• postgres

• user01

Post-implementation DB

• postgres

• user01

3.2 DemoController#demo http://localhost:8080/demo INSERT the SELECT results of user01 and user02 into postgres. Then SELECT postgres and INSERT into database within the same transaction.

Pre-implementation DB

• postgres

• database

• user01

• user02

Post-implementation DB

• postgres

• database

• user01

• user02

9. See

[Spring Boot] Dynamic data source change

Try transaction (@Transactional) propagation attribute (propagation) in Spring Boot