WIXLIB

Android Persistent Data Storage:IntroductionDouglas C. edu/ schmidtProfessor of Computer ScienceInstitute for SoftwareIntegrated SystemsVanderbilt UniversityNashville, Tennessee, USA

Android Persistent Data StorageDouglas C. SchmidtData Storage Options on Android Android offers several ways to storedata SQLite database Files SharedPreferences2

Android Persistent Data Storage:Overview of SQLiteDouglas C. edu/ schmidtProfessor of Computer ScienceInstitute for SoftwareIntegrated SystemsVanderbilt UniversityNashville, Tennessee, USA

Android Persistent Data StorageDouglas C. SchmidtLearning Objectives in this Part of the Module Understand what SQLite is & how to use it in Android4

Android Persistent Data StorageDouglas C. SchmidtAndroid SQLite Android supports SQLite, which providesa relational database for a mobile device i.e., it contains tables (consisting ofrows & columns), indexes, etc. thatform a -on-android/232900584

Android Persistent Data StorageAndroid SQLite Android supports SQLite, which providesa relational database for a mobile device It’s designed to operate within asmall footprint ( 350kB) withina single cross-platform disk file6en.wikipedia.org/wiki/SQLiteDouglas C. Schmidt

Android Persistent Data StorageAndroid SQLite Android supports SQLite, which providesa relational database for a mobile device It’s designed to operate within asmall footprint ( 300kB) withina single cross-platform disk file Implements most of SQL92 &supports so-called “ACID”transactions Atomic, Consistent, Isolated, & Durable7en.wikipedia.org/wiki/SQL-92Douglas C. Schmidt

Android Persistent Data StorageDouglas C. SchmidtAndroid SQLite Android supports SQLite, which providesa relational database for a mobile device It’s designed to operate within asmall footprint ( 300kB) withina single cross-platform disk file Implements most of SQL92 &supports so-called “ACID”transactions Access to an SQLite database typicallyinvolves accessing the Android filesystem Database operations are typicallyasynchronous since filesystem accesscan be slow e.g., access is often made via AsyncTask,AsyncQueryHandler, CursorLoader, e.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteDatabase SQLiteDatabase is the base classfor working with a SQLite databasein Android It provides the insert(),update(), & delete() tabase/sqlite/SQLiteDatabase.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteDatabase SQLiteDatabase is the base classfor working with a SQLite databasein Android It provides the insert(),update(), & delete() methods It also provides the execSQL()method that can execute anSQL statement database/sqlite/SQLiteDatabase.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteDatabase SQLiteDatabase is the base classfor working with a SQLite databasein Android Queries can be created viarawQuery() & query() methods orvia the SQLiteQueryBuilder ase/sqlite/SQLiteQueryBuilder.html11

Android Persistent Data StorageDouglas C. SchmidtContentValues The ContentValues object is usedby SQLiteDatabase to definekey/values The “key” represents the tablecolumn identifier & the “value”represents the content for thetable record in this ntent/ContentValues.html

Android Persistent Data StorageDouglas C. SchmidtContentValues The ContentValues object is usedby SQLiteDatabase to definekey/valuesContentValues can be used forinserts & updates of ndroid/content/ContentValues.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteOpenHelper Recommended means of usingSQLiteDatabase is to subclass theSQLiteOpenHelper class In constructor call the super()method of SQLiteOpenHelper,specifying database name ¤t database atabase/sqlite/SQLiteOpenHelper.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteOpenHelper Recommended means of usingSQLiteDatabase is to subclass theSQLiteOpenHelper class In constructor call the super()method of SQLiteOpenHelper,specifying database name ¤t database version Override onCreate(), which iscalled by SQLite if the databasedoes not yet exist e.g., execute CREATE oid/database/sqlite/SQLiteOpenHelper.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteOpenHelper Recommended means of usingSQLiteDatabase is to subclass theSQLiteOpenHelper class In constructor call the super()method of SQLiteOpenHelper,specifying database name ¤t database version Override onCreate(), which iscalled by SQLite if the databasedoes not yet exist Override onUpgrade(), which iscalled if the database versionincreases in App code to allowdatabase schema atabase/sqlite/SQLiteOpenHelper.html

Android Persistent Data StorageDouglas C. SchmidtSQLiteOpenHelper Recommended means of usingSQLiteDatabase is to subclass theSQLiteOpenHelper class Use SQLiteOpenHelper methods toopen & return underlying database e.g., getReadableDatabase() &getWriteableDatabase() toaccess an SQLiteDatabaseobject either in read or writemode, oid/database/sqlite/SQLiteOpenHelper.html

Android Persistent Data StorageDouglas C. SchmidtOpening an SQLite Databasepublic class ArtistDatabaseHelper extends SQLiteOpenHelper {final private static String CREATE CMD "CREATE TABLE artists (" " id" " INTEGER PRIMARY KEY AUTOINCREMENT, " "name" " TEXT NOT NULL)";SQL commands to create a table of artistspublic ArtistDatabaseHelper(Context context) {super(context, "artists db", null, 1);}Give a name to the tablepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE CMD);}Create the SQL tablepublic void onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion) { /* . */ }.Support schema evolutionIt’s common to create an SQLiteOpenHelpersubclass for each SQL table18

Android Persistent Data StorageDouglas C. SchmidtUsing an SQLite Database Via an Activitypublic class DatabaseExampleActivity extends ListActivity {final static String[] columns {" id", "name"};static SQLiteDatabase db null;public void onCreate(Bundle savedInstanceState) {.ArtistDatabaseHelper dbHelper new ontext());Make the SQLiteOpenHelper subclass instancedb ate a read/write databsedeleteLadyGaga();Cursor c readArtists();displayArtists(c);}.Perform various operationsSQLiteDatabase is often put in an19App singleton to simplify access

Android Persistent Data StorageDouglas C. SchmidtInserting Values Into an SQLite Database Method for inserting a row into the databasepublic long insert (String table, String nullColHack, ContentValues values)ParameterstableThe table to insert the row intonullColHackOptional (often null)valuesMap containing initial col values for row; keys are col String, java.lang.String,android.content.ContentValues)

Android Persistent Data StorageDouglas C. SchmidtInserting Values Into an SQLite Database Method for inserting a row into the databasepublic long insert (String table, String nullColHack, ContentValues g.String, java.lang.String,android.content.ContentValues)

Android Persistent Data StorageDouglas C. SchmidtInserting Values Into an SQLite Databaseprivate void insertArtists() {ContentValues values new ContentValues();“key” represents the table column identifier & the “value”represents the content for the table record in this columnvalues.put("name", "Lady Gaga");db.insert("artists", null, values);values.clear();values.put("name", "Johnny Cash");db.insert("artists", null, values);values.clear();values.put("name", "Sting");db.insert("artists", null, values);.}22

Android Persistent Data StorageDouglas C. SchmidtDeleting a Row From an SQLite Database Method for deleting row(s) from the databasepublic int delete(String table, String whereClause, String[] whereArgs)Parameterstablethe table to delete fromwhereClauseoptional WHERE clause to apply when deletingwhereArgsPassing null deletes all tring, java.lang.String,java.lang.String[])

Android Persistent Data StorageDouglas C. SchmidtDeleting a Row From an SQLite Database Method for deleting row(s) from the databasepublic int delete(String table, String whereClause, String[] lang.String, java.lang.String,java.lang.String[])

Android Persistent Data StorageDouglas C. SchmidtDeleting a Row From an SQLite Databaseprivate int deleteLadyGaga() {Remove Lady Gaga from the database of artistsreturn db.delete("artists","name" " ?",new String []{"Lady Gaga"}); Note the use ofthe “whereArgs”}25

Android Persistent Data StorageDouglas C. SchmidtQuerying an SQLite Database You can use rawQuery() or a query() on an SQLiteDatabasepublic Cursor rawQuery(String sql, String[] selectionArgs)Runs the provided SQL and returns a Cursor over the result setParameterssqlthe SQL query. The SQL string must not be ; terminatedselectionArgs You may include ?’s in where clause in the query, which arereplaced by the values from selectionArgs (the values will bebound as Strings)ReturnsA Cursor object, which is positioned before the first entryCursors aren’t synchronized, 26see documentation for details

Android Persistent Data StorageDouglas C. SchmidtQuerying an SQLite Database You can use rawQuery() or a query() on an SQLiteDatabasepublic Cursor query(String table, String[] columns, String selection, String[]selectionArgs, String groupBy, String having, String orderBy)query() builds up a SQL SELECT statement from its component partsParameterString tableThe table name to compile the query againstint[] columnsA list of which table columns to return ("null" returns all columns)String selectionWhere-clause filters for the selection of data (null selects all data)String[]selectionArgsYou may include ?s in the “selection” where-clause that get replacedby the values from the selectionArgs arrayString[] groupBy A filter declaring how to group rows (null means rows not grouped)String[] havingFilter for the groups (null means no filter)String[] orderBy Table columns used to order the data (null means no ordering)Returns27 the first entryA Cursor object, which is positioned before

Android Persistent Data StorageDouglas C. SchmidtUsing Query() vs. rawQuery() Using rawQuery() on an SQLiteDatabaseprivate Cursor readArtists() {// returns all rowsreturn db.rawQuery("SELECT id, name FROM artists", ang.String,java.lang.String[])

Android Persistent Data StorageDouglas C. SchmidtUsing Query() vs. rawQuery() Using rawQuery() on an SQLiteDatabaseprivate Cursor readArtists() {// returns all rowsreturn db.rawQuery("SELECT id, name FROM artists", null);} Using query() on an SQLiteDatabaseprivate Cursor readArtists() {// returns all rowsreturn db.query("artists", new String [] {" id", "name"},null, null, null, null, tring, java.lang.String[], java.lang.String, java.lang.String[],29java.lang.String, java.lang.String,java.lang.String)

Android Persistent Data StorageDouglas C. SchmidtCursor Iterators Query() returns a Cursor Iteratorthat represents result of a query &points to one row of query result This allows buffering of queryresults efficiently since all dataneedn’t be loaded into tabase/Cursor.html

Android Persistent Data StorageDouglas C. SchmidtCursor Iterators Query() returns a Cursor Iteratorthat represents result of a query &points to one row of query result getCount() returns # of elementsof the resulting abase/Cursor.html

Android Persistent Data StorageDouglas C. SchmidtCursor Iterators Query() returns a Cursor Iteratorthat represents result of a query &points to one row of query result getCount() returns # of elementsof the resulting query moveToFirst() & moveToNext()move between individual data base/Cursor.html

Android Persistent Data StorageDouglas C. SchmidtCursor Iterators Query() returns a Cursor Iteratorthat represents result of a query &points to one row of query result getCount() returns # of elementsof the resulting query moveToFirst() & moveToNext()move between individual data rows isAfterLast() checks if the end ofthe query result has been atabase/Cursor.html