并发安全的mysql序列实现

--仿照序列表
drop table if exists sequence;   
create table sequence (   
    seq_name        VARCHAR(50) NOT NULL,
    current_val     INT         NOT NULL,
    increment_val   INT         NOT NULL    DEFAULT 1,
PRIMARY KEY (seq_name)   
);
-- 增加序列 
INSERT INTO sequence(seq_name,current_val,increment_val) VALUES ('SEQ_R_USER',1,1);
INSERT INTO sequence(seq_name,current_val,increment_val) VALUES ('SEQ_R_ROLE',1,1);
INSERT INTO sequence(seq_name,current_val,increment_val) VALUES ('SEQ_TB_GARAGE',1,1);
INSERT INTO sequence(seq_name,current_val,increment_val) VALUES ('SEQ_TB_CUSTOM',1,1);
INSERT INTO sequence(seq_name,current_val,increment_val) VALUES ('SEQ_TB_FIX',1,1);
INSERT INTO sequence(seq_name,current_val,increment_val) VALUES ('SEQ_TB_PARTS',1,1);
 
-- 当前序列值
DROP FUNCTION IF EXISTS `currval`;
create function currval(v_seq_name VARCHAR(50))   
returns integer  
begin  
    declare value integer;   
    set value = 0;   
    select current_val into value   
    from sequence  
    where seq_name = v_seq_name;   
    return value;   
end;


-- 下一个序列值 nextval 需要commit之后才会更新到表中。
DROP FUNCTION IF EXISTS `nextval`;
create function nextval (v_seq_name VARCHAR(50))   
returns integer  
begin
DECLARE t_error INTEGER DEFAULT 0;  
DECLARE EXIT HANDLER FOR SQLEXCEPTION return -1;
  update sequence
  set current_val = current_val + increment_val
  where seq_name = v_seq_name;
  return currval(v_seq_name);
end;
/*
-- 设值 setval
DROP FUNCTION IF EXISTS `setval`;
create function setval(v_seq_name VARCHAR(50), v_new_val INTEGER)   
returns integer  
begin  
  update sequence  
  set current_val = v_new_val   
  where seq_name = v_seq_name;   
return currval(v_seq_name);
end;
*/
-- 测试
-- select setval('SEQ_R_USER',0);
select currval('SEQ_R_USER');
select nextval('SEQ_R_USER');


-- 事物 并发  关闭自动commit后用两个窗口测试
select nextval('SEQ_R_USER');
select nextval('SEQ_R_USER');
 
-- show function status;


-- 获取序列值的存储过程 p_nextval
DROP PROCEDURE IF EXISTS p_nextval;  
CREATE PROCEDURE p_nextval(IN v_seq_name VARCHAR(50),OUT o_retsult VARCHAR(50)) 
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
  ROLLBACK;
  SET o_retsult=-1;
 END;  
 START TRANSACTION;  
  update sequence
  set current_val = current_val + increment_val
  where seq_name = v_seq_name;
  set o_retsult= currval(v_seq_name);
 COMMIT;
END;


 
-- -- END 仿照序列表
----------------------------------
-- 使用
select * from sequence;
call p_nextval('SEQ_R_USER',@o_result);
select @o_result
 
-------------------------------------------------------

-- java jdbc调用
-------------------------------------------------------

java jdbc这么调用；

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class TestMysqlProcedure {
public static void main(String[] args) throws Exception {
Connection cnn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
cnn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/xx", "root",
"passswordxxxx");
int status = -1;
CallableStatement cs = cnn.prepareCall("{ call p_nextval (?,?) }");
int pos = 0;
cs.setString(++pos, "nnid");
cs.registerOutParameter(++pos, java.sql.Types.INTEGER);
cs.execute();
status = cs.getInt(2);
System.out.println(status);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (cnn != null) {
cnn.close();
}
}
}
}

 

-------------------------------------------------------------

ibatis这么用：

    /**
     * 获取序列
     * @param seq
     * @return
     */
    public String getSequenceByProcedure(String seq)
    {
        try
        {
            HashMap<String, Object> paramMap = new HashMap<String, Object>();
            paramMap.put("v_seq_name", seq);
            paramMap.put("o_ret", "");
            updateObject("getNextval", paramMap);
            return String.valueOf(paramMap.get("o_retsult"));
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
        return -1l;
    }

xml中配置：

<parameterMap class="java.util.Map" id="seqNameParameters"> 
<parameter property="v_seq_name" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />
<parameter property="o_retsult" javaType="java.lang.String" jdbcType="VARCHAR" mode="OUT" />
</parameterMap>
<procedure id="getNextval" parameterMap="seqNameParameters">
{call p_nextval(?, ?)}
</procedure>