SQLite.jpg

SQLite简介

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，专为数据库操作而设计，用于存取数据以及查询、更新和管理关系数据库系统。同时，sql也通常是数据库脚本文件的扩展名。

SQLite是一个实现了自给自足的、无服务器的、零配置的、事务性的SQL数据库引擎，并且是一个开源项目。Android上自带SQLite，因此是Android项目上常用的数据库。

对大部分数据库来说，大体可以看做是一个较大的Excel文件。数据库中TABLE（表）的概念，对应Excel中Sheet；数据库中row（行）、column（列）的概念，对应Excel的行（被标记为1234的那些）和列（被标记为ABCD的那些）的概念。

数据库常用的六个操作，建表、丢表（删除表）、增、删、改、查，其SQL语句如下所示。

建表

CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… );

CREATE TABLE IF NOT EXISTS 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… );

丢表

DROP TABLE 数据表名称;

DROP TABLE IF EXISTS 数据表名称;

增

INSERT INTO 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …);

INSERT INTO 目标数据表 SELECT * FROM 源数据表;

删

DELETE FROM 数据表 WHERE 条件表达式;

DELETE FROM 数据表;

改

UPDATE 数据表 SET 字段名=字段值 WHERE 条件表达式;

UPDATE 数据表 SET 字段1=值1,字段2=值2 …… 字段n=值n WHERE 条件表达式;

查

SELECT * FROM 数据表 WHERE 字段名=字段值 ORDER BY 字段名 [DESC];

SELECT * FROM 数据表 WHERE 字段名 LIKE '%字段值%' ORDER BY 字段名 [DESC];

SELECT TOP 10 * from 数据表 WHERE 字段名 ORDER BY 字段名 [DESC];

SELECT * FROM 数据表 WHERE 字段名 IN ('值1','值2','值3');

SELECT * FROM 数据表 WHERE 字段名 BETWEEN 值1 AND 值2;

Android常用数据库功能代码块

在Android上，如果用Java的方式来使用SQLite，同样需要对数据库有一定了解。

常用代码块示例如下。

建表

建表，由于要定制其各列的字段名及数据类型，所以仍然只能使用原始的SQL语句，通过SQLiteDatabase.execSQL(String)来执行。

        db.execSQL("CREATE TABLE IF NOT EXISTS "
                + TABLE_NAME + " ( "
                + KEY1 + "类型1(长度)" + ", "
                + KEY2+ "类型2(长度)"
                + " );");

丢表

丢表（此为英文直译，通常翻译为删除表），也是执行SQL。

        db.execSQL("DROP TABLE IF NOT EXISTS " + TABLE_NAME);

另外，删除数据库是通过Context.deleteDatabase(String name)来执行。而删除一个表中的所有数据，则可以用SQLiteDatabase.delete(String, String, String[])。

        // Delete all data in the table named $TABLE_NAME
        db.delete(TABLE_NAME, null, null);

增

数据库的增和删，都是针对行的。

        ContentValues cv = new ContentValues();
        cv.put(key1, value1);
        cv.put(key2, value2);

        SQLiteDatabase db = getWritableDatabase();
        db.insert(TABLE_NAME, null, cv);

上面代码的意义是，给TABLE_NAME这个表中增加一行。在这一行中，把value1放到key1这一列，把value2放到key2这一列。

删

删除虽然不需要执行SQL，但是也要用类似SQL的方式来指定删除的位置。

        String whereClause = key1 + "=?";
        String[] whereArgs = new String[]{String.valueOf(value1)};
        db.delete(TABLE_NAME, whereClause, whereArgs);

以上代码中，whereClause这个位置，填入SQL的WHERE后面的选择方式；whereArgs则是替换whereClause中的问号?，有几个问号，这个String数组中就应该给出几个参数。

通常是选择=作为判断。上面代码的意义是，删除key1这一列中，值为value1的所有行。

改

和删除类似，指定位置要用到类似SQL的方式。SQLiteDatabase的update()比delete()多了一个ContentValues参数，表示那一行需要更新的值。

        ContentValues values = new ContentValues();
        values.put(key1, value2);

        SQLiteDatabase db = getWritableDatabase();
        String whereClause = key1 + "=?";
        String[] whereArgs = new String[]{String.valueOf(value1)};
        db.update(TABLE_NAME, values, whereClause, whereArgs);

以上代码的意义是，把key1这一列等于value1的所有行，其key1的位置都替换为value2。

查

这是参数最繁琐的一个操作，SQLiteDatabase.query()一共7个参数，是糟糕的代码设计典范。而且部分操作在大部分情况下，都会被设为null。

首先拿一个Cursor出来。

        SQLiteDatabase db = getWritableDatabase();
        // Get all columns
        try (Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null)) {
            // Deal with the cursor.
        }

只传入表名，其它参数都给null，这样可以把整个表的数据都读出来。

其实这个Cursor并非是一个游标一样的东西，而更像是数据库的一个片段。在数据库比较大时，每次查询都读整张表，会造成非常大的IO压力。因此，若非必要，读取时只应该选取需要的片段。

query()的参数如下：

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

其中，columns代表需要被选取的列，以字符数组的形式传递；selection与selectionArgs，类似于whereClause和whereArgs，是在SQL的WHERE关键字后的语句；groupBy是SQL的GROUP BY后面的语句；having是SQL的HAVING后面的语句；orderBy是SQL的ORDER BY后面的语句。

在拿到Cursor后，可以用类似迭代器（Iterator）的方式去使用它。比如，遍历整个Cursor的某一列的代码如下。

        cursor.moveToFirst();
        do {
            int column = cursor.getColumnIndex(KEY1);
            String value = cursor.getString(column);
            // Do sth with the `value`.
        } while (cursor.moveToNext());

以上代码，如果放到之前// Deal with the cursor.的位置，就可以遍历整张表。

这里try () {}是Java v1.7新加入的try with resource用法，会确保cursor.close()被调用。

Android相关类介绍

在Android上使用SQLite，通过SQLiteDatabase.execSQL(String)接口，可以把SQL的语句转换为字符串，传入其中执行。这样虽然简单，但是难以调试。

另一方面也可以使用它的各种包装类。由于各种类的层次关系复杂，网络教程少，这种方法难以使用。

这些功能包装类基本上都在以下位置：

import android.database.*;
import android.database.sqlite.*;

下面做出一些介绍。

SQLiteOpenHelper

import android.database.sqlite.SQLiteOpenHelper;

这是一个抽象类，需要被继承后使用。构造函数，和两个Method一定要被override。

    public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {
        this(context, name, factory, version, null);
    }

    /**
     * Create a helper object to create, open, and/or manage a database.
     * The database is not actually created or opened until one of
     * {@link #getWritableDatabase} or {@link #getReadableDatabase} is called.
     *
     * <p>Accepts input param: a concrete instance of {@link DatabaseErrorHandler} to be
     * used to handle corruption when sqlite reports database corruption.</p>
     *
     * @param context to use to open or create the database
     * @param name of the database file, or null for an in-memory database
     * @param factory to use for creating cursor objects, or null for the default
     * @param version number of the database (starting at 1); if the database is older,
     *     {@link #onUpgrade} will be used to upgrade the database; if the database is
     *     newer, {@link #onDowngrade} will be used to downgrade the database
     * @param errorHandler the {@link DatabaseErrorHandler} to be used when sqlite reports database
     * corruption, or null to use the default error handler.
     */
    public SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version,
            DatabaseErrorHandler errorHandler) {
        if (version < 1) throw new IllegalArgumentException("Version must be >= 1, was " + version);

        mContext = context;
        mName = name;
        mFactory = factory;
        mNewVersion = version;
        mErrorHandler = errorHandler;
    }

    /**
     * Called when the database is created for the first time. This is where the
     * creation of tables and the initial population of the tables should happen.
     *
     * @param db The database.
     */
    public abstract void onCreate(SQLiteDatabase db);

    /**
     * Called when the database needs to be upgraded. The implementation
     * should use this method to drop tables, add tables, or do anything else it
     * needs to upgrade to the new schema version.
     * <p/>
     * <p>
     * The SQLite ALTER TABLE documentation can be found
     * <a href="http://sqlite.org/lang_altertable.html">here</a>. If you add new columns
     * you can use ALTER TABLE to insert them into a live table. If you rename or remove columns
     * you can use ALTER TABLE to rename the old table, then create the new table and then
     * populate the new table with the contents of the old table.
     * </p><p>
     * This method executes within a transaction.  If an exception is thrown, all changes
     * will automatically be rolled back.
     * </p>
     *
     * @param db         The database.
     * @param oldVersion The old database version.
     * @param newVersion The new database version.
     */
    public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);

在onCreate()中，通常进行建表（table）的工作；onUpgrade()中，往往处理一些升级操作，比如要换表（先DROP再CREATE）。这两个回调的触发，与构造函数中传递的version相关。

以下代码块是上述两个回调，加上onDowngrade()，根据version不同的调用情况。另外，onConfigure()和onOpen()的回调情况也在其中。

            onConfigure(db);

            db.beginTransaction();
            try {
                if (version == 0) {
                    onCreate(db);
                } else {
                    if (version > mNewVersion) {
                        onDowngrade(db, version, mNewVersion);
                    } else {
                        onUpgrade(db, version, mNewVersion);
                    }
                }
                db.setVersion(mNewVersion);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }

            onOpen(db);

通常，对于一个自用的小数据库，在不需要更新表的情况下，version可以万年不变。但是，为了预留在未来调整数据库结构的可操作空间，用SQLiteOpenHelper进行一层封装总是没错。

SQLiteDatabase

import android.database.sqlite.SQLiteDatabase;

SQLiteDatabase是Android对SQLite数据库的包装类，可以实现其增删改查的主要功能。其开关，通常由SQLiteOpenHelper来负责。

常用接口如下，基本实现了数据库的常用功能。

    public long insert(String table, String nullColumnHack, ContentValues values);
    public long replace(String table, String nullColumnHack, ContentValues initialValues);
    public int delete(String table, String whereClause, String[] whereArgs);
    public int update(String table, ContentValues values, String whereClause, String[] whereArgs);
    public Cursor rawQuery(String sql, String[] selectionArgs);
    public Cursor query(boolean distinct, String table, String[] columns,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit);

    public void execSQL(String sql) throws SQLException;
    public void execSQL(String sql, Object[] bindArgs) throws SQLException;

    public int getVersion();
    public void setVersion(int version);
    public long getMaximumSize();
    public long getPageSize();
    public void setPageSize(long numBytes);
    public static String findEditTable(String tables);
    public SQLiteStatement compileStatement(String sql) throws SQLException;
    public boolean isReadOnly();
    public boolean isInMemoryDatabase();
    public boolean isOpen();
    public boolean needUpgrade(int newVersion);
    public final String getPath();
    public void setLocale(Locale locale);
    public long setMaximumSize(long numBytes);

其常用的增删改查功能，接口实现的内部已经对引用进行了获取与释放，所以通常不需要手动close()。

DatabaseErrorHandler

这是一个回调接口，在SQLiteOpenHelper的构造函数中传入，通过实现void onCorruption(SQLiteDatabase dbObj);来使用。

/**
 * An interface to let apps define an action to take when database corruption is detected.
 */
public interface DatabaseErrorHandler {

    /**
     * The method invoked when database corruption is detected.
     * @param dbObj the {@link SQLiteDatabase} object representing the database on which corruption
     * is detected.
     */
    void onCorruption(SQLiteDatabase dbObj);
}

通常情况下，不需要使用这个回调，设置为null即可。

ContentValues

import android.content.ContentValues;

基本上可以看做是一个HashMap的包装类，所不同的是，实现了Paracable接口，多了一些Method。

public final class ContentValues implements Parcelable {
    public static final String TAG = "ContentValues";

    /** Holds the actual values */
    private HashMap<String, Object> mValues;

    /**
     * Creates an empty set of values using the default initial size
     */
    public ContentValues() {
        // Choosing a default size of 8 based on analysis of typical
        // consumption by applications.
        mValues = new HashMap<String, Object>(8);
    }

    // Omitted...
}

Cursor

import android.database.Cursor;

Cursor（游标）是SQLiteDatabase.query()后返回的一个对象，也是数据库查询的一个通用概念。

由于数据库往往是一个二维表格的形式，不能直接给个键就返回值，所以在访问时需要一个中间类进行细微的定位。Cursor会默认停留在某一个row（行），同时可访问这个row的任意一个column（列）；但如果要访问另一个row的某column，只能先换行。move*()系列操作就是换行用的，而get*()系列操作则是按行取列用的。

Cursor的主要接口如下：

    int getCount();
    int getPosition();
    boolean move(int offset);
    boolean moveToPosition(int position);
    boolean moveToFirst();
    boolean moveToLast();
    boolean moveToNext();
    boolean moveToPrevious();
    boolean isFirst();
    boolean isLast();
    boolean isBeforeFirst();
    boolean isAfterLast();
    int getColumnIndex(String columnName);
    int getColumnIndexOrThrow(String columnName) throws IllegalArgumentException;
    String getColumnName(int columnIndex);
    String[] getColumnNames();
    int getColumnCount();
    byte[] getBlob(int columnIndex);
    String getString(int columnIndex);
    void copyStringToBuffer(int columnIndex, CharArrayBuffer buffer);
    short getShort(int columnIndex);
    int getInt(int columnIndex);
    long getLong(int columnIndex);
    float getFloat(int columnIndex);
    double getDouble(int columnIndex);
    int getType(int columnIndex);
    boolean isNull(int columnIndex);
    void deactivate();
    boolean requery();
    void close();
    boolean isClosed();
    void registerContentObserver(ContentObserver observer);
    void unregisterContentObserver(ContentObserver observer);
    void registerDataSetObserver(DataSetObserver observer);
    void unregisterDataSetObserver(DataSetObserver observer);
    void setNotificationUri(ContentResolver cr, Uri uri);
    Uri getNotificationUri();
    boolean getWantsAllOnMoveCalls();
    void setExtras(Bundle extras);
    Bundle getExtras();
    Bundle respond(Bundle extras);

Cursor的很多接口都可能抛出异常，并且用完后一定要主动调用close()，所以通常用try catch finally、或try with resource包裹其使用操作。

CursorLoader

import android.content.CursorLoader;

Android自3.0开始提供Loader机制，而CursorLoader是其中一例。

CursorLoader实现了异步数据库查询，避免了大量同步查询的主线程阻塞。

主要用法是，在构造函数设置Cursor.query()用的各种参数，或者在空构造函数后一一设置，然后用loadInBackground()来获取Cursor。

    /* Runs on a worker thread */
    @Override
    public Cursor loadInBackground() {
        synchronized (this) {
            if (isLoadInBackgroundCanceled()) {
                throw new OperationCanceledException();
            }
            mCancellationSignal = new CancellationSignal();
        }
        try {
            Cursor cursor = getContext().getContentResolver().query(mUri, mProjection, mSelection,
                    mSelectionArgs, mSortOrder, mCancellationSignal);
            if (cursor != null) {
                try {
                    // Ensure the cursor window is filled.
                    cursor.getCount();
                    cursor.registerContentObserver(mObserver);
                } catch (RuntimeException ex) {
                    cursor.close();
                    throw ex;
                }
            }
            return cursor;
        } finally {
            synchronized (this) {
                mCancellationSignal = null;
            }
        }
    }

DatabaseUtils

import android.database.DatabaseUtils;

这是一个工具类，里面有很多常用的数据库操作，比如Cursor转ContentValues等。

使用前，最好细读对应源码。