HTML 5離線存儲之Web SQL
作者：kkun
WebDatabase 規范中說這份規范不再維護了,原因是同質化(幾乎實現者都選擇了Sqlite), 且不說這些,單看在HTML5中如何實現離線數據的CRUD,最基本的用法(入門級別)
本篇沒有考慮異步,多線程及SQL注入
WebDatabase 規范中說這份規范不再維護了,原因是同質化(幾乎實現者都選擇了Sqlite),
且不說這些,單看在HTML5中如何實現離線數據的CRUD,最基本的用法(入門級別)
1,打開數據庫
2,創建表
3,新增數據
4,更新數據
5,讀取數據
6,刪除數據
事實上,關鍵點在于如何拿到一個可執行SQL語句的上下文,
像創建表,刪除表,CRUD操作等僅區別于SQL語句的寫法.OK,貌似"SqlHelper"啊,換個名字,dataBaseOperator就它了
executeReader,executeScalar兩個方法與executeNonQuery嚴重同質,
下邊的代碼產生定義了我們的dataBaseOperator"類",第二行
3-5行則定義打開數據庫連接方法,"類方法",效果類似C#中的靜態方法,直接類名.方法調用
6-15行則定義executeNonQuery方法,意指查詢數據庫,與executeReader方法和executeScalar方法同質,均可返回記錄集
整個 dataBaseOperator就完整了,很簡單,唯一要指出的是,測試以下代碼時請選擇一個支持HTML5的瀏覽器!如Google Chrome
- //TODO;SQL注入
- 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;
- });
- });
- }
- dataBaseOperatordataBaseOperator.executeReader = dataBaseOperator.executeNonQuery;
- dataBaseOperatordataBaseOperator.executeScalar = dataBaseOperator.executeNonQuery;
有了"SqlHeper",再看業務處理層(Business Logic Layer)
業務處理類包括了創建表,刪除表,新增記錄,刪除記錄以及讀取記錄,這里沒有寫更新,實際上先刪后增一樣滴,即使要寫也不復雜
- 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) {
- //alert("rowsAffected:" + result.rowsAffected);
- });
- };
- this.removeUserStory = function (id) {
- dataBaseOperator.executeNonQuery("DELETE FROM tbUserStories WHERE id = ?", [id], function (result) {
- //alert("rowsAffected:" + result.rowsAffected);
- });
- };
- this.loadUserStories = function (callback) {
- dataBaseOperator.executeReader("SELECT * FROM tbUserStories", [], function (result) {
- callback(result);
- });
- //result.insertId,result.rowsAffected,result.rows24 };
- }
createUserStoryTable,dropUserStoryTable,addUserStory,removeUserStory又是嚴重同質,不說了,僅SQL語句不同而已
但loadUserStories與上述四個方法均不同,是因為它把SQLResultSetRowList返回給了調用者,這里仍然是簡單的"轉發",頁面在使用的時候需要首先創建provider實例(使用類似C#中的類實例上的方法調用)
- var _userStoryProvider = new userStoryProvider();
之后就可以調用該實例的方法了,僅舉個例子,具體代碼省去
- function loadUserStory() {
- try {
- _userStoryProvider.loadUserStories(function (result) {
- var _userStories = new Array();
- for (var i = 0; i < result.rows.length; i++) {
- var o = result.rows.item(i);
- var _userStory = new userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);
- _userStories.push(_userStory);
- }//...
- } catch (error) {
- alert("_userStoryProvider.loadUserStories:" + error);
- }}
得到_userStories這個數組后,就沒有下文了,是自動創建HTML還是綁定到EXT,發揮想象力吧...繼續
userStory是一個自定義的"Model" "類"·
- 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;
- };
最后貼出應用的代碼,業務相關的代碼,不看也罷,誰家與誰家的都不同
- /*
- http://stackoverflow.com/questions/2010892/storing-objects-in-html5-localstorage
- http://www.w3.org/TR/webdatabase/#sqlresultset
- http://html5doctor.com/introducing-web-sql-databases/
- http://stackoverflow.com/questions/844885/sqlite-insert-into-with-unique-names-getting-id
- */
- var _userStoryProvider = new userStoryProvider();
- $(document).ready(function () {
- loadUserStory();
- /* 添加用戶故事 */
- $("#btnAdd").click(function () {
- var item = { role: $("#role").val(), ability: $("#ability").val(), benefit: $("#benefit").val(), name: $("#Name").val(), importance: $("#Importance").val(), estimate: $("#Estimate").val(), notes: $("#Notes").val() };
- try {
- _userStoryProvider.addUserStory(item.role, item.ability, item.benefit, item.name, item.importance, item.estimate, item.notes);
- loadUserStory();
- } catch (error) {
- alert("_userStoryProvider.addUserStory:" + error);
- }
- });
- /* 創建用戶故事表 */
- $("#btnCreateTable").click(function () { try {
- _userStoryProvider.createUserStoryTable();
- } catch (error) {
- alert("_userStoryProvider.createUserStoryTable:" + error);
- }
- });
- /* 刪除用戶故事表 */
- $("#btnDropTable").click(function () {
- try {
- _userStoryProvider.dropUserStoryTable();
- } catch (error) {
- alert("_userStoryProvider.dropUserStoryTable:" + error);
- }
- });
- });
- /* 加載用戶故事 */
- function loadUserStory() {
- try {
- _userStoryProvider.loadUserStories(function (result) {
- var _userStories = new Array();
- for (var i = 0; i < result.rows.length; i++) {
- var o = result.rows.item(i);
- var _userStory = new userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);
- _userStories.push(_userStory);
- }
- if (!_userStories) return;
- var table = document.getElementById("user_story_table");
- if (!table) return;
- var _trs = table.getElementsByTagName("tr");
- var _len = _trs.length;
- for (var i = 0; i < _len; i++) {
- table.removeChild(_trs[i]);
- }
- {
- var tr = document.createElement("tr");
- tr.setAttribute("class", "product_backlog_row header");
- {
- tr.appendChild(CreateTd("id", "id"));
- tr.appendChild(CreateTd("name", "name"));
- tr.appendChild(CreateTd("importance", "importance"));
- tr.appendChild(CreateTd("estimate", "estimate"));
- tr.appendChild(CreateTd("description", "role"));
- tr.appendChild(CreateTd("notes", "notes"));
- tr.appendChild(CreateTd("delete", "delete"));
- };
- table.appendChild(tr);
- }
- for (var i = 0; i < _userStories.length; i++) {
- CreateRow(table, _userStories[i]);
- }
- });
- } catch (error) {
- alert("_userStoryProvider.loadUserStories:" + error);
- }
- }
- function CreateRow(table, userStory) {
- if (!table) return;
- if (!userStory) return;
- {
- var tr = document.createElement("tr");
- tr.setAttribute("class", "product_backlog_row");
- {
- tr.appendChild(CreateTd("id", userStory.id));
- tr.appendChild(CreateTd("name", userStory.name));
- tr.appendChild(CreateTd("importance", userStory.importance));
- tr.appendChild(CreateTd("estimate", userStory.estimate));
- tr.appendChild(CreateTd("description", userStory.role));
- tr.appendChild(CreateTd("notes", userStory.notes));
- tr.appendChild(CreateDeleteButton("delete_button", userStory.id));
- };
- table.appendChild(tr);
- }
- }
- function CreateTd(name, value) {
- var td = document.createElement("td");
- td.setAttribute("class", "user_story " + name);
- td.innerText = value;
- return td;
- };
- function CreateDeleteButton(name, id) {
- var td = document.createElement("td");
- td.setAttribute("class", "user_story " + name);
- /* 刪除用戶故事 */
- td.innerHTML = "<a href=\"###\" title=\"delete\" onclick=\"javascript:_userStoryProvider.removeUserStory(\'" + id + "');removeRow(this);\">>>delete</a>";
- return td;
- }
- function removeRow(obj) {
- document.getElementById("user_story_table").deleteRow(obj.parentNode.parentNode.rowIndex);
- //obj.parentNode.parentNode.removeNode(true);
- }
看完代碼復習下基本功課
1,WindowDatabase接口,注意openDatabase方法
- [Supplemental, NoInterfaceObject]
- interface WindowDatabase {
- Database openDatabase(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback);};
- Window implements WindowDatabase;
- [Supplemental, NoInterfaceObject]
- interface WorkerUtilsDatabase {
- Database openDatabase(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback); DatabaseSync openDatabaseSync(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback);};
- WorkerUtils implements WorkerUtilsDatabase;
- [Callback=FunctionOnly, NoInterfaceObject]
- interface DatabaseCallback {
- void handleEvent(in Database database);
- };
2,SQLTransaction接口,關注executeSql方法
- typedef sequence<any> ObjectArray;
- interface SQLTransaction {
- void executeSql(in DOMString sqlStatement, in optional ObjectArray arguments, in optional SQLStatementCallback callback, in optional SQLStatementErrorCallback errorCallback);};
- [Callback=FunctionOnly, NoInterfaceObject]
- interface SQLStatementCallback {
- void handleEvent(in SQLTransaction transaction, in SQLResultSet resultSet);};
- [Callback=FunctionOnly, NoInterfaceObject]
- interface SQLStatementErrorCallback {
- boolean handleEvent(in SQLTransaction transaction, in SQLError error);
- };
3,最后看下SQLResultSetRowList定義
- interface SQLResultSetRowList {
- readonly attribute unsigned long length;
- getter any item(in unsigned long index);
- };
和SQLResultSet定義
- interface SQLResultSet {
- readonly attribute long insertId;
- readonly attribute long rowsAffected;
- readonly attribute SQLResultSetRowList rows;
- };
【編輯推薦】
責任編輯：陳貽新
來源：
kkun的博客
