Interacting with SQLite Databases in Android Development

SQLite in Android Landon Cox Landon Cox March 2, 2017 March 2, 2017

What is storage? Storage is persistent Storage is persistent Stored state retains its value across launches, reboots Primary interface to storage: files Primary interface to storage: files Files can be opened, read, written to, and closed Databases are often implemented on top of files Databases provide a higher-level storage interface (e.g., SQL) Java provides a wealth of ways to interact with storage/files Your code isn t going to be very useful without storage Your code isn t going to be very useful without storage

Launch onCreate() onStart() onRestart() onResume() Process killed Running onPause() onStop() onDestroy() Shut down

Saving quiz state Apps will need to write to storage too Apps will need to write to storage too Users may expect to pick up where they left off if the app exits What quiz state should you save in case the app exits? Several ways to do this Several ways to do this SharedPreferences for simple key-value maps SQLite for more complex kinds of state SharedPreferences SharedPreferences Get a reference to SharedPreferences through Context To write: edit, put*, commit To read: get* Last time: we remembered which quiz we were taking Last time: we remembered which quiz we were taking

Saving quiz state What else might we want to save? What else might we want to save? Which quizzes we ve taken The user s progress on each quiz The user s final score on completed quiz How long the user took to complete the quiz Using a key Using a key- -value store for all of this will become unwieldy value store for all of this will become unwieldy Instead: use a relational database like SQLite! Instead: use a relational database like SQLite!

Relational databases Data is organized into tables Data is organized into tables Tables have named, typed columns Data is stored as rows in a table Can place constraints on columns (e.g., uniqueness) Structure + constraints define the schema Read/write the data base with SQL Read/write the data base with SQL Structured Query Language (SQL) SQL is declarative It describes what result you want, not how to compute it Example databases: Example databases: mysql mysql, , postgresql postgresql, , sqlite sqlite

SQLite SQLite is the primary database for Android apps SQLite is the primary database for Android apps Classes for managing your app s SQLite database Classes for managing your app s SQLite database Contract class w/ inner BaseColumns class DbHelper class that extends SQLiteOpenHelper Cursor for iterating through answers to queries https://developer.android.com/training/basics/data-storage/databases.html

Define the contract/schema Contract class Contract class Place to put all constants related to your database BaseColumns BaseColumns inner class Table names Column names inner class One One BaseColumns BaseColumns class for each table in the class for each table in the db db _id _id quiz_title quiz_title num_correct num_correct num_wrong num_wrong last_question last_question finished_quiz finished_quiz timestamp timestamp 0 0 Duke Basketball Duke Basketball 0 0 0 0 0 0 0 0 1488460945 1488460945

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Keywords: create, table CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Table name: quizprogress CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Column name: _ID CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Column type: INT CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Unique to each row: PRIMARY KEY CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Column name: quiz_title CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Create the database SQLiteOpenHelper SQLiteOpenHelper class Gives references to SQLiteDatabase instance constructor: super call to create db onCreate: create SQL command to create tables class Column type: TEXT CREATE TABLE quizprogress (_ID INT PRIMARY KEY, quiz_title TEXT, num_correct INT, num_wrong INT, last_question INT, finished_quiz INT, timestamp INT);

Put information in the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can use insert method INSERT INTO quizprogress (quiz_title, VALUES ( Duke Basketball , 0, 0, 0, 0, 1488460945) ; num_correct, num_wrong, last_question, finished_quiz, timestamp)

Put information in the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can use insert method Keywords: INSERT, INTO, VALUES INSERT INTO quizprogress (quiz_title, VALUES ( Duke Basketball , 0, 0, 0, 0, 1488460945) ; num_correct, num_wrong, last_question, finished_quiz, timestamp)

Put information in the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can use insert method List of columns to update INSERT INTO quizprogress (quiz_title, VALUES ( Duke Basketball , 0, 0, 0, 0, 1488460945) ; num_correct, num_wrong, last_question, finished_quiz, timestamp)

Put information in the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can use insert method INSERT INTO quizprogress (quiz_title, VALUES ( Duke Basketball , 0, 0, 0, 0, 1488460945) ; num_correct, num_wrong, last_question, finished_quiz, timestamp) Values for each column in new row

Read from the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use query method SELECT _ID, timestamp FROM quizprogress WHERE quiz_title LIKE Duke Basketball ORDER BY timestamp DESC;

Read from the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use query method SELECT _ID, timestamp FROM quizprogress WHERE quiz_title LIKE Duke Basketball ORDER BY timestamp DESC; Keywords: SELECT, FROM, WHERE, LIKE, ORDER BY, DESC

Read from the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use query method SELECT _ID, timestamp FROM quizprogress WHERE quiz_title LIKE Duke Basketball ORDER BY timestamp DESC; Columns in result: _ID, timestamp

Read from the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use query method SELECT _ID, timestamp FROM quizprogress WHERE quiz_title LIKE Duke Basketball ORDER BY timestamp DESC; Table to query: quizprogress

Read from the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use query method SELECT _ID, timestamp FROM quizprogress WHERE quiz_title LIKE Duke Basketball ORDER BY timestamp DESC; Filter rows using quiz_title

Read from the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use query method SELECT _ID, timestamp FROM quizprogress WHERE quiz_title LIKE Duke Basketball ORDER BY timestamp DESC; Return results in descending order using timestamp

Update the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use update method UPDATE quizprogress SET timestamp = 1488460945 WHERE quiz_title LIKE Duke Basketball ;

Update the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use update method UPDATE quizprogress SET timestamp = 1488460945 WHERE quiz_title LIKE Duke Basketball ; Keywords: UPDATE, SET, WHERE, LIKE

Update the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use update method Table to use: quizprogress UPDATE quizprogress SET timestamp = 1488460945 WHERE quiz_title LIKE Duke Basketball ;

Update the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use update method New column value: timestamp UPDATE quizprogress SET timestamp = 1488460945 WHERE quiz_title LIKE Duke Basketball ;

Update the database SQLiteDatabase SQLiteDatabase Can submit raw SQL queries w/ execSQL Can also use update method UPDATE quizprogress SET timestamp = 1488460945 WHERE quiz_title LIKE Duke Basketball ; Specify which rows to update