1 / 24

Android course

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 t able is just like the one in the Excel Each table has rows and columns

mary
Télécharger la présentation

Android course

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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.

  3. 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

  4. 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))

  5. SQLite • SQLiteDatabase class: SQLiteDatabasedb = dbHelper.getWritableDatabase(); • SQLiteOpenHelper class: to open, close, create and modify database DatabaseExamples

  6. Helper class • Extends SQLiteOpenHelper class • Overrides: • onCreate(SQLiteDatabase) • onOpen(SQLiteDatabase) • onUpgrade(SQLiteDatabase, old_ver, new_ver) • onDowngrade(SQLiteDatabase, old_ver, new_ver)

  7. Helper class public class MyDbHelper extends SQLiteOpenHelper { private static final String DATABASE_CREATE = "create table NOTES ( " + " _id integer primary key autoincrement," + " naslov text not null, " + " vreme text not null, " + " teksttext not null);"; public MyDbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase _db) { _db.execSQL(DATABASE_CREATE); } @Override public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) { _db.execSQL("DROP TABLE IF EXISTS " + NotesDbManager.DATABASE_TABLE); onCreate(_db); } }

  8. SQLite database • CRUD (Create, Read, Update, Delete): long insert(String table, String null_hack, ContentCalues entry) Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) int update(String table, ContentValues values, String whereClause, String[] whereArgs) int delete(String table, String whereClause, String[] whereArgs)

  9. Insert • Content to be inserted must be placed in the ContentValues map: ContentValues newEntry = new ContentValues(); newEntry.put(“FirstName”, “Pera”); newEntry.put(“LastName”, “Peric”); long id = db.insert(DATABASE_TABLE, null, newEntry); • Returns Id of the insterted row • Important when PK is autoincrement!

  10. 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, null, null, null);

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

  12. Cursor • To browse and read query results • Browse: move(offset) moveToFirst(), moveToLast(), moveToNext(), moveToPrevious() isFirst(), isLast(), isAfterLast(), isBeforeFirst() • Read: getCount() getString(column_index) getInt(column_index) getColumnIndex(“LastName”);

  13. Update • Updates column(s): ContentValues newValue = new ContentValues(); newValue.put(“FirstName”, “MIKA”); newValue.put(“LastName”, “MIKIC”); int rows = db.update(DATABASE_TABLE, newValue, _ID + "=?", new String[] {id}); • SQL equivalent: update DATABASE_TABLE set FirstName=“MIKA”, LastName=“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

  14. 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

  15. Data read • To start reading query results: startManagingCursor(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 ListView has a ListAdapter which binds data and ListView • SimpleCursorAdapter class is used to bind query results to a ListView DatabaseExamples

  16. SimpleCursorAdapter • A kind of ListAdapter, which binds cursor to a ListView • Procedure: execute query, create SimpleCursorAdapter, and put that adapter as a soruce to a ListView

  17. SimpleCursorAdapter • Constructor: ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.list_item, cur, new String[] { “FirstName”, “LastName”}, 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)

  18. List multimedia files • List multimeida files on a device String[] proj = { MediaStore.Video.Media._ID, MediaStore.Video.Media.DATA, MediaStore.Video.Media.DISPLAY_NAME, MediaStore.Video.Media.SIZE }; mediaCursor = managedQuery(MediaStore.Video.Media.EXTERNAL_CONTENT_URI,proj, null, null, null); DatabaseExamples

  19. List multimedia files • How to bind query result and ListView? Create custom Adapter, which extends BaseAdapter class MMediaAdapter extends BaseAdapter { private Context vContext; public MMediaAdapter(Context c) { vContext = c; } public int getCount() { return mediaCursor.getCount(); } public Object getItem(int position) { return position; } public long getItemId(int position) { return position; }

  20. List multimedia files public View getView(int position, View oldView, ViewGroup parent) { System.gc(); TextView tv; String id = null; if (oldView == null) { tv = new TextView(vContext.getApplicationContext()); int media_column_index = mediaCursor .getColumnIndexOrThrow(MediaStore.Video.Media.DISPLAY_NAME); mediaCursor.moveToPosition(position); id = mediaCursor.getString(media_column_index); media_column_index = mediaCursor .getColumnIndexOrThrow(MediaStore.Video.Media.SIZE); mediaCursor.moveToPosition(position); id += " Size(KB):" + mediaCursor.getString(media_column_index); tv.setText(id); } else tv = (TextView) oldView; return tv; } }

  21. 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.BookmarkColumns.TITLE • Browser.BookmarkColumns.URL • Browser.BookmarkColumns.CREATED • Browser.BookmarkColumns.BOOKMARK – 1 for a bookmark; 0 for history DatabaseExamples

  22. Browse bookmarks and web history String[] columns = new String[] { Browser.BookmarkColumns.TITLE, Browser.BookmarkColumns.URL, Browser.BookmarkColumns.CREATED }; Cursor cur = managedQuery(android.provider.Browser.BOOKMARKS_URI, columns, null, null, null); ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.bookmark_item, cur, new String[] { Browser.BookmarkColumns.TITLE, Browser.BookmarkColumns.URL, Browser.BookmarkColumns.CREATED}, new int[] { R.id.bookmark_title, R.id.bookmark_url, R.id.bookmark_created }); ListView mainList = (ListView) findViewById(R.id.bookmarks_list); mainList.setAdapter(adapter);

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

  24. Call history String[] columns = new String[] {Calls._ID , Calls.NUMBER, Calls.CACHED_NAME, Calls.DURATION }; Cursor cur = managedQuery(CallLog.Calls.CONTENT_URI, columns, null, null, null); ListAdapter adapter = new SimpleCursorAdapter(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 }); ListView mainList = (ListView)findViewById(R.id.call_history_list); mainList.setAdapter(adapter);

More Related