005 分库分表_多数据源切换读写分离实战

最新推荐文章于 2024-03-04 19:52:26 发布

诸般世界

最新推荐文章于 2024-03-04 19:52:26 发布

阅读量686

点赞数

分类专栏：数据分库分表

本文链接：https://blog.csdn.net/zxylwj/article/details/102752630

版权

数据分库分表专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.数据库层面Mysql（MariaDB），实现读写分离、主从切换、数据源切换：

首先实现读写分离，就意味着需要有两个数据源，当写操作的时候对主库（master）使用，当读操作的时候对从库（slave）使用。那么在启动数据库连接池的时候即要启动两个，在实际使用时可以在方法上加上自定义注解的形式来区分读写。

2.实现解析：

（1）配置好两个druid数据源，然后要区分开两个数据源：分别确定主、从数据源。

（2）通过mybatis配置文件把两个数据源注入到应用中，但要想实现读写分离，也就是能确定是吗情况下用写，什么情况下用读，需要自定义一个标示来区分。

（3）要实现一个即时切换主从数据源的标识，并且能够保证线程足够安全的基础下进行操作数据源（并发会影响数据源的获取、分不清主从，当发生从库进行写操作会影响MariaDB数据库机制，导致数据库服务器异常，当然也可以进行恢复，但千万小心谨慎，所以要使用threadlocal来解决这个问题，使用threadlocal来存储数据源的标识，判断主从库）。

（4）最后实现自定义注解，在方法上有注解则只读，无注解默认为写操作。

3.实现逻辑与步骤：

3.1在master中建立四个库，每个库中建立五张表：

3.2 创建springboot项目，maven引入依赖，配置数据源：

pom.xml：

<?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>1.5.9.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.cc</groupId>
	<artifactId>balance-order</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>balance-order</name>
	 <description>balance-order</description>
  	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
        <commons-fileupload.version>1.2.2</commons-fileupload.version>
        <commons-lang3.version>3.3.1</commons-lang3.version>
        <commons-io.version>2.4</commons-io.version>
        <commons-collections.version>3.2.2</commons-collections.version>
        <fastjson.version>1.1.26</fastjson.version>
        <mybatis.version>3.4.1</mybatis.version>
        <mybatis-spring.version>1.3.0</mybatis-spring.version>
        <druid.version>1.1.10</druid.version>
        <fasterxml.uuid.version>3.1.4</fasterxml.uuid.version>
        <github.miemiedev.version>1.2.17</github.miemiedev.version>
        <servlet-api.version>3.1.0</servlet-api.version> 
        <org.codehaus.jackson.version>1.9.13</org.codehaus.jackson.version>
	</properties>
  <dependencies>
 		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>	 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency> 
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>		  	
	    <dependency>
	    	<groupId>org.springframework.boot</groupId>
	    	<artifactId>spring-boot-autoconfigure</artifactId>
	    </dependency>	
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons-lang3.version}</version>
        </dependency>	    	
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
	    <dependency>
	      <groupId>org.mybatis.spring.boot</groupId>
	      <artifactId>mybatis-spring-boot-starter</artifactId>
	      <version>1.1.1</version>
	    </dependency>
	    <dependency>
	      <groupId>tk.mybatis</groupId>
	      <artifactId>mapper-spring-boot-starter</artifactId>
	      <version>1.1.0</version>
	    </dependency>	
		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid</artifactId>
		    <version>${druid.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.0</version>
            <exclusions>
                <exclusion>
                    <artifactId>mybatis-spring-boot-starter</artifactId>
                    <groupId>org.mybatis.spring.boot</groupId>
                </exclusion>
            </exclusions>
        </dependency> 	
        <!--对json格式的支持 -->
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>${org.codehaus.jackson.version}</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
        </dependency>   
        <dependency>
            <groupId>com.fasterxml.uuid</groupId>
            <artifactId>java-uuid-generator</artifactId>
            <version>${fasterxml.uuid.version}</version>
        </dependency>         
	    <dependency>
	        <groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
	    </dependency>	        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>${fastjson.version}</version>
        </dependency>		
  
  </dependencies>
  
  
	<dependencyManagement>
	    <dependencies>
	        <dependency>
		    <groupId>org.springframework.cloud</groupId>
		    <artifactId>spring-cloud-dependencies</artifactId>
		    <version>Dalston.SR1</version>
		    <type>pom</type>
		    <scope>import</scope>
		</dependency>
	    </dependencies>
	</dependencyManagement>  


	<build>
		<finalName>balance-order</finalName>
    	<!-- 打包时包含properties、xml -->
    	<resources>
             <resource>  
                <directory>src/main/java</directory>  
                <includes>  
                    <include>**/*.properties</include>  
                    <include>**/*.xml</include>  
                </includes>  
                <!-- 是否替换资源中的属性-->  
                <filtering>true</filtering>  
            </resource>  
            <resource>  
                <directory>src/main/resources</directory>  
            </resource>      	
    	</resources> 		
		<plugins>
	        <plugin>
	            <groupId>org.apache.maven.plugins</groupId>
	            <artifactId>maven-war-plugin</artifactId>
	        </plugin>	        
			<!-- 解解决maven update project 后版本降低为1.5的bug -->
	        <plugin>
	            <groupId>org.apache.maven.plugins</groupId>
	            <artifactId>maven-compiler-plugin</artifactId>
	            <configuration>
	                <source>1.8</source>
	                <target>1.8</target>
	            </configuration>
	        </plugin>
			<!-- 单元测试 -->
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-surefire-plugin</artifactId>
				<configuration>
					<skip>true</skip>
					<includes>
						<include>**/*Test*.java</include>
					</includes>
					<testFailureIgnore>true</testFailureIgnore>
				</configuration>
			</plugin>
	    	<plugin>
	    		<groupId>org.apache.maven.plugins</groupId>
	    		<artifactId>maven-source-plugin</artifactId>
	    		<executions>
	    			<!-- 绑定到特定的生命周期之后，运行maven-source-pluin 运行目标为jar-no-fork -->
					<execution>
						<phase>package</phase>
						<goals>
							<goal>jar-no-fork</goal>
						</goals>
					</execution>
				</executions>
	    	</plugin>	
		</plugins>
		
	</build>  
</project>

application.properties:

server.context-path=/
server.port=8001


## Spring配置:
spring.http.encoding.charset=UTF-8
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8
spring.jackson.default-property-inclusion=NON_NULL

#master主数据源order1
druid.type=com.alibaba.druid.pool.DruidDataSource
druid.order1-master.url=jdbc:mysql://192.168.85.133:3306/order1?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order1-master.driver-class-name=com.mysql.jdbc.Driver
druid.order1-master.username=root
druid.order1-master.password=zxy8900461
druid.order1-master.initialSize=5
druid.order1-master.minIdle=1
druid.order1-master.maxIdle=10
druid.order1-master.maxActive=100
druid.order1-master.maxWait=60000
druid.order1-master.timeBetweenEvictionRunsMillis=60000
druid.order1-master.minEvictableIdleTimeMillis=300000
druid.order1-master.validationQuery=SELECT 1 FROM DUAL
druid.order1-master.testWhileIdle=true
druid.order1-master.testOnBorrow=false
druid.order1-master.testOnReturn=false
druid.order1-master.poolPreparedStatements=true
druid.order1-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order1-master.filters=stat,wall,log4j
druid.order1-master.useGlobalDataSourceStat=true

#master主数据源order2
druid.order2-master.url=jdbc:mysql://192.168.85.133:3306/order2?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order2-master.driver-class-name=com.mysql.jdbc.Driver
druid.order2-master.username=root
druid.order2-master.password=zxy8900461
druid.order2-master.initialSize=5
druid.order2-master.minIdle=1
druid.order2-master.maxIdle=10
druid.order2-master.maxActive=100
druid.order2-master.maxWait=60000
druid.order2-master.timeBetweenEvictionRunsMillis=60000
druid.order2-master.minEvictableIdleTimeMillis=300000
druid.order2-master.validationQuery=SELECT 1 FROM DUAL
druid.order2-master.testWhileIdle=true
druid.order2-master.testOnBorrow=false
druid.order2-master.testOnReturn=false
druid.order2-master.poolPreparedStatements=true
druid.order2-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order2-master.filters=stat,wall,log4j
druid.order2-master.useGlobalDataSourceStat=true

#master主数据源order3
druid.order3-master.url=jdbc:mysql://192.168.85.133:3306/order3?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order3-master.driver-class-name=com.mysql.jdbc.Driver
druid.order3-master.username=root
druid.order3-master.password=zxy8900461
druid.order3-master.initialSize=5
druid.order3-master.minIdle=1
druid.order3-master.maxIdle=10
druid.order3-master.maxActive=100
druid.order3-master.maxWait=60000
druid.order3-master.timeBetweenEvictionRunsMillis=60000
druid.order3-master.minEvictableIdleTimeMillis=300000
druid.order3-master.validationQuery=SELECT 1 FROM DUAL
druid.order3-master.testWhileIdle=true
druid.order3-master.testOnBorrow=false
druid.order3-master.testOnReturn=false
druid.order3-master.poolPreparedStatements=true
druid.order3-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order3-master.filters=stat,wall,log4j
druid.order3-master.useGlobalDataSourceStat=true

#master主数据源order4
druid.order4-master.url=jdbc:mysql://192.168.85.133:3306/order4?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order4-master.driver-class-name=com.mysql.jdbc.Driver
druid.order4-master.username=root
druid.order4-master.password=zxy8900461
druid.order4-master.initialSize=5
druid.order4-master.minIdle=1
druid.order4-master.maxIdle=10
druid.order4-master.maxActive=100
druid.order4-master.maxWait=60000
druid.order4-master.timeBetweenEvictionRunsMillis=60000
druid.order4-master.minEvictableIdleTimeMillis=300000
druid.order4-master.validationQuery=SELECT 1 FROM DUAL
druid.order4-master.testWhileIdle=true
druid.order4-master.testOnBorrow=false
druid.order4-master.testOnReturn=false
druid.order4-master.poolPreparedStatements=true
druid.order4-master.maxPoolPreparedStatementPerConnectionSize= 20
druid.order4-master.filters=stat,wall,log4j
druid.order4-master.useGlobalDataSourceStat=true

#slave从数据源order1
druid.order1-slave.url=jdbc:mysql://192.168.85.134:3306/order1?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order1-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order1-slave.username=root
druid.order1-slave.password=zxy8900461
druid.order1-slave.initialSize=5
druid.order1-slave.minIdle=1
druid.order1-slave.maxIdle=10
druid.order1-slave.maxActive=100
druid.order1-slave.maxWait=60000
druid.order1-slave.timeBetweenEvictionRunsMillis=60000
druid.order1-slave.minEvictableIdleTimeMillis=300000
druid.order1-slave.validationQuery=SELECT 1 FROM DUAL
druid.order1-slave.testWhileIdle=true
druid.order1-slave.testOnBorrow=false
druid.order1-slave.testOnReturn=false
druid.order1-slave.poolPreparedStatements=true
druid.order1-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order1-slave.filters=stat,wall,log4j
druid.order1-slave.useGlobalDataSourceStat=true

#slave从数据源order2
druid.order2-slave.url=jdbc:mysql://192.168.85.134:3306/order2?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order2-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order2-slave.username=root
druid.order2-slave.password=zxy8900461
druid.order2-slave.initialSize=5
druid.order2-slave.minIdle=1
druid.order2-slave.maxIdle=10
druid.order2-slave.maxActive=100
druid.order2-slave.maxWait=60000
druid.order2-slave.timeBetweenEvictionRunsMillis=60000
druid.order2-slave.minEvictableIdleTimeMillis=300000
druid.order2-slave.validationQuery=SELECT 1 FROM DUAL
druid.order2-slave.testWhileIdle=true
druid.order2-slave.testOnBorrow=false
druid.order2-slave.testOnReturn=false
druid.order2-slave.poolPreparedStatements=true
druid.order2-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order2-slave.filters=stat,wall,log4j
druid.order2-slave.useGlobalDataSourceStat=true

#slave从数据源order3
druid.order3-slave.url=jdbc:mysql://192.168.85.134:3306/order3?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order3-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order3-slave.username=root
druid.order3-slave.password=zxy8900461
druid.order3-slave.initialSize=5
druid.order3-slave.minIdle=1
druid.order3-slave.maxIdle=10
druid.order3-slave.maxActive=100
druid.order3-slave.maxWait=60000
druid.order3-slave.timeBetweenEvictionRunsMillis=60000
druid.order3-slave.minEvictableIdleTimeMillis=300000
druid.order3-slave.validationQuery=SELECT 1 FROM DUAL
druid.order3-slave.testWhileIdle=true
druid.order3-slave.testOnBorrow=false
druid.order3-slave.testOnReturn=false
druid.order3-slave.poolPreparedStatements=true
druid.order3-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order3-slave.filters=stat,wall,log4j
druid.order3-slave.useGlobalDataSourceStat=true

#slave从数据源order4
druid.order4-slave.url=jdbc:mysql://192.168.85.134:3306/order4?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
druid.order4-slave.driver-class-name=com.mysql.jdbc.Driver
druid.order4-slave.username=root
druid.order4-slave.password=zxy8900461
druid.order4-slave.initialSize=5
druid.order4-slave.minIdle=1
druid.order4-slave.maxIdle=10
druid.order4-slave.maxActive=100
druid.order4-slave.maxWait=60000
druid.order4-slave.timeBetweenEvictionRunsMillis=60000
druid.order4-slave.minEvictableIdleTimeMillis=300000
druid.order4-slave.validationQuery=SELECT 1 FROM DUAL
druid.order4-slave.testWhileIdle=true
druid.order4-slave.testOnBorrow=false
druid.order4-slave.testOnReturn=false
druid.order4-slave.poolPreparedStatements=true
druid.order4-slave.maxPoolPreparedStatementPerConnectionSize= 20
druid.order4-slave.filters=stat,wall,log4j
druid.order4-slave.useGlobalDataSourceStat=true

mybatis.type-aliases-package=com.cc                
mybatis.mapper-locations=classpath:com/cc/mapping/*.xml

logging.level.tk.mybatis=TRACE
        
pagehelper.dialect=com.github.pagehelper.dialect.helper.MySqlDialect
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql

配置中定义了四主四从共8个数据源；

3.3 对应数据构建entity：

package com.cc.entity;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

public class Order extends BaseEntity implements Serializable{
	
	private static final long serialVersionUID = 1L;

	private String orderId;

    private String orderType;

    private String cityId;

    private String platformId;

    private String platformOrderId;

    private String poiId;

    private String senderAddress;

    private String senderPhone;

    private Integer senderLng;

    private Integer senderLat;

    private String senderName;

    private String receiverAddress;

    private String receiverPhone;

    private Integer receiverLng;

    private Integer receiverLat;

    private String receiverName;

    private String remark;

    private BigDecimal pkgPrice;

    private String createBy;

    private Date createTime;

    private String updateBy;

    private Date updateTime;

	public String getOrderId() {
		return orderId;
	}

	public void setOrderId(String orderId) {
		this.orderId = orderId;
	}

	public String getOrderType() {
		return orderType;
	}

	public void setOrderType(String orderType) {
		this.orderType = orderType;
	}

	public String getCityId() {
		return cityId;
	}

	public void setCityId(String cityId) {
		this.cityId = cityId;
	}

	public String getPlatformId() {
		return platformId;
	}

	public void setPlatformId(String platformId) {
		this.platformId = platformId;
	}

	public String getPlatformOrderId() {
		return platformOrderId;
	}

	public void setPlatformOrderId(String platformOrderId) {
		this.platformOrderId = platformOrderId;
	}

	public String getPoiId() {
		return poiId;
	}

	public void setPoiId(String poiId) {
		this.poiId = poiId;
	}

	public String getSenderAddress() {
		return senderAddress;
	}

	public void setSenderAddress(String senderAddress) {
		this.senderAddress = senderAddress;
	}

	public String getSenderPhone() {
		return senderPhone;
	}

	public void setSenderPhone(String senderPhone) {
		this.senderPhone = senderPhone;
	}

	public Integer getSenderLng() {
		return senderLng;
	}

	public void setSenderLng(Integer senderLng) {
		this.senderLng = senderLng;
	}

	public Integer getSenderLat() {
		return senderLat;
	}

	public void setSenderLat(Integer senderLat) {
		this.senderLat = senderLat;
	}

	public String getSenderName() {
		return senderName;
	}

	public void setSenderName(String senderName) {
		this.senderName = senderName;
	}

	public String getReceiverAddress() {
		return receiverAddress;
	}

	public void setReceiverAddress(String receiverAddress) {
		this.receiverAddress = receiverAddress;
	}

	public String getReceiverPhone() {
		return receiverPhone;
	}

	public void setReceiverPhone(String receiverPhone) {
		this.receiverPhone = receiverPhone;
	}

	public Integer getReceiverLng() {
		return receiverLng;
	}

	public void setReceiverLng(Integer receiverLng) {
		this.receiverLng = receiverLng;
	}

	public Integer getReceiverLat() {
		return receiverLat;
	}

	public void setReceiverLat(Integer receiverLat) {
		this.receiverLat = receiverLat;
	}

	public String getReceiverName() {
		return receiverName;
	}

	public void setReceiverName(String receiverName) {
		this.receiverName = receiverName;
	}

	public String getRemark() {
		return remark;
	}

	public void setRemark(String remark) {
		this.remark = remark;
	}

	public BigDecimal getPkgPrice() {
		return pkgPrice;
	}

	public void setPkgPrice(BigDecimal pkgPrice) {
		this.pkgPrice = pkgPrice;
	}

	public String getCreateBy() {
		return createBy;
	}

	public void setCreateBy(String createBy) {
		this.createBy = createBy;
	}

	public Date getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}

	public String getUpdateBy() {
		return updateBy;
	}

	public void setUpdateBy(String updateBy) {
		this.updateBy = updateBy;
	}

	public Date getUpdateTime() {
		return updateTime;
	}

	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}

	public static long getSerialversionuid() {
		return serialVersionUID;
	}
	

}

【此处应用了tk.mybatis组件必须使用一个主键自增的ID】

package com.cc.entity;

import javax.persistence.Id;
/*
 * 应用tk.mybatis组件必须使用一个主键自增的ID
 */
public class BaseEntity {
	@Id
	private String id;

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

}

3.4 应用threadlocal定义主从库标示：

package com.cc.config.database;

/*
 * 通过一个enum(枚举)定义标示主从库
 */
public class DataBaseContextHolder {
	
	public enum DataBaseType {
		ORDER1_MASTER("order1-master"),
		ORDER2_MASTER("order2-master"),
		ORDER3_MASTER("order3-master"),
		ORDER4_MASTER("order4-master"),
		ORDER1_SLAVE("order1-slave"),
		ORDER2_SLAVE("order2-slave"),
		ORDER3_SLAVE("order3-slave"),
		ORDER4_SLAVE("order4-slave");
		private String code;
		
		private DataBaseType(String code){
			this.code = code;
		}
		
		public String getCode(){
			return code;
		}
	}
	/*
	 * 此处应用ThreadLocal;
	 * 可以优化选用netty的FastThreadLocal，性能会更好；
	 * 通过DataBaseType来判断具体数据源
	 */
	private static final ThreadLocal<DataBaseType> contextHolder = new ThreadLocal<DataBaseType>();
	
	/**
	 * <B>方法名称：</B>设置数据源类型<BR>
	 * <B>概要说明：</B><BR>
	 */
	public static void setDataBaseType(DataBaseType dataBaseType) {
		if(dataBaseType == null) throw new NullPointerException();
		contextHolder.set(dataBaseType);
	}
	
	
	public static DataBaseType getDataBaseType(){
		//如果获取的DataBaseType为null默认选择ORDER1_MASTER("order1-master")，如果不为null则按照获取确定数据源
		return contextHolder.get() == null ? DataBaseType.ORDER1_MASTER : contextHolder.get();
	}
	
	public static void clearDataBaseType(){
		contextHolder.remove();
	}
}

3.6 注入数据源：

package com.cc.config.database;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
/*
 * 将配置中的数据源注入
 */
@Configuration		//此标签作用标示相当于一个xml配置文件
public class DataSourceConfiguration {
	
	private static Logger LOGGER = org.slf4j.LoggerFactory.getLogger(DataSourceConfiguration.class);
	
	@Value("${druid.type}")
	private Class<? extends DataSource> dataSourceType;
	
	@Bean(name = "order1-master")
	@Primary
	@ConfigurationProperties(prefix = "druid.order1-master")
	public DataSource order1MasterDataSource() throws SQLException {
		DataSource order1MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order1-master: {} ================", order1MasterDataSource);
		return order1MasterDataSource;
	}
	
	@Bean(name = "order2-master")
	@ConfigurationProperties(prefix = "druid.order2-master")
	public DataSource order2MasterDataSource() throws SQLException {
		DataSource order2MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order2-master: {} ================", order2MasterDataSource);
		return order2MasterDataSource;
	}
	
	@Bean(name = "order3-master")
	@ConfigurationProperties(prefix = "druid.order3-master")
	public DataSource order3MasterDataSource() throws SQLException {
		DataSource order3MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order3-master: {} ================", order3MasterDataSource);
		return order3MasterDataSource;
	}
	
	@Bean(name = "order4-master")
	@ConfigurationProperties(prefix = "druid.order4-master")
	public DataSource order4MasterDataSource() throws SQLException {
		DataSource order4MasterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order4-master: {} ================", order4MasterDataSource);
		return order4MasterDataSource;
	}
	
	@Bean(name = "order1-slave")
	@ConfigurationProperties(prefix = "druid.order1-slave")
	public DataSource order1SlaveDataSource() throws SQLException {
		DataSource order1SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order1-slave: {} ================", order1SlaveDataSource);
		return order1SlaveDataSource;
	}

	@Bean(name = "order2-slave")
	@ConfigurationProperties(prefix = "druid.order2-slave")
	public DataSource order2SlaveDataSource() throws SQLException {
		DataSource order2SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order2-slave: {} ================", order2SlaveDataSource);
		return order2SlaveDataSource;
	}
	
	@Bean(name = "order3-slave")
	@ConfigurationProperties(prefix = "druid.order3-slave")
	public DataSource order3SlaveDataSource() throws SQLException {
		DataSource order3SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order3-slave: {} ================", order3SlaveDataSource);
		return order3SlaveDataSource;
	}
	
	@Bean(name = "order4-slave")
	@ConfigurationProperties(prefix = "druid.order4-slave")
	public DataSource order4SlaveDataSource() throws SQLException {
		DataSource order4SlaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
		LOGGER.info("============= order4-slave: {} ================", order4SlaveDataSource);
		return order4SlaveDataSource;
	}
	
	@Bean
	public ServletRegistrationBean druidServlet(){
		//spring boot 的方式 自己写一个servlet 
		ServletRegistrationBean reg = new ServletRegistrationBean();
		reg.setServlet(new StatViewServlet());
		reg.addUrlMappings("/druid/*");
		reg.addInitParameter("allow", "192.168.1.1");
		//reg.addInitParameter("deny", "/deny");
		LOGGER.info("============= init druid servlet ================");
		return reg;
	}
	
	@Bean
	public FilterRegistrationBean druidFilter(){
		FilterRegistrationBean ftr = new FilterRegistrationBean();
		ftr.setFilter(new WebStatFilter());
		ftr.addUrlPatterns("/*");
		ftr.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico, /druid/*");
		LOGGER.info("============= init druid filter ================");
		return ftr;
	}
	
}

【通过@Value("${druid.type}")标签读取druid数据源配置，然后定义每个库为一个bean，DataSourceBuilder.create()创建并且返回，注入到spring容器即完成，在springboot2.x以后此方法取消，需要自己构造创建的方法】

3.7 数据源注入spring容器后交由mybatis管理：加载8个数据源，然后创建SessionFactory将数据源交由mybatis管理，实现池化

package com.cc.config.database;

import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.cc.config.database.DataBaseContextHolder.DataBaseType;


@Configuration
//启用事务注解(此处关闭事务)@EnableTransactionManagement
//当DataSourceConfiguration加载完成后加载MyBatisConfiguration
@AutoConfigureAfter(value = {DataSourceConfiguration.class})
public class MyBatisConfiguration {

	@Resource(name= "order1-master")
	private DataSource order1MasterDataSource;
	
	@Resource(name= "order2-master")
	private DataSource order2MasterDataSource;
	
	@Resource(name= "order3-master")
	private DataSource order3MasterDataSource;
	
	@Resource(name= "order4-master")
	private DataSource order4MasterDataSource;
	
	@Resource(name= "order1-slave")
	private DataSource order1SlaveDataSource;
	
	@Resource(name= "order2-slave")
	private DataSource order2SlaveDataSource;
	
	@Resource(name= "order3-slave")
	private DataSource order3SlaveDataSource;
	
	@Resource(name= "order4-slave")
	private DataSource order4SlaveDataSource;
	
	
	@Bean("dynamicDataSource")
	public DynamicDataSource roundRobinDataSourceProxy(){
		
		Map<Object, Object> targetDataSource = new HashMap<Object, Object>();
		
		targetDataSource.put(DataBaseType.ORDER1_MASTER, order1MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER2_MASTER, order2MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER3_MASTER, order3MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER4_MASTER, order4MasterDataSource);
		targetDataSource.put(DataBaseType.ORDER1_SLAVE, order1SlaveDataSource);
		targetDataSource.put(DataBaseType.ORDER2_SLAVE, order2SlaveDataSource);
		targetDataSource.put(DataBaseType.ORDER3_SLAVE, order3SlaveDataSource);
		targetDataSource.put(DataBaseType.ORDER4_SLAVE, order4SlaveDataSource);
		
		//	实例化动态数据源
		DynamicDataSource proxy = new DynamicDataSource();
		//	盛放所以需要切换的数据源
		proxy.setTargetDataSources(targetDataSource);
		//	设置默认的数据源
		proxy.setDefaultTargetDataSource(order1MasterDataSource);
		
		return proxy;
	}
	/*
	 * 加载完成后创建sqlSessionFactory，将数据源池化
	 */
	@Bean(name="sqlSessionFactory")
	public SqlSessionFactory sqlSessionFactoryBean(DynamicDataSource dynamicDataSource) {
		
		System.err.println("----------------执行--------------");
		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
		bean.setDataSource(dynamicDataSource);
		// 添加XML目录
		ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
		try {
			bean.setMapperLocations(resolver.getResources("classpath:com/cc/mapping/*.xml"));
			SqlSessionFactory sqlSessionFactory = bean.getObject();
			sqlSessionFactory.getConfiguration().setCacheEnabled(Boolean.TRUE);
			return sqlSessionFactory;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	

}

【在使用数据源的时候是有程序动态切换，所有需要构建一个DynamicDataSource，然后应用roundRobinDataSourceProxy()定义一个代理方式来实现动态切换，此为mybatis提供支持的动态切换方式，通过继承AbstractRoutingDataSource，并实现determineCurrentLookupKey方法】

package com.cc.config.database;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

	@Override
	protected Object determineCurrentLookupKey() {
		//	动态的去切换数据源的类型
		return DataBaseContextHolder.getDataBaseType();
	}

}

3.8 数据源动态切换的算法与时机：算法通过对uuid的取模操作来实现；

package com.cc.utils;

//数据源切换实现
public class SelectorUtil {
	//四个库
	private static final int writeDataBaseSize = 4;
	//每个库五张表
	private static final int writeTableSize = 5;
	//传入uuid
	public static Pair<Integer, Integer> getDataBaseAndTableNumber(String uuid) {
		//取uuid.hashCode的绝对值
		int hashcode = Math.abs(uuid.hashCode());
		System.err.println("hashcode: " + hashcode);
		//根据hashcode做5张表的除法，然后取余4个库再加1，获得任意数据源
		int selectDataBaseNumber = (hashcode/writeTableSize)%writeDataBaseSize + 1;
		//定位表通过5张表取模操作
		int selectTableNumber = hashcode%writeTableSize;
		System.err.println("----------- SelectorUtil: selectDataBaseNumber: " + selectDataBaseNumber + " ----------------");
		System.err.println("----------- SelectorUtil: selectTableNumber: " + selectTableNumber + " ----------------");
		return new Pair<Integer, Integer>(selectDataBaseNumber, selectTableNumber);
	}
	//单元测试：通过KeyUtil随机生成uuid，每次结果不同
	public static void main(String[] args) {
		String uuid = KeyUtil.generatorUUID();
		int code = Math.abs(uuid.hashCode());
		System.err.println("code: " + code);
		int selectDataBaseNumber = (code/5)%4 + 1;
		int selectTableNumber = code%5;
		System.err.println("selectDataBaseNumber: " + selectDataBaseNumber);
		System.err.println("selectTableNumber: " + selectTableNumber);
	}
	
}

主从切换的时机由自定义注解来实现：@SelectConnection

package com.cc.config.database;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/*
 * 自定义注解：
 * @Target 表示此注解要应用的位置(默认不加参数可以应用到任何地方)
 * @Retention 配置注解应用时机(不加参数默认CLASS类加载阶段，三种状态包含关系，RUNTIME为全包含)
 * @Documented 配置生成文档
 * @Inheritance 配置子类是否可以集成
 * 自定义注解一般使用前两个配置即可
 * 注意：每一个注解本事就是要一个接口，每一个注解默认继承java.lang.annotation.Annotation
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface SelectConnection {

	String name() default "";
	
	boolean readOnly() default false; 
	
}

应用 @Aspect （即AOP）来解析自定义注解，已达到目的；

package com.cc.config.database;

import org.apache.commons.lang3.StringUtils;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;

import com.cc.config.database.DataBaseContextHolder.DataBaseType;
import com.cc.utils.Pair;
import com.cc.utils.SelectorUtil;
/**
 * 核心链路实现
 */
@Aspect
@Component
public class SelectConnectionInterceptor implements Ordered {

	private static Logger LOGGER = org.slf4j.LoggerFactory.getLogger(SelectConnectionInterceptor.class);
	
	private static final String SUFFIX_MASTER = "-master";
	
	private static final String SUFFIX_SLAVE = "-slave";
	//注解被调用的时候开始解析
	@Around("@annotation(selectConnection)")
	public Object proceed(ProceedingJoinPoint proceedingJoinPoint, SelectConnection selectConnection) throws Throwable {
		try{
			//1 执行方法前(在加selectConnection的方法执行前进行解析)
			LOGGER.info("--------------- select database source ---------------");
			
			String currentDataBaseName = "";
			//	如果在注解上添加了: name那么就按照其去获取
			if(!StringUtils.isBlank(selectConnection.name())){
				currentDataBaseName = selectConnection.name();
			} else { //如果没写name，那么按照传入(调用方法时参数)进行解析
				String preFixTableName = (String)proceedingJoinPoint.getArgs()[0];		//	表名前缀  order 
				String uuid = (String) proceedingJoinPoint.getArgs()[1];	// uuid
				
				Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
				currentDataBaseName = preFixTableName + pair.getObject1(); 
			}
			//判断readOnly，切换主从读写分离
			if(selectConnection.readOnly()){
				currentDataBaseName = currentDataBaseName + SUFFIX_SLAVE;
			} else {
				currentDataBaseName = currentDataBaseName + SUFFIX_MASTER;
			}
			//order3-master
			System.err.println("----Interceptor: currentDataBaseName : " + currentDataBaseName);	
			
			//string-> order2-slave
			for(DataBaseType type: DataBaseContextHolder.DataBaseType.values()){
				if(!StringUtils.isBlank(currentDataBaseName)){
					String typeCode = type.getCode();
					if(typeCode.equals(currentDataBaseName)){
						DataBaseContextHolder.setDataBaseType(type);//设置切换
						System.err.println("----Interceptor: code :" + DataBaseContextHolder.getDataBaseType().getCode());
					}
				}
			}
			
			//2 开始执行方法
			Object result = proceedingJoinPoint.proceed();
			
			//3 执行方法后
			
			return result;
		} finally {
			DataBaseContextHolder.clearDataBaseType();
			LOGGER.info("---------------clear database connection---------------");
		}
	}
	
	
	@Override
	public int getOrder() {
		// TODO Auto-generated method stub
		return -1;
	}

}

【注解解析要配合着service使用，service中传入的参数preFixTableName指定具体表】

package com.cc.service;

import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;

import com.cc.config.database.SelectConnection;
import com.cc.entity.Order;
import com.cc.mapper.OrderMapper;
import com.cc.utils.FastJsonConvertUtil;
import com.cc.utils.Pair;
import com.cc.utils.SelectorUtil;

@Service
public class OrderService {

	@Autowired
	private OrderMapper orderMapper;
	
	@SelectConnection(readOnly = true)
	public Order shardSelectByPrimaryKey(String preFixTableName, String uuid){
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;
		return orderMapper.shardSelectByPrimaryKey(tableName, uuid);
	}
	
	/*注解的作用: 就是对数据源的切换
	 * String preFixTableName传入的是表名，然后解析后找到具体的表
	 * @SelectConnection(name = "order3-master")可以通过此种配置方式固定表(绕开解析过程)
	 */
	@SelectConnection
	public int shardInsert(String preFixTableName, String uuid, Order order){
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;	//order_3
		
		JSONObject json = FastJsonConvertUtil.convertObjectToJSONObject(order);
		Map<String, Object> params = FastJsonConvertUtil.convertJSONToObject(json, Map.class);
		params.put("tableName", tableName);
		return orderMapper.shardInsert(params);
	}
	
	@SelectConnection
	public int shardUpdateByPrimaryKey(String preFixTableName, String uuid, Order tradeDetail){ 
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;
		
		JSONObject json = FastJsonConvertUtil.convertObjectToJSONObject(tradeDetail);
		Map<String, Object> params = FastJsonConvertUtil.convertJSONToObject(json, Map.class);
		params.put("tableName", tableName);
		return orderMapper.shardUpdateByPrimaryKey(params);
	}

}

4. 此实现只能作为B版参考，实际业务中可以应用，但还有很多有待优化：

首先，如果是作为分库分表的基础架构组件，那么耦合度太高要降低，对于开发人员带有侵入式，作为通用基础组件，不应改变使用者的代码逻辑，保障其原本开发逻辑，即本来该如何做就如何去做；例如，封装MQ；【保障低耦合、低侵入】

@SelectConnection(readOnly = true)
	public Order shardSelectByPrimaryKey(String preFixTableName, String uuid){
		Pair<Integer/*DataBaseNumber*/, Integer/*TableNumber*/> pair = SelectorUtil.getDataBaseAndTableNumber(uuid);
		Integer tableNumber = pair.getObject2();
		String tableName = preFixTableName + "_" + tableNumber;
		return orderMapper.shardSelectByPrimaryKey(tableName, uuid);
	}

/*将String preFixTableName也放入注解中，不在此处拼接
	   @SelectConnection(readOnly = true,tableName = "table")
	   public Order shardSelectByPrimaryKey(String uuid){
		return orderMapper.shardSelectByPrimaryKey(tableName, uuid);
	}
	 */

其次，在深度优化，可以对orderMapper进行优化，对mybatis解析Mapper的过程进行嵌入；mybatis的Interceptor接口（拦截器：参考https://blog.csdn.net/wuyuxing24/article/details/89343951），在其执行前或执行后嵌入一些自定义逻辑，完成动态拼接和取模过程；

诸般世界

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
005 分库分表_多数据源切换读写分离实战

1.数据库层面Mysql（MariaDB），实现读写分离、主从切换、数据源切换：首先实现读写分离，就意味着需要有两个数据源，当写操作的时候对主库（master）使用，当读操作的时候对从库（slave）使用。那么在启动数据库连接池的时候即要启动两个，在实际使用时可以在方法上加上自定义注解的形式来区分读写。2.实现解析：（1）配置好两个druid数据源，然后要区分开两个数据源...
复制链接

扫一扫