SpringBoot集成Sharding-jdbc（水平分表）

SpringBoot集成Sharding-jdbc（水平分表）

1.Sharding-jdbc的应用场景

其实就是针对分库分表后的操作简化，相当于增强版的

JDBC

驱动

框架：

执行步骤：

• 解析SQL，获取片键值，在本文中设置的片键值是order_id
• Sharding-JDBC通过规则配置t_order_$->{order_id % 2+1}，可知当order_id为偶数时，应该往t_order_1表中插数据，为奇数时，往t_order_2表中插入数据
• 那么Sharding-JDBC根据order_id的值改写SQL语句，改写后的SQL语句是真实要执行的SQL语句
• 接着，执行改写后的真实SQL语句
• 最后将所有真正执行的SQL结果进行合并汇总，返回

从上面的执行过程可知，只需要配置好片键值，

Sharding-JDBC

自动帮你根据规则请求表，极大了简化了开发

2.实际使用

2.0 项目层级

2.1 导入依赖

<?xml version="1.0" encoding="UTF-8"?><projectxmlns="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><groupId>com.test.sharding</groupId><artifactId>Sharding-JDBC-Test</artifactId><version>0.0.1-SNAPSHOT</version><name>Sharding-JDBC-Test</name><description>Sharding-JDBC-Test</description><properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.4.2</spring-boot.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!--mybatis--><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.3.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.28</version></dependency><!--druid--><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.8</version></dependency><!--sharding-jdbc--><!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC1</version></dependency><!--mybatis分页依赖--><dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper-spring-boot-starter</artifactId><version>1.4.0</version></dependency><dependency><groupId>org.junit.jupiter</groupId><artifactId>junit-jupiter-api</artifactId><scope>test</scope></dependency><dependency><groupId>javax.xml.bind</groupId><artifactId>jaxb-api</artifactId><version>2.3.1</version></dependency><dependency><groupId>com.sun.xml.bind</groupId><artifactId>jaxb-impl</artifactId><version>2.3.1</version></dependency><dependency><groupId>org.glassfish.jaxb</groupId><artifactId>jaxb-runtime</artifactId><version>2.3.1</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency></dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-dependencies</artifactId><version>${spring-boot.version}</version><type>pom</type><scope>import</scope></dependency></dependencies></dependencyManagement><build><plugins><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><source>1.8</source><target>1.8</target><encoding>UTF-8</encoding></configuration></plugin><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId><version>${spring-boot.version}</version><configuration><mainClass>com.test.sharding.ShardingJdbcTestApplication</mainClass><skip>true</skip></configuration><executions><execution><id>repackage</id><goals><goal>repackage</goal></goals></execution></executions></plugin></plugins></build></project>

这里需要特别注意

SpringBoot、Sharding-jdbc

和

mybatis

的版本，之前我使用的是最新的

SpringBoot3.24

版本，而

Sharding-jdbc

无论用啥版本都报错，显示无法找到

url

，

驱动类

。然后如果

mybatis

版本比较低的话，就会导致使用

@Autowired

无法自动注入，找不到对应的

Bean

。

因此，最好的解决方式就是使用

SpringBoot

的

2.x

版本，

Jdk1.8

即可。

2.2 application.yml配置

server:port:8080mybatis:#mapper文件存放位置mapper-locations: classpath:/mapper/*.xml# 实体类的存放地址type-aliases-package: com.test.sharding.domain.pojo
  configuration:# 是否开启驼峰 Ming == m_ingmap-underscore-to-camel-case:truespring:main:allow-bean-definition-overriding:trueapplication:name: sharding-jdbc-test
  shardingsphere:props:sql:show:truedatasource:names: db1
      db1:type: com.alibaba.druid.pool.DruidDataSource
        #mysql驱动driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/sharding-jdbc-test?useUnicode=true&characterEncoding=utf8&useSSL=falseusername: root
        password: root
    sharding:tables:#scs_product可以任意命名，sql中一致，为了方便理解，这里一般写分表共有的# 比如我的scs_product_1  和 scs_product_2  那就写scs_product好区分# 当执行sql中出现scs_product，sharding-jdbc会将其操作到对应的表scs_product:actual-data-nodes: db1.scs_product_$->{1..2}# 指定主键生成策略为雪花id,全局主键key-generator:column: PRODUCT_ID
            type: SNOWFLAKE
          #指定scs_product表的分片策略，  分片键和分片算法 用于计算真正的表名table-strategy:inline:# 偶数进到scs_product_1  奇数进到scs_product_2# 对于根据分片字段为条件的，会先判断是否涉及两张表，# 如何是两张表则会两个表都查，如果只涉及单表，则只查询一张表。algorithm-expression: scs_product_$->{PRODUCT_ID % 2 + 1}# 指定分片键为PRODUCT_IDsharding-column: PRODUCT_ID

2.3 dao层

ShardingMapper.java

@MapperpublicinterfaceShardingMapper{voidinsertProduct(@Param("scsProduct")ScsProduct scsProduct);List<ScsProduct>selectProduct();//根据id列表查询商品List<ScsProduct>selectProductByIds(@Param("ids")List<Long> ids);}

2.4 对应的mybatis的xml文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mappernamespace="com.test.sharding.dao.ShardingMapper"><resultMapid="BaseResultMap"type="com.test.sharding.domain.pojo.ScsProduct"><[email protected]><idcolumn="PRODUCT_ID"jdbcType="BIGINT"property="productId"/><resultcolumn="PRODUCT_CODE"jdbcType="VARCHAR"property="productCode"/><resultcolumn="PRODUCT_NAME"jdbcType="VARCHAR"property="productName"/><resultcolumn="PRODUCT_AMOUNT"jdbcType="INTEGER"property="productAmount"/></resultMap><insertid="insertProduct"parameterType="com.test.sharding.domain.pojo.ScsProduct">
    insert into scs_product (PRODUCT_CODE, PRODUCT_NAME, PRODUCT_AMOUNT)
    values (#{scsProduct.productCode, jdbcType=VARCHAR}, #{scsProduct.productName, jdbcType=VARCHAR}, #{scsProduct.productAmount, jdbcType=INTEGER})
  </insert><selectid="selectProduct"resultMap="BaseResultMap">
    select PRODUCT_ID, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_AMOUNT
    from scs_product
<!--    where PRODUCT_CODE = 'SHARDING-JDBC166' --><!--此时的真是查询sql是两个表都查--><!--    PRODUCT_ID in (979716175636201483,979716175636201481) 因为都是奇数，则只查询 scs_product_2--><!-- PRODUCT_ID = 979716175636201483  此时会触发分片机制，根据主键字段分片查询对应的表 scs_product_2--><!--    PRODUCT_ID in (979716175636201483,979716175636201481,979716185538953216,979716184402296836)--><!--    order by PRODUCT_AMOUNT desc--><!-- 对于根据分片字段为条件的，会先判断是否涉及两张表，如何是两张表则会两个表都查，如果只涉及单表，则只查询一张表--></select><selectid="selectProductByIds"resultMap="BaseResultMap"resultType="com.test.sharding.domain.pojo.ScsProduct">
    select PRODUCT_ID, PRODUCT_CODE, PRODUCT_NAME, PRODUCT_AMOUNT
    from scs_product
    where PRODUCT_ID in
      <foreachcollection="ids"item="item"index="index"open="("separator=","close=")">
        #{item}
      </foreach></select></mapper>

2.5 Service层

ShardingService.java

publicinterfaceShardingService{voidinsertProduct(ScsProduct scsProduct);List<ScsProduct>queryProduct();PageInfo<ScsProduct>queryProductPage();List<ScsProduct>queryProductByIds(List<Long> ids);}

ShardingServiceImpl.java

@ServicepublicclassShardingServiceImplimplementsShardingService{@ResourceprivateShardingMapper shardingMapper;@Override@Async(value ="taskThread")//    @Transactional(rollbackFor = {TRANSACTION_REQUIRED.class})publicvoidinsertProduct(ScsProduct scsProduct){
        scsProduct.setProductName("小商品系列-sharding-jdbc"+Thread.currentThread().getName());
        shardingMapper.insertProduct(scsProduct);}@OverridepublicList<ScsProduct>queryProduct(){return shardingMapper.selectProduct();}@OverridepublicPageInfo<ScsProduct>queryProductPage(){Integer pageIndex =1;Integer pageSize =15;PageHelper.startPage(pageIndex,pageSize);List<ScsProduct> scsProductList = shardingMapper.selectProduct();returnnewPageInfo<>(scsProductList);}@OverridepublicList<ScsProduct>queryProductByIds(List<Long> ids){return shardingMapper.selectProductByIds(ids);}}

2.6 pojo

packagecom.test.sharding.domain.pojo;importlombok.Data;importjava.math.BigInteger;@DatapublicclassScsProduct{/**
    * 商品主键id
    */privateLong productId;/**
    * 商品编码
    */privateString productCode;/**
    * 商品名称
    */privateString productName;/**
    * 商品库存
    */privateInteger productAmount;}

2.7 controller

@RestController@RequestMapping("/shardingJdbcController")@Slf4jpublicclassShardingJdbcController{@ResourceprivateShardingService shardingService;@PostMapping("/insertProduct")@CrossOriginpublicStringinsertProduct(){ScsProduct scsProduct =newScsProduct();for(int i =1; i <10; i++){
            scsProduct.setProductAmount(i);
            scsProduct.setProductCode("SHARDING-JDBC"+i);
            shardingService.insertProduct(scsProduct);try{Thread.sleep(10);}catch(InterruptedException e){thrownewRuntimeException(e);}}return"插入成功！";}@PostMapping("/queryProduct")@CrossOriginpublicList<ScsProduct>queryProduct(){return shardingService.queryProduct();}@PostMapping("/queryProductPage")@CrossOriginpublicPageInfo<ScsProduct>queryProductPage(){return shardingService.queryProductPage();}}

2.8 多线程配置

@Configuration@EnableAsyncpublicclassThreadPoolConfig{@Bean(value ="taskThread")publicThreadPoolTaskExecutortaskThread(){ThreadPoolTaskExecutor executor =newThreadPoolTaskExecutor();
        executor.setCorePoolSize(12);
        executor.setMaxPoolSize(100);
        executor.setQueueCapacity(100);
        executor.setThreadNamePrefix("taskExecutor-product");//        executor.setKeepAliveSeconds(1000);
        executor.initialize();return executor;}}