HTML 5离线存储之Web SQL
本篇没有考虑异步,多线程及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
<ol class="dp-xml"> <li class="alt"><span><span>//TODO;SQL注入 </span></span></li> <li><span> function dataBaseOperator() {}; </span></li> <li class="alt"> <span> </span><span class="attribute">dataBaseOperator.openDatabase</span><span> = </span><span class="attribute-value">function</span><span> () { </span> </li> <li><span> return window.openDatabase("dataBaseUserStories", "1.0", "dataBase used for user stories", 2 * 1024 * 1024); </span></li> <li class="alt"><span> } </span></li> <li> <span> </span><span class="attribute">dataBaseOperator.executeNonQuery</span><span> = </span><span class="attribute-value">function</span><span> (sql, parameters, callback) { </span> </li> <li class="alt"> <span> var </span><span class="attribute">db</span><span> = </span><span class="attribute-value">this</span><span>.openDatabase(); </span> </li> <li><span> db.transaction(function (trans) { </span></li> <li class="alt"><span> trans.executeSql(sql, parameters, function (trans, result) { </span></li> <li><span> callback(result); </span></li> <li class="alt"><span> }, function (trans, error) { </span></li> <li><span> throw error.message; </span></li> <li class="alt"><span> }); </span></li> <li><span> }); </span></li> <li class="alt"><span> } </span></li> <li> <span> </span><span class="attribute-value">dataBaseOperator</span><span class="attribute">dataBaseOperator.executeReader</span><span> = dataBaseOperator.executeNonQuery; </span> </li> <li class="alt"> <span> </span><span class="attribute-value">dataBaseOperator</span><span class="attribute">dataBaseOperator.executeScalar</span><span> = dataBaseOperator.executeNonQuery; </span> </li> </ol>
有了"SqlHeper",再看业务处理层(Business Logic Layer)
业务处理类包括了创建表,删除表,新增记录,删除记录以及读取记录,这里没有写更新,实际上先删后增一样滴,即使要写也不复杂
<ol class="dp-xml"> <li class="alt"><span><span>function userStoryProvider() { </span></span></li> <li> <span> </span><span class="attribute">this.createUserStoryTable</span><span> = </span><span class="attribute-value">function</span><span> () { </span> </li> <li class="alt"><span> dataBaseOperator.executeNonQuery("CREATE TABLE tbUserStories(id integer primary key autoincrement,role,ability,benefit,name,importance,estimate,notes)"); </span></li> <li><span> }; </span></li> <li class="alt"> <span> </span><span class="attribute">this.dropUserStoryTable</span><span> = </span><span class="attribute-value">function</span><span> () { </span> </li> <li><span> dataBaseOperator.executeNonQuery("DROP TABLE tbUserStories"); </span></li> <li class="alt"><span> }; </span></li> <li> <span> </span><span class="attribute">this.addUserStory</span><span> = </span><span class="attribute-value">function</span><span> (role, ability, benefit, name, importance, estimate, notes) { </span> </li> <li class="alt"><span> dataBaseOperator.executeNonQuery("INSERT INTO tbUserStories(role,ability,benefit,name,importance,estimate,notes) SELECT ?,?,?,?,?,?,?", </span></li> <li><span> [role, ability, benefit, name, importance, estimate, notes], function (result) { </span></li> <li class="alt"><span> //alert("rowsAffected:" + result.rowsAffected); </span></li> <li><span> }); </span></li> <li class="alt"><span> }; </span></li> <li> <span> </span><span class="attribute">this.removeUserStory</span><span> = </span><span class="attribute-value">function</span><span> (id) { </span> </li> <li class="alt"> <span> dataBaseOperator.executeNonQuery("DELETE FROM tbUserStories WHERE </span><span class="attribute">id</span><span> = ?", [id], function (result) { </span> </li> <li><span> //alert("rowsAffected:" + result.rowsAffected); </span></li> <li class="alt"><span> }); </span></li> <li><span> }; </span></li> <li class="alt"> <span> </span><span class="attribute">this.loadUserStories</span><span> = </span><span class="attribute-value">function</span><span> (callback) { </span> </li> <li><span> dataBaseOperator.executeReader("SELECT * FROM tbUserStories", [], function (result) { </span></li> <li class="alt"><span> callback(result); </span></li> <li><span> }); </span></li> <li class="alt"><span> //result.insertId,result.rowsAffected,result.rows24 }; </span></li> <li><span> } </span></li> </ol>
createUserStoryTable,dropUserStoryTable,addUserStory,removeUserStory又是严重同质,不说了,仅SQL语句不同而已
但loadUserStories与上述四个方法均不同,是因为它把SQLResultSetRowList返回给了调用者,这里仍然是简单的"转发",页面在使用的时候需要首先创建provider实例(使用类似C#中的类实例上的方法调用)
<ol class="dp-xml"><li class="alt"><span><span>var </span><span class="attribute">_userStoryProvider</span><span> = </span><span class="attribute-value">new</span><span> userStoryProvider(); </span></span></li></ol>
之后就可以调用该实例的方法了,仅举个例子,具体代码省去
<ol class="dp-xml"> <li class="alt"><span><span>function loadUserStory() { </span></span></li> <li><span>try { </span></li> <li class="alt"><span>_userStoryProvider.loadUserStories(function (result) { </span></li> <li> <span> var </span><span class="attribute">_userStories</span><span> = </span><span class="attribute-value">new</span><span> Array(); </span> </li> <li class="alt"> <span>for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">result.rows.length</span><span>; i++) { </span></span> </li> <li> <span> var </span><span class="attribute">o</span><span> = </span><span class="attribute-value">result</span><span>.rows.item(i); </span> </li> <li class="alt"> <span> var </span><span class="attribute">_userStory</span><span> = </span><span class="attribute-value">new</span><span> userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes); </span> </li> <li><span> _userStories.push(_userStory); </span></li> <li class="alt"><span> }//... </span></li> <li><span>} catch (error) { </span></li> <li class="alt"><span> alert("_userStoryProvider.loadUserStories:" + error); </span></li> <li><span>}} </span></li> </ol>
得到_userStories这个数组后,就没有下文了,是自动创建HTML还是绑定到EXT,发挥想象力吧...继续
userStory是一个自定义的"Model" "类"·
<ol class="dp-xml"> <li class="alt"><span><span>function userStory(id, name, role, ability, benefit, importance, estimate, notes) { </span></span></li> <li> <span> </span><span class="attribute">this.id</span><span> = id; </span> </li> <li class="alt"> <span> </span><span class="attribute">this.name</span><span> = name; </span> </li> <li> <span> </span><span class="attribute">this.role</span><span> = role; </span> </li> <li class="alt"> <span> </span><span class="attribute">this.ability</span><span> = ability; </span> </li> <li> <span> </span><span class="attribute">this.benefit</span><span> = benefit; </span> </li> <li class="alt"> <span> </span><span class="attribute">this.importance</span><span> = importance; </span> </li> <li> <span> </span><span class="attribute">this.estimate</span><span> = estimate; </span> </li> <li class="alt"> <span> </span><span class="attribute">this.notes</span><span> = notes; </span> </li> <li><span> }; </span></li> </ol>
最后贴出应用的代码,业务相关的代码,不看也罢,谁家与谁家的都不同
<ol class="dp-xml"> <li class="alt"><span><span>/* </span></span></li> <li><span> http://stackoverflow.com/questions/2010892/storing-objects-in-html5-localstorage </span></li> <li class="alt"><span> http://www.w3.org/TR/webdatabase/#sqlresultset </span></li> <li><span> http://html5doctor.com/introducing-web-sql-databases/ </span></li> <li class="alt"><span> http://stackoverflow.com/questions/844885/sqlite-insert-into-with-unique-names-getting-id </span></li> <li><span> */ </span></li> <li class="alt"> <span> var </span><span class="attribute">_userStoryProvider</span><span> = </span><span class="attribute-value">new</span><span> userStoryProvider(); </span> </li> <li><span> $(document).ready(function () { </span></li> <li class="alt"><span> loadUserStory(); </span></li> <li><span> </span></li> <li class="alt"><span> /* 添加用户故事 */ </span></li> <li><span> $("#btnAdd").click(function () { </span></li> <li class="alt"> <span> var </span><span class="attribute">item</span><span> = { role: $("#role").val(), ability: $("#ability").val(), benefit: $("#benefit").val(), name: $("#Name").val(), importance: $("#Importance").val(), estimate: $("#Estimate").val(), notes: $("#Notes").val() }; </span> </li> <li><span> try { </span></li> <li class="alt"><span> _userStoryProvider.addUserStory(item.role, item.ability, item.benefit, item.name, item.importance, item.estimate, item.notes); </span></li> <li><span> loadUserStory(); </span></li> <li class="alt"><span> } catch (error) { </span></li> <li><span> alert("_userStoryProvider.addUserStory:" + error); </span></li> <li class="alt"><span> } </span></li> <li><span> }); </span></li> <li class="alt"><span> </span></li> <li><span> /* 创建用户故事表 */ </span></li> <li class="alt"><span> $("#btnCreateTable").click(function () { try { </span></li> <li><span> _userStoryProvider.createUserStoryTable(); </span></li> <li class="alt"><span> } catch (error) { </span></li> <li><span> alert("_userStoryProvider.createUserStoryTable:" + error); </span></li> <li class="alt"><span> } </span></li> <li><span> }); </span></li> <li class="alt"><span> </span></li> <li><span> /* 删除用户故事表 */ </span></li> <li class="alt"><span> $("#btnDropTable").click(function () { </span></li> <li><span> try { </span></li> <li class="alt"><span> _userStoryProvider.dropUserStoryTable(); </span></li> <li><span> } catch (error) { </span></li> <li class="alt"><span> alert("_userStoryProvider.dropUserStoryTable:" + error); </span></li> <li><span> } </span></li> <li class="alt"><span> }); </span></li> <li><span> }); </span></li> <li class="alt"><span> </span></li> <li><span> /* 加载用户故事 */ </span></li> <li class="alt"><span> function loadUserStory() { </span></li> <li><span> try { </span></li> <li class="alt"><span> _userStoryProvider.loadUserStories(function (result) { </span></li> <li> <span> var </span><span class="attribute">_userStories</span><span> = </span><span class="attribute-value">new</span><span> Array(); </span> </li> <li class="alt"> <span> for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">result.rows.length</span><span>; i++) { </span></span> </li> <li> <span> var </span><span class="attribute">o</span><span> = </span><span class="attribute-value">result</span><span>.rows.item(i); </span> </li> <li class="alt"> <span> var </span><span class="attribute">_userStory</span><span> = </span><span class="attribute-value">new</span><span> userStory(o.id, o.name, o.role, o.ability, o.benefit, o.importance, o.estimate, o.notes); </span> </li> <li><span> _userStories.push(_userStory); </span></li> <li class="alt"><span> } </span></li> <li><span> </span></li> <li class="alt"><span> if (!_userStories) return; </span></li> <li> <span> var </span><span class="attribute">table</span><span> = </span><span class="attribute-value">document</span><span>.getElementById("user_story_table"); </span> </li> <li class="alt"><span> if (!table) return; </span></li> <li> <span> var </span><span class="attribute">_trs</span><span> = </span><span class="attribute-value">table</span><span>.getElementsByTagName("tr"); </span> </li> <li class="alt"> <span> var </span><span class="attribute">_len</span><span> = </span><span class="attribute-value">_trs</span><span>.length; </span> </li> <li> <span> for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">_len</span><span>; i++) { </span></span> </li> <li class="alt"><span> table.removeChild(_trs[i]); </span></li> <li><span> } </span></li> <li class="alt"><span> { </span></li> <li> <span> var </span><span class="attribute">tr</span><span> = </span><span class="attribute-value">document</span><span>.createElement("tr"); </span> </li> <li class="alt"><span> tr.setAttribute("class", "product_backlog_row header"); </span></li> <li><span> { </span></li> <li class="alt"><span> tr.appendChild(CreateTd("id", "id")); </span></li> <li><span> tr.appendChild(CreateTd("name", "name")); </span></li> <li class="alt"><span> tr.appendChild(CreateTd("importance", "importance")); </span></li> <li><span> tr.appendChild(CreateTd("estimate", "estimate")); </span></li> <li class="alt"><span> tr.appendChild(CreateTd("description", "role")); </span></li> <li><span> tr.appendChild(CreateTd("notes", "notes")); </span></li> <li class="alt"><span> tr.appendChild(CreateTd("delete", "delete")); </span></li> <li><span> }; </span></li> <li class="alt"><span> table.appendChild(tr); </span></li> <li><span> } </span></li> <li class="alt"> <span> for (var </span><span class="attribute">i</span><span> = </span><span class="attribute-value">0</span><span>; i </span><span class="tag"><span> </span><span class="tag-name">_userStories.length</span><span>; i++) { </span></span> </li> <li><span> CreateRow(table, _userStories[i]); </span></li> <li class="alt"><span> } </span></li> <li><span> }); </span></li> <li class="alt"><span> } catch (error) { </span></li> <li><span> alert("_userStoryProvider.loadUserStories:" + error); </span></li> <li class="alt"><span> } </span></li> <li><span> } </span></li> <li class="alt"><span> function CreateRow(table, userStory) { </span></li> <li><span> if (!table) return; </span></li> <li class="alt"><span> if (!userStory) return; </span></li> <li><span> { </span></li> <li class="alt"> <span> var </span><span class="attribute">tr</span><span> = </span><span class="attribute-value">document</span><span>.createElement("tr"); </span> </li> <li><span> tr.setAttribute("class", "product_backlog_row"); </span></li> <li class="alt"><span> { </span></li> <li><span> tr.appendChild(CreateTd("id", userStory.id)); </span></li> <li class="alt"><span> tr.appendChild(CreateTd("name", userStory.name)); </span></li> <li><span> tr.appendChild(CreateTd("importance", userStory.importance)); </span></li> <li class="alt"><span> tr.appendChild(CreateTd("estimate", userStory.estimate)); </span></li> <li><span> tr.appendChild(CreateTd("description", userStory.role)); </span></li> <li class="alt"><span> tr.appendChild(CreateTd("notes", userStory.notes)); </span></li> <li><span> tr.appendChild(CreateDeleteButton("delete_button", userStory.id)); </span></li> <li class="alt"><span> }; </span></li> <li><span> table.appendChild(tr); </span></li> <li class="alt"><span> } </span></li> <li><span> } </span></li> <li class="alt"><span> function CreateTd(name, value) { </span></li> <li> <span> var </span><span class="attribute">td</span><span> = </span><span class="attribute-value">document</span><span>.createElement("td"); </span> </li> <li class="alt"><span> td.setAttribute("class", "user_story " + name); </span></li> <li> <span> </span><span class="attribute">td.innerText</span><span> = </span><span class="attribute-value">value</span><span>; </span> </li> <li class="alt"><span> return td; </span></li> <li><span> }; </span></li> <li class="alt"><span> function CreateDeleteButton(name, id) { </span></li> <li> <span> var </span><span class="attribute">td</span><span> = </span><span class="attribute-value">document</span><span>.createElement("td"); </span> </li> <li class="alt"><span> td.setAttribute("class", "user_story " + name); </span></li> <li><span> /* 删除用户故事 */ </span></li> <li class="alt"> <span> </span><span class="attribute">td.innerHTML</span><span> = </span><span class="attribute-value">"<a><span>###\" </span><span class="attribute">title</span><span>=\"delete\" </span><span class="attribute">onclick</span><span>=\"javascript:_userStoryProvider.removeUserStory(\'" + id + "');removeRow(this);\"</span><span class="tag">></span><span class="tag">></span><span class="tag">></span><span>delete</span><span class="tag"></span><span class="tag-name">a</span><span class="tag">></span><span>"; </span></a></span> </li> <li><span> return td; </span></li> <li class="alt"><span> } </span></li> <li><span> function removeRow(obj) { </span></li> <li class="alt"><span> document.getElementById("user_story_table").deleteRow(obj.parentNode.parentNode.rowIndex); </span></li> <li><span> //obj.parentNode.parentNode.removeNode(true); </span></li> <li class="alt"><span> } </span></li> </ol>
看完代码复习下基本功课
1,WindowDatabase接口,注意openDatabase方法
<ol class="dp-xml"> <li class="alt"><span><span>[Supplemental, NoInterfaceObject] </span></span></li> <li><span>interface WindowDatabase { </span></li> <li class="alt"><span> Database openDatabase(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback);}; </span></li> <li><span>Window implements WindowDatabase; </span></li> <li class="alt"><span>[Supplemental, NoInterfaceObject] </span></li> <li><span>interface WorkerUtilsDatabase { </span></li> <li class="alt"><span> 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);}; </span></li> <li><span>WorkerUtils implements WorkerUtilsDatabase; </span></li> <li class="alt"> <span>[</span><span class="attribute">Callback</span><span>=</span><span class="attribute-value">FunctionOnly</span><span>, NoInterfaceObject] </span> </li> <li><span>interface DatabaseCallback { </span></li> <li class="alt"><span>void handleEvent(in Database database); </span></li> <li><span>}; </span></li> </ol>
2,SQLTransaction接口,关注executeSql方法
<ol class="dp-xml"> <li class="alt"><span><span>typedef sequence</span><span class="tag"><span class="tag-name">any</span><span class="tag">></span><span> ObjectArray; </span></span></span></li> <li><span>interface SQLTransaction { </span></li> <li class="alt"><span> void executeSql(in DOMString sqlStatement, in optional ObjectArray arguments, in optional SQLStatementCallback callback, in optional SQLStatementErrorCallback errorCallback);}; </span></li> <li> <span>[</span><span class="attribute">Callback</span><span>=</span><span class="attribute-value">FunctionOnly</span><span>, NoInterfaceObject] </span> </li> <li class="alt"><span>interface SQLStatementCallback { </span></li> <li><span> void handleEvent(in SQLTransaction transaction, in SQLResultSet resultSet);}; </span></li> <li class="alt"> <span>[</span><span class="attribute">Callback</span><span>=</span><span class="attribute-value">FunctionOnly</span><span>, NoInterfaceObject] </span> </li> <li><span>interface SQLStatementErrorCallback { </span></li> <li class="alt"><span> boolean handleEvent(in SQLTransaction transaction, in SQLError error); </span></li> <li><span>}; </span></li> </ol>
3,最后看下SQLResultSetRowList定义
<ol class="dp-xml"> <li class="alt"><span><span>interface SQLResultSetRowList { </span></span></li> <li><span> readonly attribute unsigned long length; </span></li> <li class="alt"><span>getter any item(in unsigned long index); </span></li> <li><span>}; </span></li> </ol>
和SQLResultSet定义
<ol class="dp-xml"> <li class="alt"><span><span>interface SQLResultSet { </span></span></li> <li><span> readonly attribute long insertId; </span></li> <li class="alt"><span> readonly attribute long rowsAffected; </span></li> <li><span> readonly attribute SQLResultSetRowList rows; </span></li> <li class="alt"><span> }; <br></span></li> </ol>

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics











H5referstoHTML5,apivotaltechnologyinwebdevelopment.1)HTML5introducesnewelementsandAPIsforrich,dynamicwebapplications.2)Itsupportsmultimediawithoutplugins,enhancinguserexperienceacrossdevices.3)SemanticelementsimprovecontentstructureandSEO.4)H5'srespo

Web standards and technologies have evolved from HTML4, CSS2 and simple JavaScript to date and have undergone significant developments. 1) HTML5 introduces APIs such as Canvas and WebStorage, which enhances the complexity and interactivity of web applications. 2) CSS3 adds animation and transition functions to make the page more effective. 3) JavaScript improves development efficiency and code readability through modern syntax of Node.js and ES6, such as arrow functions and classes. These changes have promoted the development of performance optimization and best practices of web applications.

Best practices for H5 code include: 1. Use correct DOCTYPE declarations and character encoding; 2. Use semantic tags; 3. Reduce HTTP requests; 4. Use asynchronous loading; 5. Optimize images. These practices can improve the efficiency, maintainability and user experience of web pages.

H5 is not just the abbreviation of HTML5, it represents a wider modern web development technology ecosystem: 1. H5 includes HTML5, CSS3, JavaScript and related APIs and technologies; 2. It provides a richer, interactive and smooth user experience, and can run seamlessly on multiple devices; 3. Using the H5 technology stack, you can create responsive web pages and complex interactive functions.

H5 and HTML5 refer to the same thing, namely HTML5. HTML5 is the fifth version of HTML, bringing new features such as semantic tags, multimedia support, canvas and graphics, offline storage and local storage, improving the expressiveness and interactivity of web pages.

HTML5 is a key technology for building modern web pages, providing many new elements and features. 1. HTML5 introduces semantic elements such as, , etc., which enhances web page structure and SEO. 2. Support multimedia elements and embed media without plug-ins. 3. Forms enhance new input types and verification properties, simplifying the verification process. 4. Offer offline and local storage functions to improve web page performance and user experience.

HTML5 code consists of tags, elements and attributes: 1. The tag defines the content type and is surrounded by angle brackets, such as. 2. Elements are composed of start tags, contents and end tags, such as contents. 3. Attributes define key-value pairs in the start tag, enhance functions, such as. These are the basic units for building web structure.

H5 improves web user experience with multimedia support, offline storage and performance optimization. 1) Multimedia support: H5 and elements simplify development and improve user experience. 2) Offline storage: WebStorage and IndexedDB allow offline use to improve the experience. 3) Performance optimization: WebWorkers and elements optimize performance to reduce bandwidth consumption.
