HTML5 Web SQL for offline storage
This article does not consider asynchronous, multithreaded, and SQL injection
The WEBDATABASE specification says the specification is no longer maintained, because homogenization (almost all of them have chosen SQLite),
And don't say these, see how to implement the crud of offline data in HTML5, the most basic usage (entry level)
1. Open the Database
2, create a table
3, new data
4, Update data
5, reading data
6. Delete data
In fact, the key point is how to get the context of an executable SQL statement,
Like creating tables, deleting tables, crud operations, and so on are only different from the way SQL statements are written. OK, it seems "SqlHelper" Ah, change a name, databaseoperator it.
Executereader,executescalar two methods and ExecuteNonQuery serious homogeneity,
The code below produces a definition of our Databaseoperator "class", and the second line
3-5 rows define the Open database connection method, the "class method", the effect is similar to a static method in C #, the direct class name. Method invocation
6-15 rows Define the ExecuteNonQuery method, which means querying the database, and the ExecuteReader method and the ExecuteScalar method can return the recordset
The whole databaseoperator is complete, very simple, the only thing to point out is that when testing the following code, please select a browser that supports HTML5! Google Chrome
1//todo; SQL injection
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) {
Ten callback (result);
One}, function (trans, error) {
A throw error.message;
13});
14});
15}
Databaseoperator.executereader = Databaseoperator.executenonquery;
Databaseoperator.executescalar = Databaseoperator.executenonquery;
With the "Sqlheper", look at the business process layer (Logic layer)
Business processing classes include creating tables, deleting tables, adding records, deleting records, and reading Records, where there is no update, actually deleting and adding the same drops, even if it is not complicated to write
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?,?,?,?,?,?,? ",
[Role, ability, benefit, name, importance, estimate, notes], function (result) {
//alert ("rowsaffected:" + result.rowsaffected);
12});
13};
This.removeuserstory = function (ID) {
Databaseoperator.executenonquery ("DELETE from tbuserstories WHERE id =?", [ID], function (result) {
//alert ("rowsaffected:" + result.rowsaffected);
17});
18};
This.loaduserstories = function (callback) {
Databaseoperator.executereader ("SELECT * from Tbuserstories", [], function (result) {
Callback (result);
22});
//result.insertid,result.rowsaffected,result.rows
24};
25}
Createuserstorytable,dropuserstorytable,adduserstory,removeuserstory is a serious homogeneity, not to mention, only the SQL statements are different
But loaduserstories differs from the four methods above because it returns sqlresultsetrowlist to the caller, which is still a simple "forward",
The page needs to create the provider instance first (using a method call on a class instance similar to C #)
1 var _userstoryprovider = new Userstoryprovider ();
You can then invoke the method of the instance, and for example, the code eliminates
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);
}
}
Get _userstories This array, there is no following, is automatically created HTML or bound to ext, play the imagination ... Go on
Userstory is a custom "Model" "Class"
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};
Finally put out the application code, business-related code, not to see, who home and whose home is different
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/* Add User Stories */
$ ("#btnAdd"). Click (function () {
The 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);
19}
20});
21st
22/* Create User Story Table */
$ ("#btnCreateTable"). Click (function () {
try {
_userstoryprovider.createuserstorytable ();
(Error) {
Alert ("_userstoryprovider.createuserstorytable:" + error);
28}
29});
30
31/* Delete User Story table */
$ ("#btnDropTable"). Click (function () {
try {
_userstoryprovider.dropuserstorytable ();
' catch (Error) {
Alert ("_userstoryprovider.dropuserstorytable:" + error);
37}
38});
39});
40
41/* Load User Stories */
Loaduserstory function () {
try {
_userstoryprovider.loaduserstories (function (result) {
var _userstories = new Array ();
(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);
50}
51
if (!_userstories) return;
var table = document.getElementById ("user_story_table");
if (!table) return;
The var _trs = Table.getelementsbytagname ("tr");
_len var = _trs.length;
(var i = 0; i < _len; i++) {
Table.removechild (_trs[i]);
59}
60 {
The var tr = document.createelement ("tr");
Tr.setattribute ("Class", "Product_backlog_row header");
63 {
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"));
71};
Table.appendchild (TR);
73}
(var i = 0; i < _userstories.length; i++) {
CreateRow (table, _userstories[i]);
76}
77});
(Error) {
+ Alert ("_userstoryprovider.loaduserstories:" + error);
80}
81}
CreateRow function (table, userstory) {
if (!table) return;
if (!userstory) return;
85 {
The var tr = document.createelement ("tr");
Tr.setattribute ("Class", "Product_backlog_row");
88 {
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));
94 Tr.appendchild (CREATETD ("notes", userstory.notes));
Tr.appendchild (Createdeletebutton ("Delete_button", userstory.id));
96};
Table.appendchild (TR);
98}
99}
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/* Delete User Stories */
td.innerhtml = "<a href=\" ###\ "title=\" delete\ "onclick=\" javascript:_userstoryprovider.removeuserstory (\ ' "+ ID + "'); Removerow (this); \" >>>delete</a> ";
111 return TD;
112}
113 function Removerow (obj) {
document.getElementById ("User_story_table"). DeleteRow (Obj.parentNode.parentNode.rowIndex);
//obj.parentnode.parentnode.removenode (TRUE);
116}
There is a small example, click here to download (placeholder, a little bit of trouble to change the good put)
After reading the code review the basic course
1,windowdatabase interface, note the OpenDatabase method
[Supplemental, Nointerfaceobject]
Interface Windowdatabase {
Database OpenDatabase (in Domstring name, in domstring version, in Domstring DisplayName, in unsigned long estimatedsize, I N 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, I N optional databasecallback creationcallback);
Databasesync Opendatabasesync (in Domstring name, in domstring version, in Domstring DisplayName, in unsigned long estimate Dsize, in optional databasecallback creationcallback);
};
Workerutils implements Workerutilsdatabase;
[Callback=functiononly, Nointerfaceobject]
Interface Databasecallback {
void Handleevent (in database);
};
2,sqltransaction interface, focus on ExecuteSQL method
typedef sequence<any> Objectarray;
Interface SqlTransaction {
void ExecuteSQL (in domstring sqlstatement, in optional objectarray arguments, in optional sqlstatementcallback callback, I N 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, last look at the sqlresultsetrowlist definition
Interface Sqlresultsetrowlist {
ReadOnly attribute unsigned long length;
Getter any item (in unsigned long index);
};
and SqlResultSet definitions
1 interface SqlResultSet {
2 readonly attribute long Insertid;
3 readonly attribute long rowsaffected;
4 readonly attribute sqlresultsetrowlist rows;
5};
Class diagram
Because the Window object implements the Windowdatabase interface, methods such as OpenDatabase can be called directly on the Window object.
There are also workerutils objects that implement the Windowdatabse interface.
HTML5 offline storage of Web SQL
