vlambda博客
学习文章列表

CREATE TABLE student( student_id VARCHAR(32), student_name VARCHAR(32));

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.1.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.cnddcm</groupId> <artifactId>DBMasterAndSlave</artifactId> <version>0.0.1-SNAPSHOT</version> <name>DBMasterAndSlave</name> <description>mysql主从模式与读写分离</description>
 <properties> <java.version>1.8</java.version> </properties>
 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <!-- Web相关 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId> spring-boot-configuration-processor </artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>
 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> </dependencies>
 <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build>
</project>

server:  port: 10001spring:  datasource: url: jdbc:mysql://106.54.54.200:3306/springtestdemo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: username password: password driver-class-name: com.mysql.cj.jdbc.Driver
#MyBatis配置mybatis: mapper-locations: classpath:mapper/*.xml configuration: map-underscore-to-camel-case: true

package com.cnddcm.DBMasterAndSlave.dao;
import com.cnddcm.DBMasterAndSlave.domain.Student;import org.apache.ibatis.annotations.*;import org.apache.ibatis.annotations.Insert;import org.apache.ibatis.annotations.Select;@Mapperpublic interface StudentDao { @Insert("INSERT INTO student(student_id,student_name)VALUES(#{studentId},#{studentName})") public Integer insertStudent(Student student); @Select("SELECT * FROM student WHERE student_id = #{studentId}") public Student queryStudentByStudentId(Student student);}

@RestControllerpublic class DaoTest {
 @Autowired StudentDao studentDao; @GetMapping("/test") public void test01() { Student student = new Student(); student.setStudentId("20200712"); student.setStudentName("cnddcm"); studentDao.insertStudent(student); studentDao.queryStudentByStudentId(student); }}

server: port: 10001spring: datasource: master: url: jdbc:mysql://106.54.54.200:3306/springtestdemo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: username password: password driver-class-name: com.mysql.cj.jdbc.Driver slave: url: jdbc:mysql://49.235.59.171:3306/springtestdemo?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC username: username password: password driver-class-name: com.mysql.cj.jdbc.Driver#MyBatis配置mybatis: mapper-locations: classpath:mapper/*.xml configuration: map-underscore-to-camel-case: true

public class DynamicDataSource extends AbstractRoutingDataSource{ //注入主从数据源 @Resource(name="masterDataSource") private DataSource masterDataSource; @Resource(name="slaveDataSource") private DataSource slaveDataSource; @Override public void afterPropertiesSet() { setDefaultTargetDataSource(masterDataSource); Map<Object, Object> dataSourceMap = new HashMap<>(); //将两个数据源set入目标数据源 dataSourceMap.put("master", masterDataSource); dataSourceMap.put("slave", slaveDataSource); setTargetDataSources(dataSourceMap);
 super.afterPropertiesSet(); } @Override protected Object determineCurrentLookupKey() { //确定最终的目标数据源 return DynamicDataSourceHolder.getDbType(); }}

public class DynamicDataSourceHolder { private static Logger logger = LoggerFactory.getLogger(DynamicDataSourceHolder.class); private static ThreadLocal<String> contextHolder = new ThreadLocal<>(); public static final String DB_MASTER = "master"; public static final String DB_SLAVE="slave"; /** * @Description: 获取线程的DbType * @Param: args * @return: String * @Author: Object * @Date: 2019年11月30日 */ public static String getDbType() { String db = contextHolder.get(); if(db==null) { db = "master"; } return db; } /** * @Description: 设置线程的DbType * @Param: args * @return: void * @Author: Object * @Date: 2019年11月30日 */ public static void setDbType(String str) { logger.info("所使用的数据源为:"+str); contextHolder.set(str); }
 /** * @Description: 清理连接类型 * @Param: args * @return: void * @Author: Object * @Date: 2019年11月30日 */ public static void clearDbType() { contextHolder.remove(); }}

@Intercepts({  @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }), @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,RowBounds.class, ResultHandler.class }) })@Componentpublic class DynamicDataSourceInterceptor implements Interceptor { private Logger logger = LoggerFactory.getLogger(DynamicDataSourceInterceptor.class); // 验证是否为写SQL的正则表达式 private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";
 /** * 主要的拦截方法 */ @Override public Object intercept(Invocation invocation) throws Throwable { // 判断当前是否被事务管理 boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive(); String lookupKey = DynamicDataSourceHolder.DB_MASTER; if (!synchronizationActive) { //如果是非事务的，则再判断是读或者写。 // 获取SQL中的参数 Object[] objects = invocation.getArgs(); // object[0]会携带增删改查的信息，可以判断是读或者是写 MappedStatement ms = (MappedStatement) objects[0]; // 如果为读，且为自增id查询主键，则使用主库 // 这种判断主要用于插入时返回ID的操作，由于日志同步到从库有延时 // 所以如果插入时需要返回id，则不适用于到从库查询数据，有可能查询不到 if (ms.getSqlCommandType().equals(SqlCommandType.SELECT) && ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) { lookupKey = DynamicDataSourceHolder.DB_MASTER; } else { BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]); String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " "); // 正则验证 if (sql.matches(REGEX)) { // 如果是写语句 lookupKey = DynamicDataSourceHolder.DB_MASTER; } else { lookupKey = DynamicDataSourceHolder.DB_SLAVE; } } } else { // 如果是通过事务管理的，一般都是写语句,直接通过主库 lookupKey = DynamicDataSourceHolder.DB_MASTER; }
 logger.info("在" + lookupKey + "中进行操作"); DynamicDataSourceHolder.setDbType(lookupKey); // 最后直接执行SQL return invocation.proceed(); }
 /** * 返回封装好的对象，或代理对象 */ @Override public Object plugin(Object target) { // 如果存在增删改查，则直接拦截下来，否则直接返回 if (target instanceof Executor) return Plugin.wrap(target, this); else return target; }
 /** * 类初始化的时候做一些相关的设置 */ @Override public void setProperties(Properties properties) { // TODO Auto-generated method stub
 }
}