spring-boot整合shardingsphere 分库分表

1，创建表

CREATE TABLE `user_info_0` (
  `id` varchar(32) NOT NULL,
  `user_name` varchar(64) DEFAULT NULL,
  `age` int(8) DEFAULT NULL,
  `user_id` int(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
CREATE TABLE `user_info_1` (
  `id` varchar(32) NOT NULL,
  `user_name` varchar(64) DEFAULT NULL,
  `age` int(8) DEFAULT NULL,
  `user_id` int(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2，引入相关pom依赖

 		<dependency>
        	 <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter</artifactId>
             <version>2.7.10</version>
            <exclusions>
                <exclusion>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>2.7.10</version>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>com.vaadin.external.google</groupId>
                    <artifactId>android-json</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
		//不能用spring-boot3,不然启动的时候会报Property ‘sqlSessionFactory’ or 
		‘sqlSessionTemplate’ are required 这个错误,应该是不匹配,具体原因没找到
		 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.21</version>
        </dependency>
        //直接引用druid的依赖,不用druid-spring-boot-starter,不然spring-boot启动的时候
        会自动适配数据源,还得在启动类注解exclude掉DruidDataSourceAutoConfigure类
        <!--        <dependency>-->
        <!--            <groupId>org.mybatis.spring.boot</groupId>-->
        <!--            <artifactId>mybatis-spring-boot-starter</artifactId>-->
        <!--            <version>2.2.1</version>-->
        <!--        </dependency>-->
        // 这里用mybatisPlus
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
		
        <!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
            <scope>runtime</scope>
        </dependency>

3，sharding数据源及分片策略配置

mybatis.config-location=classpath:META-INF/sqlmap/mybatis-config.xml
mybatis-plus.configuration.cache-enabled=true
mybatis-plus.configuration.use-generated-keys=true
mybatis-plus.configuration.default-executor-type=reuse
mybatis-plus.configuration.use-actual-param-name=true

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://10.xxxxxxx/db_market_activity?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.ds0.username=user
spring.shardingsphere.datasource.ds0.password=123456

spring.shardingsphere.sharding.tables.user_info.actual-data-nodes=ds0.user_info_${0..1}
spring.shardingsphere.sharding.tables.user_info.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.user_info.table-strategy.inline.algorithm-expression=user_info_$->{user_id % 2}
//根据用户id分片,两张表

4，测试代码

1,新建对象

@TableName("user_info")
public class UserInfoDO {
    private String id;

    private String userName;

    private Integer age;

    private Integer userId;

2，创mapper

//记得集成mybatis-plus提供的类
public interface UserInfoDOMapper extends BaseMapper<UserInfoDO> {
}

3， 测试用例

@SpringBootTest
public class ShardingSphereTest{

    @Autowired
    UserInfoDOMapper userInfoDOMapper;
    @Test
    public void  testUserInfo(){
        UserInfoDO userInfoDO=new UserInfoDO();
        userInfoDO.setUserName("yzy");
        userInfoDO.setId("11124545");
        userInfoDO.setAge(13);
        userInfoDO.setUserId(331212);
        userInfoDOMapper.insert(userInfoDO);
        System.out.println("插入完成");
    }
}

5，执行测试方法

日志里看到了分片策略的配置表示成功了

当userId尾数为奇数的时候

执行插入的日志

成功的插入了user_info_1表

同理,当userId为偶数的时候会插入user_info_0表