|
[tr][td]本篇没有考虑异步,多线程及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 [list=1]
[*]//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;
[/list]有了"SqlHeper",再看业务处理层(Business Logic Layer) 业务处理类包括了创建表,删除表,新增记录,删除记录以及读取记录,这里没有写更新,实际上先删后增一样滴,即使要写也不复杂 [list=1]
[*]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 };
[*] }
[/list]createUserStoryTable,dropUserStoryTable,addUserStory,removeUserStory又是严重同质,不说了,仅SQL语句不同而已 但loadUserStories与上述四个方法均不同,是因为它把SQLResultSetRowList返回给了调用者,这里仍然是简单的"转发",页面在使用的时候需要首先创建provider实例(使用类似C#中的类实例上的方法调用) [list=1] [*]var _userStoryProvider = new userStoryProvider(); [/list] 之后就可以调用该实例的方法了,仅举个例子,具体代码省去 [list=1]
[*]function loadUserStory() {
[*]try {
[*]_userStoryProvider.loadUserStories(function (result) {
[*] var _userStories = new Array();
[*]for (var i = 0; i delete";
[*] return td;
[*] }
[*] function removeRow(obj) {
[*] document.getElementById("user_story_table").deleteRow(obj.parentNode.parentNode.rowIndex);
[*] //obj.parentNode.parentNode.removeNode(true);
[*] }
[/list]看完代码复习下基本功课 1,WindowDatabase接口,注意openDatabase方法 [list=1]
[*][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);
[*]};
[/list]2,SQLTransaction接口,关注executeSql方法 [list=1]
[*]typedef sequence 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);
[*]};
[/list]3,最后看下SQLResultSetRowList定义 [list=1]
[*]interface SQLResultSetRowList {
[*] readonly attribute unsigned long length;
[*]getter any item(in unsigned long index);
[*]};
[/list]和SQLResultSet定义 [list=1]
[*]interface SQLResultSet {
[*] readonly attribute long insertId;
[*] readonly attribute long rowsAffected;
[*] readonly attribute SQLResultSetRowList rows;
[*] };
[/list][/td][/tr] |
|
