SQLite Databases

Introduction
- Android provides full support for SQLite databases. Databases are accessible by name in the application, but not outside the application.
- Databases provide persistent storage for large amounts of data.
- The recommended approach is to extend the SQLiteOpenHelper class.
- The DB file is stored in /data/data/package name/databases
Using SQLiteOpenHelper
- Extend the SQLiteOpenHelper class with a nested (or not) class.
- Implement the onCreate method to create your database tables.
- Implement the onUpgrade method to deal with database upgrades (by version number).
- Override the onOpen method for things to do when opening the database.
- Use the getWritableDatabase method to get a SQLiteDatabase with read/write access, or the getReadableDatabase method to get one with read-only access.
Using SQLiteDatabase
- Use ContentValues to transfer data to and from a DB.
- Use the insert method to insert data.
- Use the update method to update data.
- Use the delete method to delete data.
- Use the execSQL method to execute raw SQL.
- Use the query method to execute SQLite queries. A Cursor is returned.
- Use the close method when you're done.
Using a SimpleCursorAdapter
- If you implement OnItemClickListener then the last argument to onItemClick is the _id of the database row that was used to create this list item.
Example
MainActivity.java
activity_main.xml
list_item.xml
DataSQLiteDB.java
strings.xml
AndroidManifest.xml
- The DB fields are _ID audio_media_id song_name plays
- The audio cursor fields are _ID TITLE DATA
- The audio _ID gets stored in the DB audio_media_id
- Add <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" /> to the manifest.
The Room Wrapper
- The Room persistence library provides an abstraction layer over SQLite to allow for more robust database access while harnessing the full power of SQLite.
- There are 3 major components in Room. The various components are annotated to allow automatic generation of corresponding Java code that manipulates the SQL database.
  - A Database defines an abstract class for the database. A database contains the database holder, and serves as the main access point for the underlying connection to the relational data.
    - The abstract class is annotated with @Database
    - A method to return a DAO is always defined.
  - An Entity defines a row of a database table
    - The class is annotated with @Entity
    - The table attributes are defined using @PrimaryKey and @ColumnInfo annotations.
    - Every attribute must have a get and set method.
    - A database can have multiple entities
  - The DAO (one per database) defines access to the database by an interface definition
    - The interface is annotated with @Dao
    - Method signatures annotated with @Query define SQL queries in the database.
    - Method signatures annotated with @Insert, @Update, and @Delete do those things with entities provided as arguments.
- The app creates and accesses the database using the databaseBuilder method.
  - Database access should not be done on the main thread. Rather use an asynchronous alternative. But for testing, use allowMainThreadQueries() when building the database.
  - There is lots of autogenerated code below the Room abstraction.
- The app's build.gradle needs some extra incantations.
Example
MainActivity.java
activity_main.xml
list_item.xml
DataRoomDB.java
DataRoomEntity.java
DataRoomAccess.java
strings.xml
AndroidManifest.xml
build.gradle