Transcript Presentation 13

SQLite Database
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
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
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
SQLiteOpenHelper class
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
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, int oldVer, int newVer) {
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, ...");
}
}
SQLiteDatabase class
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
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)
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);
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
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);
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
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);
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
Sample code – delete
SQLiteDatabase db =
myInstanceOfSQLiteOpenHelper.getWritableDatabase();
String where = "My WHERE clause (without the word WHERE)";
db.delete(MY_TABLE_NAME, where, null);
SQLiteDatabase
• rawQuery method
– Parameters
• String – SQL statement
• String [] – selection clause, to allow arguments for field names
in where clause
Sample code – rawQuery
SQLiteDatabase db =
myInstanceOfSQLiteOpenHelper.getWritableDatabase();
Cursor myCursor = db.rawQuery(“SQL statement here”, null);
SQLiteDatabase
• execSQL method
– Parameters
• String – SQL statement
Sample code – execSQL
SQLiteDatabase db =
myInstanceOfSQLiteOpenHelper.getWritableDatabase();
db.execSQL(“SQL statement here”);
Cursor class
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
Sample code – Cursor data retrieval
• Retrieving the integer in the 4th row, 3rd column
myCursor.moveToPosition(3);
int x = myCursor.getInt(2);
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;
}
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)};
ListView lv = (ListView)findViewById(R.id.myListView);
SimpleCursorAdapter records = new SimpleCursorAdapter(this,
R.layout.my_file_for_TextViews, c, from, to);
lv.setAdapter(records);
}