Android数据储存——Sqlite

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

1.什么是Sqlite?

SQLite是轻量级的、嵌入式的、关系型数据库.

2.Sqlite储存在Android系统的哪?

数据库存储的位置在data/data/<packagename>/databases/

3.使用Sqlite基本流程

[1]创建一个类继承SQLiteOpenHelper并复写其中方法

[2]通过SQLiteOpenHelper的方法获取SQLiteDatabase对象

调用SQLiteOpenHelper的getWritableDatabase()获取一个能读能写的SQLiteDatabase对象

调用SQLiteOpenHelper的getReadableDatabase()获取一个read-only的SQLiteDatabase对象

[3]调用SQLiteDatabase的方法去执行Sql语句

4.有关SQLiteOpenHelper

java.lang.Object
<-android.database.sqlite.SQLiteOpenHelper

(1)继承SQLiteOpenHelper

SQLiteOpenHelper是一个abstract class，它的构造函数:

SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler)

各个参数的意义：

context    Context上下文
name    数据库名称
factory    Cursor的工厂类，null则使用默认Cursor
version    数据库版本号
errorHandler    处理sqlite错误的Handler

继承SQLiteOpenHelper必须要Override的方法：

abstract void onCreate(SQLiteDatabase db) // Called when the database is created for the first time.(当数据库第一次创建时会被调用)
abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) // Called when the database needs to be upgraded.(当数据库版本更新时被调用)

(2)获取SQLiteDatabase对象

SQLiteDatabase getReadableDatabase() // 创建或者打开一个查询数据库
SQLiteDatabase getWritableDatabase() // 创建或者打开一个可写数据库

(3)关闭数据库

SQLiteOpenHelper.close()

5.SQLiteDatabase的增删改查

在进行增删改查的操作之前让我们先看一下一些相关的类:

ContentValues

它其实就是一个<key,value>的map

Cursor

n. 光标；（计算尺的）[计] 游标，指针

Cursor 是每行的集合，也就是说其实它是存储每行的每个值的<key,value>的map。

如何从集合中把每行数据取出来？

e.g:

while(cur.moveToNext())
{
  int nameColumn = cur.getColumnIndex("name");
    int phoneColumn = cur.getColumnIndex("number");
    String name = cur.getString(nameColumn);
    String phoneNumber = cur.getString(phoneColumn);
}

(1)Insert

long     insert(String table, String nullColumnHack, ContentValues values)
long     insertOrThrow(String table, String nullColumnHack, ContentValues values)
long     insertWithOnConflict(String table, String nullColumnHack, ContentValues initialValues, int conflictAlgorithm)

首先看一下这三个insert方法的区别:

insert():

如果成功则返回insert的行的行号，反之返回-1

insertOrThrow():

和insert()大抵是差不多的，只是当exception发生时它会throw SQLException

insertWithOnConflict():

依然大抵和insert()差不多，只是多了一个冲突处理算法，也就是当insert出现冲突(比如insert的行的主键和已有行一样)会采取conflictAlgorithm所定义的冲突解决算法去解决。

再接着，看看各个参数：

table：进行insert的Table

nullColumnHack：当values参数为空或者里面没有内容的时候，我们insert是会失败的（底层数据库不允许插入一个空行），为了防止这种情况，我们要在这里指定一个列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null，然后再向数据库中插入。（更深成层次的原因请阅读[http://blog.csdn.net/chenzheng_java/article/details/6226572]）

values：insert的行的<ColumnName,Vaule>的ContentValues。

conflictAlgorithm：解决冲突的算法（更具体的使用不是很明了）

(2)Delete

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

偷懒引用Android Developer上：

Parameters
table    the table to delete from（要进行删除的Table的名字）
whereClause    the optional WHERE clause to apply when deleting. Passing null will delete all rows.（Sql的Where的语句部分，注意不需要带"Where"）
whereArgs    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.(成功的话返回Delete语句删除的行数，反之0)

关于whereClause和whereArgs的用法：

首先，我们要明确：其实使用whereClause就可以实现所有的功能，如

whereClause="username=\"acid\" and password=\"123\"";

而whereArgs是为了防止SQL注入所采取的方法（具体如何规避SQL注入，也求大神解答）。具体使用方法:

whereClause="username=? and password=?";
whereArgs=["acid","123"];

whereClause中的"?"会依次由whereArgs数组中的String填上。

(3)Update

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

略过吧，在上面的insert和delete中都有涉及。

(4)Query

Cursor     query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Cursor     query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit, CancellationSignal cancellationSignal)
Cursor     query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
Cursor     query(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Cursor     queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit, CancellationSignal cancellationSignal)
Cursor     queryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
Cursor     rawQuery(String sql, String[] selectionArgs, CancellationSignal cancellationSignal)
Cursor     rawQuery(String sql, String[] selectionArgs)
Cursor     rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable)
Cursor     rawQueryWithFactory(SQLiteDatabase.CursorFactory cursorFactory, String sql, String[] selectionArgs, String editTable, CancellationSignal cancellationSignal)

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     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.(where子句，声明要返回的行的要求，如果为空则返回表的所有行。)
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.( where子句对应的条件值)
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.(having条件，若为空则返回全部（不建议）)
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.(限制返回的记录的条数，为空则不限制)
distinct    true if you want each row to be unique, false otherwise.
Returns
指向Query结果集的Cursor。

(针对于Query还会继续补充)。