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

		<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

key value
key value
user001 Test user 001

Post-implementation DB

key value
key value
user001 Test user 001

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

key value
key value
database database
key value
user001 Test user 001
key value
user002 Test user 002

Post-implementation DB

key value
user001 Test user 001
user002 Test user 002
key value
database database
user001 Test user 001
user002 Test user 002
key value
user001 Test user 001
key value
user002 Test user 002

9. See

[Spring Boot] Dynamic data source change

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

Recommended Posts

Dynamically switch the database to connect to
[Java / PostgreSQL] Connect the WEB application to the database
Ask Sota to speak back the database values
How to delete the database when recreating the application
The code I used to connect Rails 3 to PostgreSQL 10
3. Create a database to access from the web module
[Java] Connect to MySQL
Change the database (MySQL) primary key to any column.
Swipe to switch screens
[Output] About the database
Dynamically switch writer according to reader value with spring-batch
Java-database connection Java-MySQL connection ⑤: Connect to the database (MySQL) to search table information and display search results / September 2017
How to dynamically change the column name acquired by MyBatis
[JDBC] I tried to access the SQLite3 database from Java.
How to connect the strings in the List separated by commas
Connect to database with spring boot + spring jpa and CRUD operation
How to dynamically switch JDK when building Java in Gradle
How to switch Java in the OpenJDK era on Mac
How to dynamically switch between FIN and RST in Netty
[Docker] How to forcibly disconnect the session connected to the database [Postgres]
[Rails] How to display information stored in the database in view
How to check the database of apps deployed on Heroku
9 Corresponds to the return value
Dynamically assign Controller to JavaFX-fxml
Switch from slim3-gen to slim3-gen-jsr269
12 Corresponds to the while statement
Migrate GitBucket database to postgreSQL
Connect from Java to PostgreSQL
4 Add println to the interpreter
Connect to DB with Java
Connect to MySQL 8 with Java
Connect CentOS 7 to L2TP VPN
Let's understand the switch statement!
Connect to oracle with eclipse!
[Android] Connect to MySQL (unfinished)
Switch the version of bundler
Input to the Java console
[Swift] How to dynamically change the height of the toolbar on the keyboard
[PostgreSQL] If you want to delete the Rails app, delete the database first!
Ubuntu on WSL2: Cannot connect to the Docker daemon at unix
Connecting to a database with Java (Part 1) Maybe the basic method
Connect IoT devices to the cloud using gateway and subdevice scenarios
[Rails] How to reset the database in production environment (Capistrano version)