mybatis 自定义序列号

功能需求：每天的序列号从1开始，保留四位数，不足4位往前补0

1，新建一张表

CREATE TABLE `sequence` (
  `day_id` date DEFAULT NULL COMMENT '账期',
  `name` varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '序列的名字',
  `current_value` int(11) NOT NULL COMMENT '序列的当前值',
  `increment` int(11) NOT NULL COMMENT '序列的自增值',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='序列表';

2，mybatis  写法

    <!-- 获取序列号并更新序列号 -->
    <update id="nextval" parameterType="com.xxx.xxx.xxx.entity.Sequence">
        <selectKey resultType="java.lang.Long" keyColumn="current_value" keyProperty="current_value" order="AFTER">
            select current_value from sequence
            where name=#{name}
        </selectKey>
        UPDATE
            sequence
        SET current_value =
                CASE when day_id = CURDATE() then current_value + increment else 1 end,
            day_id =
                CASE when day_id = CURDATE() then day_id else CURDATE() end
        WHERE name = #{name};
    </update>

3，调用

//编号规则为：07XXYYYYMMDDnnnn，即 4位区号+8位日期+4位序号，如长沙分公司3月8日提交的工单为：XXXX202303080001
            String workOrderNumber = "";
            //查询序列号
            Sequence sequence = new Sequence();
            sequence.setName(name);
            myPointMapper.nextval(sequence);

            //获取当前时间
            Date date=new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
            //位数不足往前补0
            String serial_number = StringUtils.leftPad(String.valueOf(sequence.getCurrent_value()), 4, "0");

            workOrderNumber = "XXXX" + df.format(date) + serial_number;