Follow me by clicking on “Shou Zhi Tian’s Technology blog” above
In practical development, applications often need to access multiple libraries and need to configure multiple data sources. This article will introduce the typical scenario of Spring multi-data source, how to implement multi-data source elegantly, and analyze with spring and Mybatis source code, why can’t switch data source in the transaction. Finally, a complete source code case for multiple data sources is provided.
1 Typical application scenarios of multiple data sources
In real development, it is often possible to encounter situations in which multiple databases may be accessed in one application. The following are two typical scenarios.
1.1 Complex Services
The data is distributed in different databases. The author has seen a relatively complex business, in which 9 libraries are operated at the same time. The business code needs to access different libraries according to different operations of users.
1.2 Read/Write Separation
Some smaller companies do not use database access layer middleware. Multiple data sources are used to achieve simple read and write separation.
The architecture is similar to the one shown above. The difference is that in read/write separation, the master and slave databases are the same (regardless of master-slave latency). Data update operations (INSERT, UPDATE, delete) are performed on the master library, which synchronizes data changes to the slave library. When querying, you can do it on the slave library to share the burden of the master library.
It is important to note that read/write separation using multiple data sources requires the developer to determine whether the SQL being executed is read or written. If database access layer middleware is used, there is usually middleware to implement read/write separation logic, which is more transparent to the business.
2 How to Implement multiple data sources
For most Java applications, use the spring framework, spring – offers AbstractRoutingDataSource JDBC modules, its internal can contain more than one DataSource, then dynamic access which database at run time. The schema diagram for accessing the database in this way is shown below:
The application of direct manipulation is AbstractRoutingDataSource implementation class, tell AbstractRoutingDataSource which database access, Then by AbstractRoutingDataSource from configured in advance (ds1, ds2) select a data source, to access the corresponding database.
About how to implement multiple source using AbstractRoutingDataSource access, all kinds of blog has a lot of, the basic functions can be implemented, but the ease of use is not good enough, to modify a lot of code, the business is too invasive.
That’s why I wrote this article, which provides a much simpler implementation of multiple data sources that I call the RoutingDataSource. Sprign-aop is easy to get started with even if you don’t know much about it. Furthermore, the author published this component in maven’s central repository, so you can reference it directly in POM.xml.
<dependency> <groupId>io.github.tianshouzhi</groupId> <artifactId>routing-datasource</artifactId>The < version > 1.0.0 < / version ></dependency>Copy the codeRouting – a datasource, the most important is the following two components: RoutingDataSource classes: its implementation AbstractRoutingDataSource function similar to the spring, the internal management of multiple data sources. Support for specifying that all classes under this package access a library according to the package name. Routing annotation: can be added to a class or interface, or to a method for more precise data source selection control. It is also worth mentioning that the @Routing annotation supports integration with Spring transactions.
3 RoutingDataSource configuration
Suppose we have two libraries, DB1 (containing the user table) and DB2 (containing the user_Account table), as follows
# database: db1CREATE DATABASE `db1`;USE `db1`;CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# Database: DB2CREATE DATABASE `db2`;USE `db2`;CREATE TABLE `user_account` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `account` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Copy the codeNext, we need to configure two separate data sources to access the library, any data source that implements the JDBC specification (Druid, C3P0, DBCP, tomcat-JDBC, and so on).
<! SQL > select * from ds1;<bean id="ds1" class="org.apache.ibatis.datasource.pooled.PooledDataSource"> <property name="username" value="root"/> <property name="password" value="shxx12151022"/> <property name="url" value="jdbc:mysql://localhost:3306/db1"/> <property name="driver" value="com.mysql.jdbc.Driver"/></bean><! SQL > select * from ds2;<bean id="ds2" class="org.apache.ibatis.datasource.pooled.PooledDataSource"> <property name="username" value="root"/> <property name="password" value="shxx12151022"/> <property name="url" value="jdbc:mysql://localhost:3306/db2"/> <property name="driver" value="com.mysql.jdbc.Driver"/></bean>Copy the codeNext, we need to hand these two data sources over to the RoutingDataSource for management.
<! -- RoutingDataSource, which manages ds1 and DS2<bean id="routingDataSource" class="io.github.tianshouzhi.routing.RoutingDataSource"> <property name="targetDataSources"> <map><! -- Entry key, which will be used in the @routing annotation you'll see later --> <entry key="ds1" value-ref="ds1"/> <entry key="ds2" value-ref="ds2"/> </map> </property><! Configure the default data source. The RoutingDataSource will use the default data source if it can't decide which one to use. <property name="defaultTargetDataSource" value="ds1"/></bean>Copy the codeIn actual development, we usually do not directly operate the data source, but integrate with the ORM framework. Here we choose MyBatis, so we need to add myBatis related configuration
<! Mybatis SqlSessionFactoryBean; routingDataSource factoryBean<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">The < property name = "dataSource" ref = "routingDataSource" / ><! Note that if you are writing SQL in XML, you need to turn on the following configuration:<! --<property name="mapperLocations" value="classpath*:config/sqlmap/**/*.xml" />--></bean><! - the configuration MapperScannerConfigurer -- -- ><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/><! --UserMapper and UserAccountMapper are in this package --> <property name="basePackage" value="io.github.tianshouzhi.routing.mapper"/></bean>Copy the codeIn addition, we wrote two Mybatis mappers (see source below) : UserMapper and UserAccountMapper to access the DB1.user and DB2.user_Account tables, respectively. We can then add the @Routing annotation to both interfaces to give them access to different libraries.
4. Basic use of Routing annotations
The @routing annotation can be used on methods, classes or interfaces. The following is how to use the Routing annotation.
4.1 Add the @routing annotation to the method
public interface UserMapper { @Routing("ds1")// Specify that this method goes to the DS1 data source using the @routing annotation public User selectById(@Param("id") int id); // No annotations added, default data source will be used public int insert(User user);}Copy the codeThe above interface defines two methods:
SelectById method: added the @routing (” DS1 “) annotation to indicate that ds1 data source will be selected when accessing the database.
Insert method: No annotations are added, so the default data source will be used. The ds1 specified earlier when configuring the RoutingDataSource.
4.2 Adding @Routing annotations to interfaces or classes
For example, we define the @routing annotation on the UserAccountMapper interface. Both methods defined in the interface go to the DS2 data source to access DB2, and the user_Account is in DB2:
@Routing("ds2")public interface UserAccountMapper { UserAccount selectById(@Param("id") int id); int insert(UserAccount userAccount);}Copy the codeTip: We usually use a Mapper interface to operate on tables in a library, so it is recommended to add the @routing annotation directly to the interface rather than to each method separately. (Except read/write separation operations)
4.3 Add @routing annotations to interfaces and methods
@Routing("ds2")public interface UserAccountMapper {// Use the DS2 data source specified by the @routing annotation on the interface UserAccount selectById(@Param("id") int id); // Use the ds1 data source specified by the @routing annotation on the method// Note: This is an incorrect example because the user_Account table is in DB2 @Routing("ds1") int insert(UserAccount userAccount);}Copy the code4.4 Data source mapping at package level
If the directory structure of the project is well divided, Mapper interfaces for different libraries are operated under different packages, for example:
In the figure above, all the mapper interfaces under the DB1 package operate on DB1. At this point you can modify the previous RoutingDataSource configuration by adding the following configuration to directly define a mapper under a package that accesses a library, eliminating the need to define @Routing annotations on each interface.
<bean id="routingDataSource" class="io.github.tianshouzhi.routing.RoutingDataSource">. <property name="packageDataSourceKeyMap"> <map> <entry key="io.github.tianshouzhi.routing.mapper.db1" value="ds1"/> <entry key="io.github.tianshouzhi.routing.mapper.db2" value="ds2"/> </map> </property></bean>Copy the codeNote: For the @Routing annotation priority, the priority meets the following criteria: method > Interface > package
4.5 Service Layer Invocation
The Mapper interface belongs to the DAO layer. Normally, the CODE of the DAO layer is called in the Service layer. The @Routing annotation can also be added to the interface of the business layer. It is up to the Mapper Mapper method called, the @routing annotation on the interface to determine which DS is used, and if neither is defined, the default data source is used
public void business(int userId,int userAccountId) { userAccountMapper.selectById(userAccountId); userMapper.selectById(userId); }Copy the code4.6 Add the @routing annotation to the Service Layer
After the @Routing annotation is added to the business layer method, the Mapper Mapper method called internally and the Routing annotation on the interface will be ignored. All Mapper Mapper mappers called internally will follow the @Routing annotation on the business layer, in order to be compatible with the semantics of the transaction (see transaction integration analysis). Such as:
// All mapper interfaces that specify method calls must use DS2 @Routing("ds2") public void business(int userId,int userAccountId) {// The user_Account table is located in DB2, so access can succeed userAccountMapper.selectById(userAccountId);// Note: the user table is in DB1, ds2 is enforced here, so execution will fail userMapper.selectById(userId);}Copy the code4.7@routing annotation transaction support
The @routing annotation can be used in conjunction with Spring transactions, making sure that the methods in the transaction all access tables in the same library.
4.7.1 Transaction manager configuration
Spring’s transaction manager, through the PlatformTransactionManager interface, said it has two important implementation class:
DataSourceTransactionManager: used to support local transactions, in fact, its internal also is by manipulating the Java, SQL, Connection to open, commit and rollback transaction.
JtaTransactionManager: Used to support distributed transactions, which implements the JTA specification and uses the XA protocol for two-phase commit. Note that this is only a proxy, and we need to provide it with a JTA Provider, usually a Java EE Server’s transaction coordinator, which can also be independent of the container. Configure a local JTA provider. Distributed transactions are beyond the scope of this article. Interested readers can refer to my blog post on how to implement distributed transactions using Atomikos at the following address:
http://www.tianshouzhi.com/api/tutorials/distributed_transaction/386
Obviously, here, we configure DataSourceTransactionManager, means the multiple source does not support distributed transactions.
Since the RoutingDataSource manages multiple data sources, transaction management should also refer to the RoutingDataSource. Here is an example of a declarative transaction @Transactional annotation:
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="routingDataSource"/></bean><tx:annotation-driven />Copy the codeIt is necessary to introduce here, why don’t DataSourceTransactionManager support distributed transactions. For those familiar with JDBC programming, we can open or commit a transaction in the following ways
DataSource ds=...Connection conn=ds.getConnection();// Start the transactionconn.setAutoCommit(false);. CRUD operations// Commit the transactionconn.commit();Copy the codeAs you can see, transaction management is actually bound to Connection, which in turn is retrieved from some DataSource. Only one library operation a DataSource, because we are in the configuration spring transaction manager DataSourceTransactionManager, specifies a DataSource, apparently means that its only operate on a library affairs.
Although the data source configured here is the RoutingDataSource provided by the author, which internally manages multiple other data sources, it still cannot support distributed transactions, because at runtime, the RoutingDataSource selects a specific data source that it manages according to the configuration. Hand it over to the Spring transaction manager for use.
Another point to note is that you cannot switch data sources in a transaction. Spring’s @Transactional annotation is nothing more than a pointcut. Spring proxies classes that add the @Transactional annotation method. Before this method executes, get the Connection from the data source and start the transaction; After the method is executed, the transaction is committed or rolled back, depending on whether no exception is thrown. For a Transactional method with the @Transactional annotation, Spring fetches a Connection via a DataSource and starts the transaction before the Transactional method executes. This Connection has always been used and cannot be switched. Spring open the source code of the transaction, you can refer to DataSourceTransactionManager. DoBegin method:
As you can see, DataSourceTransactionManager, actually is also provided by our data source, access to the Connection, by setAutoCommit (false) to open the transaction, and we are familiar with JDBC programming.
When Spring starts a transaction, the underlying ORM framework will fetch the open transaction Connection from the Spring thread context when accessing the database. That is, all database operations in a transaction use the same Connection. For example, mybatis integrates with Spring through the MyBatis -Spring module. Mybatis -Spring has a SpringManagedTransaction class, which creates a Connection operation database. The spring-JDBC module provides the DataSourceUtils utility class to obtain the Connection as follows:
DataSourceUtils. Inside the getConnection method will first try to get Connection from spring provides the thread context, if spring open the transaction, is inevitable in this Connection. Mybatis will then use this Connection to create a Statement or PreparedStatement for adding, deleting, changing, and checking operations.
From the above code analysis, the reader should know why you cannot switch data sources in a transaction. Because when Spring starts a transaction, the Connection used is already established, and the same Connection is used throughout the transaction. A Connection can only belong to a DataSource.
For the multi-source RoutingDataSource that I provide, what it does is tell spring which source to use before it starts a transaction, again via the @Routing annotation.
4.7.2 Only use the @Transactional annotation
Using only the @Transactional annotation, operations within a method can only access the default data source. The @routing annotation for other methods that are called internally is ignored. If you access tables in other libraries, an error will be reported.
@TransactionalPublic void testDefaultTransaction(User User, UserAccount userAcccount) {// The default data source is DS1, and the user table in db1 can be accessed, so the insert succeeded userMapper.insert(user);// Note: this method will fail and the transaction will be rolled back because user_Account is in DB2 userAccountMapper.insert(userAcccount)}Copy the code4.7.3 Using @transactional / @routing Simultaneously
The Spring transaction manager will start the transaction using the data source specified in the @Routing annotation
@Transactional@routing ("ds2 ") // start the transaction using ds2Public void testRoutingTransaction(User User, UserAccount userAcccount) { userAccountMapper.insert(userAccount);// Note: this method will fail and the transaction will be rolled back because user is in DB1 userMapper.insert(user);}Copy the code6 summarizes
Although I provided a RoutingDataSource and posted it to the Maven central repository, I didn’t expect anyone to actually use it
git clone https://github.com/tianshouzhi/routing-datasource.gitCopy the codeWhen exploring source code, consider the following questions:
1, the author provides a @routing annotation, but there is no Spring AOP configuration, aop switch function is implemented, how to implement. Because @Routing itself has to proxy the class, it can tell the Spring framework which data source to use.
Both the @Routing annotation and the @Transactional annotation provided by Spring need to delegate to a class, and the former needs to come into play before the latter. Use secondary proxy? Or something more elegant?
Recently published:
Mysql branch selection: Percona Or MariaDB
Data synchronization in remote live scenarios
Overview of Distributed Transactions
Database middleware details
Identify qr code to follow me
