通过数据库函数实现自增长sequence值

    一、在mysql里面创建函数nextval

CREATE DEFINER=`root`@`%` FUNCTION `nextval`(NAME varchar(50)) RETURNS bigint(10)
BEGIN
DECLARE _cur bigint;
set _cur=(select CURRENT_VALUE from sys_sequence where NAME=NAME);
UPDATE sys_sequence
SET CURRENT_VALUE=_cur+INCREMENT
WHERE NAME=NAME;
RETURN _cur;
END

 

二、在项目中进行调用。

2-1 mapper.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.atguigu.springboot.mapper.SequenceMapper">
    <select id="nextValue" parameterType="java.lang.String" resultType="java.lang.Long">
    select nextval(#{NAME,jdbcType=VARCHAR}) from DUAL;
  </select>

</mapper>

2-2 mapper 

@Component
public interface SequenceMapper {

    Long nextValue(String NAME);
}

2-3 controller调用

@RestController
public class TeacherController {

   

    @Autowired
    private SequenceMapper sequenceMapper;
   
    public long getvalue() {

        Long result=sequenceMapper.nextValue("aaa_bbb_ccc");
        System.out.println(result);
        return  result;
    }

}

三、数据库设计结构

转载于:https://my.oschina.net/u/4117203/blog/3068759