记一次shardingJDBC主键生成策略没有生效问题

pom.xml

         <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

        <!--JDBC-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>

        <!--sharding-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

application.properties

#配置真实数据源
spring.shardingsphere.datasource.names=m1

#配置数据源
spring.shardingsphere.datasource.m1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/coursedb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456

# 指定表的分布情况 配置表在哪个数据库里，表名是什么。水平分表，分两个表：m1.course_1,m1.course_2  course逻辑表 物理表course_1
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}


# 指定表的主键生成策略
# 指定主键
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
# 指定生成算法 SNOWFLAKE：雪花算法  UUID算法
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#雪花算法的一个可选参数
spring.shardingsphere.sharding.tables.course.key-generator.props.worker.id =1


#指定分片策略 约定cid值为偶数添加到course_1表。如果是奇数添加到course_2表。
# 分片键：inline模式下按id分表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
# 根据计算的字段算出对应的表名。分片算法：ID取摩
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}

# 打开sql日志输出。
spring.shardingsphere.props.sql.show=true


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

实体类

public class Course {

    //@TableId
    private long cid;

    private String cname;

    private long userId;

    private String cstatus;

    public Long getCid() {
        return cid;
    }

    public void setCid(Long cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getCstatus() {
        return cstatus;
    }

    public void setCstatus(String cstatus) {
        this.cstatus = cstatus;
    }

    @Override
    public String toString() {
        return "Course{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", userId=" + userId +
                ", cstatus='" + cstatus + '\'' +
                '}';
    }
}

Mapper

public interface CourseMapper extends BaseMapper<Course> {

}

springboot 启动类：

@SpringBootApplication
@MapperScan("com.shardingsphere.sharding_jdbc.Mapper")
public class ShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class, args);
    }

}

测试类：

@SpringBootTest
@RunWith(SpringRunner.class)
class ShardingJdbcApplicationTests {


    @Resource
    private CourseMapper courseMapper;


    @Test
    public void add(){

        for (int i=0;i<10;i++){
            Course course = new Course();
            course.setCname("java");
            course.setUserId(100L);
            course.setCstatus("1");
            courseMapper.insert(course);
        }
    }



}

结果运行报错：

2022-03-28 15:11:17.947  INFO 53545 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO course  ( cid,
cname,
user_id,
cstatus )  VALUES  ( ?,
?,
?,
? )
2022-03-28 15:11:17.948  INFO 53545 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@71c0b742, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3bbb8c16), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@3bbb8c16, columnNames=[cid, cname, user_id, cstatus], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=67, stopIndex=67, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=70, stopIndex=70, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=73, stopIndex=73, parameterMarkerIndex=3)], parameters=[0, java, 100, 1])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=cid, generated=false, generatedValues=[0])])
2022-03-28 15:11:17.948  INFO 53545 --- [           main] ShardingSphere-SQL                       : Actual SQL: m1 ::: INSERT INTO course_1  ( cid,
cname,
user_id,
cstatus )  VALUES  (?, ?, ?, ?) ::: [0, java, 100, 1]

org.springframework.dao.DuplicateKeyException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'PRIMARY'

根据报错信息得知，是表的cid 没有设置自增id，其实不然。在application.properties配置中我们已经采用了  shardingJDBC集成的 SNOWFLAKE（雪花算法），但是该主键生成策略没有生效，即使将表的cid 改为 自增，也会得到 分表不成功， 数据将全部存入course_1 表中。

分析实际执行SQL得到：Actual SQL: m1 ::: INSERT INTO course_1  ( cid,cname,user_id,cstatus )  VALUES  (?, ?, ?, ?) ::: [0, java, 100, 1] 

怎么cid插入的0 然后仔细检查代码发现 实体类中 应该使用 LONG 类型而不是 long类型

如下：

public class Course {

    //@TableId
    private Long cid;

    private String cname;

    private Long userId;

    private String cstatus;
}

启动运行test ， 发现表中数据已经插入：