Spring Boot分表之Shardbatis插件

随着业务的发展，数据库占用的资源会越来越大；业务对数据库的增删改查操作对数据库来说都是一笔大的开销；另外，由于无法进行分布式式部署，而一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。
分库分表有两种方式：垂直切分和水平切分

垂直切分：即将表按照功能模块、关系密切程度划分出来，部署到不同的库上
水平切分：当一个表中的数据量过大时，我们可以把该表的数据按照某种规则进行划分，然后存储到多个结构相同的表，和不同的库上。

————————–本文讲述分表—————————–

用代码实现分库:
使用技术：Spring Boot、Mybatis、Shardbatis
使用工具：Idea、MySQL
建立在一个Spring Boot项目———->

创建数据库和表

以下为创建的数据库和表,以及添加数据的脚本

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for shard_1
-- ----------------------------
DROP TABLE IF EXISTS `shard_1`;
CREATE TABLE `shard_1` (
  `id` varchar(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of shard_1
-- ----------------------------
INSERT INTO `shard_1` VALUES ('1-1', 'shard_1');

-- ----------------------------
-- Table structure for shard_2
-- ----------------------------
DROP TABLE IF EXISTS `shard_2`;
CREATE TABLE `shard_2` (
  `id` varchar(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of shard_2
-- ----------------------------
INSERT INTO `shard_2` VALUES ('2-1', 'shard_2');

-- ----------------------------
-- Table structure for tables
-- ----------------------------
DROP TABLE IF EXISTS `tables`;
CREATE TABLE `tables` (
  `id` varchar(11) NOT NULL,
  `tableName` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tables
-- ----------------------------
INSERT INTO `tables` VALUES ('1-1', 'shard_1');
INSERT INTO `tables` VALUES ('2-1', 'shard_2');

引入依赖

项目的开始，依赖是不可少的

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.3.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.maiyikai</groupId>
  <artifactId>shardbatis</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>shardbatis</name>
  <description>Demo project for Spring Boot</description>

  <properties>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web-services</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid-spring-boot-starter</artifactId>
      <version>1.1.10</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.3.1</version>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!--在idea中不能使用devtools包，因为会导致shardbatis里使用ApplicationContext上下文获取null-->
    <!--<dependency>-->
      <!--<groupId>org.springframework.boot</groupId>-->
      <!--<artifactId>spring-boot-devtools</artifactId>-->
      <!--<optional>true</optional>-->
    <!--</dependency>-->

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.6</version>
    </dependency>

    <dependency>
      <groupId>org.shardbatis</groupId>
      <artifactId>shardbatis</artifactId>
      <version>2.0.0C</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>

建立目录结构

目录结构随意

Shardbatis
|--- pom.xml
|--- src
|	|--- main
|	|	|--- java
|	|	|	|--- controller
|	|	|	|	|--- com
|	|	|	|	|	|--- maiyikai
|	|	|	|	|	|	|--- shardbatis
|	|	|	|	|	|	|	|--- controller
|	|	|	|	|	|	|	|	|--- ShardbatisController.java
|	|	|	|	|	|	|	|--- service
|	|	|	|	|	|	|	|	|--- ShardService.java
|	|	|	|	|	|	|	|	|------ TablesService.java
|	|	|	|	|	|	|	|	|--- impl
|	|	|	|	|	|	|	|	|		|---  ShardServiceImpl.java
|	|	|	|	|	|	|	|	|		|---  TablesServiceImpl.java
|	|	|	|	|	|	|	|--- mapper
|	|	|	|	|	|	|	|	|--- ShardMapper.java
|	|	|	|	|	|	|	|	|--- TablesMapper.java
|	|	|	|	|	|	|	|--- config
|	|	|	|	|	|	|	|	|--- ShardConfiguration.java
|	|	|	|	|	|	|	|---- entity
|	|	|	|	|	|	|	|      |------ Shard.java
|	|	|	|	|	|	|	|      |--- Tables.java
|	|	|	|	|	|	|	|-- strategy
|	|	|	|	|	|	|	|	|--- MyShardStrategy.java
|	|	|	|	|	|	|	|--- tools
|	|	|	|	|	|	|	|      |--- SpringContextAware.java	
|	|	|	|	|	|	|	|--- ShardbatisApplication.java	
|	|	|--- resources
|	|	|	|--- mapper
|	|	|	|	|--- shard.xml
|	|	|	|	|--- tables.xml
|	|	|	|--- shard
|	|	|	|	|--- shard_config.xml
|	|	|	|--- application.yml

编写配置文件


server:
  port: 8181
spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:3306/shardbatis?useUnicode=true&characterEncoding=UTF8&&serverTimezone=UTC&useSSL=false
      username: root
      password: maiyikai
      min-idle: 5
      max-active: 150
      max-wait: 60000
      pool-prepared-statements: false
      max-open-prepared-statements: 0
      validation-query: select 'x'
      test-while-idle: true
      test-on-borrow: true
      test-on-return: false
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000	
mybatis:
  mapper-locations: classpath:mapper/*.xml
logging:
  level:
    com.maiyikai.shardbatis.mapper: debug # 调试打印sql

开始写代码了

直接就是代码

ShardbatisController.java

启动代码没什么不需要更改其他的东西

@SpringBootApplication
@MapperScan(value = "com.maiyikai.shardbatis.mapper")//扫描mapper类
public class ShardbatisApplication {

  public static void main(String[] args) {
    SpringApplication.run(ShardbatisApplication.class, args);
  }

}

配置ShardConfiguration.java

通过 ShardPlugin.java 源码，ShardPlugin 通过读取属性值 shardingConfig 获取配置文件，所以这个值不能改

@Configuration
public class ShardConfiguration {


  @Bean(name = "shardPlugin")
  public ShardPlugin shardPlugin(){
    ShardPlugin shardPlugin = new ShardPlugin();
    Properties properties = new Properties();
    properties.put("shardingConfig", "shard/shard_config.xml");//文件加载--键值必须为shardingConfig，这是类的内部要求，否则加载失败
    shardPlugin.setProperties(properties);
    return shardPlugin;
  }
}

ShardbatisController.java

定义接口，使用GetMapping是为了方便使用浏览器访问

@Controller
@RequestMapping(value = "/Shardbatis")
public class ShardbatisController {

  @Autowired
  private ShardService shardService;

  @ResponseBody
  @GetMapping (value = "/startShard")
  public String test(@RequestParam(value = "id") String id){
    return shardService.getName(id);
  }

}

ShardServiceImpl.java

略过服务类中的代码

@Service
public class ShardServiceImpl implements ShardService {

  @Autowired
  private ShardMapper shardMapper;

  @Override
  public String getName(String id) {
    Shard shard = shardMapper.getShardResult(id);//执行到这条语句时，会被拦截进入到分表策略MyShardStrategy.java中，获取对应的表名称，并且更改sql中的表名
    return StringUtils.isEmpty(shard.getName())?null:shard.getName();
  }
}

TablesService.java

用于获取对应的表名

@Service("tablesService")
public class TableServiceImpl implements TablesService {

  @Autowired
  private TablesMapper tablesMapper;

  /**
   * 因为分表的表名对应的信息存放在tables表中，所以通过这个方法访问，通过对饮的id获取对饮的表名
   * @param id
   * @return
   */
  @Override
  public String getTableName(String id) {
    Tables tables = tablesMapper.getTables(id);
    return StringUtils.isEmpty(tables.getTableName())?null:tables.getTableName();
  }
}

SpringContextAware.java

用于普通类可获取ApplicationContext使用，可以通过该applicationContext获取对应的Spring Boot应用中的Bean

@Component
public class SpringContextAware implements ApplicationContextAware {
  private static ApplicationContext applicationContexts;//使用静态，让它存储下来,通过类型即可获取

  @Override
  public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
    applicationContexts = applicationContext;
  }

  public static ApplicationContext getApplicationContexts() {
    return applicationContexts;
  }

  public static <T> T getBean(String name){
    return (T) getApplicationContexts().getBean(name);
  }
}

MyShardStrategy.java

策略类

/**
 * 继承ShardStrategy类
 */
public class MyShardStrategy implements ShardStrategy {

  @Override
  public String getTargetTableName(String baseTableName, Object params, String mapperId) {
    String id = ((Map<String, String>)params).get("id");//解析参数，获取需要的参数
    TablesService tablesService = SpringContextAware.getBean("tablesService");//获取对应的Bean
    return tablesService.getTableName(id);
  }
}

shard.xml

省略mapper类
表名一定要和shard_config.xml中配置的表名一致，否则将报错

<mapper namespace="com.maiyikai.shardbatis.mapper.ShardMapper">
  <select id="getShardResult" parameterType="java.lang.String" resultType="com.maiyikai.shardbatis.entity.Shard">
    SELECT name FROM shard WHERE id = #{id}
  </select>
</mapper>

tables.xml

省略mapper类

<mapper namespace="com.maiyikai.shardbatis.mapper.TablesMapper">
  <select id="getTables" parameterType="java.lang.String" resultType="com.maiyikai.shardbatis.entity.Tables">
    SELECT * FROM tables WHERE id = #{id}
  </select>
</mapper>

shard_config.xml

分表策略配置文件
ignoreList：忽略集合
parseList：解释集合–需要操作的集合
strategy：指定tableName对应的strategy

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE shardingConfig PUBLIC "-//shardbatis.googlecode.com//DTD Shardbatis 2.0//EN"
  "http://shardbatis.googlecode.com/dtd/shardbatis-config.dtd">
<shardingConfig>
  <!-- 忽略不进行分表策略的mapperid-即对应的方法 -->
  <ignoreList>
    <value></value>
  </ignoreList>

  <!-- 进行分表策略的mapperid-即对应的方法;一定要精确到方法，否则不会被拦截 -->
  <parseList>
    <value>com.maiyikai.shardbatis.mapper.ShardMapper.getShardResult</value>
  </parseList>

  <!-- 指定表使用对应的策略 -->
  <strategy tableName="shard" strategyClass="com.maiyikai.shardbatis.strategy.MyShardStrategy"/>

</shardingConfig>

至此，代码编写完成，执行接口即可进行分表，分表的策略根据业务进行调整

访问路劲:http://localhost:8181/Shardbatis/startShard?id=2-1
当id为2-1时 : 返回结果为:shard_2（代表访问的表）
当id为1-1时 : 返回结果为shard_1（代表访问的表）

最后更新： 2019年10月12日 20:21

原始链接： https://maiyikai.github.io/2019/04/04/1554363529/

赏

FlyingFish's Blog

随笔记

Spring Boot分表之Shardbatis插件

创建数据库和表

引入依赖

建立目录结构

编写配置文件

开始写代码了

ShardbatisController.java

配置ShardConfiguration.java

ShardbatisController.java

ShardServiceImpl.java

TablesService.java

SpringContextAware.java

MyShardStrategy.java

shard.xml

tables.xml

shard_config.xml