Database Programming with SQLite_Android

Document Sample
Database Programming with SQLite_Android Powered By Docstoc
					Database Programming with
    SQLite And Android

Portions of this presentation are modifications based on work created and shared by the Android Open
Source Project and used according to terms described in the Creative Commons 2.5 Attribution License.
• Serverless SQL Database Engine
• Source Code is Public Domain
• Lightweight (less than 300KB)
   – Useful for embedded applications
   – Useful for mobile devices
• Cross Platform Support
• Reads an writes an ordinary disk file
   – Does not support GRANT and REVOKE as it can only
     support normal file access privileges.
• Implements Most Features of SQL
   – SQLite parses foreign key constraints but does not
     enforce them
             SQLite DataTypes
• Most SQL database engines use static typing
  – The data type value is determined by the column
    it is stored in
• SQLITE uses dynamic typing
  – The datatype of a value is associated with the
    value itself
  – In order to maximize compatibility between SQLite
    and other database engines, SQLite supports
    “type affinity” of columns
     • Recommended, but not required type for the column
   SQLite Column Type Affinities
  – 1,2,3,4,6, or 8 bytes, depending on magniture
  – 8 byte floating point value
  – Binary data such as images
  SQLite Column Affinity Example
  t TEXT,             ‘500.0’, ‘500.0’, ‘500.0’,
  nu REAL,            ‘500.0’);
  no BLOB           INSERT INTO t1
  );                  VALUES(500.0, 500.0,
                      500.0, 500.0);
      Application Programming with
• Separation of Layers is Crucial for
  Application Structure
  – Persistence Tier should have no
    knowledge of Model and View Tiers
  – Model Tier should only access the
    persistence tier through a public interface
     • Model tier should have no knowledge of the
       implementation details in the persistence tier
 Android Persistence Tier Mechanisms
• Preferences
  – Key/Value pairs of primitive data types
• Files
• Databases
  – Ships with SQLite
  – Defines custom data types for useful mobile
     • Contact Information( firstName, lastName, address,phone)
     • Photo (bitmap image)
  – All databases are stored on the device in
• Network
      Android Persistence Objects:
• A ContentProvider provides a public interface
  to the persistence tier
  – Only mechanism for sharing data across
  – Android.provider package contains custom
    providers for common applications
     • Contacts
     • MediaStore
      Android Persistence Objects:
• Exposed by each content provider
• Uniquely identifies the data set managed by
  the content provider
• URIs begin with the string “content://”
• CONTENT_URI constants are defined for all
  built-in providers
  – android.provider.Contacts.Phones.CONTENT_URI
  – android.provider.Contacts.Photos.CONTENT_URI
      Android Persistence Objects:
• Applications use a ContentResolver to call
  methods on the ContentProvider
  – Manages interprocess communication with the
  – Call getContentResolver() in an Activity to obtain a
  – ContentResolver methods take a Uri as the first
    parameter in order to identify the
    Querying a Content Provider
• Required Information
  – URI that identifies the provider
  – Names of the data fields you want to receive
  – The data types for those fields
  – (Optional) If you are querying for a particular
    record, you need the ID of the record
               Query Methods
• Methods
  – ContentResolver.query()
     • Activity.startManagingCursor()
  – Activity.managedQuery()
• Return a Cursor object for iterating through
  the records and retrieving specific data from
  the columns.
• Managed Query
  – Activity will handles the Cursor lifecycle
     • Cursor will unload itself when an Activity pauses
  public final Cursor query( Uri uri,
      String[] projection,
      String selection,
      String[] selectionArgs,
      String sortOrder)
Parameter       Description
Uri             The URI, using the content:// scheme for the content to retrieve
Projection      A list of which columns to return. Null will return all columns –
                inefficient if not needed
Selection       A filter declaring which rows to return, formatted as an SQL WHERE
                clause (excluding the WHERE); null returns all rows
SelectionArgs   Selection args will be placed in any ? Place holders in the selection
sortOrder       How to order the rows, formatted as an SQL ORDER BY clause
                (excluding ORDER BY). Null specifies the default sort order
               Query Example
String[] projection = new String[] {People._ID,
            People.COUNT, People.NAME);
Uri contacts = People.CONTENT_URI;

Cursor managedCursor = managedQuery(
  contacts, //Database id
   projection, //Columns to return
   null,      //All rows
   null,      //No selection args
   People.NAME + “ ASC” //ascending order by name
• Returned by a Query
  – Positioned before the first entry or null
• Moves from record to record
  – cur.moveToFirst()
  – cur.moveToNext()
• Methods for reading each data type
  – int nameCol = cur.getColumnIndex( name)
  – String name = cur.getString( nameCol );
             Adding Records
• ContentValues object collects column name,
  column value pairs
  – ContentValues values = new ContentValues();
  – Values.put( People.NAME, “Abraham Lincoln” );
• ContentResolver.insert() adds the new record.
  – getContentResolver().insert(
    People.CONTENT_URI, values );
             SQLiteOpen Helper
• Extend the SQLiteOpenHelper class
• Database creation and version management
• Methods
  – onCreate( SQLiteDatabase)
     • Creation of tables
     • Initial population of tables
  – onUpgrade(SQLiteDatabase, int oldVersion, int
    newVersion )
     • Drop existing tables and recreate them
  – onOpen( SQLiteDatabase)
  – close()
  – getWritableDatabase()
     • Creates and opens a database for reading and writing
• Common DML database commands
  – execSQL( String sql )
     • Executes a single SQL statement that is not a query
  – close()
     • Closes the database
  – query(String table, String[] columns, String
    selection, String[] selectionArgs, String groupBy,
    String having, String orderBy)
  – delete( String table, String whereClause,
     String[] whereArgs );
     Extending ContentProvider
• Implement the following methods
  – query()
  – insert()
  – update()
  – delete()
  – getType()
  – onCreate()
      Delaring a ContentProvider
• Use a <provider> element in the
  AndroidManifest.xml file
                                                     Name of Class

<provider name=“”
                All but the path identifier in the content: URI
                        Parts of a URI
                             Identifies the source of the
                             May be a single table or a
                             query result obtained from
                             several tables


Data is                                    (Optional) Identifies a
controlled   Uniquely identifies           single data record, if
by a         the content provider.         present
content      Should correspond to
provider     package structure