通常,這種序號會有兩種呈現方式:
第一種是:在新增資料時,就要先有序號在畫面上
第二種是:資料實際要進資料庫時,才去取號
比較常見的三種產生序號的做法是:
- 寫 stored procedure,確保不會同時有兩個程式在執行
- 在insert的sql中寫序號產生的邏輯
- 將序號產生的邏輯抽出來共用,需要的時候再呼叫它
不論是那一種呈現方式,或那一種序號產生的做法,最重要的都是:
「如何避免序號重複 及 重複的處理」
所以,在建立資料庫表格的欄位時,序號的欄位一定要是 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 功能真的很強大,有空可以好好研究一下