SpringBoot+MyBatis+MySQL读写分离
1. 引言
读写分离要做的事情就是对于一条sql该选择哪个数据库去执行,至于谁来做选择数据库这件事儿,无非两个,要么中间件帮我们做,要么程序自己做。因此,一般来讲,读写分离有两种实现方式。第一种是依靠中间件(比如:mycat),也就是说应用程序连接到中间件,中间件帮我们做sql分离;第二种是应用程序自己去做分离。这里我们选择程序自己来做,主要是利用spring提供的路由数据源,以及aop
然而,应用程序层面去做读写分离最大的弱点(不足之处)在于无法动态增加数据库节点,因为数据源配置都是写在配置中的,新增数据库意味着新加一个数据源,必然改配置,并重启应用。当然,好处就是相对简单。
2. abstractroutingdatasource
基于特定的查找key路由到特定的数据源。它内部维护了一组目标数据源,并且做了路由key与目标数据源之间的映射,提供基于key查找数据源的方法。
3. 实践
关于配置请参考《mysql主从复制配置》
3.1. maven依赖
<?xml version="1.0" encoding="utf-8"?> <project xmlns="http://maven.apache.org/pom/4.0.0" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://maven.apache.org/pom/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelversion>4.0.0</modelversion> <groupid>com.cjs.example</groupid> <artifactid>cjs-datasource-demo</artifactid> <version>0.0.1-snapshot</version> <packaging>jar</packaging> <name>cjs-datasource-demo</name> <description></description> <parent> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-parent</artifactid> <version>2.0.5.release</version> <relativepath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceencoding>utf-8</project.build.sourceencoding> <project.reporting.outputencoding>utf-8</project.reporting.outputencoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-aop</artifactid> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-jdbc</artifactid> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-web</artifactid> </dependency> <dependency> <groupid>org.mybatis.spring.boot</groupid> <artifactid>mybatis-spring-boot-starter</artifactid> <version>1.3.2</version> </dependency> <dependency> <groupid>org.apache.commons</groupid> <artifactid>commons-lang3</artifactid> <version>3.8</version> </dependency> <dependency> <groupid>mysql</groupid> <artifactid>mysql-connector-java</artifactid> <scope>runtime</scope> </dependency> <dependency> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-starter-test</artifactid> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupid>org.springframework.boot</groupid> <artifactid>spring-boot-maven-plugin</artifactid> </plugin> <!--<plugin> <groupid>org.mybatis.generator</groupid> <artifactid>mybatis-generator-maven-plugin</artifactid> <version>1.3.5</version> <dependencies> <dependency> <groupid>mysql</groupid> <artifactid>mysql-connector-java</artifactid> <version>5.1.46</version> </dependency> </dependencies> <configuration> <configurationfile>${basedir}/src/main/resources/mybatisgeneratorconfig.xml</configurationfile> <overwrite>true</overwrite> </configuration> <executions> <execution> <id>generate mybatis artifacts</id> <goals> <goal>generate</goal> </goals> </execution> </executions> </plugin>--> </plugins> </build> </project>
3.2. 数据源配置
application.yml
spring: datasource: master: jdbc-url: jdbc:mysql://192.168.102.31:3306/test username: root password: 123456 driver-class-name: com.mysql.jdbc.driver slave1: jdbc-url: jdbc:mysql://192.168.102.56:3306/test username: pig # 只读账户 password: 123456 driver-class-name: com.mysql.jdbc.driver slave2: jdbc-url: jdbc:mysql://192.168.102.36:3306/test username: pig # 只读账户 password: 123456 driver-class-name: com.mysql.jdbc.driver
多数据源配置
package com.cjs.example.config; import com.cjs.example.bean.myroutingdatasource; import com.cjs.example.enums.dbtypeenum; import org.springframework.beans.factory.annotation.qualifier; import org.springframework.boot.context.properties.configurationproperties; import org.springframework.boot.jdbc.datasourcebuilder; import org.springframework.context.annotation.bean; import org.springframework.context.annotation.configuration; import javax.sql.datasource; import java.util.hashmap; import java.util.map; /** * 关于数据源配置,参考springboot官方文档第79章《data access》 * 79. data access * 79.1 configure a custom datasource * 79.2 configure two datasources */ @configuration public class datasourceconfig { @bean @configurationproperties("spring.datasource.master") public datasource masterdatasource() { return datasourcebuilder.create().build(); } @bean @configurationproperties("spring.datasource.slave1") public datasource slave1datasource() { return datasourcebuilder.create().build(); } @bean @configurationproperties("spring.datasource.slave2") public datasource slave2datasource() { return datasourcebuilder.create().build(); } @bean public datasource myroutingdatasource(@qualifier("masterdatasource") datasource masterdatasource, @qualifier("slave1datasource") datasource slave1datasource, @qualifier("slave2datasource") datasource slave2datasource) { map<object, object> targetdatasources = new hashmap<>(); targetdatasources.put(dbtypeenum.master, masterdatasource); targetdatasources.put(dbtypeenum.slave1, slave1datasource); targetdatasources.put(dbtypeenum.slave2, slave2datasource); myroutingdatasource myroutingdatasource = new myroutingdatasource(); myroutingdatasource.setdefaulttargetdatasource(masterdatasource); myroutingdatasource.settargetdatasources(targetdatasources); return myroutingdatasource; } }
这里,我们配置了4个数据源,1个master,2两个slave,1个路由数据源。前3个数据源都是为了生成第4个数据源,而且后续我们只用这最后一个路由数据源。
mybatis配置
package com.cjs.example.config; import org.apache.ibatis.session.sqlsessionfactory; import org.mybatis.spring.sqlsessionfactorybean; import org.springframework.context.annotation.bean; import org.springframework.context.annotation.configuration; import org.springframework.core.io.support.pathmatchingresourcepatternresolver; import org.springframework.jdbc.datasource.datasourcetransactionmanager; import org.springframework.transaction.platformtransactionmanager; import org.springframework.transaction.annotation.enabletransactionmanagement; import javax.annotation.resource; import javax.sql.datasource; @enabletransactionmanagement @configuration public class mybatisconfig { @resource(name = "myroutingdatasource") private datasource myroutingdatasource; @bean public sqlsessionfactory sqlsessionfactory() throws exception { sqlsessionfactorybean sqlsessionfactorybean = new sqlsessionfactorybean(); sqlsessionfactorybean.setdatasource(myroutingdatasource); sqlsessionfactorybean.setmapperlocations(new pathmatchingresourcepatternresolver().getresources("classpath:mapper/*.xml")); return sqlsessionfactorybean.getobject(); } @bean public platformtransactionmanager platformtransactionmanager() { return new datasourcetransactionmanager(myroutingdatasource); } }
由于spring容器中现在有4个数据源,所以我们需要为事务管理器和mybatis手动指定一个明确的数据源。
3.3. 设置路由key / 查找数据源
目标数据源就是那前3个这个我们是知道的,但是使用的时候是如果查找数据源的呢?
首先,我们定义一个枚举来代表这三个数据源
package com.cjs.example.enums; public enum dbtypeenum { master, slave1, slave2; }
接下来,通过threadlocal将数据源设置到每个线程上下文中
package com.cjs.example.bean; import com.cjs.example.enums.dbtypeenum; import java.util.concurrent.atomic.atomicinteger; public class dbcontextholder { private static final threadlocal<dbtypeenum> contextholder = new threadlocal<>(); private static final atomicinteger counter = new atomicinteger(-1); public static void set(dbtypeenum dbtype) { contextholder.set(dbtype); } public static dbtypeenum get() { return contextholder.get(); } public static void master() { set(dbtypeenum.master); system.out.println("切换到master"); } public static void slave() { // 轮询 int index = counter.getandincrement() % 2; if (counter.get() > 9999) { counter.set(-1); } if (index == 0) { set(dbtypeenum.slave1); system.out.println("切换到slave1"); }else { set(dbtypeenum.slave2); system.out.println("切换到slave2"); } } }
获取路由key
package com.cjs.example.bean; import org.springframework.jdbc.datasource.lookup.abstractroutingdatasource; import org.springframework.lang.nullable; public class myroutingdatasource extends abstractroutingdatasource { @nullable @override protected object determinecurrentlookupkey() { return dbcontextholder.get(); } }
设置路由key
默认情况下,所有的查询都走从库,插入/修改/删除走主库。我们通过方法名来区分操作类型(crud)
package com.cjs.example.aop; import com.cjs.example.bean.dbcontextholder; import org.apache.commons.lang3.stringutils; import org.aspectj.lang.joinpoint; import org.aspectj.lang.annotation.aspect; import org.aspectj.lang.annotation.before; import org.aspectj.lang.annotation.pointcut; import org.springframework.stereotype.component; @aspect @component public class datasourceaop { @pointcut("!@annotation(com.cjs.example.annotation.master) " + "&& (execution(* com.cjs.example.service..*.select*(..)) " + "|| execution(* com.cjs.example.service..*.get*(..)))") public void readpointcut() { } @pointcut("@annotation(com.cjs.example.annotation.master) " + "|| execution(* com.cjs.example.service..*.insert*(..)) " + "|| execution(* com.cjs.example.service..*.add*(..)) " + "|| execution(* com.cjs.example.service..*.update*(..)) " + "|| execution(* com.cjs.example.service..*.edit*(..)) " + "|| execution(* com.cjs.example.service..*.delete*(..)) " + "|| execution(* com.cjs.example.service..*.remove*(..))") public void writepointcut() { } @before("readpointcut()") public void read() { dbcontextholder.slave(); } @before("writepointcut()") public void write() { dbcontextholder.master(); } /** * 另一种写法:if...else... 判断哪些需要读从数据库,其余的走主数据库 */ // @before("execution(* com.cjs.example.service.impl.*.*(..))") // public void before(joinpoint jp) { // string methodname = jp.getsignature().getname(); // // if (stringutils.startswithany(methodname, "get", "select", "find")) { // dbcontextholder.slave(); // }else { // dbcontextholder.master(); // } // } }
有一般情况就有特殊情况,特殊情况是某些情况下我们需要强制读主库,针对这种情况,我们定义一个主键,用该注解标注的就读主库
package com.cjs.example.annotation; public @interface master { }
例如,假设我们有一张表member
package com.cjs.example.service.impl; import com.cjs.example.annotation.master; import com.cjs.example.entity.member; import com.cjs.example.entity.memberexample; import com.cjs.example.mapper.membermapper; import com.cjs.example.service.memberservice; import org.springframework.beans.factory.annotation.autowired; import org.springframework.stereotype.service; import org.springframework.transaction.annotation.transactional; import java.util.list; @service public class memberserviceimpl implements memberservice { @autowired private membermapper membermapper; @transactional @override public int insert(member member) { return membermapper.insert(member); } @master @override public int save(member member) { return membermapper.insert(member); } @override public list<member> selectall() { return membermapper.selectbyexample(new memberexample()); } @master @override public string gettoken(string appid) { // 有些读操作必须读主数据库 // 比如,获取微信access_token,因为高峰时期主从同步可能延迟 // 这种情况下就必须强制从主数据读 return null; } }
4. 测试
package com.cjs.example; import com.cjs.example.entity.member; import com.cjs.example.service.memberservice; import org.junit.test; import org.junit.runner.runwith; import org.springframework.beans.factory.annotation.autowired; import org.springframework.boot.test.context.springboottest; import org.springframework.test.context.junit4.springrunner; @runwith(springrunner.class) @springboottest public class cjsdatasourcedemoapplicationtests { @autowired private memberservice memberservice; @test public void testwrite() { member member = new member(); member.setname("zhangsan"); memberservice.insert(member); } @test public void testread() { for (int i = 0; i < 4; i++) { memberservice.selectall(); } } @test public void testsave() { member member = new member(); member.setname("wangwu"); memberservice.save(member); } @test public void testreadfrommaster() { memberservice.gettoken("1234"); } }
查看控制台
5. 工程结构
6. 参考
上一篇: C# winform 弹出输入框
下一篇: 工业机器人与数控机床融合发展的途径