Spring Boot 整合 ShardingSphere

框架：

  1. Spring boot
  2. ShardingSphere
  3. Mybatis

文章源码：https://gitee.com/yihong-sword/learn_shardingsphere

Spring boot 整合 SardingSphere (多表(分))：

Spring boot 整合 SardingSphere (单表访问以及分库分表)：

Spring boot 整合 SardingSphere (精确分表策略)：

Pom：

        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>sharding-jdbc-spring-namespaceartifactId>
            <version>4.1.1version>
        dependency>
        <dependency>
            <groupId>org.apache.shardingspheregroupId>
            <artifactId>sharding-jdbc-spring-boot-starterartifactId>
            <version>4.1.1version>
        dependency>

Mysql

  • Schema 后缀是0 - xx不限 例如：sharding0、sharding1
  • Table 后缀是0 - xx不限 
-- auto-generated definition
create table s_user0
(
    id   int auto_increment
        primary key,
    name varchar(20) null comment '名字',
    age  varchar(3)  null comment '年龄'
);

代码中数据库结构：

sharding0
    -->> s_user0
    -->> s_user1
sharding1
    -->> s_user0
    -->> s_user1

application.properties

# 应用名称
spring.application.name=learn_shardingsphere
# 应用服务 WEB 访问端口
server.port=8085

# 数据源 sharding01 sharding02
spring.shardingsphere.datasource.names=sharding0,sharding1

# 第一个数据库
spring.shardingsphere.datasource.sharding0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.sharding0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.sharding0.jdbc-url=jdbc:mysql://localhost:3306/sharding0?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.sharding0.username=root
spring.shardingsphere.datasource.sharding0.password=yihongjian

# 第二个数据库
spring.shardingsphere.datasource.sharding1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.sharding1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.sharding1.jdbc-url=jdbc:mysql://localhost:3306/sharding1?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.sharding1.username=root
spring.shardingsphere.datasource.sharding1.password=yihongjian

# 水平拆分的数据库(表) 配置分库 + 分表策略 行表达式分片策略
# 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=sharding$->{id % 2}

# 分表策略 其中 s_user 为逻辑表 分表主要取决于s_user.id的行
spring.shardingsphere.sharding.tables.s_user.actual-data-nodes=sharding$->{0..1}.s_user$->{0..1}
spring.shardingsphere.sharding.tables.s_user.table-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.s_user.table-strategy.inline.algorithm-expression=s_user$->{Integer.valueOf(age) % 2}

# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
logging.level.com.sharding.demo.mapper=DEBUG

# mybatis
mybatis.mapper-locations=classpath:/mappers/*.xml

Controller

package com.sharding.demo.controller;

import com.sharding.demo.entity.User;
import com.sharding.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

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

    @Autowired
    private UserService userService;

    @PostMapping("/save")
    public String save(@RequestBody User user) {
        userService.save(user);
        return "success";
    }

    @GetMapping("/findAll")
    public List findAll() {
        return userService.findAll();
    }

}

Bean

package com.sharding.demo.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
    private Long id;
    private String name;
    private String age;
}

Service

package com.sharding.demo.service.impl;

import com.sharding.demo.entity.User;
import com.sharding.demo.mapper.UserMapper;
import com.sharding.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public void delete(Long id) {
        userMapper.delete(id);
    }

    @Override
    public int update(User user) {
        return userMapper.update(user);
    }

    @Override
    public List findAll() {
        return userMapper.findAll();
    }

    @Override
    public void save(User user) {
        userMapper.save(user);
    }
}

Mapper

package com.sharding.demo.mapper;

import com.sharding.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    void delete(Long id);

    int update(User user);

    List findAll();

    void save(User user);
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sharding.demo.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.sharding.demo.entity.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="age" jdbcType="VARCHAR" property="age"/>
    resultMap>
    <sql id="Base_Column_List">
        id, name, age
    sql>

    <insert id="save" parameterType="com.sharding.demo.entity.User">
        insert into s_user (id, name, age) value (#{id}, #{name}, #{age})
    insert>

    <select id="findAll" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from s_user
    select>

    <delete id="delete" parameterType="java.lang.Long">
        delete from s_user
        where id = #{id,jdbcType=BIGINT}
    delete>

    <update id="update" parameterType="com.sharding.demo.entity.User">
        update s_user
        set
            name = #{name,jdbcType=VARCHAR},
            age = #{age,jdbcType=VARCHAR}
        where id = #{id,jdbcType=BIGINT}
    update>
mapper>

启动Console

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.7.RELEASE)

WARNING: Illegal reflective access by org.codehaus.groovy.reflection.CachedClass$3$1 (file:/Users/yihongjian/.m2/repository/org/codehaus/groovy/groovy/2.4.5/groovy-2.4.5-indy.jar) to method java.lang.Object.finalize()
WARNING: Please consider reporting this to the maintainers of org.codehaus.groovy.reflection.CachedClass$3$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
2021-12-26 15:13:01.055  INFO 14323 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2021-12-26 15:13:01.055  INFO 14323 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2021-12-26 15:13:01.739  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'stringToNoneShardingStrategyConfigurationConverter' of type [org.apache.shardingsphere.spring.boot.converter.StringToNoneShardingStrategyConfigurationConverter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.754  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.sharding-org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.756  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.masterslave-org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.758  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.encrypt-org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.760  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.shadow-org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.762  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere-org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.943  INFO 14323 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$f93fcc61] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:02.160  INFO 14323 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8085 (http)
2021-12-26 15:13:02.169  INFO 14323 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2021-12-26 15:13:02.169  INFO 14323 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.41]
2021-12-26 15:13:02.224  INFO 14323 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2021-12-26 15:13:02.224  INFO 14323 --- [  restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1169 ms
2021-12-26 15:13:02.380  INFO 14323 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2021-12-26 15:13:02.507  INFO 14323 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2021-12-26 15:13:02.521  INFO 14323 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2021-12-26 15:13:02.525  INFO 14323 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2021-12-26 15:13:02.526  INFO 14323 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Starting...
2021-12-26 15:13:02.530  INFO 14323 --- [  restartedMain] com.zaxxer.hikari.HikariDataSource       : HikariPool-3 - Start completed.
2021-12-26 15:13:02.607  INFO 14323 --- [  restartedMain] o.a.s.core.log.ConfigurationLogger       : ShardingRuleConfiguration:
defaultDatabaseStrategy:
  inline:
    algorithmExpression: sharding$->{id % 2}
    shardingColumn: id
tables:
  s_user:
    actualDataNodes: sharding$->{0..1}.s_user$->{0..1}
    logicTable: s_user
    tableStrategy:
      inline:
        algorithmExpression: s_user$->{Integer.valueOf(age) % 2}
        shardingColumn: age
  s_student:
    actualDataNodes: sharding$->{0..1}.s_student$->{0..1}
    logicTable: s_student
    tableStrategy:
      inline:
        algorithmExpression: s_student$->{Integer.valueOf(age) % 2}
        shardingColumn: age

2021-12-26 15:13:02.608  INFO 14323 --- [  restartedMain] o.a.s.core.log.ConfigurationLogger       : Properties:
sql.show: 'true'

2021-12-26 15:13:02.627  INFO 14323 --- [  restartedMain] ShardingSphere-metadata                  : Loading 2 logic tables' meta data.
2021-12-26 15:13:02.693  INFO 14323 --- [  restartedMain] ShardingSphere-metadata                  : Meta data load finished, cost 85 milliseconds.
2021-12-26 15:13:03.055  INFO 14323 --- [  restartedMain] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2021-12-26 15:13:03.199  INFO 14323 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2021-12-26 15:13:03.223  INFO 14323 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8085 (http) with context path ''
2021-12-26 15:13:03.233  INFO 14323 --- [  restartedMain] com.sharding.demo.DemoApplication        : Started DemoApplication in 2.596 seconds (JVM running for 9.623)

Postman 测试结果

Springboot整合系列ShardingSphere

相关

【ShardingSphere】ShardingSphere-JDBC 集成SpringBoot

欢迎 Apache ShardingSphere 社区海外新晋 Committer！

Apache ShardingSphere 5.0.0 内核优化及升级指南

【报名中】我们把你对 ShardingSphere 的好奇，都放在这场 Meetup 中

SpringBoot+ShardingSphere彻底解决生产环境数据库字段加解密问题

ShardingSphere 踩过的坑：Cannot invoke method div() on null object

Apache ShardingSphere 企业行｜走进中信云网

Apache ShardingSphere 遇上得物“彩虹桥”

ShardingSphere-JDBC进行分库分表

分库分表ShardingSphere-JDBC笔记整理

标签