Spring Boot 整合 Sharding-JDBC

概述

        ShardingSphere，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。定位为轻量级Java框架。其实就是一个增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。内部改写了SQL的添加和查询规则。适用于任何基于Java的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

技术架构：

Spring Boot 2.6.3 、 Sharding-JDBC 3.0.0.M3、MySql 1主2从模式、 JDK1.8

导入依赖

        <!-- for spring boot -->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>

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

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

application.yml配置

#sharding-jdbc
sharding.jdbc:
  datasource:
    names: ds-master,ds-slave-0,ds-slave-1
    ds-master:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.116.105:3306/test-01
      username: root
      password: root
    ds-slave-0:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.116.106:3306/test-01
      username: root
      password: root
    ds-slave-1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.116.107:3306/test-01
      username: root
      password: root
  config:
    masterslave:
      name: ds_ms
      master-data-source-name: ds-master
      slave-data-source-names: ds-slave-0,ds-slave-1
      load-balance-algorithm-type: round_robin
  props:
    sql.show: true
#mybatis
mybatis:
  config-location: classpath:mybatis/config.xml
  mapper-locations:
    - classpath:mybatis/mappers/*.xml

MySql数据表结构

CREATE TABLE `user` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10158 DEFAULT CHARSET=latin1;

实体类

package com.example.producers.domain;

import lombok.Data;

import java.io.Serializable;

/**
 * @author lanx
 * @date 2022/2/21
 */
@Data
public class User  implements Serializable {
    private  Integer id;
    private  String name;
    private  Integer age;
}

Dao层

package com.example.producers.dao;

import com.example.producers.domain.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author lanx
 * @date 2022/2/21
 */
@Mapper
public interface UserMapper {
    User selectByPrimaryKey(int id);
}

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.example.producers.dao.UserMapper">
    <resultMap id="BaseResultMap" type="com.example.producers.domain.User">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="INTEGER" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
    </resultMap>
    <sql id="Base_Column_List">
        id, name, age
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user
        where id = #{id,jdbcType=INTEGER}
    </select>
</mapper>

UserController 测试接口

package com.example.producers.web;

import com.example.producers.dao.UserMapper;
import com.example.producers.domain.User;
import com.example.producers.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * 用户管理
 * @author lanx
 * @date 2022/3/5
 */
@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/getUser")
    public User getUser(@RequestParam("id")int id){
        User user =  userMapper.selectByPrimaryKey(id);
        return user;
    }
}

接口调用

如果在整合过程中出现如下错误：

Configuration property name 'sharding.jdbc.datasource.ds_master' is not vali

报错原因
ShardingSphere 在5.0以前的版本数据源的命名、自定义分片算法命名是支持下划线命名的，如 write_ds，但是在5.0以后就不支持了。

解决方法
多个单词之间不要使用"_"分割，使用 ”-“ 分割即可，如 ds-master