一、建表
CREATE TABLE test.t_sequence (
name varchar(50) NOT NULL COMMENT 'sequence名称',
currentvalue bigint(20) NOT NULL COMMENT 'sequence当前值',
increment int(10) NOT NULL COMMENT 'sequence每次增长的步长',
PRIMARY KEY (name),
UNIQUE INDEX pk_sequence (name)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
二、创建函数
CREATE DEFINER = 'root'@'localhost'
FUNCTION test.
currval(seq_name varchar(50))
RETURNS bigint(20)
DETERMINISTIC
begin
select currentvalue into @val
from t_sequence
where name = seq_name;
return @val;
end
CREATE DEFINER = 'root'@'localhost'
FUNCTION test.
nextval(seq_name varchar(50))
RETURNS bigint(20)
DETERMINISTIC
BEGIN
UPDATE t_sequence
SET currentvalue = currentvalue + increment
WHERE name = seq_name;
RETURN currval(seq_name);
END
CREATE DEFINER = 'root'@'localhost'
FUNCTION test.
setval(seq_name varchar(50),val integer)
RETURNS bigint(20)
DETERMINISTIC
begin
update t_sequence
set currentvalue = val
where name = seq_name;
return val;
end
CREATE DEFINER = 'root'@'localhost'
FUNCTION test.
gen_id(seq_name varchar(100),source varchar(100),yearmonth varchar(6),dbtype varchar(3))
RETURNS varchar(200) CHARSET utf8
BEGIN
RETURN CONCAT(source,CONCAT(IFNULL(NULL,EXTRACT(YEAR_MONTH FROM NOW())),CONCAT(nextval(seq_name))),dbtype);
END
三、SequenceMapper.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="testwebapp.com.wangzuojia.dao.SequenceMapper" >
<resultMap id="BaseResultMap" type="testwebapp.com.wangzuojia.entity.Sequence" >
<id column="name" property="name" jdbcType="VARCHAR" />
<result column="currentvalue" property="currentvalue" jdbcType="BIGINT" />
<result column="increment" property="increment" jdbcType="INTEGER" />
</resultMap>
<select id="generateId" parameterType="java.util.Map"
statementType="CALLABLE">
{#{result,jdbcType=VARCHAR,mode=OUT} = call
gen_id(
#{seq_name,jdbcType=VARCHAR,mode=IN},
#{source,jdbcType=VARCHAR,mode=IN},
#{yearmonth,jdbcType=VARCHAR,mode=IN},
#{dbtype,jdbcType=VARCHAR,mode=IN})}
</select>
<insert id="insert" parameterType="testwebapp.com.wangzuojia.entity.Sequence" >
insert into t_sequence (name, currentvalue, increment
)
values (#{name,jdbcType=VARCHAR}, #{currentvalue,jdbcType=BIGINT}, #{increment,jdbcType=INTEGER}
)
</insert>
</mapper>