一、在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; } }
三、数据库设计结构