Android中的SQLite数据库

SQLite是一个轻量级、自给自足、无服务器、零配置、关系型、事务性的 SQL 数据库引擎。下面介绍下SQLite数据库：

一、SQLite数据库简介

1.1 特性

• 轻量级：包体积小，完全配置时小于 400KiB，省略可选功能配置时可以小于250KiB
• 自给自足：不需要任何外部的依赖
• 无服务器：不需要部署服务支持
• 零配置：即不需要安装或管理
• 事务性：支持事务操作，完全兼容 ACID 的，允许从多个进程或线程安全访问
• 关系型
• ...

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

 1.2 局限性

• 仅部分支持触发器： 不支持 FOR EACH STATEMENT 触发器
• 可以添加列，不能删除、修改列，当需要修改或者删除列名时只能重新创建表
• 视图是只读的，不能执行增、删、改操作
• 数据库以一个单独的、可跨平台的文件形式存储在设备中，写操作（增、删、改）时，会将数据库文件加锁，所以只能串行进行。只有读操作可以多任务并行。
• ...

二、SQLite3数据类型

SQLite数据库中的数据类型要区分是值的类型还是列的类型。

2.1 存储类

SQLite采用的是动态数据类型，值的数据类型与值本身相关联。与之对应的是静态类型，即值的数据类型由其容器（列）决定。

SQLite有五种存储类，每个值都对应其中一种存储类。存储类比数据类型更通用。例如，INTEGER 存储类包括 7 种不同长度的整数数据类型。这在磁盘上有所不同。 但是，一旦 INTEGER 值从磁盘读取并进入内存进行处理，它们就会被转换为最通用的数据类型（8 字节有符号整数）。所以在大多数情况下，“存储类”与“数据类型”没有区别，这两个术语可以互换使用。

存储类	描述
TEXT	值是一个文本字符串
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
BLOB	值是一个 blob 数据，完全根据它的输入存储。
NULL	值是一个 NULL 值。

SQLite3 数据库中的任何列，除了INTEGER PRIMARY KEY列，都可以用于存放任何存储类的值。

2.2 Boolean 数据类型

SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）。

2.3 亲和类型

SQLite 支持列上的“类型亲和性”概念，列的亲和性由列的声明类型确定。例如：创建表时我们每个列指定一个数据类型，那么确定亲和性的规则如下：

亲和性确定规则（优先级按序号）：

1、如果声明的类型包含字符串“INT”，则为其分配 INTEGER 亲和性。

2、如果列的声明类型包含任何字符串“CHAR”、“CLOB”或“TEXT”，则该列具有 TEXT 关联。请注意，类型 VARCHAR 包含字符串“CHAR”，因此被分配了 TEXT 亲和性。

3、如果列的声明类型包含字符串“BLOB”或者如果未指定类型，则该列具有亲缘关系 BLOB。

4、如果列的声明类型包含任何字符串“REAL”、“FLOA”或“DOUB”，则该列具有 REAL 亲和性。

5、否则，亲和性为 NUMERIC。

确定列亲和性的规则顺序很重要。

下面是支持CREATE TABLE 语句和CAST 表达式使用的数据类型，按照亲和类型分组，如下表：

数据类型	亲和类型	特性（亲和力）
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	具有 TEXT 亲和性的列使用存储类 NULL、TEXT 或 BLOB 存储所有数据。如果将数值数据插入到具有 TEXT 亲和性的列中，则在存储之前将其转换为文本形式。
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	与使用 NUMERIC 亲和性的列相同。INTEGER 和 NUMERIC 亲和力之间的区别仅在CAST 表达式中很明显：表达式“CAST(4.0 AS INT)”返回整数 4，而“CAST(4.0 AS NUMERIC)”将值保留为浮点 4.0。
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
BLOB no datatype specified	BLOB	不做任何的转换，直接以该数据所属的数据类型进行存储。
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	1、如果文本分别是格式良好的整数或实数文字，则文本的存储类将转换为 INTEGER 或 REAL（按优先顺序）。 2、如果 TEXT 值是格式正确的整数文字，它太大而无法放入 64 位有符号整数，则将其转换为 REAL。对于 TEXT 和 REAL 存储类之间的转换，仅保留数字的前 15 位有效十进制数字。 3、如果 TEXT 值不是格式正确的整数或实数，则该值存储为 TEXT。 4、如果将可以精确表示为整数的浮点值插入具有 NUMERIC 亲和力的列中，则该值将转换为整数。不会尝试转换 NULL 或 BLOB 值。 5、字符串可能看起来像带有小数点和/或指数表示法的浮点文字，但只要该值可以表示为整数，NUMERIC 亲和性就会将其转换为整数。因此，字符串“3.0e+5”以整数 300000 而非浮点值 300000.0 的形式存储在具有 NUMERIC 亲和性的列中。

三、SQLite数据库命令

3.1 表操作

三个命令CREATE、DROP和ALTER，如下：

创建表：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

 删除表：

DROP TABLE COMPANY;

修改表：

//重命名表
ALTER TABLE COMPANY RENAME TO OLD_COMPANY;

//新增列
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);

3.2 数据操作

四个命令INSERT、DELETE、UPDATE和SELECT

新增数据：

//按列名指定列的值
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

//按顺序指定所有列的值
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

//用一个表数据填充另一个表数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) 
   SELECT ID,NAME,AGE,ADDRESS,SALARY 
   FROM COMPANY_BACK
   WHERE id = 6;

删除数据：

DELETE FROM COMPANY WHERE ID = 7;

更新数据：

//以key=value的方式指定列的值
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00 
WHERE ID = 6;

查询数据：

//查询表中所有列
SELECT * FROM COMPANY;

//查询表中指定列
SELECT ID, NAME, SALARY FROM COMPANY;

//其他高级用法
...

四、可视化工具

推荐使用SQLiteStudio可视化操作数据库文件。

官网：https://sqlitestudio.pl/

五、Android中数据库

Android SDK中有一个包android.database.sqlite，其中专门对SQLite数据库进行了封装，并提供了一套供android使用的API。

5.1  创建和打开数据库

SQLiteOpenHelper 类包含一组用于管理数据库的实用 API。在我们的项目中，实现SQLiteOpenHelper并重写onCreate() 和 onUpgrade()等方法。

public class FeedReaderDbHelper extends SQLiteOpenHelper {

    //定义创建表SQL
    private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + TABLE_NAME + " (" +
        _ID + " INTEGER PRIMARY KEY," +
        COLUMN_NAME_TITLE + " TEXT," +
        COLUMN_NAME_SUBTITLE + " TEXT)";

    //如果更改数据库架构，则必须增加数据库版本。
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "FeedReader.db";

    public FeedReaderDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //重写创建表的逻辑
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    //重写数据库升级逻辑
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       ...
    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }

}

当执行数据增删改查需要用到数据库对象时，仅需调用SQLiteOpenHelper 的 getWritableDatabase() 或 getReadableDatabase() 即可获得可读写或者只读数据库对象。 getWritableDatabase()

由于这些操作可能会长时间运行，因此请务必在后台线程中调用getWritableDatabase() 或 getReadableDatabase()

数据库文件存储路径：data/data/包名/databases/

5.2  增、删、改、查

API中写库操作都是通过ContentValues封装的数据。

增：

//获取可写数据库
SQLiteDatabase db = dbHelper.getWritableDatabase();

//创建ContentValues以Key-Value的形式封装数据
ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_TITLE, title);
values.put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle);

//执行insert方法，插入数据
long newRowId = db.insert(TABLE_NAME, null, values);

改：

SQLiteDatabase db = dbHelper.getWritableDatabase();

//ContentValues 封装数据
ContentValues values = new ContentValues();
values.put(COLUMN_NAME_TITLE,  "MyNewTitle");

//条件语句
String selection = FeedEntry.COLUMN_NAME_TITLE + " LIKE ?";
//条件参数，与条件语句中的?一一对应
String[] selectionArgs = { "MyOldTitle" };

//执行更新语句
int count = db.update(
    FeedReaderDbHelper.FeedEntry.TABLE_NAME,
    values,
    selection,
    selectionArgs);

 删：

//条件语句
String selection = COLUMN_NAME_TITLE + " LIKE ?";
//条件值
String[] selectionArgs = { "MyTitle" };
// 执行删除语句
int deletedRows = db.delete(FeedEntry.TABLE_NAME, selection, selectionArgs);

查：

SQLiteDatabase db = dbHelper.getReadableDatabase();

//要查询的列名
String[] projection = {
    BaseColumns._ID,
    FeedEntry.COLUMN_NAME_TITLE,
    FeedEntry.COLUMN_NAME_SUBTITLE
    };

//查询条件
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };

//按照某个列排序
String sortOrder =
   FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";

Cursor cursor = db.query(
    FeedEntry.TABLE_NAME,   // 表明
    projection,             // 要查询的列数据
    selection,              // 条件语句
    selectionArgs,          // 条件语句的值
    null,                   
    null,                   
    sortOrder               // 排序语句
    );

查询的数据都在Cursor中，操作游标的API如下：

5.3 关闭数据库链接

由于在数据库关闭时，调用 getWritableDatabase() 和 getReadableDatabase() 的成本比较高，因此只要您有可能需要访问数据库，就应保持数据库连接处于打开状态。进而就需要我们手动关闭数据库链接：

@Override
protected void onDestroy() {
    //页面销毁等场景，主动关闭数据库链接
    dbHelper.close();
    super.onDestroy();
}

六、数据库升级方案

介绍一种升级数据库常规方案，分四步：

第一步：备份数据（将原表重命名）

第二步：创建新表

第三步：还原数据（将原表中的数据查询出来并插入到新表中）

第四步：删除备份数据（将原表删除）