SQLite Database

1. SQLite Database

2. SQLite • Public domain database • Advantages • Small (about 150 KB) • Used on devices with limited resources • Each database contained within one file • Can be seen in file explorer /data/data/packagename/database/ • Self-contained • no special administration needed

3. SQLite • Primary components • SQLiteOpenHelper • android.database.sqlite.SQLiteOpenHelper • manages aspects of the database • creating or opening database • SQLiteDatabase • android.database.sqlite.SQLiteDatabase • methods to interface with database via SQL • Cursor • android.database.Cursor • provides access to result set returned by a query

4. Typical Approaches • Approach 1: Providing a database with the app • store .db file in Assets folder • .db file created within Android • .db file created in other SQLite manager, such as Firefox plug-in • in onCreate() of extended Activity, copy the database if it does not already exist in the device’s internal memory • onCreate() method in extended SQLiteOpenHelper class typically empty • Approach 2: Database created when app first run • onCreate() method in extended SQLiteOpenHelper class contains SQL to create database • will only run if the database does not exist

5. SQLiteOpenHelper class

6. SQLiteOpenHelper • Abstract class • Subclass in a new .java file • Useful methods • onCreate (abstract) • called when DB first created – table creation done here • onUpgrade (abstract) • called when DB is changed (i.e. during new app release) • essentially used to drop and recreate database • getReadableDatabase • called to get a read-only version of database • getWriteableDatabase • called to get an editable version of database

7. Sample code - SQLiteOpenHelper public class MyClass extends SQLiteOpenHelper { public MyClass (Context ctx) { //Parameters: Context, file name, CursorFactory factory, database version) //CursorFactory rarely used – only used for customized Cursors super (ctx, "myfilename.db", null, 1); } public void onUpgrade (SQLiteDatabase db, intoldVer, intnewVer) { db.execSQL("DROP TABLE IF EXISTS TABLE_NAME"); onCreate(db); } public void onCreate(SQLiteDatabase db) { //_id field must be included!!! db.execSQL("CREATE TABLE MYTABLE (_id INTEGER PRIMARY KEY AUTOINCREMENT, ..."); } }

8. SQLiteDatabase class

9. SQLiteDatabase • Concrete class • SQL methods • Convenience methods • query – returns a Cursor • insert – returns _id of new record or –1 if error occurred • update – returns number of rows affected • delete – number of rows affected (0 if no where clause) • pass ‘1’ as where clause to get count in this case • Manual creation of SQL • rawQuery • can execute any SELECT statement and returns a Cursor • execSQL • can execute any SQL statement that is not a SELECT • void return type

10. SQLiteDatabase • query method • Parameters • String – table name • String [] – columns to select • _id field must be included if displaying in List • String – where clause (without the word WHERE) • null if not wanted • ? for arguments • String [] – selection clause, to allow arguments for field names in where clause • only if ? in preceding string • String – group by clause (without the words GROUP BY) • String – having clause (without the word HAVING) • String – order by clause (without the words ORDER BY)

11. Sample code – query String [] FROM = {List of desired columns to select}; SQLiteDatabase db = myInstanceOfSQLiteOpenHelper.getReadableDatabase(); Cursor myCursor = db.query(MY_TABLE_NAME, FROM, null, null, null, null, null);

12. SQLiteDatabase • insert method • Parameters • String – table name • String – work around to allow insertion of empty row in SQL • nullColumnHack • SQLLite does not allow: • INSERT INTO MYTABLE • ContentValues will be null • ContentValues – Object holding the corresponding field names and values

13. Sample code – insert SQLiteDatabase db = myInstanceOfSQLiteOpenHelper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(COLUMN_NAME, column value); values.put(COLUMN_NAME, column value); ... values.put(COLUMN_NAME, column value); db.insert(MY_TABLE_NAME, null, values);

14. SQLiteDatabase • update method • Parameters • String – table name • ContentValues – Object holding the corresponding field names and values • String – where clause (without the word WHERE) • String [] – selection clause, to allow arguments for field names in where clause

15. Sample code – update SQLiteDatabase db = myInstanceOfSQLiteOpenHelper.getWritableDatabase(); String where = "My WHERE clause (without the word WHERE)"; ContentValues values = new ContentValues(); values.put(COLUMN_NAME, column value); values.put(COLUMN_NAME, column value); ... values.put(COLUMN_NAME, column value); db.update(MY_TABLE_NAME, values, where, null);

16. SQLiteDatabase • delete method • Parameters • String – table name • String – where clause (without the word WHERE) • String [] – selection clause, to allow arguments for field names in where clause

17. Sample code – delete SQLiteDatabase db = myInstanceOfSQLiteOpenHelper.getWritableDatabase(); String where = "My WHERE clause (without the word WHERE)"; db.delete(MY_TABLE_NAME, where, null);

18. SQLiteDatabase • rawQuery method • Parameters • String – SQL statement • String [] – selection clause, to allow arguments for field names in where clause

19. Sample code – rawQuery SQLiteDatabase db = myInstanceOfSQLiteOpenHelper.getWritableDatabase(); Cursor myCursor = db.rawQuery(“SQL statement here”, null);

20. SQLiteDatabase • execSQL method • Parameters • String – SQL statement

21. Sample code – execSQL SQLiteDatabase db = myInstanceOfSQLiteOpenHelper.getWritableDatabase(); db.execSQL(“SQL statement here”);

22. Cursor class

23. Aspects of Cursor class • SQLiteDatabase query method returns a Cursor • Cursor must be managed by Activity • Cursor class allows manipulation of pointer • move, moveToFirst, moveToNext, moveToPosition, etc. • Cursor class allows retrieval of data • getInt, getDouble, getString, etc. • must provide column position • Cursor can be associated with an Adapter • typically SimpleCursorAdapter

24. Sample code – Cursor data retrieval • Retrieving the integer in the 4th row, 3rd column myCursor.moveToPosition(3); int x = myCursor.getInt(2);

25. Sample code – populating a Cursor from a Database private Cursor populateCursor() { String [] FROM = {List of desired DB field names}; SQLiteDatabase db = myInstanceOfSQLiteHelper.getReadableDatabase(); Cursor myCursor = db.query(MY_TABLE_NAME, FROM, null, null, null, null, null); return myCursor; }

26. Sample code – displaying Cursor contents in a ListView private void displayRecords(Cursor c) { String [] from = {List of desired field names to display}; int [] to = new int [] {List of TextViews to display field names (i.e. R.id.myTextView)}; ListViewlv = (ListView)findViewById(R.id.myListView); SimpleCursorAdapter records = new SimpleCursorAdapter(this, R.layout.my_file_for_TextViews, c, from, to); lv.setAdapter(records); }