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),在其执行前或执行后嵌入一些自定义逻辑,完成动态拼接和取模过程;