springboot+shardingsphere+mybatis-plus

原创已于 2024-11-06 11:07:25 修改 · 851 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#spring boot #数据库 #mysql

于 2024-11-06 10:58:53 首次发布

shardingsphere 专栏收录该内容

1 篇文章

订阅专栏

工作中经常遇到大数据量的问题，每天的数据量可达百万，需要进行分库分表，从网上找了很多博客学习，经常遇到各种各样的问题，大都是版本选择和配置文件错误，其实仔细研究官方文档就可以了，今天总算是跑起来了，测试也没什么大问题，后面继续优化。

一、框架版本

1、shardingsphere5.5.0

2、springboot3.2.11

3、mybatis-plus3.5.5

二、maven依赖

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

        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>3.5.5</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.23</version>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc</artifactId>
            <version>5.5.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.shardingsphere</groupId>
                    <artifactId>shardingsphere-test-util</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

三、配置文件（application.yml）

spring:
  main:
    allow-bean-definition-overriding: true
  application:
    name: ls-backend
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding1.yaml
  servlet:
    multipart:
      max-file-size: 100MB
      max-request-size: 100MB
server:
  port: 8085
  servlet:
    context-path: /
    encoding:
      force-response: true


#Mybatis扫描
mybatis:
  config-location: classpath:/mybatis-config.xml

mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

四、sharding配置文件（sharding.yaml）

dataSources:
  ds0:
    driverClassName: com.mysql.cj.jdbc.Driver
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://127.0.0.1:3306/ds0?useUnicode=true&useSSL=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
    username: root
    password: root
  ds1:
    driverClassName: com.mysql.cj.jdbc.Driver
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://127.0.0.1:3306/ds1?useUnicode=true&useSSL=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
    username: root
    password: root
rules:
- !SHARDING
  tables:
    flight:
      actualDataNodes: ds0.flight_$->{0..1}
      tableStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: flight-id-inline
  shardingAlgorithms:
    flight-id-inline:
      type: INLINE
      props:
        algorithm-expression: flight_$->{id % 2}
props:
  sql-show: true

这块要特别注意，属性名要严格遵守驼峰，"- !SHARDING"必须要加，目前不明白为什么，这块是完全按照官方文档来的，还有就是官方文档中写的url是jdbcUrl，但是运行会报错，dubug发现这块取url时使用的是url，故将此处改为url可正常运行。

五、测试

使用mybatis-plus新增数据，根据日志可发现shardingsphere会根据分片算法将逻辑表名变更为实际表名。日志如下：

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@165b679a] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@412916513 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@5ece1773] will not be managed by Spring
==>  Preparing: SELECT id,num,date FROM flight WHERE id=?
==> Parameters: 2(Integer)
2024-11-06T10:55:42.313+08:00  INFO 9892 --- [ls-backend] [nio-8085-exec-4] ShardingSphere-SQL                       : Logic SQL: SELECT id,num,date FROM flight WHERE id=?
2024-11-06T10:55:42.313+08:00  INFO 9892 --- [ls-backend] [nio-8085-exec-4] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT id,num,date FROM flight_0 WHERE id=? ::: [2]
<==    Columns: id, num, date
<==        Row: 2, 2, 2024-11-06 10:35:02
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@165b679a]

flight逻辑表名会根据取模算法替换为实际表名。