Android数据保存之SQLiteDatabase

闲话少聊，这篇文章主要参考了Google的官方文档

转载请注明出处
[我的博客]http://www.lostbug.com

首先要创建SQLiteDatabase

这段代码段定义了单个表格的表格名称和列名称

public final class FeedReaderContract {
    // To prevent someone from accidentally instantiating the contract class,
    // give it an empty constructor.
    public FeedReaderContract() {}

    /* Inner class that defines the table contents */
    public static abstract class FeedEntry implements BaseColumns {
        public static final String TABLE_NAME = "entry";
        public static final String COLUMN_NAME_ENTRY_ID = "entryid";
        public static final String COLUMN_NAME_TITLE = "title";
        public static final String COLUMN_NAME_SUBTITLE = "subtitle";
        ...
    }
}

Note: By implementing the BaseColumns interface, your inner class can inherit a primary key field called _ID that some Android classes such as cursor adaptors will expect it to have. It’s not required, but this can help your database work harmoniously with the Android framework.

Create a Database Using a SQL Helper，通过SQL Helper来创建Database

Once you have defined how your database looks, you should implement methods that create and maintain the database and tables. Here are some typical statements that create and delete a table:
一旦你确定了你要建立的数据库模式，你需要继承方法来创建和维护你的数据库表格，下面是创建和删除数据库表格的典型语句：

private static final String TEXT_TYPE = " TEXT";
private static final String COMMA_SEP = ",";
private static final String SQL_CREATE_ENTRIES =
    "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
    FeedEntry._ID + " INTEGER PRIMARY KEY," +
    FeedEntry.COLUMN_NAME_ENTRY_ID + TEXT_TYPE + COMMA_SEP +
    FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP +
    ... // Any other options for the CREATE command
    " )";

private static final String SQL_DELETE_ENTRIES =
    "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;

SQLiteOpenHelper 类中有一组有用的 API。当您使用此类获取对您数据库的引用时，系统将只在需要之时而不是 应用启动过程中执行可能长期运行的操作：创建和更新数据库。 您只需调用 getWritableDatabase() 或 getReadableDatabase()。

注意：由于它们可能长期运行，因此请确保您在后台线程中调用 getWritableDatabase() 或 getReadableDatabase() ， 比如使用 AsyncTask 或 IntentService。

创建一个继承SQLiteOpenHelper的类

public class FeedReaderDbHelper extends SQLiteOpenHelper {
    // If you change the database schema, you must increment the database version.
    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) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }
}

要访问您的数据库，请实例化 的子类：

FeedReaderDbHelper mDbHelper = new FeedReaderDbHelper(getContext());

通过SQLiteOpenHelper的getWritableDatabase()或者getReadableDatabase()方法获取数据库的实例：

SQLiteDatabase db = mFeedReaderDbHelper.getWritableDatabase();//可修改
SQLiteDatabase db = mFeedReaderDbHelper.getReadableDatabase();//只读

接下来就是数据库的维护处理了

数据库的相关操作无非读、写、删、改四板斧而已

• 写 insert

// Gets the data repository in write mode
SQLiteDatabase db = mDbHelper.getWritableDatabase();

// Create a new map of values, where column names are the keys
ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_ENTRY_ID, id);
values.put(FeedEntry.COLUMN_NAME_TITLE, title);
values.put(FeedEntry.COLUMN_NAME_CONTENT, content);

// Insert the new row, returning the primary key value of the new row
long newRowId;
newRowId = db.insert(
         FeedEntry.TABLE_NAME,//数据库名称
         FeedEntry.COLUMN_NAME_NULLABLE,//无数据时，默认填充值
         values);//填充值

• 改 update

SQLiteDatabase db = mDbHelper.getReadableDatabase();

// New value for one column
ContentValues values = new ContentValues();
values.put(FeedEntry.COLUMN_NAME_TITLE, title);

// Which row to update, based on the ID
String selection = FeedEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
String[] selectionArgs = { String.valueOf(rowId) };

int count = db.update(
    FeedReaderDbHelper.FeedEntry.TABLE_NAME,//数据库名称
    values,要更新的数据
    selection,//选中列（个人理解）
    selectionArgs);//更新行在这列中的内容

• 删 delete

// Define 'where' part of query.
String selection = FeedEntry.COLUMN_NAME_ENTRY_ID + " LIKE ?";
// Specify arguments in placeholder order.
String[] selectionArgs = { String.valueOf(rowId) };
// Issue SQL statement.
db.delete(table_name, //数据库名称
 selection,//选中列
 selectionArgs);//删除行在这列中的内容

• 读 query

SQLiteDatabase db = mDbHelper.getReadableDatabase();

// Define a projection that specifies which columns from the database
// you will actually use after this query.
String[] projection = {
    FeedEntry._ID,
    FeedEntry.COLUMN_NAME_TITLE,
    FeedEntry.COLUMN_NAME_UPDATED,
    ...
    };

// How you want the results sorted in the resulting Cursor
String sortOrder =
    FeedEntry.COLUMN_NAME_UPDATED + " DESC";

Cursor c = db.query(
    FeedEntry.TABLE_NAME,  // The table to query 数据库名称
    projection,            // The columns to return 用于指定去查询哪几列
    selection,             // The columns for the WHERE clause
    selectionArgs,         // The values for the WHERE clause  查询某一行或某几行的数据
    null,                  // don't group the rows 指定需要去group by 的列
    null,                  // don't filter by row groups 对group by之后的数据进行进一步的过滤
    sortOrder              // The sort order 指定查询结果的排序方式
    ); 

cursor.moveToFirst();
long itemId = cursor.getLong(
    cursor.getColumnIndexOrThrow(FeedEntry._ID)
);

到此为止吧，不写了