借助一个数据库表来维护主键(mybatis调用存储过程)

﹏ヽ暗。殇╰゛Y 2022-05-19 02:17 134阅读 0赞

\#\#\#创建主键表  
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;

![这里写图片描述][70]

\#\#\#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");
        }

[70]: /images/20220519/ec18b70545a944e98d5f743e98adf9ea.png