shardingsphere+druid+mybatis-plus 分库分表

1.引入需要的相关依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
        </dependency>

    </dependencies>

2.编写配置文件

server:
  port: 8888
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    #配置分库的数据源
    datasource:
      #配置分库的名称
      names: ds0,ds1
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/user?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true
        username: root
        password: 123456
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/user2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false&autoReconnect=true&rewriteBatchedStatements=true
        username: root
        password: 123456
    props:
      sql:
        show: true #控制台打印sql 方便观察
    sharding:
      tables:
        #要进行分片的表名称
        user:
          database-strategy:
            # 配置user表分片规则以及分片键
            inline:
              sharding-column: id
              #将user表中的id字段作为分片键 将值取模
              #计算出来余数是1或者0，然后进行字符串拼接 ds0 ds1
              #对应上面的数据库源配置
              algorithm-expression: ds$->{id%2}  
              #字符串可以通过hash分片
             # algorithm-expression: ds$->{Math.abs(id.hashCode()%2)}
              

#mybatis-plus
mybatis-plus:
  configuration:
  #驼峰
    map-underscore-to-camel-case: true
  global-config:
  #逻辑删除
    db-config:
      logic-delete-value: true
      logic-not-delete-value: false
  mapper-locations: classpath:mapper/*.xml

3.数据准备工作

新建2个数据库user和user2，在分别建立2张user表

CREATE DATABASE `user`；
CREATE TABLE `user` (
                        `id` bigint NOT NULL,
                        `name` varchar(100) NOT NULL,
                        `password` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                        `portrait` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                        `phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                        `deleted` tinyint(1) NOT NULL DEFAULT '0',
                        `update_time` datetime NOT NULL,
                        `version` bigint NOT NULL DEFAULT '1',
                        UNIQUE KEY `user_un` (`id`),
                        KEY `user_name_IDX` (`name`) USING BTREE,
                        KEY `user_phone_IDX` (`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE DATABASE `user2`；
CREATE TABLE `user` (
                        `id` bigint NOT NULL,
                        `name` varchar(100) NOT NULL,
                        `password` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                        `portrait` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                        `phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
                        `deleted` tinyint(1) NOT NULL DEFAULT '0',
                        `update_time` datetime NOT NULL,
                        `version` bigint NOT NULL DEFAULT '1',
                        UNIQUE KEY `user_un` (`id`),
                        KEY `user_name_IDX` (`name`) USING BTREE,
                        KEY `user_phone_IDX` (`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

4.代码实现

在entity 编写user类

@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "`user`")
public class User implements Serializable {
    /**id**/
    @TableId(type =IdType.ASSIGN_ID)
    private Long id;
    /**头像*/
    private String portrait;
    /**用户名*/
    private String name;
    /**密码*/
    private String password;
    /**手机号*/
    private String phone;
    /**修该时间*/
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private LocalDateTime updateTime;
    /**逻辑删除*/
    @TableLogic
    private boolean deleted;
    @Version
    private Long version;
}

在mapper下新增接口

public interface UserMapper extends BaseMapper<User> {
}

在config下面去自己实现BaseTypeHandler

@Component
//定义转换器支持的JAVA类型
@MappedTypes(LocalDateTime.class)
//定义转换器支持的数据库类型
@MappedJdbcTypes(value = JdbcType.TIMESTAMP, includeNullJdbcType = true)
public class LocalDateTimeTypeHandle extends BaseTypeHandler<LocalDateTime> {
    private final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType)
            throws SQLException {
        ps.setObject(i, parameter);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String target = rs.getString(columnName);
        if (StringUtils.isEmpty(target)) {
            return null;
        }
        return LocalDateTime.parse(target, dateTimeFormatter);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String target = rs.getString(columnIndex);
        if (StringUtils.isEmpty(target)) {
            return null;
        }
        return LocalDateTime.parse(target, dateTimeFormatter);
    }

    @Override
    public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String target = cs.getString(columnIndex);
        if (StringUtils.isEmpty(target)) {
            return null;
        }
        return LocalDateTime.parse(target, dateTimeFormatter);
    }
}

5测试

在测试类去实现

@Resource
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        //新增用户
        User user = new User();
        //user.setId("1234567891");
        user.setName("zhanglin2");
        user.setPassword("123456");
        user.setDeleted(false);
        user.setPhone("17623235053");
        user.setPortrait("https://www.baidu.com");
        user.setUpdateTime(LocalDateTime.now());
        user.setVersion(1l);
        int insert = userMapper.insert(user);
        System.out.println(insert+"---"+user.toString());
       //查询出所有用户
        List<User> users = userMapper.selectList(new QueryWrapper<User>());
        System.out.println(users.toString());
    }

图描述有误单数走的ds1 ,双数走的ds0

查询所有数据，是从2个库查询出来的所有用户