CPET 565 Mobile Computing Systems

Lecture on

Android SQLite for Structured Data Management

Topics of Discussion

· Relational Database and Terminology (Terms)[1][2][3]

· Storing Structured Data on Android Device Using SQLite Databases [4][5]

· Android SQLiteDatabase [6]

Storing Structured Data on Android Device Using SQLite Databases [1]

Creating a SQLite Database

· Creating a SQLite database instance using the Application Context

· Finding the application’s database file on the device file system

· Configuring the SQLite database properties

· Creating tables and other SQLite schema objects

Creating, Updating, and Deleting Database Records

· Inserting records

· Updating records

· Deleting records

· Working with transactions

Querying SQLite Databases

· Working with Cursors

· Managing Cursors as part of the application lifecycle

· Inserting rows of query results and extracting specific data

· Executing simple query

· Executing more complex queries using SQLiteQueryBuilder

· Executing raw queries without Builders and column-mapping

Closing and Deleting a SQLIte Database

· Deleting tables and other SQLite objects

· Closing a SQLIte database

· Deleting a SQLite database instance using the Application Context

Designing Persistent Databases

· Keeping track of database field names

· Extending the SQLiteOpenHelper class

Binding Data to the Application User Interface

· Working with database data

· Binding data to controls using data adapters

· Storing nonprimitive types (such an images) into the database

public final class SQLIteDatabase extends SQLIteClosable, http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

Class Overview [6]

· Expose methods to manage a SQLite database

· Has methods to create, delete, execute SQL commands, and perform other common database management tasks

Public Methods

· void beginTransaction()

o // EXCLUSIVE mode (Exclusive lock state operation for writing to DB operation):

o A database can be in one of the 5 states: UNLOCKED, SHARED, RESERVED, PENDING, and EXCLUSIVE, http://www.sqlite.org/lockingv3.html

· void beginTransactionNonExclusive() // IMMEDIATE Mode

· void beginTransactionWithListener()

· ..

SimpleDatabase Example Program from Chapter 10[7]

References

[ 1] Database, http://en.wikipedia.org/wiki/Database

[ 2] Relational database, http://en.wikipedia.org/wiki/Relational_database

[ 3] Lesson 2: Understanding Database Terminology, Database Fundamentals, College of Information Sciences and Technology, The Pennsylvania State University, http://www.personal.psu.edu/glh10/ist110/topic/topic07/topic07_03.htmlAndroid SQLiteDatabase, http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

[ 4] SQL keywords (commands) as Understood by SQLite, http://www.sqlite.org/lang.html

[ 5] SQLIte, http://www.sqlite.org/

[ 6] Android SQLiteDatabase, http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

[ 7] Android Wirelss Application Development, 2^nd edition, by Lauren Darcey and Shane Conder, published by Addison-Wesley

[ 8]