Example of Spring+MyBatis multi data source configuration implementation
- 2020-06-01 09:47:58
- OfStack
Recently, I used MyBatis to configure multiple data sources. I thought it would be a simple configuration, but I found that it still takes some trouble after the actual operation. I will record it here for a reminder
No more nonsense, directly on the code, there will be a simple implementation
Configuration of jdbc and log4j
# Define the output format
ConversionPattern=%d %-5p [%t] %c - %m%n
log4j.rootLogger=DEBUG,Console
log4j.logger.com.cnblogs.lzrabbit=DEBUG
log4j.logger.org.springframework=ERROR
log4j.logger.org.mybatis=ERROR
log4j.logger.org.apache.ibatis=ERROR
log4j.logger.org.quartz=ERROR
log4j.logger.org.apache.axis2=ERROR
log4j.logger.org.apache.axiom=ERROR
log4j.logger.org.apache=ERROR
log4j.logger.httpclient=ERROR
#log4j.additivity.org.springframework=false
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.Threshold=DEBUG
log4j.appender.Console.Target=System.out
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=${ConversionPattern}
#log4j.appender.Console.encoding=UTF-8
#org.apache.log4j.DailyRollingFileAppender
log4j.appender.DailyFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.DailyFile.DatePattern='.'yyyy-MM-dd'.log'
log4j.appender.DailyFile.File=${myApp.root}/logs/daily.log
log4j.appender.DailyFile.Append=true
log4j.appender.DailyFile.Threshold=DEBUG
log4j.appender.DailyFile.layout=org.apache.log4j.PatternLayout
log4j.appender.DailyFile.layout.ConversionPattern=${ConversionPattern}
log4j.appender.DailyFile.encoding=UTF-8
# %c Output the full name of the class to which the log information belongs
# %d Output the date or time of the log point in the default format of ISO8601 , you can also specify the format later, such as: %d{yyy-MM-dd HH:mm:ss} , the output is similar to: 2002-10-18- 22 : 10 : 28
# %f Output the class name of the class to which the log information belongs
# %l Where the output log event occurs, the statement that outputs the log information is on the first line of its class
# %m Output the information specified in the code, such as log(message) In the message
# %n The output 1 Enter line feed, Windows Platform for" rn ", Unix Platform for" n "
# %p Output priority, i.e DEBUG . INFO . WARN . ERROR . FATAL . If it's a call debug() Output, is DEBUG And so on
# %r Output the number of milliseconds it takes to output this log information from application startup to output
# %t Output the name of the thread that produced the log event
#============================================================================
# MySQL
#============================================================================
jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
jdbc.mysql.username=root
jdbc.mysql.password=root
#============================================================================
# MS SQL Server
#============================================================================
#jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;database=test;
#jdbc.sqlserver.username=sa
#jdbc.sqlserver.password=sa
#============================================================================
# MS SQL Server (JTDS)
#============================================================================
jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/test
jdbc.sqlserver.username=sa
jdbc.sqlserver.password=sa
#============================================================================
# General configuration
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000
Spring configuration file for single data source
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"/>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.mysql.driver}"/>
<property name="url" value="${jdbc.mysql.url}"/>
<property name="username" value="${jdbc.mysql.username}"/>
<property name="password" value="${jdbc.mysql.password}"/>
<property name="initialSize" value="${jdbc.initialSize}"/>
<property name="minIdle" value="${jdbc.minIdle}"/>
<property name="maxIdle" value="${jdbc.maxIdle}"/>
<property name="maxActive" value="${jdbc.maxActive}"/>
<property name="maxWait" value="${jdbc.maxWait}"/>
<property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
<property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
<property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
<property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- mybatis.spring Automatic mapping -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.cnblogs.lzrabbit"/>
</bean>
<!-- Automatic scanning of multiple packages A comma -->
<context:component-scan base-package="com.cnblogs.lzrabbit"/>
<aop:aspectj-autoproxy/>
</beans>
Spring configuration file for multiple data sources
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:jdbc.properties"/>
</bean>
<bean id="sqlServerDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.sqlserver.driver}"/>
<property name="url" value="${jdbc.sqlserver.url}"/>
<property name="username" value="${jdbc.sqlserver.username}"/>
<property name="password" value="${jdbc.sqlserver.password}"/>
<property name="initialSize" value="${jdbc.initialSize}"/>
<property name="minIdle" value="${jdbc.minIdle}"/>
<property name="maxIdle" value="${jdbc.maxIdle}"/>
<property name="maxActive" value="${jdbc.maxActive}"/>
<property name="maxWait" value="${jdbc.maxWait}"/>
<property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
<property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
<property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
<property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
</bean>
<bean id="mySqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.mysql.driver}"/>
<property name="url" value="${jdbc.mysql.url}"/>
<property name="username" value="${jdbc.mysql.username}"/>
<property name="password" value="${jdbc.mysql.password}"/>
<property name="initialSize" value="${jdbc.initialSize}"/>
<property name="minIdle" value="${jdbc.minIdle}"/>
<property name="maxIdle" value="${jdbc.maxIdle}"/>
<property name="maxActive" value="${jdbc.maxActive}"/>
<property name="maxWait" value="${jdbc.maxWait}"/>
<property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
<property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
<property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
<property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
</bean>
<bean id="multipleDataSource" class="com.cnblogs.lzrabbit.MultipleDataSource">
<property name="defaultTargetDataSource" ref="mySqlDataSource"/>
<property name="targetDataSources">
<map>
<entry key="mySqlDataSource" value-ref="mySqlDataSource"/>
<entry key="sqlServerDataSource" value-ref="sqlServerDataSource"/>
</map>
</property>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="multipleDataSource"/>
</bean>
<!-- mybatis.spring Automatic mapping -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.cnblogs.lzrabbit"/>
</bean>
<!-- Automatic scanning of multiple packages A comma -->
<context:component-scan base-package="com.cnblogs.lzrabbit"/>
<aop:aspectj-autoproxy/>
</beans>
MultipleDataSource implementation
package com.cnblogs.lzrabbit;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* Created by rabbit on 14-5-25.
*/
public class MultipleDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> dataSourceKey = new InheritableThreadLocal<String>();
public static void setDataSourceKey(String dataSource) {
dataSourceKey.set(dataSource);
}
@Override
protected Object determineCurrentLookupKey() {
return dataSourceKey.get();
}
}
MyBatis interface Mapper definition, implemented directly using annotations
public interface MySqlMapper {
@Select("select * from MyTable")
List<Map<String,Object>> getList();
}
public interface SqlServerMapper {
@Select("select * from MyTable")
List<Map<String,Object>> getList();
}
Manual data source switching calls
package com.cnblogs.lzrabbit;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
* Created by rabbit on 14-5-25.
*/
public class Main {
public static void main(String[] args) {
// Initialize the ApplicationContext
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
MySqlMapper mySqlMapper = applicationContext.getBean(MySqlMapper.class);
SqlServerMapper sqlServerMapper = applicationContext.getBean(SqlServerMapper.class);
// Set the data source to MySql, Using the AOP Please comment the following line when testing
MultipleDataSource.setDataSourceKey("mySqlDataSource");
mySqlMapper.getList();
// Set the data source to SqlServer, use AOP Please comment the following line when testing
MultipleDataSource.setDataSourceKey("sqlServerDataSource");
sqlServerMapper.getList();
}
}
Use SpringAOP mode to achieve automatic switching
package com.cnblogs.lzrabbit;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.stereotype.Component;
@Component
@Aspect
public class MultipleDataSourceAspectAdvice {
@Around("execution(* com.cnblogs.lzrabbit.*.*(..))")
public Object doAround(ProceedingJoinPoint jp) throws Throwable {
if (jp.getTarget() instanceof MySqlMapper) {
MultipleDataSource.setDataSourceKey("mySqlDataSource");
} else if (jp.getTarget() instanceof SqlServerMapper) {
MultipleDataSource.setDataSourceKey("sqlServerDataSource");
}
return jp.proceed();
}
}
Call log
2014-05-25 20:02:04,319 DEBUG [main] com.jb51.lzrabbit.MySqlMapper.getList - ooo Using Connection [jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true, UserName=root@192.168.1.32, MySQL Connector Java]
2014-05-25 20:02:04,333 DEBUG [main] com.jb51.lzrabbit.MySqlMapper.getList - ==> Preparing: select * from MyTable
2014-05-25 20:02:04,371 DEBUG [main] com.jb51.lzrabbit.MySqlMapper.getList - ==> Parameters:
2014-05-25 20:02:04,396 DEBUG [main] com.jb51.lzrabbit.MySqlMapper.getList - <== Total: 8
2014-05-25 20:02:04,620 DEBUG [main] com.jb51.lzrabbit.SqlServerMapper.getList - ooo Using Connection [jdbc:jtds:sqlserver://127.0.0.1:1433/test, UserName=sa, jTDS Type 4 JDBC Driver for MS SQL Server and Sybase]
2014-05-25 20:02:04,620 DEBUG [main] com.jb51.lzrabbit.SqlServerMapper.getList - ==> Preparing: select * from TmallCityMap
2014-05-25 20:02:04,621 DEBUG [main] com.jb51.lzrabbit.SqlServerMapper.getList - ==> Parameters:
2014-05-25 20:02:04,681 DEBUG [main] com.jb51.lzrabbit.SqlServerMapper.getList - <== Total: 397Here make a simple explanation on the implementation of the above, in a single configuration data when we can see the data source types using the org. apache. commons. dbcp. BasicDataSource, and this code implements javax. sql. DataSource interface
When configuration sqlSessionFactory org. mybatis. spring. dataSource SqlSessionFactoryBean injection parameter type is javax. sql. DataSource
The way to implement multiple data sources is that we have customized 1 MultipleDataSource. This class inherits from AbstractRoutingDataSource, and AbstractRoutingDataSource inherits from AbstractDataSource. AbstractDataSource implements the javax.sql.DataSource interface, so our MultipleDataSource also implements the javax.sql.DataSource interface, which can be assigned to the dataSource property of sqlSessionFactory
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {}
public abstract class AbstractDataSource implements DataSource {}
Then there is the implementation principle of MultipleDataSource. MultipleDataSource implements the AbstractRoutingDataSource abstract class, and then implements the determineCurrentLookupKey method, which is used to select which data source in targetDataSources is to be used
#============================================================================
# MySQL
#============================================================================
jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
jdbc.mysql.username=root
jdbc.mysql.password=root
#============================================================================
# MS SQL Server
#============================================================================
#jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;database=test;
#jdbc.sqlserver.username=sa
#jdbc.sqlserver.password=sa
#============================================================================
# MS SQL Server (JTDS)
#============================================================================
jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/test
jdbc.sqlserver.username=sa
jdbc.sqlserver.password=sa
#============================================================================
# General configuration
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000
Can see Spring multipleDataSource set up two properties in the configuration of defaultTargetDataSource and targetDataSources, these two properties define the AbstractRoutingDataSource, when MyBatis query execution to choose the data source, select order according to determineCurrentLookupKey method return value into targetDataSources find, if you can find how returns the corresponding data source, if could not find the data source to return to the default defaultTargetDataSource, specific reference AbstractRoutingDataSource source
#============================================================================
# MySQL
#============================================================================
jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
jdbc.mysql.username=root
jdbc.mysql.password=root
#============================================================================
# MS SQL Server
#============================================================================
#jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;database=test;
#jdbc.sqlserver.username=sa
#jdbc.sqlserver.password=sa
#============================================================================
# MS SQL Server (JTDS)
#============================================================================
jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/test
jdbc.sqlserver.username=sa
jdbc.sqlserver.password=sa
#============================================================================
# General configuration
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000
When the data source method is dynamically switched, AOP method is selected to be implemented. The implementation here is simple and rough, and the specific application should be flexible according to the actual needs
Digression, here SqlServer drive way choice, the current SqlServer main driver of Microsoft's official drive and JTDS drive two kinds, the two driving test I ever did, batch updates, in a small amount of data (below 100), the relative Microsoft JTDS drive performance slightly high 1, when the increasing amount of data in the tens of thousands to millions, Microsoft driver has obvious advantage, so if the performance is more sensitive, it is recommended to use Microsoft driver, or casually
The Microsoft driver is not available in the Maven library, which is frustrating, and you have to install it locally to use maven, which is annoying
JTDS is easy to use and Maven can be directly referenced
Related jar maven references
#============================================================================
# MySQL
#============================================================================
jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
jdbc.mysql.username=root
jdbc.mysql.password=root
#============================================================================
# MS SQL Server
#============================================================================
#jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1433;database=test;
#jdbc.sqlserver.username=sa
#jdbc.sqlserver.password=sa
#============================================================================
# MS SQL Server (JTDS)
#============================================================================
jdbc.sqlserver.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.sqlserver.url=jdbc:jtds:sqlserver://127.0.0.1:1433/test
jdbc.sqlserver.username=sa
jdbc.sqlserver.password=sa
#============================================================================
# General configuration
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000