Android course Database dr Milan Vidakovi Chair of

Android course Database dr Milan Vidaković Chair of Informatics Faculty of Technical Sciences University of Novi Sad

Database • What is relational database model? • simplified answer: database consists of tables • table is just like the one in the Excel • Each table has rows and columns • Each column has: name, type (int, string, . . . ), undefined value or not (null), is it primary key or not (primary key), etc. 2/24

Primary key • Primary key: a column(s) which identifies a row • Student index, vehicle registration plate • Primary key can be autoincrement • Integer value which is incremented when a row is inserted in a table 3/24

Android database • Android comes with SQLite database • Features: • • self-contained, serverless, zero-configuration and transactional • All the tables are placed in the /data folder (the only read-write folder on Android (except for the /sdcard folder content – SD card)) 4/24

SQLite • SQLite. Database class: SQLite. Database db = db. Helper. get. Writable. Database(); SQLite. Open. Helper class: to open, close, create and modify database Database. Examples 5/24

Helper class • Extends SQLite. Open. Helper class • Overrides: • • on. Create(SQLite. Database) on. Open(SQLite. Database) on. Upgrade(SQLite. Database, old_ver, new_ver) on. Downgrade(SQLite. Database, old_ver, new_ver) 6/24

Helper class public class My. Db. Helper extends SQLite. Open. Helper { private static final String DATABASE_CREATE = "create table NOTES ( " + " _id integer primary key autoincrement, " + " naslov text not null, " + " vreme text not null, " + " tekst text not null); "; public My. Db. Helper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void on. Create(SQLite. Database _db) { _db. exec. SQL(DATABASE_CREATE); } @Override public void on. Upgrade(SQLite. Database _db, int _old. Version, int _new. Version) { _db. exec. SQL("DROP TABLE IF EXISTS " + Notes. Db. Manager. DATABASE_TABLE); on. Create(_db); } } 7/24

SQLite database • CRUD (Create, Read, Update, Delete): long insert(String table, String null_hack, Content. Calues entry) Cursor query (String table, String[] columns, String selection, String[] selection. Args, String group. By, String having, String order. By, String limit) int update(String table, Content. Values values, String where. Clause, String[] where. Args) int delete(String table, String where. Clause, String[] where. Args) 8/24

Insert • Content to be inserted must be placed in the Content. Values map: Content. Values new. Entry = new Content. Values(); new. Entry. put(“First. Name”, “Pera”); new. Entry. put(“Last. Name”, “Peric”); long id = db. insert(DATABASE_TABLE, null, new. Entry); • Returns Id of the insterted row • Important when PK is autoincrement! 9/24

Query • Return value is a cursor which is used to browse query results • If criteria, group by, having, order by, and limit is omitted, lists the whole table: Cursor c = db. query(DATABASE_TABLE, new String[] { _ID, TITLE, TIMESTAMP, TEXT }, null, null); 10/24

Query: cursor = db. query(true, DATABASE_TABLE, new String[] {_ID, TITLE, TIMESTAMP, TEXT}, _ID + "=? ", new String[] {id}, null, null); • Raw query: cursor = db. raw. Query("select _ID, TITLE, TIMESTAMP, TEXT from DATABASE_TABLE where ID=? ", new String[] {id}); 11/24

Cursor • To browse and read query results • Browse: move(offset) move. To. First(), move. To. Last(), move. To. Next(), move. To. Previous() is. First(), is. Last(), is. After. Last(), is. Before. First() • Read: get. Count() get. String(column_index) get. Int(column_index) get. Column. Index(“Last. Name”); 12/24

Update • Updates column(s): Content. Values new. Value = new Content. Values(); new. Value. put(“First. Name”, “MIKA”); new. Value. put(“Last. Name”, “MIKIC”); int rows = db. update(DATABASE_TABLE, new. Value, _ID + "=? ", new String[] {id}); • SQL equivalent: update DATABASE_TABLE set First. Name=“MIKA”, Last. Name=“MIKIC” where _ID=id • If filter criteria is omitted, it will update all rows! • Last two arguments are filter criteria and its parameters • Returns the number of affected rows 13/24

Delete • Deletes a row: int rows = db. delete(DATABASE_TABLE, _ID + "=? ", new String[] {id}); • SQL equivalent: delete from DATABASE_TABLE where _ID=id If filter criteria is omitted, it will update all rows! Last two arguments are filter criteria and its parameters Returns the number of affected rows 14/24

Data read • To start reading query results: start. Managing. Cursor(cur); • This method binds cursor to Activity from which it has been created • If an activity is stopped, it will invoke cursor. deactivate() • If an activity is restarted, it will invoke cursor. requery() • If an activity is destroyed, it will close the cursor • Each List. View has a List. Adapter which binds data and List. View – Simple. Cursor. Adapter class is used to bind query results to a List. View Database. Examples 15/24

Simple. Cursor. Adapter • A kind of List. Adapter, which binds cursor to a List. View • Procedure: execute query, create Simple. Cursor. Adapter, and put that adapter as a soruce to a List. View 16/24

Simple. Cursor. Adapter • Constructor: List. Adapter adapter = new Simple. Cursor. Adapter(this, R. layout. list_item, cur, new String[] { “First. Name”, “Last. Name”}, new int[] { R. id. firstname, R. id. lastname }); • Second argument (R. layout. list_item) is a resource ID which will visualise a row • Third argument is a cursor • Fourth argument is a list of column names in a db table • Fifth argument is a list of component ids bound to appropriate columns in a table • Components are part of a row component (R. layout. list_item) 17/24

List multimedia files • List multimeida files on a device String[] proj = { Media. Store. Video. Media. _ID, Media. Store. Video. Media. DATA, Media. Store. Video. Media. DISPLAY_NAME, Media. Store. Video. Media. SIZE }; media. Cursor = managed. Query(Media. Store. Video. Media. EXTERN AL_CONTENT_URI, proj, null, null); Database. Examples 18/24

List multimedia files • How to bind query result and List. View? Create custom Adapter, which extends Base. Adapter class MMedia. Adapter extends Base. Adapter { private Context v. Context; public MMedia. Adapter(Context c) { v. Context = c; } public int get. Count() { return media. Cursor. get. Count(); } public Object get. Item(int position) { return position; } public long get. Item. Id(int position) { return position; } 19/24

List multimedia files public View get. View(int position, View old. View, View. Group parent) { System. gc(); Text. View tv; String id = null; if (old. View == null) { tv = new Text. View(v. Context. get. Application. Context()); int media_column_index = media. Cursor. get. Column. Index. Or. Throw(Media. Store. Video. Media. DISPLAY_NAME); media. Cursor. move. To. Position(position); id = media. Cursor. get. String(media_column_index); media_column_index = media. Cursor. get. Column. Index. Or. Throw(Media. Store. Video. Media. SIZE); media. Cursor. move. To. Position(position); id += " Size(KB): " + media. Cursor. get. String(media_column_index); tv. set. Text(id); } else tv = (Text. View) old. View; return tv; } } 20/24

Browse bookmarks and web history • Android browsers record all bookmarks and history in a database • Table has an alias: android. provider. Browser. BOOKMARKS_URI • Columns: • • Browser. Bookmark. Columns. TITLE Browser. Bookmark. Columns. URL Browser. Bookmark. Columns. CREATED Browser. Bookmark. Columns. BOOKMARK – 1 for a bookmark; 0 for history Database. Examples 21/24

Browse bookmarks and web history String[] columns = new String[] { Browser. Bookmark. Columns. TITLE, Browser. Bookmark. Columns. URL, Browser. Bookmark. Columns. CREATED }; Cursor cur = managed. Query(android. provider. Browser. BOOKMARKS_URI, columns, null, null); List. Adapter adapter = new Simple. Cursor. Adapter(this, R. layout. bookmark_item, cur, new String[] { Browser. Bookmark. Columns. TITLE, Browser. Bookmark. Columns. URL, Browser. Bookmark. Columns. CREATED}, new int[] { R. id. bookmark_title, R. id. bookmark_url, R. id. bookmark_created }); List. View main. List = (List. View) find. View. By. Id(R. id. bookmarks_list); main. List. set. Adapter(adapter); 22/24

Call history • Android records all voice calls history in a database • Table alias: Call. Log. Calls. CONTENT_URI • Columns: • • • Calls. NUMBER Calls. CACHED_NAME Calls. DURATION Calls. DATE Calls. TYPE (incoming, outgoing, missed) Database. Examples 23/24

Call history String[] columns = new String[] {Calls. _ID , Calls. NUMBER, Calls. CACHED_NAME, Calls. DURATION }; Cursor cur = managed. Query(Call. Log. Calls. CONTENT_URI, columns, null, null); List. Adapter adapter = new Simple. Cursor. Adapter(this, R. layout. call_history_item, cur, new String[] { Calls. NUMBER, Calls. CACHED_NAME, Calls. DURATION}, new int[] { R. id. call_number, R. id. call_name, R. id. call_duration }); List. View main. List = (List. View)find. View. By. Id(R. id. call_history_list); main. List. set. Adapter(adapter); 24/24