HTML5 离线存储之Web SQL

王朝学院·作者佚名  2016-08-09
窄屏简体版  字體: 小  |  中  |  大  |  超大  

本篇没有考虑异步,多线程及SQL注入

WebDatabase 规范中说这份规范不再维护了,原因是同质化(几乎实现者都选择了Sqlite),且不说这些,单看在HTML5中如何实现离线数据的CRUD,最基本的用法(入门级别)

1,打开数据库

2,创建表

3,新增数据

4,更新数据

5,读取数据

6,删除数据

事实上,关键点在于如何拿到一个可执行SQL语句的上下文,像创建表,删除表,CRUD操作等仅区别于SQL语句的写法.OK,貌似 “SqlHelper”啊,换个名字,dataBaSEOperator就它了executeReader,executeScalar两个方法与 executeNonQuery严重同质,下边的代码产生定义了我们的dataBaseOperator“类”,第2行、3-5行则定义打开数据库连接方法,“类方法”,效果类似C#中的静态方法,直接类名。

方法调用6-15行则定义executeNonQuery方法,意指查询数据库,与executeReader方法和executeScalar方法同质,均可返回记录集整个 dataBaseOperator就完整了,很简单,唯一要指出的是,测试以下代码时请选择一个支持HTML5的浏览器!如GoogleChrome。

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”,再看业务处理层(Business Logic Layer)业务处理类包括了创建表,删除表,新增记录,删除记录以及读取记录,这里没有写更新,实际上先删后增一样滴,即使要写也不复杂

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又是严重同质,不说了,仅SQL语句不同而已,但loadUserStories与上述四个方法均不同,是因为它把SQLResultSetRowList 返回给了调用者,这里仍然是简单的“转发”,页面在使用的时候需要首先创建provider实例(使用类似C#中的类实例上的方法调用)

1 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” “类”

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 };

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

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 /* 创建用户故事表*/

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 }

有一个小例子,点这里下载(占位,有点小毛病改好就放)

看完代码复习下基本功课

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接口,关注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 };

 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
© 2005- 王朝網路 版權所有 導航