ShardingJDBC使用笔记

视频地址
官网

SpringBoot的pom.xml

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

<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>mybatis-plus-boot-starter</artifactId>
   <version>3.0.5</version>
</dependency>

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

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

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

<dependency>
   <groupId>com.oracle</groupId>
   <artifactId>ojdbc12</artifactId>
   <version>12.0.1.2</version>
</dependency>

<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
</dependency>

<!--注意：必须要为以下版本，不然会有问题！！-->
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
   <version>4.0.0-RC1</version>
</dependency>

注意：其中shardingsphere的版本一定要为4.0.0-RC1,不然就无法使用！！！

SpringBoot中的配置文件

# 设置数据源的名称
spring.shardingsphere.datasource.names=ds0

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/xx?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.ds0.username=xxxx
spring.shardingsphere.datasource.ds0.password=xxxx

# 指定配置表在哪个数据库，表名都是什么
spring.shardingsphere.sharding.tables.t_gggl_log.actual-data-nodes=ds0.t_gggl_log$->{0..1}

# 指定配置表的主键是什么，以及生成的策略(SNOWFLAKE指使用雪花算法)
spring.shardingsphere.sharding.tables.t_gggl_log.key-generator.column=id
spring.shardingsphere.sharding.tables.t_gggl_log.key-generator.type=SNOWFLAKE

# 指定分片策略 奇数数在t_gggl_log0中，偶数在t_gggl_log1中
spring.shardingsphere.sharding.tables.t_gggl_log.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_gggl_log.table-strategy.inline.algorithm-expression=t_gggl_log$->{id % 2}

# 设置打印sql
spring.shardingsphere.props.sql.show=true

spring.main.allow-bean-definition-overriding=true

遇到的问题：

1.遇到datasource冲突

问题原因

因为DruidDataSourceAutoConfigure创建了一个dataSource。SpringBootConfiguration默认也要创建一个dataSource,所以就冲突了。

***************************
APPLICATION FAILED TO START
***************************

Description:

The bean 'dataSource', defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/DruidDataSourceAutoConfigure.class] and overriding is disabled.

Action:

Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true

解决方案

在springboot的配置文件中加上以下配置

spring.main.allow-bean-definition-overriding=true

2.id长度不够

执行插入的时候，插入主键使用雪花算法生成，所有普通的int无法满足长度要求，需要做以下修改：
首先，数据库中的字段要修改字段长度

修改po类中id的类型为Long型

package com.shardingjdbcdemo.model;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "T_GGGL_LOG")
public class Log {

    private Long id;

    private String mc;
}

3.启动加载整个库的表元数据，导致启动不起来

只需要设置大于1个数据源，并且没有设置默认的数据源，就不会加载所有的表元数据

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=oracle.jdbc.OracleDriver
spring.shardingsphere.datasource.ds0.url=jdbc:oracle:thin:@xxx.xxx.xx.x:1521:xxxx
spring.shardingsphere.datasource.ds0.username=xxx
spring.shardingsphere.datasource.ds0.password=xxx

spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=oracle.jdbc.OracleDriver
spring.shardingsphere.datasource.ds1.url=jdbc:oracle:thin:@xxx.xxx.xx.x:1521:xxxx
spring.shardingsphere.datasource.ds1.username=xxx
spring.shardingsphere.datasource.ds1.password=xxx
# 其中的ds1是没有使用到的数据源