HTML 5离线存储之Web SQL

本篇没有考虑异步,多线程及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

<ol class="dp-xml">
<li class="alt"><span><span>//TODO;SQL注入   </span></span></li>
<li><span>  function dataBaseOperator() {};   </span></li>
<li class="alt">
<span> </span><span class="attribute">dataBaseOperator.openDatabase</span><span> = </span><span class="attribute-value">function</span><span> () {   </span>
</li>
<li><span>     return window.openDatabase("dataBaseUserStories", "1.0", "dataBase used for user stories", 2 * 1024 * 1024);   </span></li>
<li class="alt"><span> }   </span></li>
<li>
<span> </span><span class="attribute">dataBaseOperator.executeNonQuery</span><span> = </span><span class="attribute-value">function</span><span> (sql, parameters, callback) {   </span>
</li>
<li class="alt">
<span>     var </span><span class="attribute">db</span><span> = </span><span class="attribute-value">this</span><span>.openDatabase();   </span>
</li>
<li><span>     db.transaction(function (trans) {   </span></li>
<li class="alt"><span>         trans.executeSql(sql, parameters, function (trans, result) {  </span></li>
<li><span>             callback(result);  </span></li>
<li class="alt"><span>         }, function (trans, error) {  </span></li>
<li><span>             throw error.message;  </span></li>
<li class="alt"><span>         });  </span></li>
<li><span>     });  </span></li>
<li class="alt"><span> }  </span></li>
<li>
<span> </span><span class="attribute-value">dataBaseOperator</span><span class="attribute">dataBaseOperator.executeReader</span><span> = dataBaseOperator.executeNonQuery;  </span>
</li>
<li class="alt">
<span> </span><span class="attribute-value">dataBaseOperator</span><span class="attribute">dataBaseOperator.executeScalar</span><span> = dataBaseOperator.executeNonQuery; </span>
</li>
</ol>

有了"SqlHeper",再看业务处理层(Business Logic Layer)

业务处理类包括了创建表,删除表,新增记录,删除记录以及读取记录,这里没有写更新,实际上先删后增一样滴,即使要写也不复杂

<ol class="dp-xml">
<li class="alt"><span><span>function userStoryProvider() {   </span></span></li>
<li>
<span>     </span><span class="attribute">this.createUserStoryTable</span><span> = </span><span class="attribute-value">function</span><span> () {   </span>
</li>
<li class="alt"><span>         dataBaseOperator.executeNonQuery("CREATE TABLE tbUserStories(id integer primary key autoincrement,role,ability,benefit,name,importance,estimate,notes)");   </span></li>
<li><span>     };   </span></li>
<li class="alt">
<span>     </span><span class="attribute">this.dropUserStoryTable</span><span> = </span><span class="attribute-value">function</span><span> () {   </span>
</li>
<li><span>         dataBaseOperator.executeNonQuery("DROP TABLE tbUserStories");   </span></li>
<li class="alt"><span>     };   </span></li>
<li>
<span>     </span><span class="attribute">this.addUserStory</span><span> = </span><span class="attribute-value">function</span><span> (role, ability, benefit, name, importance, estimate, notes) {   </span>
</li>
<li class="alt"><span>         dataBaseOperator.executeNonQuery("INSERT INTO tbUserStories(role,ability,benefit,name,importance,estimate,notes) SELECT ?,?,?,?,?,?,?",  </span></li>
<li><span>              [role, ability, benefit, name, importance, estimate, notes], function (result) {  </span></li>
<li class="alt"><span>                  //alert("rowsAffected:" + result.rowsAffected);  </span></li>
<li><span>               });  </span></li>
<li class="alt"><span>     };  </span></li>
<li>
<span>     </span><span class="attribute">this.removeUserStory</span><span> = </span><span class="attribute-value">function</span><span> (id) {  </span>
</li>
<li class="alt">
<span>         dataBaseOperator.executeNonQuery("DELETE FROM tbUserStories WHERE </span><span class="attribute">id</span><span> = ?", [id], function (result) {  </span>
</li>
<li><span>             //alert("rowsAffected:" + result.rowsAffected);  </span></li>
<li class="alt"><span>          });  </span></li>
<li><span>     };  </span></li>
<li class="alt">
<span>     </span><span class="attribute">this.loadUserStories</span><span> = </span><span class="attribute-value">function</span><span> (callback) {  </span>
</li>
<li><span>         dataBaseOperator.executeReader("SELECT * FROM tbUserStories", [], function (result) {  </span></li>
<li class="alt"><span>             callback(result);  </span></li>
<li><span>         });  </span></li>
<li class="alt"><span>         //result.insertId,result.rowsAffected,result.rows24      };  </span></li>
<li><span> } </span></li>
</ol>

createUserStoryTable,dropUserStoryTable,addUserStory,removeUserStory又是严重同质,不说了,仅SQL语句不同而已

但loadUserStories与上述四个方法均不同,是因为它把SQLResultSetRowList返回给了调用者,这里仍然是简单的"转发",页面在使用的时候需要首先创建provider实例(使用类似C#中的类实例上的方法调用)

<ol class="dp-xml"><li class="alt"><span><span>var </span><span class="attribute">_userStoryProvider</span><span> = </span><span class="attribute-value">new</span><span> userStoryProvider(); </span></span></li></ol>

之后就可以调用该实例的方法了,仅举个例子,具体代码省去

<ol class="dp-xml">
<li class="alt"><span><span>function loadUserStory() {      </span></span></li>
<li><span>try {          </span></li>
<li class="alt"><span>_userStoryProvider.loadUserStories(function (result) {             </span></li>
<li>
<span> var </span><span class="attribute">_userStories</span><span> = </span><span class="attribute-value">new</span><span> Array();              </span>
</li>
<li class="alt">
<span>for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">result.rows.length</span><span>; i++) {                 </span></span>
</li>
<li>
<span> var </span><span class="attribute">o</span><span> = </span><span class="attribute-value">result</span><span>.rows.item(i);                </span>
</li>
<li class="alt">
<span> var </span><span class="attribute">_userStory</span><span> = </span><span class="attribute-value">new</span><span> userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);                 </span>
</li>
<li><span> _userStories.push(_userStory);           </span></li>
<li class="alt"><span>   }//...      </span></li>
<li><span>} catch (error) {       </span></li>
<li class="alt"><span>   alert("_userStoryProvider.loadUserStories:" + error);      </span></li>
<li><span>}} </span></li>
</ol>

得到_userStories这个数组后,就没有下文了,是自动创建HTML还是绑定到EXT,发挥想象力吧...继续

userStory是一个自定义的"Model" "类"·

<ol class="dp-xml">
<li class="alt"><span><span>function userStory(id, name, role, ability, benefit, importance, estimate, notes) {   </span></span></li>
<li>
<span>     </span><span class="attribute">this.id</span><span> = id;   </span>
</li>
<li class="alt">
<span>     </span><span class="attribute">this.name</span><span> = name;   </span>
</li>
<li>
<span>     </span><span class="attribute">this.role</span><span> = role;   </span>
</li>
<li class="alt">
<span>     </span><span class="attribute">this.ability</span><span> = ability;   </span>
</li>
<li>
<span>     </span><span class="attribute">this.benefit</span><span> = benefit;   </span>
</li>
<li class="alt">
<span>     </span><span class="attribute">this.importance</span><span> = importance;   </span>
</li>
<li>
<span>     </span><span class="attribute">this.estimate</span><span> = estimate;   </span>
</li>
<li class="alt">
<span>     </span><span class="attribute">this.notes</span><span> = notes;  </span>
</li>
<li><span> }; </span></li>
</ol>

最后贴出应用的代码,业务相关的代码,不看也罢,谁家与谁家的都不同

<ol class="dp-xml">
<li class="alt"><span><span>/*    </span></span></li>
<li><span>     http://stackoverflow.com/questions/2010892/storing-objects-in-html5-localstorage    </span></li>
<li class="alt"><span>     http://www.w3.org/TR/webdatabase/#sqlresultset    </span></li>
<li><span>     http://html5doctor.com/introducing-web-sql-databases/    </span></li>
<li class="alt"><span>     http://stackoverflow.com/questions/844885/sqlite-insert-into-with-unique-names-getting-id    </span></li>
<li><span>  */    </span></li>
<li class="alt">
<span> var </span><span class="attribute">_userStoryProvider</span><span> = </span><span class="attribute-value">new</span><span> userStoryProvider();    </span>
</li>
<li><span> $(document).ready(function () {    </span></li>
<li class="alt"><span>     loadUserStory();   </span></li>
<li><span>    </span></li>
<li class="alt"><span>     /* 添加用户故事 */   </span></li>
<li><span>     $("#btnAdd").click(function () {   </span></li>
<li class="alt">
<span>         var </span><span class="attribute">item</span><span> = { role: $("#role").val(), ability: $("#ability").val(), benefit: $("#benefit").val(), name: $("#Name").val(), importance: $("#Importance").val(), estimate: $("#Estimate").val(), notes: $("#Notes").val() };   </span>
</li>
<li><span>         try {   </span></li>
<li class="alt"><span>             _userStoryProvider.addUserStory(item.role, item.ability, item.benefit, item.name, item.importance, item.estimate, item.notes);   </span></li>
<li><span>             loadUserStory();   </span></li>
<li class="alt"><span>         } catch (error) {   </span></li>
<li><span>             alert("_userStoryProvider.addUserStory:" + error);   </span></li>
<li class="alt"><span>         }   </span></li>
<li><span>     });   </span></li>
<li class="alt"><span>    </span></li>
<li><span>     /* 创建用户故事表 */   </span></li>
<li class="alt"><span>     $("#btnCreateTable").click(function () {          try {   </span></li>
<li><span>             _userStoryProvider.createUserStoryTable();   </span></li>
<li class="alt"><span>         } catch (error) {   </span></li>
<li><span>             alert("_userStoryProvider.createUserStoryTable:" + error);   </span></li>
<li class="alt"><span>         }   </span></li>
<li><span>     });   </span></li>
<li class="alt"><span>    </span></li>
<li><span>     /* 删除用户故事表 */   </span></li>
<li class="alt"><span>     $("#btnDropTable").click(function () {   </span></li>
<li><span>         try {   </span></li>
<li class="alt"><span>             _userStoryProvider.dropUserStoryTable();   </span></li>
<li><span>         } catch (error) {   </span></li>
<li class="alt"><span>             alert("_userStoryProvider.dropUserStoryTable:" + error);   </span></li>
<li><span>         }   </span></li>
<li class="alt"><span>     });   </span></li>
<li><span> });   </span></li>
<li class="alt"><span>    </span></li>
<li><span> /* 加载用户故事 */   </span></li>
<li class="alt"><span> function loadUserStory() {   </span></li>
<li><span>     try {   </span></li>
<li class="alt"><span>         _userStoryProvider.loadUserStories(function (result) {   </span></li>
<li>
<span>             var </span><span class="attribute">_userStories</span><span> = </span><span class="attribute-value">new</span><span> Array();   </span>
</li>
<li class="alt">
<span>             for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">result.rows.length</span><span>; i++) {   </span></span>
</li>
<li>
<span>                 var </span><span class="attribute">o</span><span> = </span><span class="attribute-value">result</span><span>.rows.item(i);   </span>
</li>
<li class="alt">
<span>                 var </span><span class="attribute">_userStory</span><span> = </span><span class="attribute-value">new</span><span> userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);   </span>
</li>
<li><span>                 _userStories.push(_userStory);   </span></li>
<li class="alt"><span>             }   </span></li>
<li><span>    </span></li>
<li class="alt"><span>             if (!_userStories) return;   </span></li>
<li>
<span>             var </span><span class="attribute">table</span><span> = </span><span class="attribute-value">document</span><span>.getElementById("user_story_table");   </span>
</li>
<li class="alt"><span>             if (!table) return;   </span></li>
<li>
<span>             var </span><span class="attribute">_trs</span><span> = </span><span class="attribute-value">table</span><span>.getElementsByTagName("tr");   </span>
</li>
<li class="alt">
<span>             var </span><span class="attribute">_len</span><span> = </span><span class="attribute-value">_trs</span><span>.length;   </span>
</li>
<li>
<span>             for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">_len</span><span>; i++) {   </span></span>
</li>
<li class="alt"><span>                 table.removeChild(_trs[i]);   </span></li>
<li><span>             }   </span></li>
<li class="alt"><span>             {   </span></li>
<li>
<span>                 var </span><span class="attribute">tr</span><span> = </span><span class="attribute-value">document</span><span>.createElement("tr");   </span>
</li>
<li class="alt"><span>                 tr.setAttribute("class", "product_backlog_row header");   </span></li>
<li><span>                 {   </span></li>
<li class="alt"><span>                     tr.appendChild(CreateTd("id", "id"));   </span></li>
<li><span>                     tr.appendChild(CreateTd("name", "name"));   </span></li>
<li class="alt"><span>                     tr.appendChild(CreateTd("importance", "importance"));   </span></li>
<li><span>                     tr.appendChild(CreateTd("estimate", "estimate"));   </span></li>
<li class="alt"><span>                     tr.appendChild(CreateTd("description", "role"));   </span></li>
<li><span>                     tr.appendChild(CreateTd("notes", "notes"));   </span></li>
<li class="alt"><span>                     tr.appendChild(CreateTd("delete", "delete"));   </span></li>
<li><span>                 };   </span></li>
<li class="alt"><span>                 table.appendChild(tr);   </span></li>
<li><span>             }   </span></li>
<li class="alt">
<span>             for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">_userStories.length</span><span>; i++) {   </span></span>
</li>
<li><span>                 CreateRow(table, _userStories[i]);   </span></li>
<li class="alt"><span>             }   </span></li>
<li><span>         });   </span></li>
<li class="alt"><span>     } catch (error) {   </span></li>
<li><span>         alert("_userStoryProvider.loadUserStories:" + error);   </span></li>
<li class="alt"><span>     }   </span></li>
<li><span> }   </span></li>
<li class="alt"><span> function CreateRow(table, userStory) {   </span></li>
<li><span>     if (!table) return;  </span></li>
<li class="alt"><span>     if (!userStory) return;   </span></li>
<li><span>     {   </span></li>
<li class="alt">
<span>         var </span><span class="attribute">tr</span><span> = </span><span class="attribute-value">document</span><span>.createElement("tr");   </span>
</li>
<li><span>         tr.setAttribute("class", "product_backlog_row");   </span></li>
<li class="alt"><span>         {   </span></li>
<li><span>             tr.appendChild(CreateTd("id", userStory.id));   </span></li>
<li class="alt"><span>             tr.appendChild(CreateTd("name", userStory.name));   </span></li>
<li><span>             tr.appendChild(CreateTd("importance", userStory.importance));   </span></li>
<li class="alt"><span>             tr.appendChild(CreateTd("estimate", userStory.estimate));   </span></li>
<li><span>             tr.appendChild(CreateTd("description", userStory.role));   </span></li>
<li class="alt"><span>             tr.appendChild(CreateTd("notes", userStory.notes));   </span></li>
<li><span>             tr.appendChild(CreateDeleteButton("delete_button", userStory.id));   </span></li>
<li class="alt"><span>         };   </span></li>
<li><span>         table.appendChild(tr);   </span></li>
<li class="alt"><span>     }   </span></li>
<li><span> }  </span></li>
<li class="alt"><span> function CreateTd(name, value) {  </span></li>
<li>
<span>     var </span><span class="attribute">td</span><span> = </span><span class="attribute-value">document</span><span>.createElement("td");  </span>
</li>
<li class="alt"><span>     td.setAttribute("class", "user_story " + name);  </span></li>
<li>
<span>     </span><span class="attribute">td.innerText</span><span> = </span><span class="attribute-value">value</span><span>;  </span>
</li>
<li class="alt"><span>     return td;  </span></li>
<li><span> };  </span></li>
<li class="alt"><span> function CreateDeleteButton(name, id) {  </span></li>
<li>
<span>     var </span><span class="attribute">td</span><span> = </span><span class="attribute-value">document</span><span>.createElement("td");  </span>
</li>
<li class="alt"><span>     td.setAttribute("class", "user_story " + name);  </span></li>
<li><span>     /* 删除用户故事 */  </span></li>
<li class="alt">
<span>     </span><span class="attribute">td.innerHTML</span><span> = </span><span class="attribute-value">"<a><span>###\" </span><span class="attribute">title</span><span>=\"delete\" </span><span class="attribute">onclick</span><span>=\"javascript:_userStoryProvider.removeUserStory(\'" + id + "');removeRow(this);\"</span><span class="tag">></span><span class="tag">></span><span class="tag">></span><span>delete</span><span class="tag"></span><span class="tag-name">a</span><span class="tag">></span><span>";  </span></a></span>
</li>
<li><span>     return td;  </span></li>
<li class="alt"><span> }  </span></li>
<li><span> function removeRow(obj) {  </span></li>
<li class="alt"><span>     document.getElementById("user_story_table").deleteRow(obj.parentNode.parentNode.rowIndex);  </span></li>
<li><span>     //obj.parentNode.parentNode.removeNode(true);  </span></li>
<li class="alt"><span> }   </span></li>
</ol>

看完代码复习下基本功课

1,WindowDatabase接口,注意openDatabase方法

<ol class="dp-xml">
<li class="alt"><span><span>[Supplemental, NoInterfaceObject]  </span></span></li>
<li><span>interface WindowDatabase {   </span></li>
<li class="alt"><span> Database openDatabase(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback);};  </span></li>
<li><span>Window implements WindowDatabase;  </span></li>
<li class="alt"><span>[Supplemental, NoInterfaceObject]  </span></li>
<li><span>interface WorkerUtilsDatabase {   </span></li>
<li class="alt"><span> 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);};  </span></li>
<li><span>WorkerUtils implements WorkerUtilsDatabase;  </span></li>
<li class="alt">
<span>[</span><span class="attribute">Callback</span><span>=</span><span class="attribute-value">FunctionOnly</span><span>, NoInterfaceObject]  </span>
</li>
<li><span>interface DatabaseCallback {    </span></li>
<li class="alt"><span>void handleEvent(in Database database);  </span></li>
<li><span>}; </span></li>
</ol>

2,SQLTransaction接口,关注executeSql方法

<ol class="dp-xml">
<li class="alt"><span><span>typedef sequence</span><span class="tag"><span class="tag-name">any</span><span class="tag">></span><span> ObjectArray;  </span></span></span></li>
<li><span>interface SQLTransaction {   </span></li>
<li class="alt"><span> void executeSql(in DOMString sqlStatement, in optional ObjectArray arguments, in optional SQLStatementCallback callback, in optional SQLStatementErrorCallback errorCallback);};  </span></li>
<li>
<span>[</span><span class="attribute">Callback</span><span>=</span><span class="attribute-value">FunctionOnly</span><span>, NoInterfaceObject]  </span>
</li>
<li class="alt"><span>interface SQLStatementCallback {  </span></li>
<li><span>  void handleEvent(in SQLTransaction transaction, in SQLResultSet resultSet);};  </span></li>
<li class="alt">
<span>[</span><span class="attribute">Callback</span><span>=</span><span class="attribute-value">FunctionOnly</span><span>, NoInterfaceObject]  </span>
</li>
<li><span>interface SQLStatementErrorCallback {   </span></li>
<li class="alt"><span> boolean handleEvent(in SQLTransaction transaction, in SQLError error);  </span></li>
<li><span>}; </span></li>
</ol>

3,最后看下SQLResultSetRowList定义

<ol class="dp-xml">
<li class="alt"><span><span>interface SQLResultSetRowList {  </span></span></li>
<li><span>  readonly attribute unsigned long length;    </span></li>
<li class="alt"><span>getter any item(in unsigned long index);  </span></li>
<li><span>}; </span></li>
</ol>

和SQLResultSet定义

<ol class="dp-xml">
<li class="alt"><span><span>interface SQLResultSet {  </span></span></li>
<li><span>   readonly attribute long insertId;  </span></li>
<li class="alt"><span>   readonly attribute long rowsAffected;  </span></li>
<li><span>   readonly attribute SQLResultSetRowList rows;  </span></li>
<li class="alt"><span> }; <br></span></li>
</ol>