技術(shù)學(xué)習(xí)視頻教程網(wǎng)盤資源整理 https://pan.baidu.com/s/13dbR69NLIEyP1tQyRTl4xw
讀寫分離要做的事情就是對(duì)于一條SQL該選擇哪個(gè)數(shù)據(jù)庫去執(zhí)行，至于誰來做選擇數(shù)據(jù)庫這件事兒，無非兩個(gè)，要么中間件幫我們做，要么程序自己做。因此，一般來講，讀寫分離有兩種實(shí)現(xiàn)方式。第一種是依靠中間件（比如：MyCat），也就是說應(yīng)用程序連接到中間件，中間件幫我們做SQL分離；第二種是應(yīng)用程序自己去做分離。這里我們選擇程序自己來做，主要是利用Spring提供的路由數(shù)據(jù)源，以及AOP
然而，應(yīng)用程序?qū)用嫒プ鲎x寫分離最大的弱點(diǎn)（不足之處）在于無法動(dòng)態(tài)增加數(shù)據(jù)庫節(jié)點(diǎn)，因?yàn)閿?shù)據(jù)源配置都是寫在配置中的，新增數(shù)據(jù)庫意味著新加一個(gè)數(shù)據(jù)源，必然改配置，并重啟應(yīng)用。當(dāng)然，好處就是相對(duì)簡(jiǎn)單。
基于特定的查找key路由到特定的數(shù)據(jù)源。它內(nèi)部維護(hù)了一組目標(biāo)數(shù)據(jù)源，并且做了路由key與目標(biāo)數(shù)據(jù)源之間的映射，提供基于key查找數(shù)據(jù)源的方法。
MySQL主從復(fù)制配置(https://www.cnblogs.com/cjsblog/p/9706370.html)》
<?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>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
多數(shù)據(jù)源配置
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; /** * 關(guān)于數(shù)據(jù)源配置，參考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個(gè)數(shù)據(jù)源，1個(gè)master，2兩個(gè)slave，1個(gè)路由數(shù)據(jù)源。前3個(gè)數(shù)據(jù)源都是為了生成第4個(gè)數(shù)據(jù)源，而且后續(xù)我們只用這最后一個(gè)路由數(shù)據(jù)源。
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容器中現(xiàn)在有4個(gè)數(shù)據(jù)源，所以我們需要為事務(wù)管理器和MyBatis手動(dòng)指定一個(gè)明確的數(shù)據(jù)源。
目標(biāo)數(shù)據(jù)源就是那前3個(gè)這個(gè)我們是知道的，但是使用的時(shí)候是如果查找數(shù)據(jù)源的呢？
首先，我們定義一個(gè)枚舉來代表這三個(gè)數(shù)據(jù)源
package com.cjs.example.enums; public enum DBTypeEnum { MASTER, SLAVE1, SLAVE2;}接下來，通過ThreadLocal將數(shù)據(jù)源設(shè)置到每個(gè)線程上下文中
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(); }}設(shè)置路由key
默認(rèn)情況下，所有的查詢都走從庫，插入/修改/刪除走主庫。我們通過方法名來區(qū)分操作類型（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... 判斷哪些需要讀從數(shù)據(jù)庫，其余的走主數(shù)據(jù)庫 */// @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(); // } // }}有一般情況就有特殊情況，特殊情況是某些情況下我們需要強(qiáng)制讀主庫，針對(duì)這種情況，我們定義一個(gè)主鍵，用該注解標(biāo)注的就讀主庫
package com.cjs.example.annotation; public @interface Master {}例如，假設(shè)我們有一張表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) { // 有些讀操作必須讀主數(shù)據(jù)庫 // 比如，獲取微信access_token，因?yàn)楦叻鍟r(shí)期主從同步可能延遲 // 這種情況下就必須強(qiáng)制從主數(shù)據(jù)讀 return null; }}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"); }}查看控制臺(tái)
聯(lián)系客服
