HTML5 offline storage Web SQL, html5 offline websql
HTML5 Web SQL stored offline
Asynchronous, multithreading, and SQL injection are not considered in this article.
According to the WebDatabase specification, this specification is no longer maintained, because it is homogeneous (Sqlite is selected for almost all current users ),
Not to mention this, just look at 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. Add data
4. Update Data
5. Read data
6. delete data
In fact, the key point is how to get the context of an executable SQL statement,
For example, creating a table, deleting a table, and performing CRUD operations are only different from SQL statement writing. OK, it looks like "SqlHelper". Change the name to dataBaseOperator.
The executeReader and executeScalar methods are seriously homogeneous with executeNonQuery,
The code below defines our dataBaseOperator "class" and the second line
3-5 rows define the method for opening the database connection, "class method", the effect is similar to the static method in C #, direct class name. method call
Lines 6-15 define the executeNonQuery method, which means to query the database. It is the same as the executeReader method and the executeScalar method and can return the record set.
The entire dataBaseOperator is complete, which is very simple. The only thing to note is that you should select a browser that supports HTML5 when testing the following code! For example, 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.exe cuteNonQuery = function (SQL, parameters, callback ){
7 var db = this. openDatabase ();
8 db. transaction (function (trans ){
9 trans.exe cuteSql (SQL, parameters, function (trans, result ){
10 callback (result );
11}, function (trans, error ){
12 throw error. message;
13 });
14 });
15}
16 dataBaseOperator.exe cuteReader = dataBaseOperator.exe cuteNonQuery;
17 dataBaseOperator.exe cuteScalar = dataBaseOperator.exe cuteNonQuery;
With "SqlHeper", let's look at the Business Logic Layer)
The business processing class includes creating tables, deleting tables, adding new records, deleting records, and reading records. There is no write update here. In fact, the same drop is added after deletion, even if writing is required, it is not complex.
1 function userStoryProvider (){
2 this. createUserStoryTable = function (){
3 dataBaseOperator.exe cuteNonQuery ("create table tbUserStories (id integer primary key autoincrement, role, ability, benefit, name, importance, estimate, notes )");
4 };
5 this. dropUserStoryTable = function (){
6 dataBaseOperator.exe cuteNonQuery ("drop table tbUserStories ");
7 };
8 this. addUserStory = function (role, ability, benefit, name, importance, estimate, notes ){
9 dataBaseOperator.exe cuteNonQuery ("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.exe cuteNonQuery ("delete from tbUserStories WHERE id =? ", [Id], function (result ){
16 // alert ("rowsAffected:" + result. rowsAffected );
17 });
18 };
19 this. loadUserStories = function (callback ){
20 dataBaseOperator.exe cuteReader ("SELECT * FROM tbUserStories", [], function (result ){
21 callback (result );
22 });
23 // result. insertId, result. rowsAffected, result. rows
24 };
25}
CreateUserStoryTable, dropUserStoryTable, addUserStory, and removeUserStory are seriously homogeneous. If you don't talk about them, the SQL statements are different.
However, loadUserStories is different from the above four methods because it returns SQLResultSetRowList to the caller. Here it is still simple "forwarding ",
When using the page, you must first create a provider instance (using a method call similar to the class instance in C)
1 var _ userStoryProvider = new userStoryProvider ();
Then you can call the method of the instance. For example, the Code is omitted.
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 );
}
}
After getting the _ userStories array, there will be no more details. Do you want to automatically create HTML or bind it to EXT? Use your imagination... continue.
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, the application code and business-related code are pasted. No matter which one is different from the other.
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/
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 */
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/* create a user story table */
23 $ ("# btnCreateTable"). click (function (){
24 try {
25 _ userStoryProvider. createUserStoryTable ();
26} catch (error ){
27 alert ("_ userStoryProvider. createUserStoryTable:" + error );
28}
29 });
30
31/* delete a user story table */
32 $ ("# btnDropTable"). click (function (){
33 try {
34 _ userStoryProvider. dropUserStoryTable ();
35} catch (error ){
36 alert ("_ userStoryProvider. dropUserStoryTable:" + error );
37}
38 });
39 });
40
41/* load user stories */
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/* delete user stories */
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}
There is a small example, click here to download (placeholder, Put it after a small problem is fixed)
After reviewing the Code
1. WindowDatabase interface. Pay attention to the openDatabase method.
[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 interface. Follow the executeSql method.
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. Finally, let's take a look at the definition of SQLResultSetRowList.
Interface SQLResultSetRowList {
Readonly attribute unsigned long length;
Getter any item (in unsigned long index );
};
And SQLResultSet Definition
1 interface SQLResultSet {
2 readonly attribute long insertId;
3 readonly attribute long rowsAffected;
4 readonly attribute SQLResultSetRowList rows;
5 };
Class Diagram
Since the window object implements the WindowDatabase interface, you can directly call methods such as openDatabase on the window object.
WorkerUtils objects are also implemented for the WindowDatabse interface.
