SQLite
游标(Cursor)相当于指向底层数据中结果集的指针，而不是提取和返回结果值的副本，是在结果集中对位置(行)进行控制的管理方式。
moveToFirst：把游标移动到查询结果的第一行
moveToNext：把游标移动到下一行
moveToPrevious：把游标移动到前一行
getCount：返回结果集中的行数
getColumnIndexOrThrow：返回具有指定名称的列的索引
getColumnName：返回指定索引的列的名称
getColumnNames：返回当前游标中的所有列名的字符串数组
moveToPosition：移动到特定的行的游标
getPosition：返回当前的游标位置
Adapter类负责管理数据库交互（数据模型，更具对象属性设置增删改查的逻辑），使用SQLiteOpenHelper的扩展类协助创建数据表，获取SQLiteDatabase对象进行增删改查。
扩展SQLiteOpenHelper，由扩展类完成数据表的创建，数据库的更新。
package com.paad.todolist; import java.util.Date; import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log; public class ToDoDBAdapter {
private static final String DATABASE_NAME = "todoList.db";
private static final String DATABASE_TABLE = "todoItems";
private static final int DATABASE_VERSION = 1; public static final String KEY_ID = "_id";
public static final String KEY_TASK = "task";
public static final String KEY_CREATION_DATE = "creation_date"; private SQLiteDatabase db;
private final Context context;
private toDoDBOpenHelper dbHelper; public ToDoDBAdapter(Context _context) {
this.context = _context;
dbHelper = new toDoDBOpenHelper(context, DATABASE_NAME,
null, DATABASE_VERSION);
} public void close() {
db.close();
} public void open() throws SQLiteException {
try {
db = dbHelper.getWritableDatabase();
} catch (SQLiteException ex) {
db = dbHelper.getReadableDatabase();
}
} //Insert a new task
public long insertTask(ToDoItem _task) {
// Create a new row of values to insert.
ContentValues newTaskValues = new ContentValues();
// Assign values for each row.
newTaskValues.put(KEY_TASK, _task.getTask());
newTaskValues.put(KEY_CREATION_DATE, _task.getCreated().getTime());
// Insert the row.
return db.insert(DATABASE_TABLE, null, newTaskValues);
} // Remove a task based on its index
public boolean removeTask(long _rowIndex) {
return db.delete(DATABASE_TABLE, KEY_ID + "=" + _rowIndex, null) > 0;
} // Update a task
public boolean updateTask(long _rowIndex, String _task) {
ContentValues newValue = new ContentValues();
newValue.put(KEY_TASK, _task);
return db.update(DATABASE_TABLE, newValue, KEY_ID + "=" + _rowIndex, null) > 0;
} public Cursor getAllToDoItemsCursor() {
return db.query(DATABASE_TABLE,
new String[] { KEY_ID, KEY_TASK, KEY_CREATION_DATE},
null, null, null, null, null);
} public Cursor setCursorToToDoItem(long _rowIndex) throws SQLException {
Cursor result = db.query(true, DATABASE_TABLE,
new String[] {KEY_ID, KEY_TASK},
KEY_ID + "=" + _rowIndex, null, null, null,
null, null);
if ((result.getCount() == 0) || !result.moveToFirst()) {
throw new SQLException("No to do items found for row: " + _rowIndex);
}
return result;
} public ToDoItem getToDoItem(long _rowIndex) throws SQLException {
Cursor cursor = db.query(true, DATABASE_TABLE,
new String[] {KEY_ID, KEY_TASK},
KEY_ID + "=" + _rowIndex, null, null, null,
null, null);
if ((cursor.getCount() == 0) || !cursor.moveToFirst()) {
throw new SQLException("No to do item found for row: " + _rowIndex);
} String task = cursor.getString(cursor.getColumnIndex(KEY_TASK));
long created = cursor.getLong(cursor.getColumnIndex(KEY_CREATION_DATE)); ToDoItem result = new ToDoItem(task, new Date(created));
return result;
} private static class toDoDBOpenHelper extends SQLiteOpenHelper { public toDoDBOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
} // SQL Statement to create a new database.
private static final String DATABASE_CREATE = "create table " +
DATABASE_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " +
KEY_TASK + " text not null, " + KEY_CREATION_DATE + " long);"; @Override
public void onCreate(SQLiteDatabase _db) {
_db.execSQL(DATABASE_CREATE);
} @Override
public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) {
Log.w("TaskDBAdapter", "Upgrading from version " +
_oldVersion + " to " +
_newVersion + ", which will destroy all old data"); // Drop the old table.
_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
// Create a new one.
onCreate(_db);
}
}
}
Content
ContentProvider可使用SQLiteOpenHelper辅助操作数据（用数据库持久化）。由ContentProvider的扩展类完成数据交互，ContentProvider扩展类的对象则是通过获取到应用上下文中的ContentResolver对象，根据其方法及URI，由系统内部执行ContentProvider扩展类对象中重写的方法。
ContentProvider扩展类的实现：
public class EarthquakeProvider extends ContentProvider {
public static final Uri CONTENT_URI = Uri.parse("content://com.paad.provider.earthquake/earthquakes");
@Override
public boolean onCreate() {
Context context = getContext();
earthquakeDatabaseHelper dbHelper = new earthquakeDatabaseHelper(context,
DATABASE_NAME,
null,
DATABASE_VERSION);
earthquakeDB = dbHelper.getWritableDatabase();
return (earthquakeDB == null) ? false : true;
}
@Override
public Cursor query(Uri uri,
String[] projection,
String selection,
String[] selectionArgs,
String sort) {
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
qb.setTables(EARTHQUAKE_TABLE);
// If this is a row query, limit the result set to the passed in row.
switch (uriMatcher.match(uri)) {
case QUAKE_ID: qb.appendWhere(KEY_ID + "=" + uri.getPathSegments().get(1));
break;
default : break;
}
// If no sort order is specified sort by date / time
String orderBy;
if (TextUtils.isEmpty(sort)) {
orderBy = KEY_DATE;
} else {
orderBy = sort;
}
// Apply the query to the underlying database.
Cursor c = qb.query(earthquakeDB,
projection,
selection, selectionArgs,
null, null,
orderBy);
// Register the contexts ContentResolver to be notified if
// the cursor result set changes.
c.setNotificationUri(getContext().getContentResolver(), uri);
// Return a cursor to the query result.
return c;
}
@Override
public Uri insert(Uri _uri, ContentValues _initialValues) {
// Insert the new row, will return the row number if
// successful.
long rowID = earthquakeDB.insert(EARTHQUAKE_TABLE, "quake", _initialValues);
// Return a URI to the newly inserted row on success.
if (rowID > 0) {
Uri uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
getContext().getContentResolver().notifyChange(uri, null);
return uri;
}
throw new SQLException("Failed to insert row into " + _uri);
}
@Override
public int delete(Uri uri, String where, String[] whereArgs) {
int count;
switch (uriMatcher.match(uri)) {
case QUAKES:
count = earthquakeDB.delete(EARTHQUAKE_TABLE, where, whereArgs);
break;
case QUAKE_ID:
String segment = uri.getPathSegments().get(1);
count = earthquakeDB.delete(EARTHQUAKE_TABLE, KEY_ID + "="
+ segment
+ (!TextUtils.isEmpty(where) ? " AND ("
+ where + ')' : ""), whereArgs);
break;
default: throw new IllegalArgumentException("Unsupported URI: " + uri);
}
getContext().getContentResolver().notifyChange(uri, null);
return count;
}
@Override
public int update(Uri uri, ContentValues values, String where, String[] whereArgs) {
int count;
switch (uriMatcher.match(uri)) {
case QUAKES: count = earthquakeDB.update(EARTHQUAKE_TABLE, values,
where, whereArgs);
break;
case QUAKE_ID: String segment = uri.getPathSegments().get(1);
count = earthquakeDB.update(EARTHQUAKE_TABLE, values, KEY_ID
+ "=" + segment
+ (!TextUtils.isEmpty(where) ? " AND ("
+ where + ')' : ""), whereArgs);
break;
default: throw new IllegalArgumentException("Unknown URI " + uri);
}
getContext().getContentResolver().notifyChange(uri, null);
return count;
}
@Override
public String getType(Uri uri) {
switch (uriMatcher.match(uri)) {
case QUAKES: return "vnd.android.cursor.dir/vnd.paad.earthquake";
case QUAKE_ID: return "vnd.android.cursor.item/vnd.paad.earthquake";
default: throw new IllegalArgumentException("Unsupported URI: " + uri);
}
}
// Create the constants used to differentiate between the different URI
// requests.
private static final int QUAKES = 1;
private static final int QUAKE_ID = 2;
private static final UriMatcher uriMatcher;
// Allocate the UriMatcher object, where a URI ending in 'earthquakes' will
// correspond to a request for all earthquakes, and 'earthquakes' with a
// trailing '/[rowID]' will represent a single earthquake row.
static {
uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
uriMatcher.addURI("com.paad.provider.Earthquake", "earthquakes", QUAKES);
uriMatcher.addURI("com.paad.provider.Earthquake", "earthquakes/#", QUAKE_ID);
}
//The underlying database
private SQLiteDatabase earthquakeDB;
private static final String TAG = "EarthquakeProvider";
private static final String DATABASE_NAME = "earthquakes.db";
private static final int DATABASE_VERSION = 1;
private static final String EARTHQUAKE_TABLE = "earthquakes";
// Column Names
public static final String KEY_ID = "_id";
public static final String KEY_DATE = "date";
public static final String KEY_DETAILS = "details";
public static final String KEY_LOCATION_LAT = "latitude";
public static final String KEY_LOCATION_LNG = "longitude";
public static final String KEY_MAGNITUDE = "magnitude";
public static final String KEY_LINK = "link";
// Column indexes
public static final int DATE_COLUMN = 1;
public static final int DETAILS_COLUMN = 2;
public static final int LONGITUDE_COLUMN = 3;
public static final int LATITUDE_COLUMN = 4;
public static final int MAGNITUDE_COLUMN = 5;
public static final int LINK_COLUMN = 6;
// Helper class for opening, creating, and managing database version control
private static class earthquakeDatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_CREATE =
"create table " + EARTHQUAKE_TABLE + " ("
+ KEY_ID + " integer primary key autoincrement, "
+ KEY_DATE + " INTEGER, "
+ KEY_DETAILS + " TEXT, "
+ KEY_LOCATION_LAT + " FLOAT, "
+ KEY_LOCATION_LNG + " FLOAT, "
+ KEY_MAGNITUDE + " FLOAT), "
+ KEY_LINK + " TEXT);";
public earthquakeDatabaseHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + EARTHQUAKE_TABLE);
onCreate(db);
}
}
}
ContentProvider扩展类的使用：
private void loadQuakesFromProvider() {
// Clear the existing earthquake array
earthquakes.clear();
ContentResolver cr = getContentResolver();
// Return all the saved earthquakes
Cursor c = cr.query(EarthquakeProvider.CONTENT_URI, null, null, null, null);
if (c.moveToFirst()) {
do {
// Extract the quake details.
Long datems = c.getLong(EarthquakeProvider.DATE_COLUMN);
String details = c.getString(EarthquakeProvider.DETAILS_COLUMN);
Float lat = c.getFloat(EarthquakeProvider.LATITUDE_COLUMN);
Float lng = c.getFloat(EarthquakeProvider.LONGITUDE_COLUMN);
Double mag = c.getDouble(EarthquakeProvider.MAGNITUDE_COLUMN);
String link = c.getString(EarthquakeProvider.LINK_COLUMN);
Location location = new Location("dummy");
location.setLongitude(lng);
location.setLatitude(lat);
Date date = new Date(datems);
Quake q = new Quake(date, details, location, mag, link);
addQuakeToArray(q);
} while(c.moveToNext());
}
}
private void addNewQuake(Quake _quake) {
ContentResolver cr = getContentResolver();
// Construct a where clause to make sure we don�t already have this
// earthquake in the provider.
String w = EarthquakeProvider.KEY_DATE + " = " + _quake.getDate().getTime();
// If the earthquake is new, insert it into the provider.
if (cr.query(EarthquakeProvider.CONTENT_URI, null, w, null, null).getCount()==0){
ContentValues values = new ContentValues();
values.put(EarthquakeProvider.KEY_DATE, _quake.getDate().getTime());
values.put(EarthquakeProvider.KEY_DETAILS, _quake.getDetails());
double lat = _quake.getLocation().getLatitude();
double lng = _quake.getLocation().getLongitude();
values.put(EarthquakeProvider.KEY_LOCATION_LAT, lat);
values.put(EarthquakeProvider.KEY_LOCATION_LNG, lng);
values.put(EarthquakeProvider.KEY_LINK, _quake.getLink());
values.put(EarthquakeProvider.KEY_MAGNITUDE, _quake.getMagnitude());
cr.insert(EarthquakeProvider.CONTENT_URI, values);
earthquakes.add(_quake);
addQuakeToArray(_quake);
}
}