本文内容
定义一个Contract ,
在其内部实现一个静态内部类，内部类实现BaseColumns接口，
内部类中 定义表的模式。！注意： 系统创建表时会自动添加一个_id列。
public final class FeedReaderContract { // To prevent someone from accidentally instantiating the contract class, // make the constructor private. private FeedReaderContract() {} /* Inner class that defines the table contents */ public static class FeedEntry implements BaseColumns { public static final String TABLE_NAME = "entry"; public static final String COLUMN_NAME_TITLE = "title"; public static final String COLUMN_NAME_SUBTITLE = "subtitle"; } }
先写好entry表 创建和删除的SQL语句
private static final String SQL_CREATE_ENTRIES = "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" + FeedEntry._ID + " INTEGER PRIMARY KEY," + FeedEntry.COLUMN_NAME_TITLE + " TEXT," + FeedEntry.COLUMN_NAME_SUBTITLE + " TEXT)"; private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;创建一个FeedReaderDbHelper 类， 继承自SQLiteOpenHelper
其中 当entry表的模式修改了, DATABASE_VERSION则要进行修改，否则再调试时，还是原来的旧表。除非app删除再安装。
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); } }使用SQLite Helper 的getWritableDatabase()获取一个可写数据库对象db
插入数据需要用到一个ContentValues类, 使用其put方法以键值的方式存入数据
再调用db的insert方法 ，其中第二个参数为null表示当values值为空,则不执行插入
FeedReaderDbHelper dbHelper = new FeedReaderDbHelper(getContext()); // Gets the data repository in write mode SQLiteDatabase db = dbHelper.getWritableDatabase(); // Create a new map of values, where column names are the keys ContentValues values = new ContentValues(); values.put(FeedEntry.COLUMN_NAME_TITLE, title); values.put(FeedEntry.COLUMN_NAME_SUBTITLE, subtitle); // Insert the new row, returning the primary key value of the new row long newRowId = db.insert(FeedEntry.TABLE_NAME, null, values);使用SQLite Helper 的getReadableDatabase()获取一个读数据库对象db
然后 一个SQL 语句被拆成以下四个部分
定义一个projection ,为要查询的列名串
定义一个selection,为要查询的where 条件
定义一个selectionArgs, 其值与selection 中的 ? 一 一对应
定义一个sortOrder，其为排序方式
最后创建一个游标对象查询
SQLiteDatabase db = dbHelper.getReadableDatabase(); // Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { BaseColumns._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE }; // Filter results WHERE "title" = 'My Title' String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?"; String[] selectionArgs = { "My Title" }; // How you want the results sorted in the resulting Cursor String sortOrder = FeedEntry.COLUMN_NAME_SUBTITLE + " DESC"; Cursor cursor = db.query( FeedEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order );使用游标的getLong或getString方法查询，
但是要传入所要查询的列名的索引作为参数，调用getColumnIndex或getColumnIndexOrThrow 获取
List itemIds = new ArrayList<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong( cursor.getColumnIndexOrThrow(FeedEntry._ID)); itemIds.add(itemId); } cursor.close();定义selection 和 selectionArgs
再调用delete方法
// Define 'where' part of query. String selection = FeedEntry.COLUMN_NAME_TITLE + " LIKE ?"; // Specify arguments in placeholder order. String[] selectionArgs = { "MyTitle" }; // Issue SQL statement. int deletedRows = db.delete(FeedEntry.TABLE_NAME, selection, selectionArgs);使用ContentValues类，以键值方式存储，其中键为要更新的列名，值为更新后的新值
selection 为where条件，selectionArgs中的值与selection中的？一 一 对应，系统执行时替换其中的问号
最后调用SQLiteDatabase 的update方法
SQLiteDatabase db = dbHelper.getWritableDatabase(); // New value for one column String title = "MyNewTitle"; ContentValues values = new ContentValues(); values.put(FeedEntry.COLUMN_NAME_TITLE, title); // Which row to update, based on the title String selection = FeedEntry.COLUMN_NAME_TITLE + " LIKE ?"; String[] selectionArgs = { "MyOldTitle" }; int count = db.update( FeedReaderDbHelper.FeedEntry.TABLE_NAME, values, selection, selectionArgs);数据库的开启极其占用内存，如在调用getWritableDatabase和getReadDatabase时
所以仅在activity 的 onDestroy时关闭
@Override protected void onDestroy() { dbHelper.close(); super.onDestroy(); }本文参考安卓官方文档 Save data using SQLite
本文作者：林东豪 原文链接
