194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill/* 294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Copyright (C) 2008 Google Inc. 394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Licensed under the Apache License, Version 2.0 (the "License"); you may not 594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * use this file except in compliance with the License. You may obtain a copy of 694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * the License at 794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * http://www.apache.org/licenses/LICENSE-2.0 994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 1094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Unless required by applicable law or agreed to in writing, software 1194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT 1294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the 1394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * License for the specific language governing permissions and limitations under 1494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * the License. 1594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 1694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 1794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillpackage com.android.demo.notepad1; 1894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 1994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.content.ContentValues; 2094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.content.Context; 2194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.database.Cursor; 2294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.database.SQLException; 2394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.database.sqlite.SQLiteDatabase; 2494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.database.sqlite.SQLiteOpenHelper; 2594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillimport android.util.Log; 2694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 2794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill/** 2894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Simple notes database access helper class. Defines the basic CRUD operations 2994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * for the notepad example, and gives the ability to list all notes as well as 3094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * retrieve or modify a specific note. 3194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 3294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * This has been improved from the first version of this tutorial through the 3394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * addition of better error handling and also using returning a Cursor instead 3494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * of using a collection of inner classes (which is less scalable and not 3594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * recommended). 3694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 3794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neillpublic class NotesDbAdapter { 3894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 3994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public static final String KEY_TITLE = "title"; 4094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public static final String KEY_BODY = "body"; 4194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public static final String KEY_ROWID = "_id"; 4294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 4394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private static final String TAG = "NotesDbAdapter"; 4494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private DatabaseHelper mDbHelper; 4594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private SQLiteDatabase mDb; 4677560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill 4794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 4894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Database creation sql statement 4994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 5094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private static final String DATABASE_CREATE = 5177560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill "create table notes (_id integer primary key autoincrement, " 5277560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill + "title text not null, body text not null);"; 5394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 5494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private static final String DATABASE_NAME = "data"; 5594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private static final String DATABASE_TABLE = "notes"; 5694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private static final int DATABASE_VERSION = 2; 5794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 5894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private final Context mCtx; 5994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 6094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill private static class DatabaseHelper extends SQLiteOpenHelper { 6194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 6294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill DatabaseHelper(Context context) { 6394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill super(context, DATABASE_NAME, null, DATABASE_VERSION); 6494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 6594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 6694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill @Override 6794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public void onCreate(SQLiteDatabase db) { 6894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 6994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill db.execSQL(DATABASE_CREATE); 7094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 7194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 7294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill @Override 7394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 7494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill Log.w(TAG, "Upgrading database from version " + oldVersion + " to " 7594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill + newVersion + ", which will destroy all old data"); 7694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill db.execSQL("DROP TABLE IF EXISTS notes"); 7794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill onCreate(db); 7894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 7994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 8094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 8194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 8294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Constructor - takes the context to allow the database to be 8394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * opened/created 8494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 8594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param ctx the Context within which to work 8694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 8794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public NotesDbAdapter(Context ctx) { 8894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill this.mCtx = ctx; 8994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 9094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 9194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 9294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Open the notes database. If it cannot be opened, try to create a new 9394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * instance of the database. If it cannot be created, throw an exception to 9494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * signal the failure 9594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 9694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @return this (self reference, allowing this to be chained in an 9794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * initialization call) 9894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @throws SQLException if the database could be neither opened or created 9994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 10094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public NotesDbAdapter open() throws SQLException { 10194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill mDbHelper = new DatabaseHelper(mCtx); 10294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill mDb = mDbHelper.getWritableDatabase(); 10394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill return this; 10494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 10577560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill 10694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public void close() { 10794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill mDbHelper.close(); 10894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 10994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 11094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 11194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 11294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Create a new note using the title and body provided. If the note is 11394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * successfully created return the new rowId for that note, otherwise return 11494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * a -1 to indicate failure. 11594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 11694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param title the title of the note 11794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param body the body of the note 11894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @return rowId or -1 if failed 11994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 12094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public long createNote(String title, String body) { 12194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill ContentValues initialValues = new ContentValues(); 12294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill initialValues.put(KEY_TITLE, title); 12394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill initialValues.put(KEY_BODY, body); 12494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 12594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill return mDb.insert(DATABASE_TABLE, null, initialValues); 12694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 12794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 12894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 12994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Delete the note with the given rowId 13094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 13194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param rowId id of note to delete 13294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @return true if deleted, false otherwise 13394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 13494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public boolean deleteNote(long rowId) { 13594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 13694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0; 13794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 13894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 13994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 14094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Return a Cursor over the list of all notes in the database 14194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 14294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @return Cursor over all notes 14394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 14494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public Cursor fetchAllNotes() { 14594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 14694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, 14794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill KEY_BODY}, null, null, null, null, null); 14894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 14994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 15094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 15194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Return a Cursor positioned at the note that matches the given rowId 15294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 15394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param rowId id of note to retrieve 15494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @return Cursor positioned to matching note, if found 15594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @throws SQLException if note could not be found/retrieved 15694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 15794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public Cursor fetchNote(long rowId) throws SQLException { 15894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 15994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill Cursor mCursor = 16094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 16177560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, 16277560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill KEY_TITLE, KEY_BODY}, KEY_ROWID + "=" + rowId, null, 16377560fc704f2933e0df8a4be0768b2439a0bab2aTom O'Neill null, null, null, null); 16494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill if (mCursor != null) { 16594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill mCursor.moveToFirst(); 16694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 16794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill return mCursor; 16894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 16994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 17094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 17194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill /** 17294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * Update the note using the details provided. The note to be updated is 17394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * specified using the rowId, and it is altered to use the title and body 17494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * values passed in 17594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * 17694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param rowId id of note to update 17794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param title value to set note title to 17894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @param body value to set note body to 17994b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill * @return true if the note was successfully updated, false otherwise 18094b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill */ 18194b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill public boolean updateNote(long rowId, String title, String body) { 18294b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill ContentValues args = new ContentValues(); 18394b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill args.put(KEY_TITLE, title); 18494b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill args.put(KEY_BODY, body); 18594b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill 18694b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0; 18794b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill } 18894b6d06899ddb3f97cd0171bce5de1cb60602d01Tom O'Neill} 189