如何创建和使用SQLite数据库

前言

什么是SQLite？

SQLite是一款轻型数据库，它遵守ACID，能够嵌入到使用它的应用程序中。作为一个自包含的、基于文件的数据库，SQLite提供了非常出色的工具集能够处理所有类型的数据，与托管在服务器上基于进程的关系型数据库相比它的约束更少，也更易用。

当应用程序使用SQLite时，SQLite并非作为一个独立进程通过某种通信协议（例如socket）与应用程序通信，而是作为应用程序的一部分，应用程序通过调用SQLite的接口直接访问数据文件。

为什么要用 SQLite？

不需要一个单独的服务器进程或操作的系统（无服务器的）。
SQLite 不需要配置，这意味着不需要安装或管理。
一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。
SQLite 是自给自足的，这意味着不需要任何外部的依赖。
SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。
SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。
SQLite支持的数据类型
SQLite支持的数据类型包括：NULL、INTEGER、REAL、TEXT、BLOB。

SQLite的优点

基于文件整个数据库完全由磁盘上的一个.db文件构成，这使得它的可移植性非常好。
标准化尽管它看起来像一个“简化版”的数据库实现，但是SQLite确实支持SQL。它省略了一些功能（RIGHT OUTER JOIN和FOR EACH STATEMENT），但同时也增加了一些额外的功能。
非常适合开发甚至是测试在大多数应用程序的开发阶段，大部分开发人员可能都非常需要一个能够支持并发扩展的解决方案。SQLite 包含丰富的功能，所能提供的特性超乎开发所需，使用起来也非常简洁——只需要一个文件和一个C链接库。

SQLite的缺点

没有用户管理高级数据库都支持用户系统，例如管理连接对数据库和表的访问权限。鉴于SQLite的目的和性质（没有多客户端并发的高层设计），它并不包含这些功能。
缺少通过优化获得额外性能的空间还是由于设计方面的原因，无法通过优化SQLite获得大量的额外性能。这个类库非常容易调整、也非常容易使用。它并不复杂，所以从技术上无法让它变得更快，因为它已经很快了。

何时应该使用SQLite

嵌入式应用程序所有需要可移植性、不需要扩展的应用程序，例如单用户的本地应用、移动应用或者游戏。
替代磁盘访问在很多情况下，需要直接读写磁盘文件的应用程序可以切换到SQLite从而受益于SQLite提供的额外功能以及使用结构化查询语言（SQL）所带来的简便性。
测试对大部分应用程序而言没必要使用额外的进程测试业务逻辑（例如应用程序的主要目标：功能）。

何时不应该使用SQLite

多用户应用程序如果有多个客户端需要访问并使用同一个数据库，那么最好使用功能完整的关系型数据库（例如MySQL），而不是选择SQLite。
需要高写入量的应用程序写操作是SQLite的一个局限。该DBMS在同一时刻仅允许一个写操作，因而也限制了其吞吐量。

说了这么多，那如何创建和使用SQLite数据库呢？

步骤

创建一个继承 SQLiteOpenHelper 的类。
重写 onCreate() 和 onUpgrade() 方法。
在 onCreate() 中执行建表语句。
通过 getWritableDatabase() 或 getReadableDatabase() 获取数据库实例。
使用 insert()、query()、update()、delete() 操作数据。

创建类继承SQLiteOpenHelper，并重写onCreate和onUpgrade方法。

public final class DBContract {
    private DBContract() {}
    public static class UserEntity implements BaseColumns {
        public static final String TABLE_NAME = "user";
        public static final String COLUMN_NAME_UESR_ID = "_id";
        public static final String COLUMN_NAME_USER_NAME = "name";
        public static final String COLUMN_NAME_USER_AGE = "age";
    }
}

这里我一般会写一个契约类，通过ORM的思想，将数据表抽象成对象，用来记录数据表的相关信息，方便编辑和调用。

编写sql，在onCreate中通过db.execSQL(sql)执行sql。

@Override
public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + DBContract.UserEntity.TABLE_NAME + "( "
                + DBContract.UserEntity.COLUMN_NAME_UESR_ID + " INTEGER PRIMARY KEY NOT NULL, "
                + DBContract.UserEntity.COLUMN_NAME_USER_NAME + " TEXT, "
                + DBContract.UserEntity.COLUMN_NAME_USER_AGE +" INTEGER );";
        db.execSQL(sql);
}

注意sql的编写规范及空格，建议关键字大写。

在构造方法中传入数据库的名字和版本号等参数。

private static final int DB_VERSION = 1;
public DBHelper(Context c, String dbName) {
    super(c, dbName, null, DB_VERSION);
}

在onUpgrade方法中可以升级数据库。

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + DBContract.UserEntity.TABLE_NAME);
            onCreate(db);
        }
}

这里判断如果有新版本，就执行sql删除数据表再创建新的，也可以做其他操作。

实例化该类获取db对象。

mHelper = new DBHelper(context, dbName);
//获取可写的数据库对象，用于增删改
SQLiteDatabase db = mHelper.getWritableDatabase();
//获取只读的数据库对象，用于查询
SQLiteDatabase db = mHelper.getReadableDatabase();

CRUD操作。
(1) Create(创建)

public boolean insert(User user) {
    final SQLiteDatabase db = mHelper.getWritableDatabase();
    ContentValues values = new ContentValues();
    if (user.getId() != 0) values.put(DBContract.UserEntity.COLUMN_NAME_UESR_ID, user.getId());
    if (!TextUtils.isEmpty(user.getName())) values.put(DBContract.UserEntity.COLUMN_NAME_USER_NAME, user.getName());
    if (user.getAge() != 0) values.put(DBContract.UserEntity.COLUMN_NAME_USER_AGE, user.getAge());
    long _id = db.insert(DBContract.UserEntity.TABLE_NAME, null, values);
    db.close();
    return _id > 0;
}

这里主要是利用ContentValues类，通过SQLiteDatabase的insert方法，将值插入进去。当然熟悉sql的还可以利用sql来执行。

String sql = "insert into " + TAB_PERSON + " values (?,'test','1');";
try {
    getWritableDatabase().beginTransaction();
    SQLiteStatement sqLiteStatement = getReadableDatabase().compileStatement(sql);
    int count = 0;
    while (count < 100) {
        count++;
        sqLiteStatement.clearBindings();
        sqLiteStatement.bindLong(1, count);
        sqLiteStatement.executeInsert();
    }
    getWritableDatabase().setTransactionSuccessful();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    getWritableDatabase().endTransaction();
}

注意：有两点可以优化db的执行效率的，一是手动执行事务操作，二是利用SQLiteStatement预处理，戳这里http://www.jianshu.com/p/c942d5d9cf2e

(2) Retrieve(读取)

@Override
public User selectById(int userId) {
    final SQLiteDatabase db = mHelper.getReadableDatabase();
    final String selection = DBContract.UserEntity.COLUMN_NAME_UESR_ID + "=?";
    final String[] selectionArgs = new String[]{String.valueOf(userId)};
    Cursor cursor = db.query(DBContract.UserEntity.TABLE_NAME, null, selection, selectionArgs, null, null, null);
    cursor.moveToFirst();
    User user = null;
    while (cursor.moveToNext()) {
        int _id = cursor.getInt(cursor.getColumnIndex(DBContract.UserEntity.COLUMN_NAME_UESR_ID));
        String name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntity.COLUMN_NAME_USER_NAME));
        int age = cursor.getInt(cursor.getColumnIndex(DBContract.UserEntity.COLUMN_NAME_USER_AGE));
            user = new User(_id, name, age);
        }
        cursor.close();
        db.close();
        return user;
}
@Override
public List<User> selectAll() {
    final SQLiteDatabase db = mHelper.getReadableDatabase();
    List<User> list = new ArrayList<>();
    Cursor cursor = db.query(DBContract.UserEntity.TABLE_NAME, null, null, null, null, null, DBContract.UserEntity.COLUMN_NAME_UESR_ID);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        int _id = cursor.getInt(cursor.getColumnIndex(DBContract.UserEntity.COLUMN_NAME_UESR_ID));
        String name = cursor.getString(cursor.getColumnIndex(DBContract.UserEntity.COLUMN_NAME_USER_NAME));
        int age = cursor.getInt(cursor.getColumnIndex(DBContract.UserEntity.COLUMN_NAME_USER_AGE));
        list.add(new User(_id, name, age));
        cursor.moveToNext();
    }
    cursor.close();
    db.close();
    return list;
}

(3) Update(修改)

@Override
public boolean update(int userId, User user) {
    final SQLiteDatabase db = mHelper.getWritableDatabase();
    final String selection = DBContract.UserEntity.COLUMN_NAME_UESR_ID + "=?";
    final String[] selectionArgs = new String[]{String.valueOf(userId)};
    ContentValues values = new ContentValues();
    if (!TextUtils.isEmpty(user.getName())) values.put(DBContract.UserEntity.COLUMN_NAME_USER_NAME, user.getName());
    if (user.getAge() != 0) values.put(DBContract.UserEntity.COLUMN_NAME_USER_AGE, user.getAge());
    Cursor query = mHelper.getReadableDatabase().query(DBContract.UserEntity.TABLE_NAME, null, selection, selectionArgs, null, null, null);
    int _id = 0;
    if (query.getCount() > 0) {
        _id = db.update(DBContract.UserEntity.TABLE_NAME, values, selection, selectionArgs);
    }
    query.close();
    db.close();
    return _id > 0;
}

(4) Delete(删除)

@Override
public boolean delete(int userId) {
    final SQLiteDatabase db = mHelper.getWritableDatabase();
    final String selection = DBContract.UserEntity.COLUMN_NAME_UESR_ID + "=?";
    final String[] selectionArgs = new String[]{String.valueOf(userId)};
    Cursor query = mHelper.getReadableDatabase().query(DBContract.UserEntity.TABLE_NAME, null, selection, selectionArgs, null, null, null);
    int _id = 0;
    if (query.getCount() > 0) {
        _id = db.delete(DBContract.UserEntity.TABLE_NAME, selection, selectionArgs);
    }
    query.close();
    db.close();
    return _id > 0;
}

总结

SQLite在数据存储方面有着其独特的方式，轻便、高效等等都是它的特点，在开发中其api也很友好，是数据持久化的好帮手，希望本文对您平时的开发有所帮助。