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>

핫 AI 도구

Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool
무료로 이미지를 벗다

Clothoff.io
AI 옷 제거제

AI Hentai Generator
AI Hentai를 무료로 생성하십시오.

인기 기사

뜨거운 도구

메모장++7.3.1
사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경

드림위버 CS6
시각적 웹 개발 도구

SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)

뜨거운 주제











이 기사는 & lt; audio & gt를 사용하여 HTML5에 오디오를 포함시키는 방법을 설명합니다. 형식 선택에 대한 모범 사례 (MP3, OGG Vorbis), 파일 최적화 및 재생에 대한 JavaScript 컨트롤을 포함한 요소. 다중 오디오를 사용하는 것을 강조합니다

이 기사에서는 HTML5 페이지 가시성 API를 사용하여 페이지 가시성을 감지하고 사용자 경험을 향상 시키며 리소스 사용량을 최적화하는 것에 대해 설명합니다. 주요 측면에는 미디어 일시 정지, CPU 부하 감소 및 가시성 변경에 기반한 분석 관리가 포함됩니다.

이 기사에서는 Viewport Meta 태그를 사용하여 모바일 장치의 페이지 스케일링을 제어하여 폭과 최적의 응답 성 및 성능을위한 초기 스케일과 같은 설정에 중점을 둡니다. character count : 159

이 기사에서는 HTML5 양식을 작성하고 검증하는 방법을 설명합니다. 그것은 & lt; form & gt; 요소, 입력 유형 (텍스트, 이메일, 번호 등) 및 속성 (필수, 패턴, 최소, 최대). HTML5의 장점은 오래된 방법에 비해 형성됩니다

이 기사는 지리적 위치 API를 사용하여 사용자 위치 개인 정보 및 권한 관리, 권한 요청, 데이터 보안 보장 및 개인 정보 보호법 준수에 대한 모범 사례를 강조하는 것에 대해 설명합니다.

이 기사는 JavaScript를 사용하여 대화식 HTML5 게임을 만드는 자세한 내용입니다. 게임 디자인, HTML 구조, CSS 스타일, JavaScript 로직 (이벤트 처리 및 애니메이션 포함) 및 오디오 통합을 다룹니다. 필수 JavaScript 라이브러리 (Phaser, PI

이 기사는 HTML5 드래그 앤 드롭 API를 사용하여 대화식 사용자 인터페이스를 생성하고 요소를 드래그 가능하게 만들고 주요 이벤트를 처리하며 사용자 정의 피드백으로 사용자 경험을 향상시키는 방법을 자세히 설명합니다. 또한 일반적인 함정에 대해 설명합니다

이 기사에서는 실시간, 양방향 클라이언트 서버 커뮤니케이션을위한 HTML5 WebSockets API를 설명합니다. 클라이언트 측 (JavaScript) 및 서버 측 (Python/Flask) 구현에 대해 자세히 설명하여 확장 성, 상태 관리,
