HTML 5离线存储之Web SQL
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB원래의
- 2016-05-17 09:09:401399검색
本篇没有考虑异步,多线程及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>
성명：
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
이전 기사：HTML5 or Silverlight?다음 기사：避免常见的六种HTML5错误用法 (2)