当前位置：Gxlcms > html代码 > HTML5离线存储之WebSQL

HTML5离线存储之WebSQL

时间：2021-07-01 10:21:17 帮助过：3人阅读

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

  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.  dataBaseOperatordataBaseOperator.executeReader = dataBaseOperator.executeNonQuery;  
  17.  dataBaseOperatordataBaseOperator.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.rows24      };  
  24.  }

createUserStoryTable,dropUserStoryTable,addUserStory,removeUserStory又是严重同质,不说了,仅SQL语句不同而已

但loadUserStories与上述四个方法均不同,是因为它把SQLResultSetRowList返回给了调用者,这里仍然是简单的"转发",页面在使用的时候需要首先创建provider实例(使用类似C#中的类实例上的方法调用)

  1. var _userStoryProvider = new userStoryProvider();

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

  1. function loadUserStory() {      
  2. try {          
  3. _userStoryProvider.loadUserStories(function (result) {             
  4.  var _userStories = new Array();              
  5. for (var i = 0; i < result.rows.length; i++) {                 
  6.  var o = result.rows.item(i);                
  7.  var _userStory = new userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);                 
  8.  _userStories.push(_userStory);           
  9.    }//...      
  10. } catch (error) {       
  11.    alert("_userStoryProvider.loadUserStories:" + error);      
  12. }}

得到_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 () {          try {   
  24.              _userStoryProvider.createUserStoryTable();   
  25.          } catch (error) {   
  26.              alert("_userStoryProvider.createUserStoryTable:" + error);   
  27.          }   
  28.      });   
  29.     
  30.      /* 删除用户故事表 */   
  31.      $("#btnDropTable").click(function () {   
  32.          try {   
  33.              _userStoryProvider.dropUserStoryTable();   
  34.          } catch (error) {   
  35.              alert("_userStoryProvider.dropUserStoryTable:" + error);   
  36.          }   
  37.      });   
  38.  });   
  39.     
  40.  /* 加载用户故事 */   
  41.  function loadUserStory() {   
  42.      try {   
  43.          _userStoryProvider.loadUserStories(function (result) {   
  44.              var _userStories = new Array();   
  45.              for (var i = 0; i < result.rows.length; i++) {   
  46.                  var o = result.rows.item(i);   
  47.                  var _userStory = new userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes);   
  48.                  _userStories.push(_userStory);   
  49.              }   
  50.     
  51.              if (!_userStories) return;   
  52.              var table = document.getElementById("user_story_table");   
  53.              if (!table) return;   
  54.              var _trs = table.getElementsByTagName("tr");   
  55.              var _len = _trs.length;   
  56.              for (var i = 0; i < _len; i++) {   
  57.                  table.removeChild(_trs[i]);   
  58.              }   
  59.              {   
  60.                  var tr = document.createElement("tr");   
  61.                  tr.setAttribute("class", "product_backlog_row header");   
  62.                  {   
  63.                      tr.appendChild(CreateTd("id", "id"));   
  64.                      tr.appendChild(CreateTd("name", "name"));   
  65.                      tr.appendChild(CreateTd("importance", "importance"));   
  66.                      tr.appendChild(CreateTd("estimate", "estimate"));   
  67.                      tr.appendChild(CreateTd("description", "role"));   
  68.                      tr.appendChild(CreateTd("notes", "notes"));   
  69.                      tr.appendChild(CreateTd("delete", "delete"));   
  70.                  };   
  71.                  table.appendChild(tr);   
  72.              }   
  73.              for (var i = 0; i < _userStories.length; i++) {   
  74.                  CreateRow(table, _userStories[i]);   
  75.              }   
  76.          });   
  77.      } catch (error) {   
  78.          alert("_userStoryProvider.loadUserStories:" + error);   
  79.      }   
  80.  }   
  81.  function CreateRow(table, userStory) {   
  82.      if (!table) return;  
  83.      if (!userStory) return;   
  84.      {   
  85.          var tr = document.createElement("tr");   
  86.          tr.setAttribute("class", "product_backlog_row");   
  87.          {   
  88.              tr.appendChild(CreateTd("id", userStory.id));   
  89.              tr.appendChild(CreateTd("name", userStory.name));   
  90.              tr.appendChild(CreateTd("importance", userStory.importance));   
  91.              tr.appendChild(CreateTd("estimate", userStory.estimate));   
  92.              tr.appendChild(CreateTd("description", userStory.role));   
  93.              tr.appendChild(CreateTd("notes", userStory.notes));   
  94.              tr.appendChild(CreateDeleteButton("delete_button", userStory.id));   
  95.          };   
  96.          table.appendChild(tr);   
  97.      }   
  98.  }  
  99.  function CreateTd(name, value) {  
  100.      var td = document.createElement("td");  
  101.      td.setAttribute("class", "user_story " + name);  
  102.      td.innerText = value;  
  103.      return td;  
  104.  };  
  105.  function CreateDeleteButton(name, id) {  
  106.      var td = document.createElement("td");  
  107.      td.setAttribute("class", "user_story " + name);  
  108.      /* 删除用户故事 */  
  109.      td.innerHTML = "###\" title=\"delete\" onclick=\"javascript:_userStoryProvider.removeUserStory(\'" + id + "');removeRow(this);\">>>deletea>";  
  110.      return td;  
  111.  }  
  112.  function removeRow(obj) {  
  113.      document.getElementById("user_story_table").deleteRow(obj.parentNode.parentNode.rowIndex);  
  114.      //obj.parentNode.parentNode.removeNode(true);  
  115.  }

看完代码复习下基本功课

1,WindowDatabase接口,注意openDatabase方法

  1. [Supplemental, NoInterfaceObject]  
  2. interface WindowDatabase {   
  3.  Database openDatabase(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback);};  
  4. Window implements WindowDatabase;  
  5. [Supplemental, NoInterfaceObject]  
  6. interface WorkerUtilsDatabase {   
  7.  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);};  
  8. WorkerUtils implements WorkerUtilsDatabase;  
  9. [Callback=FunctionOnly, NoInterfaceObject]  
  10. interface DatabaseCallback {    
  11. void handleEvent(in Database database);  
  12. };

2,SQLTransaction接口,关注executeSql方法

  1. typedef sequence<any> ObjectArray;  
  2. interface SQLTransaction {   
  3.  void executeSql(in DOMString sqlStatement, in optional ObjectArray arguments, in optional SQLStatementCallback callback, in optional SQLStatementErrorCallback errorCallback);};  
  4. [Callback=FunctionOnly, NoInterfaceObject]  
  5. interface SQLStatementCallback {  
  6.   void handleEvent(in SQLTransaction transaction, in SQLResultSet resultSet);};  
  7. [Callback=FunctionOnly, NoInterfaceObject]  
  8. interface SQLStatementErrorCallback {   
  9.  boolean handleEvent(in SQLTransaction transaction, in SQLError error);  
  10. };

3,最后看下SQLResultSetRowList定义

  1. interface SQLResultSetRowList {  
  2.   readonly attribute unsigned long length;    
  3. getter any item(in unsigned long index);  
  4. };

和SQLResultSet定义

  1. interface SQLResultSet {  
  2.    readonly attribute long insertId;  
  3.    readonly attribute long rowsAffected;  
  4.    readonly attribute SQLResultSetRowList rows;  
  5.  };

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！