Preface

      
After the users of the website reach a certain scale, Database becomes the bottleneck of website because of the high load pressure. Fortunately, most of the current mainstream databases provide the master-slave hot standby function, By configuring the master-slave relationship of two databases, You can synchronize data updates from one database to another server. Website uses this function of database, Realize the separation of reading and writing of database, To improve database load pressure. As shown in the figure below:

 

 


   When the application server writes data, Accessing the master database, Master database synchronizes data updates to slave database through master-slave replication mechanism, So when the application server reads the data, You can get data from the database. In order to facilitate application access to the database after read-write separation, Usually, the application server uses a special database access module, Make database read-write separation transparent to application.

Excerpt from《 Technical framework of large website_ Core principles and case analysis》

       This blog is to realize“ Special database access module”, Make database read-write separation transparent to application. in addition,mysql The master-slave replication of the database can refer to my
mysql5.7.18 Installation and master-slave replication of <http://www.cnblogs.com/youzhibing/p/7293888.html>
. Be careful, Database realizes master-slave replication, To separate the reading and writing of database, therefore, If there is no master-slave database replication, remember to implement the master-slave database replication first

Configure read and write data sources( Master slave database)

       mysqldb.properties
# Master database data source jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://
192.168.0.4:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root jdbc.password=123456 jdbc.initialSize=1 jdbc.minIdle=1
jdbc.maxActive=20 jdbc.maxWait=60000 jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=180 jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.minEvictableIdleTimeMillis=300000 jdbc.validationQuery=SELECT 1
jdbc.testWhileIdle=true jdbc.testOnBorrow=false jdbc.testOnReturn=false
# From database data source slave.jdbc.driverClassName=com.mysql.jdbc.Driver slave.jdbc.url
=jdbc:mysql://
192.168.0.221:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false
slave.jdbc.username=root slave.jdbc.password=123456 slave.jdbc.initialSize=1
slave.jdbc.minIdle=1 slave.jdbc.maxActive=20 slave.jdbc.maxWait=60000
slave.jdbc.removeAbandoned=true slave.jdbc.removeAbandonedTimeout=180
slave.jdbc.timeBetweenEvictionRunsMillis=60000
slave.jdbc.minEvictableIdleTimeMillis=300000 slave.jdbc.validationQuery=SELECT 1
slave.jdbc.testWhileIdle=true slave.jdbc.testOnBorrow=false
slave.jdbc.testOnReturn=false
     main, Remember to change the database address to your own, Your account and password need to be changed to your own; Other configuration items, You can set your own

       mybatis-spring.xml
<?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"
xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation
="http://www.springframework.org/schema/beans http://
www.springframework.org/schema/beans/spring-beans.xsd http://
www.springframework.org/schema/context http://
www.springframework.org/schema/context/spring-context.xsd http://
www.springframework.org/schema/aop http://
www.springframework.org/schema/aop/spring-aop.xsd http://
www.springframework.org/schema/tx http://
www.springframework.org/schema/tx/spring-tx.xsd"> <!-- master data source --> <bean
id="masterDataSource"class="com.alibaba.druid.pool.DruidDataSource"> <!-- Basic attributes
url,user,password --> <property name="driverClassName"
value="${jdbc.driverClassName}" /> <property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" /> <property name="password"
value="${jdbc.password}" /> <property name="initialSize"
value="${jdbc.initialSize}" /> <property name="minIdle" value="${jdbc.minIdle}"
/> <property name="maxActive" value="${jdbc.maxActive}" /> <property
name="maxWait" value="${jdbc.maxWait}" /> <!-- Whether to recycle beyond the time limit --> <property
name="removeAbandoned" value="${jdbc.removeAbandoned}" /> <!-- How long does it exceed the time limit; -->
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"
/> <!-- How often is the configuration interval checked, Detect idle connections that need to be closed, In milliseconds --> <property
name="timeBetweenEvictionRunsMillis"
value="${jdbc.timeBetweenEvictionRunsMillis}" /> <!-- Configure the minimum lifetime of a connection in the pool, In milliseconds
--> <property name="minEvictableIdleTimeMillis"
value="${jdbc.minEvictableIdleTimeMillis}" /> <!-- Used to check whether the connection is validsql, Request is a query statement-->
<property name="validationQuery" value="${jdbc.validationQuery}" /> <!--
Detect when applying for connection --> <property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
<!-- Execute on connection requestvalidationQuery Check if the connection is valid, Configured astrue Will degrade performance --> <property
name="testOnBorrow" value="${jdbc.testOnBorrow}" /> <!--
Execute on return connectionvalidationQuery Check if the connection is valid, Configured astrue Will degrade performance --> <property name="testOnReturn"
value="${jdbc.testOnReturn}" /> </bean> <!-- slave data source --> <bean
id="slaveDataSource"class="com.alibaba.druid.pool.DruidDataSource"> <property
name="driverClassName" value="${slave.jdbc.driverClassName}" /> <property
name="url" value="${slave.jdbc.url}" /> <property name="username"
value="${slave.jdbc.username}" /> <property name="password"
value="${slave.jdbc.password}" /> <property name="initialSize"
value="${slave.jdbc.initialSize}" /> <property name="minIdle"
value="${slave.jdbc.minIdle}" /> <property name="maxActive"
value="${slave.jdbc.maxActive}" /> <property name="maxWait"
value="${slave.jdbc.maxWait}" /> <property name="removeAbandoned"
value="${slave.jdbc.removeAbandoned}" /> <property
name="removeAbandonedTimeout" value="${slave.jdbc.removeAbandonedTimeout}" />
<property name="timeBetweenEvictionRunsMillis"
value="${slave.jdbc.timeBetweenEvictionRunsMillis}" /> <property
name="minEvictableIdleTimeMillis"
value="${slave.jdbc.minEvictableIdleTimeMillis}" /> <property
name="validationQuery" value="${slave.jdbc.validationQuery}" /> <property
name="testWhileIdle" value="${slave.jdbc.testWhileIdle}" /> <property
name="testOnBorrow" value="${slave.jdbc.testOnBorrow}" /> <property
name="testOnReturn" value="${slave.jdbc.testOnReturn}" /> </bean> <!--
Dynamic data source, according toservice The annotation on the interface determines which data source to take --> <bean id="dataSource"class
="com.yzb.util.DynamicDataSource"> <property name="targetDataSources"> <map
key-type="java.lang.String"> <!-- write or slave --> <entry key="slave"
value-ref="slaveDataSource"/> <!-- read or master --> <entry key="master"
value-ref="masterDataSource"/> </map> </property> <property
name="defaultTargetDataSource" ref="masterDataSource"/> </bean> <!-- Mybatis file
--> <bean id="sqlSessionFactory"class
="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation"
value="classpath:mybatis-config.xml" /> <property name="dataSource"
ref="dataSource" /> <!-- Map file path --> <property name="mapperLocations"
value="classpath*:dbmappers/*.xml" /> </bean> <beanclass
="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property
name="basePackage" value="com.yzb.dao" /> <property
name="sqlSessionFactoryBeanName" value="sqlSessionFactory" /> </bean> <!--
Transaction manager --> <bean id="transactionManager"class
="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property
name="dataSource" ref="dataSource" /> </bean> <!-- Declarative opening -->
<tx:annotation-driven transaction-manager="transactionManager" proxy-target-
class="true" order="1"/> <!-- Method analysis for business logic [email protected] annotation
  For the current threadHandleDataSource Inject data source -->  <bean id="dataSourceAspect"class
="com.yzb.util.DataSourceAspect" /> <aop:config proxy-target-class="true">
<aop:aspect id="dataSourceAspect" ref="dataSourceAspect" order="2">
<aop:pointcut id="tx" expression="execution(* com.yzb.service.impl..*.*(..))
"/> <aop:before pointcut-ref="tx" method="before" /> </aop:aspect>
</aop:config> </beans>
AOP Dynamic switch of data source

DataSource.java
package com.yzb.util; import java.lang.annotation.ElementType; import
java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target; /** * RUNTIME * The compiler will record the comments in the class file, At run time VM
Will keep comments, So it can be read reflexively. **/ @Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)public @interface DataSource { String value(); }
DataSourceAspect.java
package com.yzb.util; import java.lang.reflect.Method; import
org.aspectj.lang.JoinPoint;import org.aspectj.lang.reflect.MethodSignature;
public class DataSourceAspect { /** * staydao Layer method acquisitiondatasource Before object, Specify the current thread data source in the slice */
public void before(JoinPoint point) { Object target = point.getTarget(); String
method= point.getSignature().getName(); Class<?>[] classz =
target.getClass().getInterfaces();// Get the interface of the target class, [email protected] Need to be written on the interface Class<?>[]
parameterTypes = ((MethodSignature) point.getSignature())
.getMethod().getParameterTypes();try { Method m = classz[0].getMethod(method,
parameterTypes);if (m != null && m.isAnnotationPresent(DataSource.class)) {
DataSource data= m.getAnnotation(DataSource.class); System.out.println(
" User selects database type:" + data.value()); HandleDataSource.putDataSource(data.value()); //
Put the data source into the current thread } } catch (Exception e) { e.printStackTrace(); } } }
DynamicDataSource.java
package com.yzb.util; import
org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;public
class DynamicDataSource extends AbstractRoutingDataSource { /** * Get data source relatedkey
thiskey yesMap<String,DataSource> resolvedDataSources Data source boundkey value *
Pass throughdetermineTargetDataSource Use when getting the target data source*/ @Override protected Object
determineCurrentLookupKey() {return HandleDataSource.getDataSource(); } }
HandleDataSource.java
package com.yzb.util; public class HandleDataSource { public static final
ThreadLocal<String> holder =new ThreadLocal<String>(); /** * Bind current thread data source * *
@param key */ public static void putDataSource(String datasource) {
holder.set(datasource); }/** * Get the data source of the current thread * * @return */ public static String
getDataSource() {return holder.get(); } }
service Application on [email protected] Implement data source specification
package com.yzb.service; import java.util.List; import com.yzb.model.Person;
import com.yzb.util.DataSource; public interface IPersonService { /** *
Load allperson *@return */ List<Person> listAllPerson(); /** * Query someone's information * @param
personId *@return */ @DataSource("slave") // Specify use from data source Person getPerson(int
personId);boolean updatePerson(Person person); }
Attention points

   When testing, How to know that it's from the database? We can modify the value of a certain field of the record queried from the database, Distinguish the main, From database;

       Transaction needs attention, Try to keep transactions on one data source;

       When a certainservice There are more than one.aop Time, Need attentionaop The order of weaving, utilizeorder Key words control weaving order;

   Complete projectgithub address <https://github.com/youzhibing/maven-ssm-web>
:https://github.com/youzhibing/maven-ssm-web, Realized in the projectredis cache, Not to visit:http://localhost:8080/maven-ssm-web/personController/showPerson There's no problem, Of course you canredis Build and integrate services;

   testurl:http://localhost:8080/maven-ssm-web/personController/person?personId=1

Reference resources

  《 Technical framework of large website_ Core principles and case analysis》

  Spring+MyBatis Implementation of database read-write separation scheme <http://www.jianshu.com/p/2222257f96d3>