SQLite数据库的增删改查基本操作

一、概述

SQLite是Android系统的核心数据存储服务之一，它是一个轻型的嵌入式数据库，占用非常少的资源却能提供很好很快的数据存取服务，许多大型的需要数据存储的Android项目都有用到SQLite（也可以用于桌面应用程序）。

下面介绍一下SQLite的创建数据库、表的操作，以及基本的增删改查操作。

二、基本操作API简介

在Android中，SQLiteDatabase类提供了SQLite的底层API，但在使用SQLite数据库时，我们往往不会直接操作SQLiteDatabase这个类，而是自己创建一个继承自SQLitOpenHelper的子类来实现数据库操作。这样做的目的一是为了以后如果数据库升级不至于要改动太多代码，已实现封装；二则是为了我们使用更方便。

1、创建数据库和表

SQLiteOpenHelper是一个抽象类，在这个类里有两个抽象方法，OnCreate和OnUpgrade，前者用于第一次创建数据库，后者用于数据库升级，创建类DBServices如下：

<code class="hljs java has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">DBServices</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">extends</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">SQLiteOpenHelper</span>{</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> version = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> String dbName = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"plan"</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-title" style="box-sizing: border-box;">DBServices</span>(Context context){        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">super</span>(context,dbName,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,version);    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onCreate</span>(SQLiteDatabase db) {        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// TODO Auto-generated method stub</span>        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建今日计划表</span>        String create_today_plan_sql = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"CREATE TABLE [_today_plan] ("</span>                + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[_Date] varchar(10) not null,"</span>                 + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Item] varchar(200),"</span>                + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Check] varchar(5) )"</span>;        db.execSQL(create_today_plan_sql);    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onUpgrade</span>(SQLiteDatabase db, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> oldVersion, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> newVersion) {        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// TODO Auto-generated method stub</span>    }}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li></ul>

示例里定义两个变量，一个是数据库的版本号，一个是数据库名。当Android应用运行时，SQLiteOpenHelper会先检查是否已经存在数据库，如果不存在，就创建数据库，然后打开数据库，最后调用OnCreate方法，所以我们需要再OnCreate中创建表（视图等）；如果数据库已存在，而版本号比上次创建的数据库版本号高，就调用OnUpgrade，用于升级。

2、数据的增——insert

在创建了数据库和表之后，我们就可以给数据库和表添加数据了。 
添加数据的操作和其他数据库一样，也是使用insert，只是SQLite的insert是函数，而且使用起来非常方便，下面是方法（属于上面的DBServices类，附录里有完整代码）：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">public void <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span>(String <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span>, String nullColumnHack, ContentValues <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>){        SQLiteDatabase db = this.getWritableDatabase();</span>        db.<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span>, nullColumnHack, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>);</span>    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>

参数说明： 
table：表名，直接使用字符串指定； 
nullColumnHack：指定null值的列，SQLite里不允许空行，使用这个参数可以指定一个列的值为null，当存入行为空时，这个列的值就被指定为null； 
values：使用类似map键值对映射的数据结构ContentValues来指定插入的数据

添加数据示例：

<code class="hljs javascript has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">            <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span>[] args = {                    today,                    content,                    <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">Boolean</span>.toString(checked)            };            <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span>[] column = {                    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[_Date]"</span>,                    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Item]"</span>,                    <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Check]"</span>            };            <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//数据库中添加数据</span>            ContentValues c = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ContentValues();            <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">for</span>(int i=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>;i<args.length;i++){                c.put(column[i], args[i]);            }            dbServices.insert(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"_today_plan"</span>, <span class="hljs-literal" style="color: rgb(0, 102, 102); box-sizing: border-box;">null</span>, c);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li></ul>

3、数据的删——delete

删除和添加一样，也是通过传入参数调用方法来实现，方法：

<code class="hljs javascript has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">public <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">delete</span>(<span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> table , <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> whereClause , <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span>[] whereArgs){        SQLiteDatabase db = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>.getWritableDatabase();        db.delete(table, whereClause, whereArgs);        Log.d(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Delete"</span>,whereClause);    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>

参数说明： 
table：表名； 
whereClause：可选，指定删除条件，相当于SQL语句WHERE语句之后的类容，可通过？来指定参数； 
whereArgs：当whereClause指定了？参数，这个字符串数组里就是？所代表的参数，个数应与？数一致；

删除数据示例：

<code class="hljs javascript has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> args[] ={    today,        content,        <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">Boolean</span>.toString(checked)};dbServices.delete(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"_today_plan"</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[_Date]=? and [Item]=? and [Check]=?"</span>,args);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>

4、数据的修改——update

修改与添加、删除相差不多，下面是update方法：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">public void <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">update</span>(String <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span>, ContentValues <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>,        String whereClause, String[] whereArgs){        SQLiteDatabase db = this.getWritableDatabase();</span>        db.<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">update</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>, whereClause, whereArgs);</span>    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>

参数说明： 
table：表名； 
values：同上，是需要修改的列和值的映射集合； 
whereClause：修改的行所需符合的条件； 
whereArgs：指定条件里的参数；

修改数据示例：

<code class="hljs javascript has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">String</span> args[] ={    today,        content,        <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">Boolean</span>.toString(!m)};ContentValues c = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> ContentValues();c.put(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Check]"</span>, <span class="hljs-built_in" style="color: rgb(102, 0, 102); box-sizing: border-box;">Boolean</span>.toString(m));dbServices.update(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"_today_plan"</span>, c,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[_Date]=? and [StartTime]=? and [Item]=? and [Check]=?"</span>,args);</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li></ul>

5、数据的查询——read

在这里就和前面有所不同了，读取数据所用的方法是直接执行查询语句，获取游标，然后通过游标来遍历数据库，方法如下：

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> Cursor <span class="hljs-title" style="box-sizing: border-box;">read</span>(String sql ,String[] args){        SQLiteDatabase db = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>.getReadableDatabase();        Cursor cursor = db.rawQuery(sql, args);        Log.d(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Database"</span>,cursor.getColumnName(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>));        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">return</span> cursor;    }</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li></ul>

方法说明： 
请注意：在这里db获取的是只读的数据库（getReadableDatabase），而在上述三种操作里都是使用的可写数据库（getWritableDatabase）；至于游标的用法此处就不再赘述，只需看一下API的名字就能掌握基本用法，但最后一定要记得将游标关闭（close）！

三、附录

附录了DBServices类，仅作参考

<code class="hljs java has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">package</span> com.plan;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> android.content.ContentValues;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> android.content.Context;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> android.database.Cursor;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> android.database.sqlite.SQLiteDatabase;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> android.database.sqlite.SQLiteOpenHelper;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">import</span> android.util.Log;<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-class" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">class</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">DBServices</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">extends</span> <span class="hljs-title" style="box-sizing: border-box; color: rgb(102, 0, 102);">SQLiteOpenHelper</span>{</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> version = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">final</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">static</span> String dbName = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"plan"</span>;    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-title" style="box-sizing: border-box;">DBServices</span>(Context context){        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">super</span>(context,dbName,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,version);    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onCreate</span>(SQLiteDatabase db) {        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// TODO Auto-generated method stub</span>        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//创建今日计划表</span>        String create_today_plan_sql = <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"CREATE TABLE [_today_plan] ("</span>                + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[_Date] varchar(10) not null,"</span>                 + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Item] varchar(200),"</span>                + <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"[Check] varchar(5) )"</span>;        db.execSQL(create_today_plan_sql);    }    <span class="hljs-annotation" style="color: rgb(155, 133, 157); box-sizing: border-box;">@Override</span>    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">onUpgrade</span>(SQLiteDatabase db, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> oldVersion, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> newVersion) {        <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">// TODO Auto-generated method stub</span>    }    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> Cursor <span class="hljs-title" style="box-sizing: border-box;">read</span>(String sql ,String[] args){        SQLiteDatabase db = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>.getReadableDatabase();        Cursor cursor = db.rawQuery(sql, args);        Log.d(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Database"</span>,cursor.getColumnName(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>));        <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">return</span> cursor;    }    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">insert</span>(String table, String nullColumnHack, ContentValues values){        SQLiteDatabase db = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>.getWritableDatabase();        db.insert(table, nullColumnHack, values);    }    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">delete</span>(String table , String whereClause , String[] whereArgs){        SQLiteDatabase db = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>.getWritableDatabase();        db.delete(table, whereClause, whereArgs);        Log.d(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"Delete"</span>,whereClause);    }    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">public</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">void</span> <span class="hljs-title" style="box-sizing: border-box;">update</span>(String table, ContentValues values,        String whereClause, String[] whereArgs){        SQLiteDatabase db = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">this</span>.getWritableDatabase();        db.update(table, values, whereClause, whereArgs);    }}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li><li style="box-sizing: border-box; padding: 0px 5px;">52</li><li style="box-sizing: border-box; padding: 0px 5px;">53</li><li style="box-sizing: border-box; padding: 0px 5px;">54</li><li style="box-sizing: border-box; padding: 0px 5px;">55</li><li style="box-sizing: border-box; padding: 0px 5px;">56</li><li style="box-sizing: border-box; padding: 0px 5px;">57</li><li style="box-sizing: border-box; padding: 0px 5px;">58</li><li style="box-sizing: border-box; padding: 0px 5px;">59</li><li style="box-sizing: border-box; padding: 0px 5px;">60</li><li style="box-sizing: border-box; padding: 0px 5px;">61</li><li style="box-sizing: border-box; padding: 0px 5px;">62</li></ul>

有关SQLite的高级特性，如索引、视图以及触发器等，大家可以去看看SQlite的官方文档。

上述类在手机重启时，数据库会重新创建，原因是数据库没有关闭（但因为要提供游标，所以控制起来会比较困难），所以这里提出一个解决办法，就是数据库的操作（建表、增、删、改）均使用事务方式，示例如下：

<code class="hljs cs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">db.beginTransaction();          <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//事务开始</span><span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//建表、增、删、改、查</span>db.setTransactionSuccessful();      <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//事务成功</span>db.endTransaction();                <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//提交事务</span></code>