借助数据库行级锁实现每天001,002这样的自增序号

-- 流水号自增表创建
DROP TABLE IF EXISTS code_date_sequence;
CREATE TABLE code_date_sequence (
  code_date VARCHAR(255) not null comment '日期字符串yyMMdd格式',
  code_type varchar(255) not NULL COMMENT '业务类型 通常是生成业务序号的前缀',
  sequence_no BIGINT not NULL COMMENT '序列号',
  UNIQUE KEY PK_date_code (code_date,code_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='序列号增长表';

-- 查询函数创建
DROP FUNCTION IF EXISTS getCodeDateNumber;
CREATE  FUNCTION getCodeDateNumber(codeType VARCHAR(255),codeDate VARCHAR(255),startValue INTEGER(11)) RETURNS bigint(20)
BEGIN
	if startValue is  null then
    set startValue = 1;
  end if;
	set @sn = startValue;
	INSERT INTO code_date_sequence (code_date,code_type,sequence_no) 
	VALUES (codeDate,codeType,startValue) ON DUPLICATE KEY UPDATE sequence_no=@sn:=sequence_no+1;
    RETURN(@sn);
END;

 

@Service
public class GeneratorBiz implements IGeneratorBiz {
	private static final Logger logger = LoggerFactory.getLogger(GeneratorBiz.class);
	
	private static final String prefix0 = "00";
	
	@Resource
	private CodeDateMybatisDao codeDateMybatisDao;
	
	@Override
	@Transactional(propagation = Propagation.REQUIRES_NEW)
	public String generatorStandardCode (String standardType) {
		String codeDate = DateUtil.format(new Date(), DateUtil.DATE_TIME_FORMAT_YMD);
		int suffixLength = 3;
		String result = null;
		String number = generatorSerialNumber(codeDate, standardType);
		if (StringUtils.isNotBlank(number)) {
			result = StringUtils.right(prefix0+number, suffixLength);
			if(number.length()>suffixLength){
				result = number;
			}
			result = standardType + codeDate + result;
		}
		
		return result;
	}
	
	@Override
	@Transactional(propagation = Propagation.REQUIRES_NEW)
	public String generatorTaskCode (String standardCode, String warehouseCode) {
		String codeDate = DateUtil.format(new Date(), "MMdd");
		int suffixLength = 2;
		String result = null;
		String codeType = standardCode + "-" +  warehouseCode;
		String number = generatorSerialNumber(codeDate, codeType);
		if (StringUtils.isNotBlank(number)) {
			result = StringUtils.right(prefix0+number, suffixLength);
			if(number.length()>suffixLength){
				result = number;
			}
			result = codeType + "-" + codeDate + "-" + result;
		}
		
		return result;
	}
	
	private String generatorSerialNumber(String codeDate, String codeType) {
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("codeDate", codeDate);
		map.put("codeType", codeType);
		codeDateMybatisDao.insertOnDuplicate(map);
		Integer number = codeDateMybatisDao.getCodeDateNumber(map);
		logger.info(DateUtil.format(new Date()) + ",codeDate:" + codeDate + ",codeType:" + codeType  + ",number" + number + ",threadId" + Thread.currentThread().getId());
		return number != null ? number.toString() : null;
	}

 

@MyBatisRepository
public interface CodeDateMybatisDao {
	public Integer getCodeDateNumber(Map<String,Object> paramMap);
	public void insertOnDuplicate(Map<String,Object> paramMap);
}

 

<?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">
<!-- namespace必须指向Dao接口 -->
<mapper namespace="com.sf.wop.qms.dao.mybatis.CodeDateMybatisDao">

	<!-- 查询生成流水 -->
	<select id="getCodeDateNumber" parameterType="map" resultType="Integer">
		select sequence_no from tb_qms_code_date_sequence 
		where (code_date,code_type) in ((#{codeDate},#{codeType}))
	</select>
	
	<!-- 使用行级锁保证拿到的序号唯一 -->
	<insert id="insertOnDuplicate" parameterType="map">
		INSERT INTO tb_qms_code_date_sequence (code_date,code_type,sequence_no) 
		VALUES (#{codeDate},#{codeType},1) ON DUPLICATE KEY UPDATE sequence_no=sequence_no+1
	</insert>

</mapper>