Java implementation of MySQL database read/write separation definition Multi-data source mode

The example is based on spring-provided Abstractroutingdatasource, which implements a dynamic data source that defines multiple databases in the spring configuration as primary, from the database, and implements the primary table operation when the record is saved and modified. The query then operates from the table, thus enabling the read and write separation of the database tables. This helps improve the performance of the site, especially at the database level. Because in the actual application, the database is read more write less (the frequency of reading data is high, the frequency of updating data is relatively small), and the reading data is usually time-consuming, occupy the database server more CPU, thereby affecting the user experience. Our common practice is to extract queries from the main repository, using multiple slave libraries, and use load balancing to reduce the pressure on each query from the library. The example does not describe database synchronization, only the separation of read and write operations is implemented:

Before you proceed, just briefly say something about Abstractroutingdatasource:

1 Abstractroutingdatasource inherits the Abstractdatasource, and Abstractdatasource is the subclass of DataSource. DataSource is the data source interface for Javax.sql, which is defined as follows:2 3  Public Abstract classAbstractroutingdatasourceextendsAbstractdatasourceImplementsInitializingbean {}4 5  Public InterfaceDataSourceextendsCommondatasource,wrapper {6 7   /**8 * <p>attempts to establish a connection with the data source9 * This <code>DataSource</code> object represents.Ten    * One    * @returna connection to the data source A    * @exceptionSQLException If a database access error occurs -    */ -Connection getconnection ()throwsSQLException; the  -   /** - * <p>attempts to establish a connection with the data source - * This <code>DataSource</code> object represents. +    * -    * @paramusername The database user on whose behalf the connection is + * being made A    * @parampassword The user ' s password at    * @returna connection to the data source -    * @exceptionSQLException If a database access error occurs -    * @since1.4 -    */ - Connection getconnection (string Username, string password) -     throwsSQLException; in  - } to  +  -  PublicConnection getconnection ()throwsSQLException { the         returnDeterminetargetdatasource (). getconnection (); *     } $ Panax Notoginseng      PublicConnection getconnection (string Username, string password)throwsSQLException { -         returnDeterminetargetdatasource (). getconnection (username, password); the     } +  A protectedDataSource Determinetargetdatasource () { theAssert.notnull ( This. resolveddatasources, "DataSource Router not initialized"); +Object LookupKey =Determinecurrentlookupkey (); -DataSource DataSource = This. Resolveddatasources.get (LookupKey); $         if(DataSource = =NULL&& ( This. lenientfallback | | LookupKey = =NULL)) { $DataSource = This. Resolveddefaultdatasource; -         } -         if(DataSource = =NULL) { the             Throw NewIllegalStateException ("Cannot determine target DataSource for lookup key [" + LookupKey + "]"); -         }Wuyi         returnDataSource; the}

View Code

It is not difficult to see from the above code that getting a data source is the first to get the value of the resolveddatasources corresponding key by calling Determinecurrentlookupkey (). It is therefore possible to create a dynamic data source class that inherits Abstractroutingdatasource, Determinecurrentlookupkey () to customize the settings and obtain the Resolveddatasources key.

The steps are as follows:

The first step:

<?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:aop= "HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/AOP"Xmlns:context= "Http://www.springframework.org/schema/context"Xmlns:jdbc= "Http://www.springframework.org/schema/jdbc" xmlns:tx= "Http://www.springframework.org/schema/tx"XMLNS:JPA= "HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/DATA/JPA"xsi:schemalocation="http//Www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.1.xsdhttp//WWW.SPRINGFRAMEWORK.ORG/SCHEMA/AOPhttp://www.springframework.org/schema/aop/spring-aop-3.1.xsdhttp//Www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-3.1.xsdhttp//Www.springframework.org/schema/jdbchttp://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsdhttp//Www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.1.xsdhttp//WWW.SPRINGFRAMEWORK.ORG/SCHEMA/DATA/JPAhttp://www.springframework.org/schema/data/jpa/spring-jpa.xsd"    default-lazy-init= "true" > <!--Introducing configuration Files--<context:component-scan base- Package= "com.he"/> <bean id= "Masterdatasource"class= "Org.apache.commons.dbcp.BasicDataSource"Destroy-method= "Close" > <property name= "driverclassname" value= "Com.mysql.jdbc.Driver"/> <property N Ame= "url" value= "jdbc:mysql://localhost:3306/test"/> <property name= "username" value= "root"/> &lt ;p roperty name= "password" value= "111111"/> </bean> <bean id= "Slavedatasource"class= "Org.apache.commons.dbcp.BasicDataSource"Destroy-method= "Close" > <property name= "driverclassname" value= "Com.mysql.jdbc.Driver"/> <property N Ame= "url" value= "Jdbc:mysql://localhost:3306/test2"/> <property name= "username" value= "root"/> &L T;property name= "Password" value= "111111"/> </bean> <bean id= "DataSource"class= "Com.he.mysql.test.DynamicDataSource" > <property name= "targetdatasources" > <map Key-typ E= "Java.lang.String" > <!--write--<entry key= "Masterdatasource" Value-re f= "Masterdatasource"/> <!--read--<entry key= "Slavedatasource" value-ref= "Slavedatasource"/> </map> </property> <property name= "de Faulttargetdatasource "ref=" Masterdatasource "/> </bean> <bean id=" Sqlsessionfactory "class= "Org.mybatis.spring.SqlSessionFactoryBean" > <property name= "dataSource" ref= "DataSource"/> &L T;property name= "mapperlocations" value= "Classpath:com/he/dao/*.xml" ></property> </bean> <beanclass= "Org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name= "basepackage" value= "Com.he.dao"/>          <property name= "Sqlsessionfactorybeanname" value= "Sqlsessionfactory" ></property> </bean> <bean id= "TransactionManager"class= "Org.springframework.jdbc.datasource.DataSourceTransactionManager" > <property name= "DataSource" ref= "DataS Ource "/> </bean> <!--annotated transaction management, which needs to be labeled on the service class @transactional--<tx:annotation-driven tra nsaction-manager= "TransactionManager"/> </beans>

View Code

Step Two:

1  Public class extends Abstractroutingdatasource {23    @Override4     protected  Object Determinecurrentlookupkey () {5      6         return  Dynamicdatasourceholder.getdatasouce (); 7     }89 }

To create a Dynamic Data source class inheritance Abstractroutingdatasource

Step Three:

1  Public classDynamicdatasourceholder {2      Public Static Finalthreadlocal<string> holder =NewThreadlocal<string>();3 4      Public Static voidPutdatasource (String name) {5 Holder.set (name);6     }7 8      Public StaticString getdatasouce () {9         returnholder.get ();Ten     } One}

set and get which data source each thread accesses

Fourth Step:

1@Service ("UserService")  2 @Transactional3  Public classUserserviceimplImplementsuserservice{4 5 @Autowired6     PrivateUsermapper Userdao; Public voidAdd (user user) {7         8Dynamicdatasourceholder.putdatasource ("Masterdatasource");9 userdao.add (user);Ten     } One  A      Public voidUpdate (user user) { -          -Dynamicdatasourceholder.putdatasource ("Masterdatasource"); the userdao.updates (user); -      -          -     } +  -@Transactional (propagation =propagation.not_supported) +      PublicList<user>query () { A          atDynamicdatasourceholder.putdatasource ("Slavedatasource"); -List<user> User =userdao.query (); -         returnuser; -          -     } -  in      -      to      +}

set up data source code for service implementation tier join

The above for the realization of the key part of the read and write separation, just to do a simple example, after the completion of the above operation, the database can be self-new and query operation, to see the effect

Java implementation of MySQL database read/write separation definition Multi-data source mode