###创建主键表
CREATE TABLE c_table_key
(
table_name
varchar(50) NOT NULL COMMENT ‘需要创建主键的表名’,
last_key
bigint(20) NOT NULL COMMENT ‘最后一次使用的主键,新的主键将在此基础上累加’,
create_date_time
datetime NOT NULL COMMENT ‘创建时间’,
update_date_time
datetime NOT NULL COMMENT ‘更新时间’,
PRIMARY KEY (table_name
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘创建表主键时的参照信息表名’;
###新建一个存储过程
参数:IN table_name_in
varchar(50),IN sequence_count
int,OUT d_sequence_value
int
BEGIN
START TRANSACTION;
SELECT last_key INTO d_sequence_value FROM c_table_key WHERE table_name = table_name_in FOR UPDATE;
IF(d_sequence_value IS NULL) THEN
SET d_sequence_value = 1;
-- 插入新主键
INSERT INTO c_table_key (table_name, last_key,create_date_time,update_date_time) values(table_name_in, 1,NOW(),NOW());
END IF;
-- 更新表中主键
UPDATE c_table_key SET last_key = d_sequence_value + sequence_count,update_date_time = NOW() WHERE last_key = d_sequence_value AND table_name = table_name_in;
COMMIT;
END
###调试
CALL getGeneratorId(‘aa’,‘1’,@cout);
SELECT @cout;
###mybatis中存储过程语句
CALL getGeneratorId(#{tableName,mode=IN,jdbcType=VARCHAR},#{tableCount,mode=IN,jdbcType=INTEGER},#{generatorId,mode=OUT,jdbcType=BIGINT});
###java中
void getGeneratorId(Map<String, Object> paramMap);
@Override
@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
public <T> long getLongGeneratorId(Class<T> doMain) {
Map<String, Object> paramMap = new HashMap<>(3);
paramMap.put("tableName", doMain.getAnnotation(Table.class).name());
paramMap.put("generatorId", 0);
paramMap.put("tableCount",1);
cTableKeyMapper.getGeneratorId(paramMap);
return(long) paramMap.get("generatorId");
}