点击下载
本文文档

当前位置：首页 - 科技 - 知识百科 - 正文

HTML5离线存储之WebSQL

来源：懂视网责编：小采时间：2020-11-27 15:16:11

HTML5离线存储之WebSQL

HTML5离线存储之WebSQL:本篇没有考虑异步,多线程及SQL注入 WebDatabase 规范中说这份规范不再维护了,原因是同质化(几乎实现者都选择了Sqlite), 且不说这些,单看在HTML5中如何实现离线数据的CRUD,最基本的用法(入门级别) 1,打开数据库 2,创建表 3,新增数据 4,更新数据 5,

推荐度：

点击下载本文 文档为doc格式

导读HTML5离线存储之WebSQL:本篇没有考虑异步,多线程及SQL注入 WebDatabase 规范中说这份规范不再维护了,原因是同质化(几乎实现者都选择了Sqlite), 且不说这些,单看在HTML5中如何实现离线数据的CRUD,最基本的用法(入门级别) 1,打开数据库 2,创建表 3,新增数据 4,更新数据 5,

本篇没有考虑异步,多线程及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 = "###\" title=\"delete\" onclick=\"javascript:_userStoryProvider.removeUserStory(\'" + id + "');removeRow(this);\">>>delete"; 
 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 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; 
 };

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

HTML5离线存储之WebSQL

推荐度：

点击下载本文 文档为doc格式

标签：存储 sql html5

热门焦点

HTML5离线存储之WebSQL

HTML5离线存储之WebSQL

HTML5离线存储之WebSQL

最新推荐

猜你喜欢

热门推荐