《SpringBoot2从入门到工程实战》第九篇:集成Sharding-Sphere的自定义分库分表及自动读写分离

在实际业务中,针对大规模数据业务的MySQL使用,一般有两种模式,一种是集群模式,使用Proxy来实现,一种是使用主从模式。

本章主要是使用主从模式示例,支持自动读写分离以及自定义模式的分库分表规则(取模、MD5、年月日或者其他规则都行)。

本章示例工程名称:springboot_worker_multi_masterslave_hash

代码地址:https://github.com/stamhe/SpringBoot-Work-Example

目录结构如下:

示例MySQL建表语句:

create database user_201908;
use user_201908;

CREATE TABLE `users_201908_009` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `email` varchar(70) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


create database article_201908;
use article_201908;

CREATE TABLE `t_article_201908_009` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `summary` varchar(1024) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

pom.xml内容:

<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.stamhe</groupId>
  <artifactId>springboot_worker_multi_masterslave_hash</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>springboot_worker_multi_masterslave_hash</name>
  <url>http://maven.apache.org</url>



	<properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
    </properties>
  
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
      <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-jdbc</artifactId>  
	    </dependency>  
	    
	    <dependency>  
	        <groupId>mysql</groupId>  
	        <artifactId>mysql-connector-java</artifactId>  
	        <version>8.0.15</version>
	    </dependency> 
	    
	    <dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>druid-spring-boot-starter</artifactId>
		    <version>1.1.13</version>
	    </dependency>
	    
	    <dependency>
	      <groupId>org.mybatis.spring.boot</groupId>
	      <artifactId>mybatis-spring-boot-starter</artifactId>
	      <version>1.3.3</version>
	    </dependency>
	    
	    
	    <!-- 
	    https://github.com/apache/incubator-shardingsphere/issues/1722
	    需要使用sharding-jdbc 3.1.0.1以上版本,否则limit offset会出现@1722 的bug
	    -->
	    <dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-core</artifactId>
		    <version>3.1.0</version>
		</dependency>
	    
	    <dependency>
		    <groupId>io.shardingsphere</groupId>
		    <artifactId>sharding-jdbc-core</artifactId>
		    <version>3.1.0</version>
		</dependency>
    
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
        </dependency>
    </dependencies>
    <!-- 
    使用Spring Boot微服务搭建框架,在eclipse和Idea下能正常运行,但是在打成jar包部署或者直接使用java -jar命令的时候,
    提示了xxxxxx.jar中没有主清单属性.
    添加 spring-boot-maven-plugin然后再执行mvn install 或者 mvn clean package 即可解决.
    -->
    <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
     </build>
</project>

application.properties内容:

spring.datasource.user-master.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user-master.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user-master.username = root
spring.datasource.user-master.password = 
spring.datasource.user-master.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.user-slave01.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user-slave01.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user-slave01.username = root
spring.datasource.user-slave01.password = 
spring.datasource.user-slave01.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.user-slave02.url = jdbc:mysql://127.0.0.1:3306/user_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.user-slave02.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.user-slave02.username = root
spring.datasource.user-slave02.password = 
spring.datasource.user-slave02.type = com.alibaba.druid.pool.DruidDataSource



spring.datasource.article-master.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article-master.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article-master.username = root
spring.datasource.article-master.password = 
spring.datasource.article-master.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.article-slave01.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article-slave01.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article-slave01.username = root
spring.datasource.article-slave01.password = 
spring.datasource.article-slave01.type = com.alibaba.druid.pool.DruidDataSource

spring.datasource.article-slave02.url = jdbc:mysql://localhost:3306/article_201908?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.article-slave02.driverClassName = com.mysql.cj.jdbc.Driver
spring.datasource.article-slave02.username = root
spring.datasource.article-slave02.password = 
spring.datasource.article-slave02.type = com.alibaba.druid.pool.DruidDataSource



mybatis.type-aliases-package = com.stamhe.springboot.model

App.java的内容:

package com.stamhe.springboot;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;


@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class, DruidDataSourceAutoConfigure.class})
@EnableTransactionManagement(proxyTargetClass = true)   //开启事务管理功能
@ComponentScan({"com.stamhe.springboot"})
public class App 
{
    public static void main( String[] args )
    {
    	SpringApplication.run(App.class, args);
    }
}

UserModel.java的内容:

package com.stamhe.springboot.model;


/*
create database user_201908;
use user_201908;

CREATE TABLE `users_201908_009` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `email` varchar(70) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 */
public class UserModel {
	private Long user_id;
    private String name;
    private String email;
    
    
	public Long getUser_id() {
		return user_id;
	}
	public void setUser_id(Long user_id) {
		this.user_id = user_id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	
	@Override
	public String toString() {
		return "UserModel [user_id=" + user_id + ", name=" + name + ", email=" + email + "]";
	}
}

ArticleModel.java的内容:

package com.stamhe.springboot.model;

import java.util.Date;

/*
create database article_201908;
use article_201908;

CREATE TABLE `t_article_201908_009` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `summary` varchar(1024) NOT NULL DEFAULT '',
  `status` int(11) NOT NULL DEFAULT '0',
  `type` int(11) NOT NULL,
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `public_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 */
public class ArticleModel {
	private Long id;
    private String title;
    private String summary;
    private Integer status;
    private Integer type;
    private Long userId;
    
    private Date createTime;
    private Date publicTime;
    private Date updateTime;
    
    
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	public Integer getStatus() {
		return status;
	}
	public void setStatus(Integer status) {
		this.status = status;
	}
	public Integer getType() {
		return type;
	}
	public void setType(Integer type) {
		this.type = type;
	}
	public Long getUserId() {
		return userId;
	}
	public void setUserId(Long userId) {
		this.userId = userId;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public Date getPublicTime() {
		return publicTime;
	}
	public void setPublicTime(Date publicTime) {
		this.publicTime = publicTime;
	}
	public Date getUpdateTime() {
		return updateTime;
	}
	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}
	
	@Override
	public String toString() {
		return "ArticleModel [id=" + id + ", title=" + title + ", summary=" + summary + ", status=" + status + ", type="
				+ type + ", userId=" + userId + ", createTime=" + createTime + ", publicTime=" + publicTime
				+ ", updateTime=" + updateTime + "]";
	}
}

以下是mapper的内容,注意,涉及到分库分表时,在mapper中使用的,都是虚拟表名,这个虚拟表名需要在DatabaseMSShardingAlgorithm.java和TableMSShardingAlgorithm.java中使用。

UserMapper.java的内容:

package com.stamhe.springboot.mapper.user;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.springframework.context.annotation.Configuration;

import com.stamhe.springboot.model.UserModel;


@Configuration
public interface UserMapper 
{
	@Select("select * from t_user_x where user_id=#{user_id}")
	@Results({
		@Result(property="user_id", column="user_id"),
		@Result(property="name", column="name"),
		@Result(property="email", column="email"),
	})
	public UserModel getOne(Long user_id);
	

	@Select("select * from t_user_x")
	@Results({
		@Result(property="user_id", column="user_id"),
		@Result(property="name", column="name"),
		@Result(property="email", column="email"),
	})
	public List<UserModel> getAll();
	

	@Insert("insert into t_user_x (user_id, name, email) "
			+ "values (#{user_id}, #{name}, #{email})")
	@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="user_id", before=false, resultType=long.class)
	public void insert(UserModel userModel);	
}

ArticleMapper.java的内容:

package com.stamhe.springboot.mapper.article;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;
import org.springframework.context.annotation.Configuration;

import com.stamhe.springboot.model.ArticleModel;

@Configuration
public interface ArticleMapper 
{
	@Select("select * from t_article_x where user_id=#{user_id} and id=#{id}")
	@Results({
		@Result(property="userId", column="user_id"),
		@Result(property="createTime", column="create_time"),
		@Result(property="updateTime", column="update_time"),
		@Result(property="publicTime", column="public_time"),
	})
	public ArticleModel getOne(@Param("user_id")Long user_id, @Param("id")Long id);
	

	@Select("select * from t_article_x where user_id = #{user_id} or user_id != #{user_id}")
	@Results({
		@Result(property="userId", column="user_id"),
		@Result(property="createTime", column="create_time"),
		@Result(property="updateTime", column="update_time"),
		@Result(property="publicTime", column="public_time"),
	})
	public List<ArticleModel> getAll(Long user_id);
	
	// articleModel.getId()
	@Insert("insert into t_article_x (id, title, summary, status, type, user_id, create_time, update_time, public_time) "
			+ "values (#{id}, #{title}, #{summary}, #{status}, #{type}, #{userId}, #{createTime}, #{updateTime}, #{publicTime})")
	@SelectKey(statement="select LAST_INSERT_ID()", keyProperty="id", before=false, resultType=long.class)
	public void insert(ArticleModel articleModel);
	
	// 返回受影响的行数
	@Update("update t_article_x set summary=#{summary} where user_id = #{user_id} and id=#{id}")
	public Integer update(@Param("user_id")Long user_id, @Param("articleModel")ArticleModel articleModel);
	
	// 返回受影响的行数
	@Delete("delete from t_article_x where user_id = #{user_id} and id=#{id}")
	public Integer delete(@Param("user_id")Long user_id, @Param("id")Long id);
}

UserController.java的内容:

package com.stamhe.springboot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.stamhe.springboot.mapper.user.UserMapper;
import com.stamhe.springboot.model.UserModel;

@RestController
@RequestMapping("/user")
public class UserController {

	@Autowired
	private UserMapper userMapper;
	
	// http://localhost:8080/user/add
	@RequestMapping("/add")
	public Long addAction()
	{
		Long user_id = 1L;

		UserModel userModel = new UserModel();
		userModel.setUser_id(user_id);
		userModel.setName("user-测试-201908-001");
		userModel.setEmail("stamhe-201908-001@gmail.com");
		
		userMapper.insert(userModel);
		
		return user_id;
	}
	
	// http://localhost:8080/user/detail/1
	@RequestMapping(value="/detail/{user_id}")
	public UserModel detailAction(@PathVariable("user_id")Long user_id)
	{
		UserModel userModel = userMapper.getOne(user_id);
		return userModel;
	}
}

ArticleController.java的内容:

package com.stamhe.springboot.controller;

import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.stamhe.springboot.mapper.article.ArticleMapper;
import com.stamhe.springboot.model.ArticleModel;

@RestController
@RequestMapping("/article")
public class ArticleController {

	@Autowired
	private ArticleMapper articleMapper;
	
	// http://localhost:8080/article/add
	@RequestMapping("/add")
	public Long addAction()
	{
		Long id = 1L;

		ArticleModel articleModel = new ArticleModel();
		
		articleModel.setId(id);
		articleModel.setTitle("article-测试-201908-001");
		articleModel.setSummary("article-测试-201908-001");
		articleModel.setStatus(1);
		articleModel.setType(1);
		articleModel.setUserId(1L);
		articleModel.setCreateTime(new Date());
		articleModel.setUpdateTime(new Date());
		articleModel.setPublicTime(new Date());
        articleMapper.insert(articleModel);
        
		return id;
	}

	// http://localhost:8080/article/all/1
	@RequestMapping(value = "/all/{user_id}")
	public List<ArticleModel> allAction(@PathVariable("user_id")Long user_id)
	{
		List<ArticleModel> list = articleMapper.getAll(user_id);
		return list;
	}
	
	// http://localhost:8080/article/detail/1/1
	@RequestMapping(value="/detail/{user_id}/{id}")
	public ArticleModel detailAction(@PathVariable("user_id") Long user_id, @PathVariable("id")Long id)
	{
		ArticleModel articleModel = articleMapper.getOne(user_id, id);
		return articleModel;
	}
}

下面是Sharding-Sphere使用的关键模块:

下面主要是根据 虚拟表名 shardingValue.getLogicTableName() 及 分库分表字段 shardingValue.getValue() 来计算当次请求需要使用的datasource,在 MSDSConfig.java中定义,并返回这个datasource。

DatabaseMSShardingAlgorithm.java的内容:

package com.stamhe.springboot.algorithm;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

/*
 * 
 * 分库分表: hash、取模、按时间
 * https://blog.csdn.net/myshy025tiankong/article/details/83063887
 * 
 * https://www.cnblogs.com/mr-yang-localhost/p/8313360.html
 * StandardShardingStrategy 标准分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,
 * 提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
 * PreciseShardingAlgorithm 用于处理=和IN的分片
 * RangeShardingAlgorithm 用于处理BETWEEN AND分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理
 * 
 * 
 * ComplexShardingStrategy 复合分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
 * ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此Sharding-JDBC并未做过多的封装,
 * 而是直接将分片键值组合以及分片操作符交于算法接口,完全由应用开发者实现,提供最大的灵活度。
 * 
 * 
 * InlineShardingStrategy Inline表达式分片策略。使用Groovy的Inline表达式,提供对SQL语句中的=和IN的分片操作支持。
 * InlineShardingStrategy只支持单分片键,对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,
 * 如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表,表名称为t_user_0到t_user_7。
 * 
 * 
 * HintShardingStrategy 通过Hint而非SQL解析的方式分片的策略。
 * 
 * 
 * NoneShardingStrategy 不分片的策略。
 */

public class DatabaseMSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
		String logic_table = shardingValue.getLogicTableName();
//		System.out.println("logic_table = " + logic_table);
		
		String ds_name = "ds_hash_";
		String subfix  = "";
		
		switch(logic_table)
		{
			case "t_user_x":
				return "ds_hash_user";
			case "t_article_x":
				return "ds_hash_article";
			default:
				break;
		}
		
		try {
			Date date = (Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(shardingValue.getValue().toString());

			String year	= String.format("%tY", date);
			String mon	= String.format("%tm", date);
			subfix		= year + mon;
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// ds_name 格式【ds_db_201810】
		ds_name = ds_name + subfix;
//		System.out.println("ds_name = " + ds_name + " new");
		return ds_name;
	}

}

下面主要是根据 虚拟表名 shardingValue.getLogicTableName() 及 分库分表字段 shardingValue.getValue() 来计算当次请求需要使用的真实表名,并返回这个真实的表名。

TableMSShardingAlgorithm.java的内容:

package com.stamhe.springboot.algorithm;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;


/*
 * 
 * 分库分表: hash、取模、按时间
 * https://blog.csdn.net/myshy025tiankong/article/details/83063887
 * 
 * https://www.cnblogs.com/mr-yang-localhost/p/8313360.html
 * StandardShardingStrategy 标准分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键,
 * 提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法
 * PreciseShardingAlgorithm 用于处理=和IN的分片
 * RangeShardingAlgorithm 用于处理BETWEEN AND分片,如果不配置RangeShardingAlgorithm,SQL中的BETWEEN AND将按照全库路由处理
 * 
 * 
 * ComplexShardingStrategy 复合分片策略。提供对SQL语句中的=, IN和BETWEEN AND的分片操作支持。
 * ComplexShardingStrategy支持多分片键,由于多分片键之间的关系复杂,因此Sharding-JDBC并未做过多的封装,
 * 而是直接将分片键值组合以及分片操作符交于算法接口,完全由应用开发者实现,提供最大的灵活度。
 * 
 * 
 * InlineShardingStrategy Inline表达式分片策略。使用Groovy的Inline表达式,提供对SQL语句中的=和IN的分片操作支持。
 * InlineShardingStrategy只支持单分片键,对于简单的分片算法,可以通过简单的配置使用,从而避免繁琐的Java代码开发,
 * 如: tuser${user_id % 8} 表示t_user表按照user_id按8取模分成8个表,表名称为t_user_0到t_user_7。
 * 
 * 
 * HintShardingStrategy 通过Hint而非SQL解析的方式分片的策略。
 * 
 * 
 * NoneShardingStrategy 不分片的策略。
 */
public class TableMSShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

	@Override
	public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
		String logic_table = shardingValue.getLogicTableName();
		
		String tb_name = "";
		
		switch(logic_table) {
			case "t_user_x":
				tb_name = "users_201908_009";
				return tb_name;
			case "t_article_x":
				tb_name = "t_article_201908_009";
				return tb_name;
			default:
				tb_name = "xxx_";
		}
		
		try {
			Date date = (Date) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(shardingValue.getValue().toString());

			String year = String.format("%tY", date);
			String mon = String.format("%tm", date);
			String dat = String.format("%td", date);
			// 表名【gps_20181011】
			tb_name = tb_name + year + mon + dat;
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

//		System.out.println("tb_name = " + tb_name);
		return tb_name;
	}

}

MSDSConfig.java的内容:

package com.stamhe.springboot.dsconfig;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.stamhe.springboot.algorithm.DatabaseMSShardingAlgorithm;
import com.stamhe.springboot.algorithm.TableMSShardingAlgorithm;

import io.shardingsphere.api.algorithm.masterslave.RoundRobinMasterSlaveLoadBalanceAlgorithm;
import io.shardingsphere.api.config.rule.MasterSlaveRuleConfiguration;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

/*
 * 由于我们在SpringBootApplication中禁掉了自动数据源配置,需要在这儿手动创建 application.properteis 中的数据源
 * 
 * 分库分表: hash、取模、按时间
 * https://blog.csdn.net/myshy025tiankong/article/details/83063887
 * 
 * 主从读写分离
 */
@Configuration
@MapperScan(basePackages = {"com.stamhe.springboot.mapper"}, sqlSessionFactoryRef = "sqlSessionFactoryUserMSHash")
public class MSDSConfig {
    List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() {
  		MasterSlaveRuleConfiguration msUserRuleConfig = new MasterSlaveRuleConfiguration("ds_hash_user", "ds_user_master", 
  				Arrays.asList("ds_user_slave01", "ds_user_slave02"), new RoundRobinMasterSlaveLoadBalanceAlgorithm());

  		MasterSlaveRuleConfiguration msArticleRuleConfig = new MasterSlaveRuleConfiguration("ds_hash_article", "ds_article_master", 
  				Arrays.asList("ds_article_slave01", "ds_article_slave02"), new RoundRobinMasterSlaveLoadBalanceAlgorithm());

  		List<MasterSlaveRuleConfiguration> list = new ArrayList<>();
  		list.add(msUserRuleConfig);
  		list.add(msArticleRuleConfig);
  		return list;
    }
    
    
    Map<String, DataSource> createDataSourceMap() {
        final Map<String, DataSource> result = new HashMap<>();
        
        result.put("ds_user_master", DBUserDataSourceMaster());
        result.put("ds_user_slave01", DBUserDataSourceSlave01());
        result.put("ds_user_slave02", DBUserDataSourceSlave02());
        
        result.put("ds_article_master", DBArticleDataSourceMaster());
        result.put("ds_article_slave01", DBArticleDataSourceSlave01());
        result.put("ds_article_slave02", DBArticleDataSourceSlave02());
        
        return result;
    }
    

  	@Bean(name="shardingDataSourceUserMSHash")
  	@Primary
  	public DataSource shardingDataSource() throws SQLException {
  		ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        
  		//用户表配置,可以添加多个配置
  		shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());
  		shardingRuleConfig.getTableRuleConfigs().add(getArticleTableRuleConfiguration());
  		
//  		shardingRuleConfig.getBindingTableGroups().add("gps_table, gps_20190227_x, gps_20190227_y");
		shardingRuleConfig.getBindingTableGroups().add("t_user_x, t_article_x");
        
  		//设置数据库策略,传入的是user_id
  		shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new DatabaseMSShardingAlgorithm()));
  		//设置数据表策略,传入的是user_id
  		shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("user_id", new TableMSShardingAlgorithm()));

  		shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations());
  		
  		Properties props = new Properties();
  		// 是否显示sql 和 DataSource
  		props.setProperty("sql.show", "true");
  		
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new ConcurrentHashMap<String, Object>(), props);
  	}
  	
  	
  	/**
     * 需要手动配置事务管理器
     *
     * @param shardingDataSource
     * @return
     */
    @Bean(name="transactitonManagerUserMSHash")
    public DataSourceTransactionManager transactitonManager(DataSource shardingDataSourceMSHash) {
        return new DataSourceTransactionManager(shardingDataSourceMSHash);
    }

    @Bean(name="sqlSessionFactoryUserMSHash")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSourceUserMSHash") DataSource shardingDataSourceMSHash) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(shardingDataSourceMSHash);
        return bean.getObject();
    }

    @Bean(name="testSqlSessionTemplateUserMSHash")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("sqlSessionFactoryUserMSHash") SqlSessionFactory sqlSessionFactoryObjGpsMSHash) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryObjGpsMSHash);
    }
    

    /**
     * 设置Druid参数
     * @param dataSource
     * @return
     * @throws SQLException 
     */
    public DruidDataSource setDataSource(DruidDataSource druidDataSource)
    {
        // 初始化大小,最小,最大
        druidDataSource.setMaxActive(10);
        druidDataSource.setInitialSize(4);
        druidDataSource.setMinIdle(3);
        
        // 配置获取连接等待超时的时间 单位: ms
        druidDataSource.setMaxWait(100);
        
        // 打开后,增强timeBetweenEvictionRunsMillis的周期性连接检查,minIdle内的空闲连接,每次检查强制验证连接有效性. 参考:https://github.com/alibaba/druid/wiki/KeepAlive_cn
        druidDataSource.setKeepAlive(true);
        // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位: ms
        druidDataSource.setTimeBetweenEvictionRunsMillis(30000);
        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);

        // 配置一个连接在池中最小生存的时间,单位: ms
        druidDataSource.setMinEvictableIdleTimeMillis(300000);
        
        // 用来检测连接是否有效的sql,要求是一个查询语句。 如果validationQuery为null,testOnBorrow、testOnReturn、 testWhileIdle都不会其作用。
        druidDataSource.setValidationQuery("select 'x'");
        
        // 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于 timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
        druidDataSource.setTestWhileIdle(true);
        // 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
        druidDataSource.setTestOnBorrow(false);
        // 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能
        druidDataSource.setTestOnReturn(false);
        
        /* 打开PSCache,并且指定每个连接上PSCache的大小。
        如果用Oracle,则把poolPreparedStatements配置为true,
        mysql可以配置为false。分库分表较多的数据库,建议配置为false */
        druidDataSource.setPoolPreparedStatements(false);
        // 指定每个连接上PSCache的大小
        druidDataSource.setMaxOpenPreparedStatements(20);
        // 合并多个DruidDataSource的监控数据
        //druidDataSource.setUseGlobalDataSourceStat(true);
        
        // 自动提交设置为 true
        druidDataSource.setDefaultAutoCommit(true);
        
        /*
         * 连接泄露检查,打开removeAbandoned功能 , 连接从连接池借出后,长时间不归还,将触发强制回连接。回收周期随timeBetweenEvictionRunsMillis进行.
         * 如果连接为从连接池借出状态,并且未执行任何sql,并且从借出时间起已超过removeAbandonedTimeout时间,则强制归还连接到连接池中。
         */
        druidDataSource.setRemoveAbandoned(true);
        // 单位: s
        druidDataSource.setRemoveAbandonedTimeout(5);
        // 关闭abanded连接时输出错误日志,这样出现连接泄露时可以通过错误日志定位忘记关闭连接的位置
        druidDataSource.setLogAbandoned(true);
        
        //配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
        /*
        属性类型是字符串,通过别名的方式配置扩展插件, 
		常用的插件有: 
		监控统计用的filter:stat  
		日志用的filter:log4j 
		防御sql注入的filter:wall
         */
        try {
        	druidDataSource.setFilters("stat,wall,slf4j");
        }catch(Exception e) {
        	e.printStackTrace();
        }
        // 配置监控统计日志的输出间隔,每次输出所有统计数据会重置,酌情开启. 单位: ms
        druidDataSource.setTimeBetweenLogStatsMillis(10000);
            
    	return druidDataSource;
    }
    

  	@Bean
  	public TableRuleConfiguration getUserTableRuleConfiguration() {
  		TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
   
  		tableRuleConfig.setLogicTable("t_user_x");
  		// 设置使用sharding-jdbc产生id的列名,如果不用sharding-jdbc产生,则不要设置任何值,否则insert报错
//		tableRuleConfig.setKeyGeneratorColumnName("id");
  		return tableRuleConfig;
  	}

  	@Bean
  	public TableRuleConfiguration getArticleTableRuleConfiguration() {
  		TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
   
  		tableRuleConfig.setLogicTable("t_article_x");
  		// 设置使用sharding-jdbc产生id的列名,如果不用sharding-jdbc产生,则不要设置任何值,否则insert报错
//		tableRuleConfig.setKeyGeneratorColumnName("id");
  		return tableRuleConfig;
  	}

	@Bean(name = "db_user_master")
    @ConfigurationProperties(prefix="spring.datasource.user-master")
    public DataSource DBUserDataSourceMaster() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_user_slave01")
    @ConfigurationProperties(prefix="spring.datasource.user-slave01")
    public DataSource DBUserDataSourceSlave01() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_user_slave02")
    @ConfigurationProperties(prefix="spring.datasource.user-slave02")
    public DataSource DBUserDataSourceSlave02() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    

	@Bean(name = "db_article_master")
    @ConfigurationProperties(prefix="spring.datasource.article-master")
    public DataSource DBArticleDataSourceMaster() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_article_slave01")
    @ConfigurationProperties(prefix="spring.datasource.article-slave01")
    public DataSource DBArticleDataSourceSlave01() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
    
    @Bean(name = "db_article_slave02")
    @ConfigurationProperties(prefix="spring.datasource.article-slave02")
    public DataSource DBArticleDataSourceSlave02() {
    	DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
    	return setDataSource(dataSource);
    }
}

示例完。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

code

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据