Recently, I am doing margin balance query optimization. When the project starts, the balance needs to be fully loaded into the local cache, because the margin balance of all riders needs to be fully queried. In order not to affect the performance of the main database, consider leaving the query. So it involves the need to configure multiple data sources in a project and be able to switch dynamically. After some exploration, dynamic switching is perfectly realized, and the configuration method is recorded for your reference.
Overall design ideas
Spring-Boot+AOP method realizes multi-data source switching, inherits AbstractRoutingDataSource to achieve dynamic data source acquisition, and specifies data sources using annotations at the service layer.
step
1. Multi-data source configuration
In application.properties, our configuration is like this
#Master data source druid.master.url=jdbc:mysql://url/masterdb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNulldruid.master.username=xxxdruid.master.password=123druid.master.driver-class-name=com.mysql.jdbc.Driverdruid.master.max-wait=5000druid.master.max-active=100druid.master.test-on-borrow=truedruid.master.validation-query=SELECT 1#From the data source druid.slave.url=jdbc:mysql://url/slavedb?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNulldruid.slave.username=xxxdruid.slave.password=123druid.slave.driver-class-name=com.mysql.jdbc.Driverdruid.slave.max-wait=5000druid.slave.max-active=100druid.slave.test-on-borrow=truedruid.slave.validation-query=SELECT 1
Read configuration
<!-- master data source--><bean primary="true" id="masterdb" init-method="init" destroy-method="close"> <!-- Basic properties url, user, password --> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${druid.master.url}"/> <property name="username" value="${druid.master.username}"/> <property name="password" value="${druid.master.password}"/> <!-- Configure the maximum initialization --> <property name="maxActive" value="${druid.master.max-active}"/> <!-- Configure the time to get the connection waiting timeout --> <property name="maxWait" value="${druid.master.max-wait}"/> <property name="validationQuery" value="${druid.master.validation-query}"/> <property name="testOnBorrow" value="${druid.master.test-on-borrow}"/></bean><!-- slave data source--><bean primary="true" id="slavedb" init-method="init" destroy-method="close"> <!-- Basic properties url, user, password --> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="${druid.slave.url}"/> <property name="username" value="${druid.slave.username}"/> <property name="password" value="${druid.slave.password}"/> <!-- Configure initialization size, minimum, and maximum --> <property name="maxActive" value="${druid.slave.max-active}"/> <!-- Configure the time to get the connection waiting timeout --> <property name="maxWait" value="${druid.slave.max-wait}"/> <property name="validationQuery" value="${druid.slave.validation-query}"/> <property name="testOnBorrow" value="${druid.slave.test-on-borrow}"/></bean><!-- Dynamic data source, determine which data source to get based on the annotations on the service interface--><bean id="dataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="slave" value-ref="slavedb"/> <entry key="master" value-ref="masterdb"/> </map> </property> <property name="defaultTargetDataSource" ref="masterdb"/></bean><!-- Spring JdbcTemplate --><bean id="jdbcTemplate"> <property name="dataSource" ref="dataSource" /></bean><!-- Spring Transaction Manager--><bean id="transactionManager"> <property name="dataSource" ref="dataSource" /></bean><bean id="transactionTemplate"> <property name="transactionManager" ref="transactionManager"/></bean><tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true" order="2" /><!-- depositdbSqlSessionFactory --><bean id="sqlSessionFactory"> <property name="dataSource" ref="dataSource" /> <property name="mapperLocations" value="classpath*:mapper-xxdb/*Mapper*.xml" /></bean><bean> <property name="basePackage" value="xxdb.mapper"/> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/></bean>2. Dynamic data source
spring provides us with AbstractRoutingDataSource, a data source with routing. After inheriting, we need to implement its determineCurrentLookupKey(), which is used to customize the routing method of the actual data source name. Since we save the information to ThreadLocal, we just need to take it out.
public class DynamicDataSource extends AbstractRoutingDataSource { private Logger logger = LoggerFactory.getLogger(this.getClass()); @Override protected Object determineCurrentLookupKey() { String dataSource = JdbcContextHolder.getDataSource(); logger.info("The data source is {}",dataSource); return dataSource; }}3. Dynamic switching class of data source
Dynamic data source switching is based on AOP, so we need to declare an AOP section and do data source switching in front of the section. After the section is completed, the data source name is removed.
@Aspect@Order(1) //Set the AOP execution order (need to be before the transaction, otherwise the transaction will only occur in the default library) @Componentpublic class DataSourceAspect { private Logger logger = LoggerFactory.getLogger(this.getClass()); //Cut point @Pointcut("execution(* com.xxx.service.*.*(..))") public void aspect() { } @Before("aspect()") private void before(JoinPoint point) { Object target = point.getTarget(); String method = point.getSignature().getName(); Class<?> classz = target.getClass();// Get the target class Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()) .getMethod().getParameterTypes(); try { Method m = classz.getMethod(method, parameterTypes); if (m != null && m.isAnnotationPresent(MyDataSource.class)) { MyDataSource data = m.getAnnotation(MyDataSource.class); logger.info("method :{},datasource:{}",m.getName() ,data.value().getName()); JdbcContextHolder.putDataSource(data.value().getName());// Put the data source in the current thread} } catch (Exception e) { logger.error("get datasource error",e); // Master JdbcContextHolder.putDataSource(DataSourceType.Master.getName());// Put the data source in the current thread} } @AfterReturning("aspect()") public void after(JoinPoint point) { JdbcContextHolder.clearDataSource(); }}4. Data source management category
public class JdbcContextHolder { private final static ThreadLocal<String> local = new ThreadLocal<>(); public static void putDataSource(String name) { local.set(name); } public static String getDataSource() { return local.get(); } public static void clearDataSource() { local.remove(); }}5. Data source annotations and enumerations
When we switch data sources, we generally implement it before calling the method of the specific interface, so we define a method annotation. When AOP detects that the annotation is present on the method, it switches according to the name corresponding to the value in the annotation.
@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.METHOD)public @interface MyDataSource { DataSourceType value();}public enum DataSourceType { // Master("master"), // Slave("slave"); private String name; private DataSourceType(String name) { this.name = name; } public String getName() { return name; } public void setName(String name) { this.name = name; }}6. Cut point annotation
Since our dynamic data source has a default library, if the method is to operate the default library, there is no need for annotations. If you want to operate a non-default data source, we need to add @MyDataSource("DataSource Name") annotation to the method, so that you can use AOP to achieve dynamic switching.
@Componentpublic class xxxServiceImpl { @Resource private XxxMapperExt xxxMapperExt; @MyDataSource(value= DataSourceType.Slave) public List<Object> getAll(){ return xxxMapperExt.getAll(); }}The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.