Previously I tried to support multiple databases using DynamicAbstractRoutingDataSource, As I confirmed later, rollback did not work well, so I tried another method to support multiple data sources.
If you want something that works first, please refer to the source link at the bottom of the page.
Introductory books on Spring Boot introduced distributed transactions using Atomikos, and there were many samples using Atomicos for information on the net. However, since the handling of XID, which is the unique key of each data source, was suspicious when the connection destination DB was variable (remember), this time we will support multiple databases using Narayana JTA.
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-narayana</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>com.integralblue</groupId>
<artifactId>log4jdbc-spring-boot-starter</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
</dependencies>
--Data can be read by connecting to multiple data sources at the same time. --Distributed transactions can be realized by TwoPahseCommit --Corresponding to the case where the number of data source connections is variable (for example, if you have a database for each branch code in the branch list received in the request)
The cooperation between Spring Boot and Narayana itself can be realized by registering the Narayana DataSource Bean provided by Spring as a DataSource Bean.
For example, you can connect for distributed transactions using Narayana just by registering with Bean like this.
DatasourceConfigOne.java
package com.example.datasource;
import com.example.common.DataSourceUtil;
import com.example.config.MyDataBaseProperties;
import com.example.constance.DataBaseConst;
import javax.sql.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
@Configuration
@EnableConfigurationProperties(MyDataBaseProperties.class)
@MapperScan(sqlSessionFactoryRef = DataBaseConst.DataSourceOneConst.NAME,
basePackages = DataBaseConst.DataSourceOneConst.MAPPER)
public class DatasourceConfigOne {
@Autowired
MyDataBaseProperties properties;
@Autowired
MybatisProperties mybatisProperty;
/**
* Get dataSource one.
*
* @return
*/
@Primary
@Bean
public DataSource getDataSourceOne() {
return DataSourceUtil.getDataSource(
this.properties.getProperty(DataBaseConst.DataSourceOneConst.DATA_SOURCE).getDetail());
}
}
MyDataBaseProperties gets the data source information by specifying the property name from the information list of the data source described in the yaml file. In DataSourceUtil # getDataSource, NarayanaDataSourceBean is created based on the acquired property.
Also, since Mybatis is used as ORM this time, a method to generate SqlSessionFactoryBean for Mybatis is also added to this class.
DatasourceConfigOne.java
/**
* Get SqlSessionFactory one.
*
* @return
*/
@Primary
@Bean(name = DataBaseConst.DataSourceOneConst.NAME)
public SqlSessionFactoryBean getSqlSessionFactoryOne() {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setConfiguration(this.mybatisProperty.getConfiguration());
factory.setDataSource(getDataSourceOne());
return factory;
}
All you have to do is read Mybatis Config and create a session with the data source information. With this, this data source setting is utilized when the DAO corresponding to the MapperScan basePackages declared in the class is DI.
@MapperScan(sqlSessionFactoryRef = DataBaseConst.DataSourceOneConst.NAME,
basePackages = DataBaseConst.DataSourceOneConst.MAPPER)
If the database to connect to is decided, distributed transactions can be performed without problems even with declarative transactions by increasing this class by the number of databases connected based on this.
Distributed transactions are certainly possible using the NarayanaDataSourceBean provided by Spring. However, as you can see from the excerpt source below, only a few features are available.
java:org.springframework.boot.jta.narayana.NarayanaDataSourceBean
@Override
public Connection getConnection() throws SQLException {
Properties properties = new Properties();
properties.put(TransactionalDriver.XADataSource, this.xaDataSource);
return ConnectionManager.create(null, properties);
}
java:com.arjuna.ats.internal.jdbc.ConnectionManager
/*
* Connections are pooled for the duration of a transaction.
*/
public static synchronized Connection create (String dbUrl, Properties info) throws SQLException
{
String user = info.getProperty(TransactionalDriver.userName, "");
String passwd = info.getProperty(TransactionalDriver.password, "");
String dynamic = info.getProperty(TransactionalDriver.dynamicClass, "");
String poolConnections = info.getProperty(TransactionalDriver.poolConnections, "true");
Object xaDataSource = info.get(TransactionalDriver.XADataSource);
int maxConnections = Integer.valueOf(info.getProperty(TransactionalDriver.maxConnections, "10"));
～～～ Omitted ～～～
}
Since only XADataSource is passed as a property when creating a connection, neither the connection pool usage setting nor the maximum number of connections can be set. Other properties are used only to confirm that the data sources are the same, so there is not much effect, but if you use 10 or more data sources, this should not be the case. Moreover, dynamicClass, poolConnections, and maxConnections are setting items that do not exist in PGXADataSource of PostgreSql.
Therefore, create a class that extends NarayanaDataSourceBean and a class that extends PGXADataSource. Regarding the extension of PGXADataSource, it may not be necessary if you implement it by passing the property value from the outside, but still create it.
MyXaDataSource.java
package com.example.common;
import java.util.Objects;
import org.postgresql.xa.PGXADataSource;
/**
* Extends PGXADataSource for TransactionalDriver.
*
* @author suimyakunosoko
*
*/
public class MyXaDataSource extends PGXADataSource {
/** enable pool connection. */
private boolean poolConnections = true;
/** max pool connection counts. */
private int maxConnections = 10;
public String getDynamicClass() {
return this.getClass().getName();
}
public boolean getPoolConnections() {
return this.poolConnections;
}
public void setPoolConnections(boolean poolConnections) {
this.poolConnections = poolConnections;
}
public int getMaxConnections() {
return this.maxConnections;
}
public void setMaxConnections(int maxConnections) {
this.maxConnections = maxConnections;
}
@Override
public boolean equals(Object obj) {
if (!(obj instanceof MyXaDataSource)) {
return false;
}
MyXaDataSource casted = (MyXaDataSource) obj;
return Objects.equals(casted.getURL(), this.getURL())
&& Objects.equals(casted.getUser(), this.getUser())
&& Objects.equals(casted.getPassword(), this.getPassword());
}
}
Add the properties poolConnections and maxConnections that were missing in MyXaDataSource, which is an extension of PGXADataSource. Match the initial value to Connection Manager. Since getDynamicClass is used only for data source check, pass the class name appropriately.
The equals override is also done by using the XADataSource's equal method when the Connection Manager checks for the existence of the data source. If you leave the default, it will be a comparison between hash values, so we will override it. Since the URL of PGXADataSource includes the user name and password, it is not necessary, but I will add it here.
MyNarayanaDataSourceBean.java
package com.example.common;
import com.arjuna.ats.internal.jdbc.ConnectionManager;
import com.arjuna.ats.jdbc.TransactionalDriver;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.springframework.boot.jta.narayana.NarayanaDataSourceBean;
/**
* Extends NarayanaDataSourceBean for ConnectionManager and enable TransactionalDriverProperties.
*
* @author suimyakunosoko
*
*/
public class MyNarayanaDataSourceBean extends NarayanaDataSourceBean {
private final Properties properties;
/**
* Wrap NarayanaDataSourceBean for ConnectionManager.
*
* @param myXaDataSource MyXaDataSource
*/
public MyNarayanaDataSourceBean(MyXaDataSource myXaDataSource) {
super(myXaDataSource);
this.properties = new Properties();
this.properties.put(TransactionalDriver.userName, myXaDataSource.getUser());
this.properties.put(TransactionalDriver.password, myXaDataSource.getPassword());
this.properties.put(TransactionalDriver.dynamicClass, myXaDataSource.getDynamicClass());
this.properties.put(TransactionalDriver.poolConnections,
String.valueOf(myXaDataSource.getPoolConnections()));
this.properties.put(TransactionalDriver.XADataSource, myXaDataSource);
this.properties.put(TransactionalDriver.maxConnections,
String.valueOf(myXaDataSource.getMaxConnections()));
}
@Override
public Connection getConnection() throws SQLException {
return ConnectionManager.create(null, this.properties);
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return ConnectionManager.create(null, this.properties);
}
}
Modify to set items that were not set in MyNarayanaDataSourceBean, which is an extension of NarayanaDataSourceBean. MyXaDataSource is specified as an argument, but if the PostgreSql server, MySql server, and Oracle server all support it, I feel that an interface that covers the necessary setting items will be prepared. This time, I don't care as long as I can connect to PostgreSQL.
It works as it is and rolls back properly, but Narayana's initial setting is OnePhaseCommit and some strange WARN log flows.
WARN 4812 --- [ main] com.arjuna.ats.common : ARJUNA048002: Could not find configuration file, URL was: null
This is the message if you try to read the jbossts-properties.xml file and it is not found. In addition to enabling TwoPahseCommit, I will store this file under the resource.
jbossts-properties.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<!-- (default is YES) -->
<!-- (twoPhaseCommit is NO) -->
<entry key="CoordinatorEnvironmentBean.commitOnePhase">NO</entry>
<!-- (Must be unique across all Arjuna instances.) -->
<!-- (default is 1) -->
<entry key="CoreEnvironmentBean.nodeIdentifier">1</entry>
</properties>
This allows WARN log suppression and Two Phase Commit.
With the implementation so far, distributed transactions for static data sources (such as the main DB of the own system and the DB of the linked system) can be realized. But suppose that a crazy design was done here. Moreover, it cannot go against the design. The content of the design is ** "Yes! Since there are many branches, let's build a server for each branch and have a database!" ** Actually it is impossible ... I wanted to think that it was impossible, but I encountered an event similar to this, so I have no choice but to deal with it.
Since the data source to be used is not decided at the timing when each controller and each service are executed, the DI of Spring is given up. Therefore, it is necessary to realize the following functions absorbed by the framework.
Implemented 1 and 2 by referring to the official website of Mybatis. 3 is realized by using the function of AOP.
Basically, if you read the official Mybatis document and implement it obediently, there are few points to be careful about. It should work properly if you manage the created Session information for each request (every Thread), do not create the same session many times, and at least pay attention to these two.
This time, create SqlSessionUtil for creating SqlSession and SqlSessionManager for managing SqlSession. Both are used as DI.
SqlSessionUtil.java
package com.example.common;
import com.example.config.MyDataBaseProperties;
import com.example.constance.DataBaseConst;
import java.util.Objects;
import javax.sql.DataSource;
import javax.sql.XADataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@Component
public class SqlSessionUtil {
@Autowired
MyDataBaseProperties properties;
@Autowired
MybatisProperties mybatisProperty;
@Autowired
SqlSessionManager manager;
/**
* create SqlSessionFactory by DataBase name.
*
* @param name DataBase name
* @return
*/
public SqlSessionFactory getSqlSessionFactory(String name) {
return getSqlSessionFactory(name, null);
}
/**
* <p>
* create SqlSessionFactory by DataBase name.
* </p>
* <p>
* when no DataBase name on yml, create SqlSessionFactory by defBase.
* </p>
*
* @param name DataBase name
* @param defBase use when yml dose not contain DataBaseName
* @return
*/
public SqlSessionFactory getSqlSessionFactory(String name, String defBase) {
XADataSource dataSourceprop = this.properties.getProperty(name, defBase).getDetail();
DataSource dataSource = DataSourceUtil.getDataSource(dataSourceprop);
TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration configuration = DataSourceUtil.fillNullByDefault(new Configuration(environment),
this.mybatisProperty.getConfiguration());
configuration.addMappers(DataBaseConst.DataSourceDefault.MAPPER);
return new SqlSessionFactoryBuilder().build(configuration);
}
/**
* Get SqlSession by name.
*
* @param name DataBase name
* @return
*/
public SqlSession getSqlSession(String name) {
return getSqlSession(name, null);
}
/**
* <p>
* create SqlSession by DataBase name.
* </p>
* <p>
* when no DataBase name on yml, create SqlSession by defBase.
* </p>
*
* @param name DataBase name
* @param defBase use when yml dose not contain DataBaseName
* @return
*/
public SqlSession getSqlSession(String name, String defBase) {
SqlSession session = this.manager.get(name);
if (!Objects.isNull(session)) {
return session;
}
session = getSqlSessionFactory(name, defBase).openSession();
this.manager.put(name, session);
return session;
}
}
In addition to the function to create a data source from a configuration file in SqlSession, a function to create connection information from a template when there is no connection information is also added. The SqlSessionManager that looks like a Singletone has a ThreadLocal variable internally, so it is not affected by other threads. There may be a method that passes an instance of SqlSessionManager in consideration of batch update etc.
SqlSessionManager.java
package com.example.common;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;
import org.apache.ibatis.session.SqlSession;
import org.springframework.stereotype.Component;
@Component
public class SqlSessionManager {
ThreadLocal<Map<String, SqlSession>> sessionMap = new ThreadLocal<>();
/**
* put SqlSession.
*
* @param key key
* @param session SqlSession
*/
public void put(String key, SqlSession session) {
init();
if (Objects.isNull(this.sessionMap.get())) {
this.sessionMap.set(new HashMap<String, SqlSession>());
}
this.sessionMap.get().put(key, session);
}
/**
* get SqlSession by key.
*
* @param key key
* @return
*/
public SqlSession get(String key) {
init();
return this.sessionMap.get().get(key);
}
/**
* close all session.
*/
public void close() {
init();
this.sessionMap.get().forEach((key, session) -> session.close());
this.sessionMap.set(null);
}
private void init() {
if (Objects.isNull(this.sessionMap.get())) {
this.sessionMap.set(new HashMap<String, SqlSession>());
}
}
}
SqlSessionManagerd keeps the created SqlSession Map. Instance creation timing! = Thread start timing, so NULL check is performed at the beginning of each method as an NPE countermeasure. It might have been good to create an instance of LocalThread at the time of request acceptance in AOP.
It is good to create a SqlSession, but if you close the SqlSession created after the transaction starts before the transaction ends, it will not work properly. Therefore, I will close the Session after the declarative Transaction is completed in AOP.
SessionCloseAspect.java
package com.example.aop;
import com.example.common.SqlSessionManager;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
@Aspect
@Component
@Order(Ordered.LOWEST_PRECEDENCE - 1)
@Transactional
public class SessionCloseAspect {
@Autowired
SqlSessionManager manager;
@After("@within(org.springframework.transaction.annotation.Transactional)")
public void closeSqlSession() {
this.manager.close();
}
}
Order is specified from the one with the largest number. Personally, the priority is high = I felt that it was executed first, so it feels a little strange, but if it is executed in this order, that would be the case.
It is OK if no exception occurs even if the declarative transaction is exited after the operation with the dynamically created SqlSession.
No modifier information found for db. Connection will be closed immediately
I'm worried that logs will always come out. It seems that the option to disconnect while synchronizing with other connections when terminating the connection is disabled.
I also want to merge jbossts-properties.xml into the yaml file as much as possible, but it seems to be useless because the WARN log appears.
Narayana Spring Boot example
Narayana official sample. Since there is little information in English, I made a trial and error based on this.
jbossts-properties.xml
When I got Narayana from Maven, there was no XML file for setting, so I borrowed it from here
If you want to see the source because the explanation is difficult to understand, or if you want something that can be used quickly because the entrustment is good, please click here. https://github.com/suimyakunosoko/narayana-spring-boot-mybatis-postgresql