版本配置

pom文件

<dependencyManagement>
    <dependencies>
        <!-- Override Spring Data release train provided by Spring Boot -->
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-releasetrain</artifactId>
            <version>Fowler-SR2</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-dependencies</artifactId>
            <version>2.2.6.RELEASE</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
  </dependencyManagement>
  
  <dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.1.tmp</version>
    </dependency>
    
    <!-- Starter for using JDBC with the HikariCP connection pool -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <!-- Starter for testing Spring Boot applications with libraries including JUnit, Hamcrest and Mockito -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <!-- exclude JUnit 4 support -->
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <!-- Starter for logging using Logback. Default logging starter -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-logging</artifactId>
    </dependency>
    
    <!--MySQL JDBC驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>3.1.0</version>
    </dependency>
  </dependencies>
  
  <build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
  </build>

application.yaml

spring:
  application:
    name: spring-sharding-jdbc
#  shardingsphere:
#    props.sql.show: true
#    datasource:
#      names: ds0,ds1 ##数据源名称，多数据源以逗号分隔
#      ds0:
#        type: com.zaxxer.hikari.HikariDataSource
#        driver-class-name: com.mysql.cj.jdbc.Driver
#        url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=utf-8&connectTimeout=60000&socketTimeout=60000&serverTimezone=GMT%2B8
#        username: root
#        password: hy190418
#        sql-script-encoding: UTF-8 #spring.shardingsphere.datasource.<data-source-name>.xxx= #数据库连接池的其它属性
#      ds1:
#        type: com.zaxxer.hikari.HikariDataSource
#        driver-class-name: com.mysql.cj.jdbc.Driver
#        url: jdbc:mysql://127.0.0.1:3306/test_2?useUnicode=true&characterEncoding=utf-8&connectTimeout=60000&socketTimeout=60000&serverTimezone=GMT%2B8
#        username: root
#        password: hy190418
#        sql-script-encoding: UTF-8
#    sharding:
#      tables:
#        t_order:
#          #spring.shardingsphere.sharding.tables.<logic-table-name>.actual-data-nodes
#          #由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
#          actual-data-nodes: ds$->{0..1}.t_order$->{0..1}
#          table-strategy:
#            inline:
#              sharding-column: order_id
#              algorithm-expression: t_order$->{order_id % 2}
#          key-generator:
#            column: order_id
#            type: SNOWFLAKE
#        t_order_item: 
#          actual-data-nodes: ds$->{0..1}.t_order_item$->{0..1}
#          table-strategy:
#            inline.sharding-column: order_id
#            inline.algorithm-expression: t_order_item$->{order_id % 2}
#          key-generator:
#            column: order_item_id
#            type: SNOWFLAKE
#      binding-tables: t_order,t_order_item
#      broadcast-tables: t_config
#      
#      default-database-strategy:
#        inline.sharding-column: user_id
#        inline.algorithm-expression: ds$->{user_id % 2}

sharding:
  jdbc:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        #IllegalArgumentException: jdbcUrl is required with driverClassName.
        #url: jdbc:mysql://127.0.0.1:3306/test_0?useUnicode=true&characterEncoding=utf-8&connectTimeout=60000&socketTimeout=60000&serverTimezone=GMT%2B8
        username: root
        password: hy190418
        jdbc-url: jdbc:mysql://127.0.0.1:3306/test_0?useUnicode=true&characterEncoding=utf-8&connectTimeout=60000&socketTimeout=60000&serverTimezone=GMT%2B8
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        #url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=utf-8&connectTimeout=60000&socketTimeout=60000&serverTimezone=GMT%2B8
        jdbc-url: jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=utf-8&connectTimeout=60000&socketTimeout=60000&serverTimezone=GMT%2B8
        username: root
        password: hy190418
    config:
      sharding:
        #default-data-source-name: ds0
        tables:
          t_order:
            #spring.shardingsphere.sharding.tables.<logic-table-name>.actual-data-nodes
            #由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
            actual-data-nodes: ds$->{0..1}.t_order_$->{0..1}
            table-strategy:
              inline:
                sharding-column: order_id
                algorithm-expression: t_order_$->{order_id % 2}
            key-generator:
              column: order_id
              type: SNOWFLAKE
          t_order_item: 
            actual-data-nodes: ds$->{0..1}.t_order_item$->{0..1}
            table-strategy:
              inline.sharding-column: order_id
              inline.algorithm-expression: t_order_item$->{order_id % 2}
            key-generator:
              column: order_item_id
              type: SNOWFLAKE
        binding-tables: t_order,t_order_item
        #broadcast-tables: t_config
        
        default-database-strategy:
          inline.sharding-column: user_id
          inline.algorithm-expression: ds$->{user_id % 2}

datasource配置数据库链接是时使用的是jdbc-url，这是因为我用的链接池是hikari，其他常用链接池请用url

数据库

库表结构.jpg

建表SQL

CREATE TABLE `t_order_0`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED,
  `order_id` bigint(20) UNSIGNED,
  `update_time` timestamp(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `create_time` timestamp(0),
  PRIMARY KEY (`id`)
);

CREATE TABLE `t_order_item_0`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) UNSIGNED,
  `order_item_id` bigint(20) UNSIGNED,
  `update_time` timestamp(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `create_time` timestamp(0),
  PRIMARY KEY (`id`)
);

CREATE TABLE `t_order_1`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED,
  `order_id` bigint(20) UNSIGNED,
  `update_time` timestamp(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `create_time` timestamp(0),
  PRIMARY KEY (`id`)
);

CREATE TABLE `t_order_item_1`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) UNSIGNED,
  `item_id` bigint(20) UNSIGNED,
  `update_time` timestamp(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `create_time` timestamp(0),
  PRIMARY KEY (`id`)
);

t_order_item暂时没用到

代码

bean类

@Data
@TableName(value = "t_order")
public class Order {

    @TableId
    private Long id;
    private Long userId;
    private Long orderId;
    private Timestamp updateTime;
    private Timestamp createTime;
    
}

这里的TbaleName对应application.yaml中的binding-tables

Mapper接口

public interface OrderMapper extends BaseMapper<Order> {

    @Select("SELECT * FROM t_order WHERE user_id=#{userId}")
    List<Order> selectByUserId(long userId);
}

配置类

@Configuration
@Import(value = {SpringBootConfiguration.class})
@MapperScan(basePackages = {"com.study.spring.sharding.mapper"})
public class ShardingConfig {

}

测试类

@SpringBootTest
public class OrderMapperTest {

    @Autowired
    private OrderMapper orderMapper;
    
    @Test
    public void insertTest() {
        Timestamp time = Timestamp.valueOf(LocalDateTime.now());
        
        Order order1 = new Order();
        order1.setUserId(1L);
        order1.setOrderId(1L);
        order1.setUpdateTime(time);
        order1.setCreateTime(time);
        
        System.out.println(orderMapper.insert(order1));
        
        Order order2 = new Order();
        order2.setUserId(2L);
        order2.setOrderId(2L);
        order2.setUpdateTime(time);
        order2.setCreateTime(time);
        
        System.out.println(orderMapper.insert(order2));
        
        Order order3 = new Order();
        order3.setUserId(1L);
        order3.setOrderId(2L);
        order3.setUpdateTime(time);
        order3.setCreateTime(time);
        
        System.out.println(orderMapper.insert(order3));
        
        Order order4 = new Order();
        order4.setUserId(2L);
        order4.setOrderId(1L);
        order4.setUpdateTime(time);
        order4.setCreateTime(time);
        
        System.out.println(orderMapper.insert(order4));
    }
    
    @Test
    public void selectTest() {
        System.out.println(orderMapper.selectByUserId(1));
    }
}

其中遇到的问题

1. dataSource已经被注册

Invalid bean definition with name 'dataSource' defined in class path

通过配置(exclude = {DataSourceAutoConfiguration.class})可解决，但又会出现IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required。

解决办法

@Import(value = {SpringBootConfiguration.class})

既上面配置类中的写法