HTML5 内置本地数据库之Sqlite
一、关于HTML5和web本地数据库
SQLite 可以很好的支持关系型数据库所具备的一些基本特征,如标准SQL语法、事物、数据表和索引等,且占用资源较少,可在移动设备上轻松使用。
HTML5中添加了很多功能,将一些以前必须保存在服务器上的数据转向本地保存,提高了Web应用程序性能的同时还减轻了服务器的负担。然而关于Web SQL Database有一个不太好的消息(来自网络):
我们经常在数据库中处理大量结构化数据,html5引入Web SQL Database概念,它使用 SQL 来操纵客户端数据库的 API,这些 API 是异步的,规范中使用的是SQLlite,悲剧正是产生于此,Web SQL Database规范页面有着这样的声明
This document was on the W3C Recommendation track but specification work has stopped. The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path.
大概意思就是
这个文档曾经在W3C推荐规范上,但规范工作已经停止了。目前已经陷入了一个僵局:目前的所有实现都是基于同一个SQL后端(SQLite),但是我们需要更多的独立实现来完成标准化。
也就是说这是一个废弃的标准了,虽然部分浏览器已经实现
一上来就泼凉水确实不太好 ,但是学习一下总归没有什么坏处的,对吧?
目前我已知的支持Web SQL Database的浏览器有chrome(估计还有别的,只是我没用到~),然而火狐和IE并不支持。
W3C目前力推的H5本地数据库是IndexedDB
二、H5 Sqlite数据库三个核心API
opendatabase:使用现有数据库或创建新数据库创建数据库对象
<code class="language-js hljs has-numbering"><span class="hljs-keyword">var</span> db = openDatabase(<span class="hljs-string">'myTel'</span>,<span class="hljs-string">'1.0'</span>,<span class="hljs-string">'test db'</span>,<span class="hljs-number">1024</span>*<span class="hljs-number">100</span>); <span class="hljs-comment">//四个参数分别是数据库名 版本 数据库描述 大小</span> <span class="hljs-comment">//当该数据库不存在的时候创建并打开数据库,否则直接打开数据库。</span> <span class="hljs-comment">//创建的数据库存于本地浏览器的Cache 文件夹。</span></code><ul style="display: block;" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li></ul>
transaction:事务,可根据情况控制事务提交或回滚
<code class="language-js hljs has-numbering">db.transaction(callback) <span class="hljs-comment">//用作事务处理,来操作数据库</span> </code><ul style="display: block;" class="pre-numbering"><li>1</li><li>2</li><li>3</li></ul>
executeSql:用于执行SQL查询
<code class="language-js hljs has-numbering">db.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx)</span>{</span> tx.executeSql(<span class="hljs-string">'SQL语句'</span>,[参数数组],dataHandler,errorHandler) }) <span class="hljs-comment">//executeSql的四个参数:</span> <span class="hljs-comment">//1、操作数据库的SQL语句</span> <span class="hljs-comment">//2、SQL语句中使用的参数的数组</span> <span class="hljs-comment">//3、语句操作成功调用的方法</span> <span class="hljs-comment">//4、语句操作失败调用的方法</span></code><ul style="display: block;" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li></ul>
关于dataHandler和errorHandler:
<code class="language-js hljs has-numbering"><span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">dataHandler</span><span class="hljs-params">(transaction,results)</span>; // 其中,<span class="hljs-title">results</span>的<span class="hljs-title">rows</span>属性中保存了查询到的每一条记录,<span class="hljs-title">rows</span>.<span class="hljs-title">length</span>属性可以获取记录的条数 // 可使用<span class="hljs-title">rows</span>[<span class="hljs-title">index</span>]或<span class="hljs-title">rows</span>[<span class="hljs-title">item</span>]来单独取到某条记录 <span class="hljs-title">function</span> <span class="hljs-title">errorHandler</span><span class="hljs-params">(transaction,errMsg)</span>;</span></code><ul style="display: block;" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li></ul>
三、一个例子
下面使用一个比较完整的例子来演示。
<code class="language-html hljs has-numbering"><span class="hljs-doctype"><!DOCTYPE html></span> <span class="hljs-tag"><<span class="hljs-title">html</span> <span class="hljs-attribute">lang</span>=<span class="hljs-value">"en"</span>></span> <span class="hljs-tag"><<span class="hljs-title">head</span>></span> <span class="hljs-tag"><<span class="hljs-title">meta</span> <span class="hljs-attribute">charset</span>=<span class="hljs-value">"UTF-8"</span>></span> <span class="hljs-tag"><<span class="hljs-title">title</span>></span>Sqlite<span class="hljs-tag"></<span class="hljs-title">title</span>></span> <span class="hljs-tag"></<span class="hljs-title">head</span>></span> <span class="hljs-tag"><<span class="hljs-title">body</span> <span class="hljs-attribute">onload</span>=<span class="hljs-value">"init()"</span>></span> <span class="hljs-comment"><!-- 创建数据库对象、使用事务进行数据库操作 --></span> 姓名:<span class="hljs-tag"><<span class="hljs-title">input</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"text"</span> <span class="hljs-attribute">id</span>=<span class="hljs-value">"name"</span>/></span> 电话:<span class="hljs-tag"><<span class="hljs-title">input</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"text"</span> <span class="hljs-attribute">id</span>=<span class="hljs-value">"tel"</span> /></span> <span class="hljs-tag"><<span class="hljs-title">input</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"button"</span> <span class="hljs-attribute">value</span>=<span class="hljs-value">"保存"</span> <span class="hljs-attribute">onclick</span>=<span class="hljs-value">"saveData()"</span> /></span> <span class="hljs-tag"><<span class="hljs-title">hr</span>/></span> <span class="hljs-tag"><<span class="hljs-title">input</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"button"</span> <span class="hljs-attribute">onclick</span>=<span class="hljs-value">"showAllData()"</span> <span class="hljs-attribute">value</span>=<span class="hljs-value">"显示全部"</span>></span> <span class="hljs-tag"><<span class="hljs-title">input</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"button"</span> <span class="hljs-attribute">onclick</span>=<span class="hljs-value">"delAllData()"</span> <span class="hljs-attribute">value</span>=<span class="hljs-value">"清空全部"</span>></span> <span class="hljs-tag"><<span class="hljs-title">hr</span>/></span> <span class="hljs-tag"><<span class="hljs-title">table</span> <span class="hljs-attribute">id</span>=<span class="hljs-value">"datatable"</span> <span class="hljs-attribute">border</span>=<span class="hljs-value">"1"</span>></span> <span class="hljs-tag"></<span class="hljs-title">table</span>></span> <span class="hljs-tag"><<span class="hljs-title">p</span> <span class="hljs-attribute">id</span>=<span class="hljs-value">"'msg"</span>></span><span class="hljs-tag"></<span class="hljs-title">p</span>></span> <span class="hljs-tag"></<span class="hljs-title">body</span>></span> <span class="hljs-tag"><<span class="hljs-title">script</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"text/javascript"</span>></span><span class="javascript"> <span class="hljs-keyword">var</span> datatable = <span class="hljs-literal">null</span>; <span class="hljs-keyword">var</span> db = openDatabase(<span class="hljs-string">'myTel'</span>,<span class="hljs-string">'1.0'</span>,<span class="hljs-string">'test db'</span>,<span class="hljs-number">1024</span>*<span class="hljs-number">100</span>);<span class="hljs-comment">//数据库名 版本 数据库描述 大小</span> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">init</span><span class="hljs-params">()</span>{</span><span class="hljs-comment">//初始化工作</span> datatable = document.getElementById(<span class="hljs-string">'datatable'</span>); showAllData(); } <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">removeAllData</span><span class="hljs-params">()</span>{</span><span class="hljs-comment">//移除页面上展示的数据</span> <span class="hljs-keyword">for</span>(<span class="hljs-keyword">var</span> i = datatable.childNodes.length-<span class="hljs-number">1</span>;i>=<span class="hljs-number">0</span>;i--){ datatable.removeChild(datatable.childNodes[i]); } <span class="hljs-keyword">var</span> tr = document.createElement(<span class="hljs-string">'tr'</span>); <span class="hljs-keyword">var</span> th1 = document.createElement(<span class="hljs-string">'th'</span>); <span class="hljs-keyword">var</span> th2 = document.createElement(<span class="hljs-string">'th'</span>); th1.innerHTML = <span class="hljs-string">'姓名'</span>; th2.innerHTML = <span class="hljs-string">'电话'</span>; tr.appendChild(th1); tr.appendChild(th2); datatable.appendChild(tr); } <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">showData</span><span class="hljs-params">(row)</span>{</span><span class="hljs-comment">//显示数据</span> <span class="hljs-keyword">var</span> tr = document.createElement(<span class="hljs-string">'tr'</span>); <span class="hljs-keyword">var</span> td1 = document.createElement(<span class="hljs-string">'td'</span>); td1.innerHTML = row.name; <span class="hljs-keyword">var</span> td2 = document.createElement(<span class="hljs-string">'td'</span>); td2.innerHTML = row.tel; tr.appendChild(td1); tr.appendChild(td2); datatable.appendChild(tr); } <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">showAllData</span><span class="hljs-params">()</span>{</span><span class="hljs-comment">//显示所有数据</span> db.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-params">(tx)</span>{</span> tx.executeSql(<span class="hljs-string">'create table if not exists TelData(name TEXT,tel TEXT)'</span>,[],<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx,res)</span>{</span> },<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx,err)</span>{</span> alert(err.message) }); tx.executeSql(<span class="hljs-string">'select * from TelData'</span>,[],<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx,result)</span>{</span> removeAllData(); <span class="hljs-keyword">for</span>(<span class="hljs-keyword">var</span> i = <span class="hljs-number">0</span> ;i<result.rows.length;i++){ showData(result.rows.item(i)); } }) }) } <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">saveData</span><span class="hljs-params">()</span>{</span><span class="hljs-comment">//保存数据</span> <span class="hljs-keyword">var</span> name = document.getElementById(<span class="hljs-string">'name'</span>).value; <span class="hljs-keyword">var</span> tel = document.getElementById(<span class="hljs-string">'tel'</span>).value; addData(name,tel); showAllData(); } <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">addData</span><span class="hljs-params">(name,tel)</span>{</span><span class="hljs-comment">//添加数据</span> db.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx)</span>{</span> tx.executeSql(<span class="hljs-string">'insert into TelData values(?,?)'</span>,[name,tel],<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx,rs)</span>{</span> alert(<span class="hljs-string">'yes'</span>); }, <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-params">(tx,err)</span>{</span> alert(err.source +<span class="hljs-string">'===='</span>+err.message); }) }) } <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">delAllData</span><span class="hljs-params">()</span>{</span><span class="hljs-comment">//删除所有数据</span> db.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx)</span>{</span> tx.executeSql(<span class="hljs-string">'delete from TelData'</span>,[],<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx,res)</span>{</span> alert(<span class="hljs-string">'删除成功~'</span>); },<span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-params">(tx,err)</span>{</span> alert(<span class="hljs-string">'删除失败'</span>+err.message); }) }) showAllData(); } </span><span class="hljs-tag"></<span class="hljs-title">script</span>></span> <span class="hljs-tag"></<span class="hljs-title">html</span>></span></code><ul style="display: block;" class="pre-numbering"><li>1</li><li>2</li><li>3</li><li>4</li><li>5</li><li>6</li><li>7</li><li>8</li><li>9</li><li>10</li><li>11</li><li>12</li><li>13</li><li>14</li><li>15</li><li>16</li><li>17</li><li>18</li><li>19</li><li>20</li><li>21</li><li>22</li><li>23</li><li>24</li><li>25</li><li>26</li><li>27</li><li>28</li><li>29</li><li>30</li><li>31</li><li>32</li><li>33</li><li>34</li><li>35</li><li>36</li><li>37</li><li>38</li><li>39</li><li>40</li><li>41</li><li>42</li><li>43</li><li>44</li><li>45</li><li>46</li><li>47</li><li>48</li><li>49</li><li>50</li><li>51</li><li>52</li><li>53</li><li>54</li><li>55</li><li>56</li><li>57</li><li>58</li><li>59</li><li>60</li><li>61</li><li>62</li><li>63</li><li>64</li><li>65</li><li>66</li><li>67</li><li>68</li><li>69</li><li>70</li><li>71</li><li>72</li><li>73</li><li>74</li><li>75</li><li>76</li><li>77</li><li>78</li><li>79</li><li>80</li><li>81</li><li>82</li><li>83</li><li>84</li><li>85</li><li>86</li><li>87</li><li>88</li><li>89</li><li>90</li><li>91</li><li>92</li><li>93</li><li>94</li><li>95</li><li>96</li><li>97</li><li>98</li></ul>
执行结果: