本篇沒有考慮異步，多線程及SQL注入
WebDatabase 規(guī)范中說這份規(guī)范不再維護(hù)了，原因是同質(zhì)化(幾乎實(shí)現(xiàn)者都選擇了Sqlite)，且不說這些，單看在HTML5中如何實(shí)現(xiàn)離線數(shù)據(jù)的CRUD，最基本的用法(入門級別)
1，打開數(shù)據(jù)庫
2，創(chuàng)建表
3，新增數(shù)據(jù)
4，更新數(shù)據(jù)
5，讀取數(shù)據(jù)
6，刪除數(shù)據(jù)
事實(shí)上，關(guān)鍵點(diǎn)在于如何拿到一個(gè)可執(zhí)行SQL語句的上下文，像創(chuàng)建表，刪除表，CRUD操作等僅區(qū)別于SQL語句的寫法.OK，貌似 “SqlHelper”啊，換個(gè)名字，dataBaseOperator就它了executeReader，executeScalar兩個(gè)方法與 executeNonQuery嚴(yán)重同質(zhì)，下邊的代碼產(chǎn)生定義了我們的dataBaseOperator“類”，第2行、3-5行則定義打開數(shù)據(jù)庫連接方法，“類方法”，效果類似C#中的靜態(tài)方法，直接類名。
方法調(diào)用6-15行則定義executeNonQuery方法，意指查詢數(shù)據(jù)庫，與executeReader方法和executeScalar方法同質(zhì)，均可返回記錄集整個(gè) dataBaseOperator就完整了，很簡單，唯一要指出的是，測試以下代碼時(shí)請選擇一個(gè)支持HTML5的瀏覽器!如Google Chrome。
|
1 //TODO;SQL注入
2 function dataBaseOperator() {};
3 dataBaseOperator.openDatabase= function () {
4 return window.openDatabase("dataBaseUserStories","1.0","dataBase used for user stories",2 * 1024 * 1024);
5 }
6 dataBaseOperator.executeNonQuery= function (sql, parameters, callback) {
7 var db= this.openDatabase();
8 db.transaction(function (trans) {
9 trans.executeSql(sql, parameters,function (trans, result) {
10 callback(result);
11 },function (trans, error) {
12 throw error.message;
13 });
14 });
15 }
16 dataBaseOperator.executeReader= dataBaseOperator.executeNonQuery;
17 dataBaseOperator.executeScalar= dataBaseOperator.executeNonQuery;
|
有了“SqlHeper”，再看業(yè)務(wù)處理層(Business Logic Layer)業(yè)務(wù)處理類包括了創(chuàng)建表，刪除表，新增記錄，刪除記錄以及讀取記錄，這里沒有寫更新，實(shí)際上先刪后增一樣滴，即使要寫也不復(fù)雜
|
1 function userStoryProvider() {
2 this.createUserStoryTable= function () {
3 dataBaseOperator.executeNonQuery("CREATE TABLE tbUserStories(id integer primary key autoincrement,role,ability,benefit,name,importance,estimate,notes)");
4 };
5 this.dropUserStoryTable= function () {
6 dataBaseOperator.executeNonQuery("DROP TABLE tbUserStories");
7 };
8 this.addUserStory= function (role, ability, benefit, name, importance, estimate, notes) {
9 dataBaseOperator.executeNonQuery("INSERT INTO tbUserStories(role,ability,benefit,name,importance,estimate,notes) SELECT ?,?,?,?,?,?,?",
10 [role, ability, benefit, name, importance, estimate, notes],function (result) {
11 //alert("rowsAffected:" + result.rowsAffected);
12 });
13 };
14 this.removeUserStory= function (id) {
15 dataBaseOperator.executeNonQuery("DELETE FROM tbUserStories WHERE id = ?", [id],function (result) {
16 //alert("rowsAffected:" + result.rowsAffected);
17 });
18 };
19 this.loadUserStories= function (callback) {
20 dataBaseOperator.executeReader("SELECT * FROM tbUserStories", [],function (result) {
21 callback(result);
22 });
23 //result.insertId,result.rowsAffected,result.rows
24 };
25 }
|
createUserStoryTable，dropUserStoryTable，addUserStory，removeUserStory又是嚴(yán)重同質(zhì)，不說了，僅SQL語句不同而已，但loadUserStories與上述四個(gè)方法均不同，是因?yàn)樗裇QLResultSetRowList 返回給了調(diào)用者，這里仍然是簡單的“轉(zhuǎn)發(fā)”，頁面在使用的時(shí)候需要首先創(chuàng)建provider實(shí)例(使用類似C#中的類實(shí)例上的方法調(diào)用)
|
1 var _userStoryProvider= new userStoryProvider();
|
之后就可以調(diào)用該實(shí)例的方法了，僅舉個(gè)例子，具體代碼省去
|
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這個(gè)數(shù)組后，就沒有下文了，是自動創(chuàng)建HTML還是綁定到EXT，發(fā)揮想象力吧。..繼續(xù)
userStory是一個(gè)自定義的“Model” “類”
|
1 function userStory(id, name, role, ability, benefit, importance, estimate, notes) {
2 this.id= id;
3 this.name= name;
4 this.role= role;
5 this.ability= ability;
6 this.benefit= benefit;
7 this.importance= importance;
8 this.estimate= estimate;
9 this.notes= notes;
10 };
|
最后貼出應(yīng)用的代碼，業(yè)務(wù)相關(guān)的代碼，不看也罷，誰家與誰家的都不同
|
1 /*
2 http://stackoverflow.com/questions/2010892/storing-objects-in-html5-localstorage
3 http://www.w3.org/TR/webdatabase/#sqlresultset
4 http://html5doctor.com/introducing-web-sql-databases/
5 http://stackoverflow.com/questions/844885/sqlite-insert-into-with-unique-names-getting-id
6 */
7 var _userStoryProvider= new userStoryProvider();
8 $(document).ready(function () {
9 loadUserStory();
10
11 /* 添加用戶故事*/
12 $("#btnAdd").click(function () {
13 var item= { role: $("#role").val(), ability: $("#ability").val(), benefit: $("#benefit").val(), name: $("#Name").val(), importance: $("#Importance").val(), estimate: $("#Estimate").val(), notes: $("#Notes").val() };
14 try {
15 _userStoryProvider.addUserStory(item.role, item.ability, item.benefit, item.name, item.importance, item.estimate, item.notes);
16 loadUserStory();
17 }catch (error) {
18 alert("_userStoryProvider.addUserStory:" + error);
19 }
20 });
21
22 /* 創(chuàng)建用戶故事表*/
23 $("#btnCreateTable").click(function () {
24 try {
25 _userStoryProvider.createUserStoryTable();
26 }catch (error) {
27 alert("_userStoryProvider.createUserStoryTable:" + error);
28 }
29 });
30
31 /* 刪除用戶故事表*/
32 $("#btnDropTable").click(function () {
33 try {
34 _userStoryProvider.dropUserStoryTable();
35 }catch (error) {
36 alert("_userStoryProvider.dropUserStoryTable:" + error);
37 }
38 });
39 });
40
41 /* 加載用戶故事*/
42 function loadUserStory() {
43 try {
44 _userStoryProvider.loadUserStories(function (result) {
45 var _userStories= new Array();
46 for (var i= 0; i< result.rows.length; i++) {
47 var o= result.rows.item(i);
48 var _userStory= new userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);
49 _userStories.push(_userStory);
50 }
51
52 if (!_userStories)return;
53 var table= document.getElementById("user_story_table");
54 if (!table)return;
55 var _trs= table.getElementsByTagName("tr");
56 var _len= _trs.length;
57 for (var i= 0; i< _len; i++) {
58 table.removeChild(_trs[i]);
59 }
60 {
61 var tr= document.createElement("tr");
62 tr.setAttribute("class","product_backlog_row header");
63 {
64 tr.appendChild(CreateTd("id","id"));
65 tr.appendChild(CreateTd("name","name"));
66 tr.appendChild(CreateTd("importance","importance"));
67 tr.appendChild(CreateTd("estimate","estimate"));
68 tr.appendChild(CreateTd("description","role"));
69 tr.appendChild(CreateTd("notes","notes"));
70 tr.appendChild(CreateTd("delete","delete"));
71 };
72 table.appendChild(tr);
73 }
74 for (var i= 0; i< _userStories.length; i++) {
75 CreateRow(table, _userStories[i]);
76 }
77 });
78 }catch (error) {
79 alert("_userStoryProvider.loadUserStories:" + error);
80 }
81 }
82 function CreateRow(table, userStory) {
83 if (!table)return;
84 if (!userStory)return;
85 {
86 var tr= document.createElement("tr");
87 tr.setAttribute("class","product_backlog_row");
88 {
89 tr.appendChild(CreateTd("id", userStory.id));
90 tr.appendChild(CreateTd("name", userStory.name));
91 tr.appendChild(CreateTd("importance", userStory.importance));
92 tr.appendChild(CreateTd("estimate", userStory.estimate));
93 tr.appendChild(CreateTd("description", userStory.role));
94 tr.appendChild(CreateTd("notes", userStory.notes));
95 tr.appendChild(CreateDeleteButton("delete_button", userStory.id));
96 };
97 table.appendChild(tr);
98 }
99 }
100 function CreateTd(name, value) {
101 var td= document.createElement("td");
102 td.setAttribute("class","user_story" + name);
103 td.innerText= value;
104 return td;
105 };
106 function CreateDeleteButton(name, id) {
107 var td= document.createElement("td");
108 td.setAttribute("class","user_story" + name);
109 /* 刪除用戶故事*/
110 td.innerHTML= "<a href="###" title="delete" onclick="javascript:_userStoryProvider.removeUserStory('" + id+ "');removeRow(this);">>>delete</a>";
111 return td;
112 }
113 function removeRow(obj) {
114 document.getElementById("user_story_table").deleteRow(obj.parentNode.parentNode.rowIndex);
115 //obj.parentNode.parentNode.removeNode(true);
116 }
|
有一個(gè)小例子，點(diǎn)這里下載(占位，有點(diǎn)小毛病改好就放)
看完代碼復(fù)習(xí)下基本功課
1，WindowDatabase接口，注意openDatabase方法
|
[Supplemental, NoInterfaceObject]
interface WindowDatabase {
Database openDatabase(in DOMString name,in DOMString version,in DOMString displayName,in unsignedlong estimatedSize,in optional DatabaseCallback creationCallback);
};
Window implements WindowDatabase;
[Supplemental, NoInterfaceObject]
interface WorkerUtilsDatabase {
Database openDatabase(in DOMString name,in DOMString version,in DOMString displayName,in unsignedlong estimatedSize,in optional DatabaseCallback creationCallback);
DatabaseSync openDatabaseSync(in DOMString name,in DOMString version,in DOMString displayName,in unsignedlong estimatedSize,in optional DatabaseCallback creationCallback);
};
WorkerUtils implements WorkerUtilsDatabase;
[Callback=FunctionOnly, NoInterfaceObject]
interface DatabaseCallback {
void handleEvent(in Database database);
};
|
2，SQLTransaction接口，關(guān)注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 unsignedlong length;
getter any item(in unsignedlong index);
};
|
和SQLResultSet定義
|
1 interface SQLResultSet {
2 readonly attributelong insertId;
3 readonly attributelong rowsAffected;
4 readonly attribute SQLResultSetRowList rows;
5 };
|
【網(wǎng)站聲明】本站除付費(fèi)源碼經(jīng)過測試外，其他素材未做測試，不保證完整性，網(wǎng)站上部分源碼僅限學(xué)習(xí)交流，請勿用于商業(yè)用途。如損害你的權(quán)益請聯(lián)系客服QQ：2655101040 給予處理，謝謝支持。
