您当前的位置:网站首页>虫儿飞,SpringBoot+MyBatis+MySQL读写别离(实例),汾酒

虫儿飞,SpringBoot+MyBatis+MySQL读写别离(实例),汾酒

2019-04-20 00:34:13 投稿作者:admin 围观人数:310 评论人数:0次

1. 导言

读写分别要做的工作便是关于一条SQL该挑选哪个数据库去履行,至于谁来做挑选数据库这件事儿,无非两个,要么中间件帮咱们做,要么程序自己做。因而,一般来讲,读写分别有两种完成办法。第一种是依托中间件(比方:MyCat),也便是说运用程序连接到中间件,中间件帮咱们做SQL分别;第二种是运用程序自己去做分别。这儿咱们挑选程序自己来做,主要是运用Spring供给的路由数据源,以及AOP。

可是,运用程序层面去做读写分别最大的缺点(不足之处)在于无法动态添加数据库游戏排名节点,由于数据源装备都是写在装备中的,新增数据库意味着新加一个数据源,必定改装备,并重启运用。当然,优点便是相对简略。


2. AbstractRoutingDataSource

根据特定的查找key路由到特定的数据源。它内部保护了一组方针数据源,并且做了路由key与方针数据源之间的映射,供给根据key查找数据源的办法。


3. 实践

关于装备请参阅:

https://www.cnblogs.com/cjsblog/p/9706370.html

3.1俺去啦. maven依靠天气预报30天查询


xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
4.0.0
com.cjs.example
cjs-datasource-demo
0.0.1-SNAPSHOT
jar
cjs-datasource-demo


org.springframework.boot
spring-boot-starter-parent
2.0.5.RELEASE



UTF-8
UTF-8
1.8



org.springframework.boot
spring-boot-starter-aop


org.springframework.boot
spring-boot-starter-jdbc


org.springframework.boot
spring-boot-starter-web


org.mybatis.spring.boot
mybatis-spring-boot-starter
1.3.2


org.apache.co明华堂mmons
commons-lang3
3.8


mysql
mysql-connector-java
runtime


org.springframework.boot
spring-boot-starter-test
test





org.springframework.boot
spring-boot-maven-plugin





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.DataSou地包天r妻为上ce;
import java.util.HashMap;
import java.util.Map;
/**
* 关于数据源装备,参阅SpringBoot官方文档第79章《Data Access》
* 79. Data Acces眉山s
* 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 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);
myRoutingData虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒Source.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;
impo回忆碎片rt 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 = "my重生追美记RoutingDataSource")
private DataSource myRoutingDataSource;
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSe右胸口疼是怎么回事ssionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xm虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒l"));
return sqlSessionFactoryBean.getObject();
}
@Bean
public Pl小舟的折法atformTransactionManager platformTransactionManager() {
return new DataSourc弗莱轮运送eTransactionManager(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 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.aspect虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒j.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) " +
"&& (executi属虎的和什么属相最配on(* 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*(虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒..)) " +
"|| execution(* com.cjs.example.service..*.update*(..)) " +
"|| execution(* com.cjs.exam曹璐ple.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")) {
// DBContextHo虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒lder.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.exa琳mple.mapper.MemberMapper;
import com.cjs.example.service.MemberService;
import o虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒rg.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 selectAll() {
return memberMapper.selectByExample(new MemberExample());
}
@Master
@Override
public String getToken(Strin姑苏旺道seog 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.j虫儿飞,SpringBoot+MyBatis+MySQL读写分别(实例),汾酒unit4.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 (intsmartisys 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. 参阅

www.jianshu.com/p/f2f4256a2310

www.cnblogs.com/gl-developer/p/6170423.html

www.cnblogs.com/huangjuncong/p/8576935.html

blog.csdn.net/liu976180578/article/details/77684583

the end
大大方方随时可以公开的恋爱,爱情三十六计