HTML5 離線存儲 Web SQL 入門（一）：從開啟資料庫到 CRUD 封裝的極簡實踐

Web SQL Database 雖已不再維護，但其簡潔的 SQL 風格 API 仍是理解客戶端結構化存儲的絕佳切入點。本文以「用戶故事」為例，演示如何在支援 HTML5 的瀏覽器（如 Chrome）中完成資料庫開啟、建表、增刪改查全流程，並封裝出類 ADO.NET 風格的 dataBaSEOperator 與業務層 userStoryProvider。

1. 核心封裝：dataBaSEOperator

function dataBaSEOperator() {};
dataBaSEOperator.openDatabase = function () {
    return window.openDatabase("dataBaseUserStorIEs", "1.0", "dataBase used for user storIEs", 2 * 1024 * 1024);
};
dataBaSEOperator.executeNonQuery = function (sql, parameters, callback) {
    var db = this.openDatabase();
    db.transaction(function (trans) {
        trans.executeSql(sql, parameters,
            function (trans, result) { callback(result); },
            function (trans, error) { throw error.message; }
        );
    });
};
dataBaSEOperator.executeReader = dataBaSEOperator.executeNonQuery;
dataBaSEOperator.executeScalar = dataBaSEOperator.executeNonQuery;

2. 業務層：userStoryProvider

function userStoryProvider() {
    this.createUserStoryTable = function () {
        dataBaSEOperator.executeNonQuery("CREATE TABLE tbUserStorIEs(id INTEGER PRIMARY KEY AUTOINCREMENT, role, ability, benefit, name, importance, estimate, notes)");
    };
    this.dropUserStoryTable = function () {
        dataBaSEOperator.executeNonQuery("DROP TABLE tbUserStorIEs");
    };
    this.addUserStory = function (role, ability, benefit, name, importance, estimate, notes) {
        dataBaSEOperator.executeNonQuery("INSERT INTO tbUserStorIEs(role,ability,benefit,name,importance,estimate,notes) SELECT ?,?,?,?,?,?,?",
            [role, ability, benefit, name, importance, estimate, notes], function (result) { /* console.log(result.rowsAffected); */ });
    };
    this.removeUserStory = function (id) {
        dataBaSEOperator.executeNonQuery("DELETE FROM tbUserStorIEs WHERE id = ?", [id], function (result) { /* ... */ });
    };
    this.loadUserStorIEs = function (callback) {
        dataBaSEOperator.executeReader("SELECT * FROM tbUserStorIEs", [], function (result) { callback(result); });
    };
}

3. 模型與調用

function userStory(id, name, role, ability, benefit, importance, estimate, notes) {
    this.id = id; this.name = name; this.role = role; this.ability = ability;
    this.benefit = benefit; this.importance = importance; this.estimate = estimate; this.notes = notes;
}
var _userStoryProvider = new userStoryProvider();
_userStoryProvider.createUserStoryTable();
_userStoryProvider.addUserStory("User", "Login", "Access system", "Login Feature", 10, 5, "None");

關鍵點： 所有操作均在 db.transaction 回呼中進行，天然支援異步與事務原子性。參數化查詢（? 佔位符）有效防範 SQL 注入。實際專案中需補充錯誤處理、版本遷移與 IndexedDB 降級方案。