Android SQLite数据库使用

浏览数：27 / 时间：2015年06月11日

　　又是一段时间过去了，坚持不是说说而已啊，表示UC浏览器电脑版一堆小bug，真是受不了啊。虽然有些小功能做的很人性化。

　　SQLite作为系统内默认的数据库，作为平常存储一些数据来说是足够了。并且也有对应的api来进行操作还是很方便的。官方的推荐是使用类加上SQLiteOpenHelper来对数据库进行管理。将表中的字段当做类的成员属性，并让表结构这个类实现BaseColumns，该接口中有_COUNT和_ID两个字段。_COUNT是系统统计所有的行。_ID是每一行的独特id。这里我要设计一个存储每次定位信息的一个表。对应的类为LocinfoTable.java.

 1 package com.example.sqlitetest.db;
 2 
 3 import android.provider.BaseColumns;
 4 
 5 public final class Locinfo {
 6     private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS "
 7             + LocinfoTable.TABLE_NAME;
 8 
 9     public static abstract class LocinfoTable implements BaseColumns {
10         //定位信息表，某一次定位的基本信息
11         public static final String TABLE_NAME = "locinfo";
12         //记录是第几次定位
13         public static final String COLUMN_NAME_LOC_COUNT = "LOC_COUNT";
14         //起始位置经度，即起点的经度
15         public static final String COLUMN_NAME_START_LONGITUDE = "START_LONGITUDE";
16         //起始位置纬度，即起点的纬度
17         public static final String COLUMN_NAME_START_LATITUDE = "START_LATITUDE";
18         //结束位置经度，即起点的纬度
19         public static final String COLUMN_NAME_END_LONGITUDE = "END_LONGITUDE";
20         //结束位置纬度，即起点的纬度
21         public static final String COLUMN_NAME_END_LATITUDE = "END_LATITUDE";
22         //起始位置的时间
23         public static final String COLUMN_NAME_START_TIME = "START_TIME";
24         //结束位置的时间
25         public static final String COLUMN_NAME_END_TIME = "END_TIME";
26         //起点到终点的距离
27         public static final String COLUMN_NAME_DISTANCE = "DISTANCE";
28         //本次定位的类型
29         public static final String COLUMN_NAME_LOCTYPE = "LOCTYPE";
30         //备注信息
31         public static final String COLUMN_NAME_REMARKS = "REMARKS";
32     }
33 }

　　有了数据库表的结构，接下来就是对数据库进行管理。按照官方的推荐，要继承SQLiteOpenHelper,并重新写其构造函数。SQLiteOpenHelper中主要有两个函数需要重点注意，也是继承后必须实现的。分别是onCreate，onUpgrade。onCreat函数在数据库没有创建时调用，即新建数据库时会调用该函数，onUpgrade函数则是在数据库版本进行升级时调用，常见的数据库升级比如修改表结构等。并且在继承SQLiteOpenHelper时必须添加以下构造函数

public LocinfoDBHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
    }

　　咋一看好像函数非常多，不过我们真要创建数据库时其实不用这么麻烦，所以我们可以再写一个构造函数来重载。上下文环境是必须的，还有数据库名字是必须的，所以可以重载一个只有两个参数的构造函数

1 public LocinfoDBHelper(Context context, String dbname)

　　那么我们来看看这个SQLiteOpenhelper都有什么方法来让我们方便的操作数据库。

函数名	函数说明
public synchronized void close ()	关闭任何一个已打开的数据库
public String getDatabaseName ()	获取当前一打开的数据库的名字
public SQLiteDatabase getReadableDatabase ()	获取一个可读的数据库实例，如果想要获取的数据库实例不存在则会创建一个数据库。
public SQLiteDatabase getWritableDatabase ()	获取一个可写的数据库实例，同上，如果不存在则创建。
public abstract void onCreate (SQLiteDatabase db)	在第一次创建数据库时调用，通常将建表语句放在这里。
public void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)	数据库版本降级是时调用，通常较少使用。
public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)	数据库版本升级时使用，比如更新表结构时。

　　看了上面的函数之后大概有个了解了。SQLiteOpenHelper只是提供一个数据库管理的类，最后操作数据库是还是通过其getWritableDatabase ()返回的SQLiteDataBase来进行操作。

　　于是我们再来看看这个SQLiteDataBase类。对于数据库来说，最基本的当然是增删改查这类DML语句，所以我们来着重讲讲这些API是怎么么使用的。

　　1.插入函数:

public long insert (String table, String nullColumnHack, ContentValues values)

Added in API level 1
Convenience method for inserting a row into the database.

Parameters
table//表名    the table to insert the row into
nullColumnHack//通常为null    optional; may be null. SQL doesn‘t allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can‘t be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty.
values//要插入的字段和其值    this map contains the initial column values for the row. The keys should be the column names and the values the column values
Returns
the row ID of the newly inserted row, or -1 if an error occurred

　　2.更新操作

public int update (String table, ContentValues values, String whereClause, String[] whereArgs)

Added in API level 1
Convenience method for updating rows in the database.

Parameters
table//表名    the table to update in
values//要更新的字段和其属性组成的映射    a map from column names to new column values. null is a valid value that will be translated to NULL.
whereClause//where子句    the optional WHERE clause to apply when updating. Passing null will update all rows.
whereArgs//wehere条件，用来替换子句中的"?"    You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
Returns
the number of rows affected

　　3.查询操作

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Added in API level 1
Query the given table, returning a Cursor over the result set.

Parameters
table//表名    The table name to compile the query against.
columns//希望查询的列名    A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn‘t going to be used.
selection//查询子句，相当于where中的字段名    A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs//查询条件    You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy//排序字段    A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having//    A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy//排序字段    How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
limit//返回的    Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
Returns//返回值是游标对象，通过游标来对返回的记录逐行进行操作
A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

　　4.删除函数

public int delete (String table, String whereClause, String[] whereArgs)

Added in API level 1
Convenience method for deleting rows in the database.

Parameters
table//表名    the table to delete from
whereClause//where子句中字段部分    the optional WHERE clause to apply when deleting. Passing null will delete all rows.
whereArgs//where子句条件    You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
Returns
the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.

　　经过上面的几个函数我们能看出来API是将原来的sql语句中的每一段拆成一个参数，能基本满足一些简单的查询。但是如果复杂的语句怎么处理呢，所以就提供了一个原始的方法，即直接执行sql语句的函数(这个也是有局限的，不过在手机上一般不会有什么复杂的sql语句，毕竟手机也不是服务器):

public void execSQL (String sql)

Added in API level 1
Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.
//执行的只能是不要求返回数据的sql语句
It has no means to return any data (such as the number of affected rows). Instead, you‘re encouraged to use insert(String, String, ContentValues), update(String, ContentValues, String, String[]), et al, when possible.

When using enableWriteAheadLogging(), journal_mode is automatically managed by this class. So, do not set journal_mode using "PRAGMA journal_mode‘" statement if your app is using enableWriteAheadLogging()

Parameters
sql    the SQL statement to be executed. Multiple statements separated by semicolons are not supported.
Throws
SQLException//sql异常    if the SQL string is invalid

　　介绍了上面的这些函数的使用方法，接下来我们来看看一些我学习时遇到的问题:

　　如果数据库字段太多，我们往往容易写错字段名或者字段类型。那么这个时候就要删除表再重新来建表，在这个时候就会触发onUpgrade函数，数据库的version就会升级，如果我们此时继续用version=1作为参数去获取帮助类的实例，然后通过这个实例去获取数据库对象进行数据库操作，就会报错:

android.database.sqlite.SQLiteException: Can‘t downgrade database from version 2 to 1
　所以我们需要去把version也升级到2.但是这样手动升级感觉很不合适，每次要是更新一下就去升级，岂不是很麻烦，所以我想去通过sharedpreferences记住这个version，每次需要的时候去取出来，并且如果数据库发生更新，则在onUpgrade函数中更新sharedpreferences中的version。