HTML 5离线存储之Web 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 = nam