android 数据库操作
定义schema:
1 public final class FeedReaderContract { 2 // To prevent someone from accidentally instantiating the contract class, 3 // give it an empty constructor. 4 public FeedReaderContract() {} 5 6 /* Inner class that defines the table contents */ 7 public static abstract class FeedEntry implements BaseColumns { 8 public static final String TABLE_NAME = "entry"; 9 public static final String COLUMN_NAME_ENTRY_ID = "entryid"; 10 public static final String COLUMN_NAME_TITLE = "title"; 11 public static final String COLUMN_NAME_SUBTITLE = "subtitle"; 12 ... 13 } 14 }
自定义SQL Helper:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public class FeedReaderDbHelper extends SQLiteOpenHelper { 2 // If you change the database schema, you must increment the database version. 3 public static final int DATABASE_VERSION = 1; 4 public static final String DATABASE_NAME = "FeedReader.db"; 5 6 private static final String TEXT_TYPE = " TEXT"; 7 private static final String COMMA_SEP = ","; 8 private static final String SQL_CREATE_ENTRIES = 9 "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" + 10 FeedEntry._ID + " INTEGER PRIMARY KEY," + 11 FeedEntry.COLUMN_NAME_ENTRY_ID + TEXT_TYPE + COMMA_SEP + 12 FeedEntry.COLUMN_NAME_TITLE + TEXT_TYPE + COMMA_SEP + 13 ... // Any other options for the CREATE command 14 " )"; 15 16 private static final String SQL_DELETE_ENTRIES = 17 "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME; 18 19 20 public FeedReaderDbHelper(Context context) { 21 super(context, DATABASE_NAME, null, DATABASE_VERSION); 22 } 23 public void onCreate(SQLiteDatabase db) { 24 db.execSQL(SQL_CREATE_ENTRIES); 25 } 26 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 27 // This database is only a cache for online data, so its upgrade policy is 28 // to simply to discard the data and start over 29 db.execSQL(SQL_DELETE_ENTRIES); 30 onCreate(db); 31 } 32 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 33 onUpgrade(db, oldVersion, newVersion); 34 } 35 }
插入条目:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 FeedReaderDbHelper mDbHelper = new FeedReaderDbHelper(getContext()); 2 3 // Gets the data repository in write mode 4 SQLiteDatabase db = mDbHelper.getWritableDatabase(); 5 6 // Create a new map of values, where column names are the keys 7 ContentValues values = new ContentValues(); 8 values.put(FeedEntry.COLUMN_NAME_ENTRY_ID, id); 9 values.put(FeedEntry.COLUMN_NAME_TITLE, title); 10 values.put(FeedEntry.COLUMN_NAME_CONTENT, content); 11 12 // Insert the new row, returning the primary key value of the new row 13 long newRowId; 14 newRowId = db.insert( 15 FeedEntry.TABLE_NAME, 16 FeedEntry.COLUMN_NAME_NULLABLE, 17 values);
查找、更新、删除:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQLiteDatabase db = mDbHelper.getReadableDatabase(); 2 3 // Define a projection that specifies which columns from the database 4 // you will actually use after this query. 5 String[] projection = { 6 FeedEntry._ID, 7 FeedEntry.COLUMN_NAME_TITLE, 8 FeedEntry.COLUMN_NAME_UPDATED, 9 ... 10 }; 11 12 // How you want the results sorted in the resulting Cursor 13 String sortOrder = 14 FeedEntry.COLUMN_NAME_UPDATED + " DESC"; 15 16 Cursor c = db.query( 17 FeedEntry.TABLE_NAME, // The table to query 18 projection, // The columns to return 19 selection, // The columns for the WHERE clause 20 selectionArgs, // The values for the WHERE clause 21 null, // don't group the rows 22 null, // don't filter by row groups 23 sortOrder // The sort order 24 );
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SQLiteDatabase db = mDbHelper.getReadableDatabase(); 2 3 // Define a projection that specifies which columns from the database 4 // you will actually use after this query. 5 String[] projection = { 6 FeedEntry._ID, 7 FeedEntry.COLUMN_NAME_TITLE, 8 FeedEntry.COLUMN_NAME_UPDATED, 9 ... 10 }; 11 12 // How you want the results sorted in the resulting Cursor 13 String sortOrder = 14 FeedEntry.COLUMN_NAME_UPDATED + " DESC"; 15 16 Cursor c = db.query( 17 FeedEntry.TABLE_NAME, // The table to query 18 projection, // The columns to return 19 selection, // The columns for the WHERE clause 20 selectionArgs, // The values for the WHERE clause 21 null, // don't group the rows 22 null, // don't filter by row groups 23 sortOrder // The sort order 24 );
// 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);
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);
访问数据库数据:
cursor.moveToFirst(); long itemId = cursor.getLong( cursor.getColumnIndexOrThrow(FeedEntry._ID) );