简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
Home > Web Front-end > H5 Tutorial > body text

HTML 5离线存储之Web SQL

WBOY
Release： 2016-05-17 09:09:40
Original
1293 people have browsed it

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

有了"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>
Copy after login

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>
Copy after login

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

<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>
Copy after login

得到_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>
Copy after login

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

<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>
Copy after login

看完代码复习下基本功课

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>
Copy after login

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>
Copy after login

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>
Copy after login

和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>
Copy after login
Related labels：
admin
source：php.cn
Previous article：HTML5 or Silverlight? Next article：避免常见的六种HTML5错误用法 (2)
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
Regular expression filter for MERN stack search boxes and checkboxes I'm trying to understand how the MERN stack works together by learning by doing, and I'm f...
From 2024-04-06 14:53:12
0
1
425
Can PDF files run HTML5 and Javascript? I have a stupid idea to try and make a program that won't be blocked on any computer since...
From 2024-04-05 12:57:00
0
1
456
Prestashop installation issue: Unable to install custom module I am new to Prestashop module development. I'm trying to build Amy's first prestashop modu...
From 2024-04-03 19:24:44
0
1
402
PHP Tutorial: How to upload text and images to Linkedin using PHP? I try to upload text, images and links using LinkedinsRestAPI (https://learn.microsoft.com...
From 2024-04-03 18:42:29
0
1
420
Unable to receive information from my mySQL database despite having seen tutorials doing the exact same thing So I'm trying to make a program that reads and writes to a MySQL database, which led me to...
From 2024-04-03 14:46:04
0
1
298
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn：Public welfare online PHP training，Help PHP learners grow quickly！