Thursday, September 22, 2011

使用Spring JdbcTemplate產生序號(流水號)

常常會有碰到需求是要 按照一定的規則產生流水號
通常,這種序號會有兩種呈現方式:
第一種是:在新增資料時,就要先有序號在畫面上
第二種是:資料實際要進資料庫時,才去取號
比較常見的三種產生序號的做法是:

  1. 寫 stored procedure,確保不會同時有兩個程式在執行
  2. 在insert的sql中寫序號產生的邏輯
  3. 將序號產生的邏輯抽出來共用,需要的時候再呼叫它

不論是那一種呈現方式,或那一種序號產生的做法,最重要的都是:
「如何避免序號重複 及 重複的處理」
所以,在建立資料庫表格的欄位時,序號的欄位一定要是 unique或primary key
那當序號有重複時,就可以做進一步的處理:
如讓程式拋出exception、或是重新再取一次序號
JDBC偵測的方式可參考這篇文章:JDBC 欄位資料重複的偵測方式

以下是informix資料庫,搭配spring JdbcTemplate 並用上述的第三種做法,把它抽出來做成元件。JdbcTemplate會將JDBC的錯誤解譯後在丟出比較恰當的例外。在這裡的程式會判斷是否丟出DataIntegrityViolationException。
序號的規則是:兩碼英文字 + 三碼中國年 + 二碼月份 + 五碼流水號的做法,再加上判斷每個月的流水號要再重新開始。

資料庫的規格是:

   1: CREATE TABLE foo (
   2:     id INT PRIMARY KEY,
   3:     serial_number NVARCHAR(16) UNIQUE,
   4:     time_created DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND
   5: );
   6: CREATE SEQUENCE foo_seq; 




class-1。序號新增的程式



   1: public class SerialNumberedInserter {
   2: /**
   3: * 看有幾種不同的序號邏輯,都要繼承這個介面
   4: */
   5: public interface SerialNumberGenerator {
   6:     String generate();
   7: }
   8: /**
   9: * 建構子,可以自訂重覆取號的最大次數
  10: * @param jdbcTemplate 對資料庫存取的jdbcTemplate
  11: * @param generator 產生流水號的邏輯,實作Generator介面
  12: * @param maxRetry 最多重複嘗試產生流水號的次數
  13: */
  14: public SerialNumberedInserter(JdbcTemplate jdbcTemplate,
  15:                         SerialNumberGenerator generator,
  16:                         int maxRetry) {
  17:     this.jdbcTemplate = jdbcTemplate;
  18:     this.generator = generator;
  19:     this.maxRetry = maxRetry;
  20: }
  21: /**
  22: * 建構子,預設可以重覆取號三次
  23: * @param jdbcTemplate 對資料庫存取的jdbcTemplate
  24: * @param generator 產生流水號的邏輯,實作Generator介面
  25: */
  26: public SerialNumberedInserter(JdbcTemplate jdbcTemplate,
  27:                     SerialNumberGenerator generator) {
  28:     this(jdbcTemplate, generator, 3);
  29: }
  30:  
  31:  
  32: /**
  33: * @param sql 要執行新增的sql,如:
  34: *     INSERT INTO foo (id, serial_number) VALUES( ?, ? )
  35: * @param param jdbcTemplate要執行sql時需要的參數,如: 
  36: *     Object[] param = new Object[]{ foo_seq, "" ); 序號的地方要傳空值。
  37: * @param serialNumberPos 序號欄位在param中的第幾位(第一位是0)。
  38: * @param retries 目前因失敗已重複嘗試產生序號的次數
  39: */ 
  40:  
  41: private void tryInsert(String sql, Object[] param,
  42:                 int serialNumberPos, int retries) {
  43:     param[serialNumberPos] = generator.generate(); //取號
  44:     try {
  45:         jdbcTemplate.update(sql, param);
  46:     }
  47:     catch (DataIntegrityViolationException ex) {
  48:         if (retries < maxRetry) {
  49:         tryInsert(sql, param, serialNumberPos, retries + 1);
  50:     }
  51:     throw ex;
  52:     }
  53: }
  54:  
  55:  
  56: /**
  57: * 產生序號,並執行資料新增
  58: * @param sql 要執行新增的sql,如:
  59: *     INSERT INTO foo (id, serial_number) VALUES( ?, ? )
  60: * @param param jdbcTemplate要執行sql時需要的參數,如: 
  61: *     Object[] param = new Object[]{ foo_seq, "" ); 序號的地方要傳空值。
  62: * @param serialNumberPos 序號欄位在param中的第幾位(第一位是0)
  63: */ 
  64:  
  65: public String insert(String sql, Object[] param, int         serialNumberPos) {
  66:     tryInsert(sql, param, serialNumberPos, 0);
  67:     //如果有需要將產生的序號,存到另一個TABLE當FK時,可以將序號傳回
  68:     return param[serialNumberPos].toString();
  69: }
  70:  
  71: }



class-2。序號邏輯的程式



   1: /**
   2: * 都要實作 "序號邏輯"
   3: */
   4: public class SerialNumberGenerator implements SerialNumberedInserter.SerialNumberGenerator {
   5:     JdbcTemplate jdbcTemplate;
   6:     String tableName;
   7:     String serialNumberName;
   8:     String timeCreatedName;
   9:     String functionName
  10: /**
  11: * 建構子,在這裡將序號產生需要的參數都傳進來
  12: */
  13: public SerialNumberGenerator(JdbcTemplate      jdbcTemplate, //spring jdbcTemplate
  14:                String tableName, //資料表的名稱
  15:                String serialNumberName, //你取的序號欄位的名稱
  16:               String timeCreatedName, //資料建立的時間的欄位名稱
  17:                String functionName) { //序號的前兩碼功能代碼
  18:        this.jdbcTemplate = jdbcTemplate;
  19:        this.functionName = functionName;
  20:        this.tableName = tableName;
  21:        this.serialNumberName = serialNumberName;
  22:        this.timeCreatedName = timeCreatedName;
  23: }
  24:  
  25:  
  26: /**
  27: * 把資料庫中,目前最大的序號,找出來,informix找不到時,會回傳0
  28: *
  29: * 解析SQL:
  30: * MAX(%3$s) -> MAX(serialNumberName):
  31: *        先把最大筆的資料找出來,這樣資料庫的效能會比較好
  32: *
  33: * CAST( SUBSTR(MAX(%3$s), -5) AS int) -> 
  34: *        把序號 TW1000900001 取出來為 00001,並轉為數字
  35: *
  36: * timeCreatedName >= MDY(MONTH(TODAY), 1, YEAR(TODAY)):
  37: *        大於每個月1號,每個月流水號要從新開始
  38: *        (注意:這是INFORMIX的寫法,其它資料庫未必符合)
  39: *
  40: * %3$s LIKE ? -> serialNumberName LIKE TW10009%:
  41: *        這樣寫會讓效能比較好,不需要特別拆字去比較
  42: */
  43: private static String COUNT_SQL = 
  44:     "SELECT CAST(SUBSTR(MAX(%3$s), -5) AS int) FROM %1$s" +
  45:     " WHERE %2$s >= MDY(MONTH(TODAY), 1, YEAR(TODAY))" +
  46:     " AND %3$s LIKE ?" ;
  47:  
  48:  
  49: /**
  50: * 實作 "序號邏輯.序號產生" 的方法
  51: */
  52: public String generate() {
  53:     String serialNumber = null;
  54:     String prefix;
  55:     int count;
  56:     Calendar cal = Calendar.getInstance();
  57:     int year = cal.get(Calendar.YEAR) - 1911; //改成中國年
  58:     prefix = String.format("%1$s%2$03d%3$tm", functionName, year, new Date() ); //java.util Formatter
  59:     //取得目前資料庫中最大的序號
  60:     count = jdbcTemplate.queryForInt(
  61:     String.format( COUNT_SQL, tableName, timeCreatedName, serialNumberName),prefix + "%");
  62:     return String.format("%s%05d", prefix, count + 1); //回傳目前資料庫最大的序號加1,沒有資料會回傳0
  63: }
  64: }






class-3。需要取序號的程式


   1: public void insert(BeanObj bean) {
   2:     //新增資料的sql
   3:     String sql = "INSERT INTO foo (id, serial_number )" +
   4:                 " VALUES ( foo_seq, ? )";
   5:     String function = "TW";
   6:     /*
   7:     * 產生序號,請傳:
   8:     * 1、JdbcTemplate
   9:     * 2、資料庫table名稱
  10:     * 3、資料庫中序號的欄位名稱
  11:     * 4、資料庫中資料建立日期的欄位名稱、
  12:     * 5、功能代碼(序號所需要的代碼,可以多個 = =)
  13:     */
  14:     SerialNumberGenerator generator = new SerialNumberGenerator (jdbcTemplate,"table_name", "serial_number", "time_created", function);
  15:     //對應到新增資料時需要傳的參數
  16:     Object[] param = new Object[]{""};
  17:   
  18:      //請將序號的欄位,傳空格進來
  19:     SerialNumberedInserter inserter = new SerialNumberedInserter(jdbcTemplate, generator);
  20:     
  21:     inserter.insert(sql, param, 0);
  22:     //0的話是指:序號是在你組的 新增資料的sql中的第幾個位置,我是把它放在最前面,就第一個問號(?)的地方
  23:     //實際執行新增的程式,請傳入:1你組好的新增sql,2新增的資料(param),3指定序號是在param中的第幾個,(從0開始)
  24: }





備註:java.util 的 Formatter 功能真的很強大,有空可以好好研究一下

No comments:

Post a Comment