Flicker方案用于生成自增ID

主要思路采用了MySQL自增长ID的机制(auto_increment + replace into)

建表语句：

CREATE TABLE `tb_seqno` (
  `a` varchar(1) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8

代码实现：

/**
 * Created by huoshaofeng on 2018/5/29.
 * 生成交易流水号：6位日期+10位源应用号+9位自增整数
 */

//@RunWith(SpringJUnit4ClassRunner.class)
//@ContextConfiguration(locations = "/applicationContext.xml")
@Service
@Slf4j
public class GenSeqNoUtil {

    @Qualifier("jade.dataSource.com.XXXXXX.YYYY.ZZZZ.dao")
    @Autowired
    org.apache.commons.dbcp.BasicDataSource dataSource;

    String SQL_REPLACE = "REPLACE INTO tb_seqNo(`a`) VALUE('a')";

//    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public String genSeqNo(){

        Date currentDate = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
        String currentDateStr = sdf.format(currentDate);

        String sourceAppId = BaixinTradeConstants.KEY_SOURCE_APP_ID;

        Long autoNumber = getSerialId(SQL_REPLACE) % 1000000000L;
        String autoNumberStr = String.format("%09d", autoNumber);

//        System.out.println(currentDateStr + sourceAppId + autoNumberStr);
        return currentDateStr + sourceAppId + autoNumberStr;
    }

    private long getSerialId(String genSql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
            stmt.execute(genSql);
            rs = stmt.executeQuery("select last_insert_id()");
            rs.next();
            long id = rs.getLong(1);
            return id;
        } catch (Throwable t) {
            log.error("fail to get serial id", t);
        } finally {
            try {
                if(rs != null) {
                    rs.close();
                }
                if(stmt != null) {
                    stmt.close();
                }
                if(conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                log.error("fail to close db connection resource", e);
            }
        }
        return -1;
    }
}