Tag: sqlite

LiveData

LiveData is an observable data holder class. This means it sits between the database and UI, and monitors changes in the database. On observing a change, the LiveData object (will have its setValue method called and) will notify the Observers which will reflect the new data in the UI. Unlike a regular observable, LiveData is lifecycle-aware, meaning it respects the lifecycle of other app components, such as activities, fragments, or services. This awareness ensures LiveData only updates app component observers that are in an active lifecycle state.

Runs by default outside of the main thread.

Often used in conjunction with ViewModel.

To add the dependencies for LiveData, see here.

To observe data in a database, simply wrap the return type in a LiveData<> tag (this example corresponds to the code in the @Entity, @DAO and Build a Database posts). In the @DAO file:

@Dao
public interface ContactDao {

    @Query("SELECT * FROM contacts ORDER BY age")
    LiveData<List<ContactEntry>> loadAllContacts();

    @Insert
    void insertContact(ContactEntry contactEntry);
 
    @Update(onConflict = OnConflictStrategy.REPLACE)
    void updateContact(ContactEntry contactEntry);
 
    @Delete
    void deleteContact(ContactEntry contactEntry);
 
    @Query("SELECT * FROM contacts WHERE name = :name")
    ContactEntry loadEntryByName(String name);
}



Any references/calls to this method will need to have their type wrapped in LiveData<> also.

We set up an Observer in our Activity (do this just once at onCreate and not e.g. in onResume so that the data will only be refreshed when it is changed):


        final LiveData<List<ContactEntry>> contacts = mDb.contactDao().loadAllContacts(); // mDb is instance of database (extends RoomDatabase). contactDao is the @DAO for the database (see above)
        contacts.observe(this, new Observer<List<ContactEntry>>() { // LivecycleOwner is set to 'this' in this example
            @Override
            public void onChanged(@Nullable List<ContactEntry> contactEntries) {
                // Do something with the new data
            }
        });

@TypeConverters – SQL/Room

When storing data in SQLite databases it must conform to the accepted data types e.g. a date must be stored in TEXT, REAL or INTEGER format. Therefore we can specify code to convert a date from a timestamp to a Date object (and back), and Room will use these when reading or writing data. TypeConverters must be declared when Building A Database.

public class DateConverter {
@TypeConverter
public static Date toDate(Long timestamp) { // Room will use this when reading from the database
return timestamp == null ? null : new Date(timestamp);
}

@TypeConverter
public static Long toTimestamp(Date date) { { // Room will use this when writing to the database
return date == null ? null : date.getTime();
}
}

Build a database – Room

Code to create a Room database in correspondence with the @Entity and @DAO entries.

Create an abstract class to outline the creation of your table
@Database(entities = {ContactEntry.class}, version = 1, exportSchema = false) // Add @Database annotation and declare class(es) containing @Entity
@TypeConverters(DateConverter.class) // See the @TypeConverters post: https://an.aldezu.com/archives/454 - not in use in this example but included for context
public abstract class AppDatabase extends RoomDatabase { // Extend RoomDatabase

    private static final String LOG_TAG = AppDatabase.class.getSimpleName();
    private static final Object LOCK = new Object();
    private static final String DATABASE_NAME = "contacts"; // Define database name
    private static AppDatabase sInstance;

    public static AppDatabase getInstance(Context context) {
        if (sInstance == null) { // This will only ever generate ONE instance of the AppDatabase (Singleton pattern)
            synchronized (LOCK) {
                Log.d(LOG_TAG, "Creating new database instance");
                sInstance = Room.databaseBuilder(context.getApplicationContext(), // Requires current context, the database class and database name
                        AppDatabase.class, AppDatabase.DATABASE_NAME)
                        .build();
            }
        }
        Log.d(LOG_TAG, "Getting the database instance");
        return sInstance; // Will return new instance if null, old instance if not
    }

    public abstract ContactDao contactDao(); // Create an instance of the DAO for this database

}



To interact with the table you will need a @DAO. To define the table you will need to use @Entity.

@DAO (Data Access Object) – Room

An interface (API) which allows us to interact with our SQLite database tables. This example corresponds to the @Entity and Build A Database entries. Once these have been coded use LiveData to observe the data.

Create a custom class to define your table, using Entity to annotate it

@Dao // Required
public interface ContactDao {

    @Query("SELECT * FROM contacts ORDER BY age") // Database query
    List<ContactEntry> loadAllContacts(); // When loadAllContacts() is  called it will return a List<ContactEntry> containing the results of the query

    @Insert
    void insertContact(ContactEntry contactEntry); // Inserts a new ContactEntry into the table

    @Update(onConflict = OnConflictStrategy.REPLACE) // Set to replace (other options: Abort, Fail, Ignore, Rollback)
    void updateContact(ContactEntry contactEntry); // Update an existing Contact entry

    @Delete
    void deleteContact(ContactEntry contactEntry); // Delete an existing Contact entry

    @Query("SELECT * FROM contacts WHERE name = :name") // We can use the 'name' variable in the query by prefixing it with :
    ContactEntry loadEntryByName(String name); // Returns the ContactEntry which matches the name input by the user
}

To generate the table you will need to Build A Database. To define the table you will need to use @Entity.

@Entity – Room

Defines our SQLite database tables. This example corresponds to the @DAO and Build A Database entries.

Create a custom class to define your table, using Entity to annotate it
@Entity(tableName = "contacts") // Name of table specified as 'contacts'. It is common for database tables and their corresponding classes to not have same name
public class ContactEntry {

    @PrimaryKey(autoGenerate = true) // States that the next variable will be the table's primary key (and therefore unique)
    private int id; // Each member variable corresponds to a column in the table
    private String name; // Each member variable corresponds to a column in the table
    @ColumnInfo(name = "contactAge") // If you wish to specifically state the name of the column use this annotation
    private int age; // Each member variable corresponds to a column in the table

    @Ignore // If you do not want a variable to appear in the table use the @ignore annotation
    private String address;

    @Ignore // Tell Room to use the other constructor (which includes the id field) using the @Ignore annotation, as Room can only use one constructor. This constructor will be used when the user adds a new entry, as they will not know the id of the new entry.
    public ContactEntry (String name, int age) {
        this.description = name;
        this.age = age;
    }

    public ContactEntry (int id, String name, int age) {
        this.id = id;
        this.description = name;
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name= name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}



To create the table you will need to Build A Database. To interact with the table you will need to create a @DAO.

Room

Room is one of the Android App Architecture Components. The Room persistence library (Object Relational Mapping library) provides an abstraction layer over SQLite to allow for more robust database access while harnessing the full power of SQLite. It will map our database objects to Java objects.

Its features include:

  • Less boilerplate code
  • SQL Validation at compile time – incorrect SQL statements will be picked up
  • Built to work with LiveData and RxJava for data observation

Room uses Annotations and has 3 main components:

  • @Entity – to define our database tables
  • @DAO – provides an API for reading and writing data
  • @Database – represents a database holder. Includes a list of @Entity and @DAO annotations and allow us to create a new database or acquire a connection to our db at runtime

To add the dependencies for Room, see here.

SQLite Databases

SQLite databases are commonly used for on-device storage of data. Databases can be created and manipulated using the standard SQL commands. The following example uses a Contract class file to define the names of the table columns and the URI for the Content Authority, which would allow a third-party app access to the data in your database if you wished it to be a Content Provider:

Create a Contract class:
import android.content.ContentResolver;
import android.content.ContentUris;
import android.net.Uri;
import android.provider.BaseColumns;

public class ExampleContract implements BaseColumns {

    // This is the content authority for our app Content Provider.
    public static final String CONTENT_AUTHORITY = "com.example.android.ourapp";

    // This is the {@link Uri} on which all other Uris for our app are built.
    public static final Uri BASE_CONTENT_URI = Uri.parse("content://" + CONTENT_AUTHORITY);

    // The path for a particular database of data e.g. 'data'
    public static final String PATH_DATA = "data";

    // This is the {@link Uri} used to get a full list of names and ages.
    public static final Uri CONTENT_URI =
            BASE_CONTENT_URI.buildUpon().appendPath(PATH_DATA).build();


    // This is a String type that denotes a Uri references a list or directory.
    public static final String CONTENT_TYPE =
            ContentResolver.CURSOR_DIR_BASE_TYPE + "/" + CONTENT_AUTHORITY + "/" + PATH_TERMS;

    // This is a String type that denotes a Uri references a single item.
    public static final String CONTENT_ITEM_TYPE =
            ContentResolver.CURSOR_ITEM_BASE_TYPE + "/" + CONTENT_AUTHORITY + "/" + PATH_TERMS;


    // Declaring all these as constants makes code a lot more readable.
    // It also looks a more like SQL.

    // This is the version of the database
    public static final int DATABASE_VERSION = 1;

    // This is the name of the SQL table for data.
    public static final String DATA_TABLE = "data_entries";
    // This is the name of the SQL database for data.
    public static final String DATABASE_NAME = "data";

    // This is the column name in the SQLiteDatabase for our first column, 'name'.
    public static final String COLUMN_NAME = "name";
    // This is the column name in the SQLiteDatabase for our first column, 'age'.
    public static final String COLUMN_AGE = "age";

    // This is an array containing all the column headers in the data table.
    public static final String[] COLUMNS =
            {_ID, COLUMN_NAME, COLUMN_AGE};

    // This is the index of the ID in the data table
    public static final int COLUMN_INDEX_ID = 0;
    // This is the index of the name in the data table
    public static final int COLUMN_INDEX_NAME = 1;
    // This is the index of the age in the data table
    public static final int COLUMN_INDEX_AGE = 2;

    /**
     * This method creates a {@link Uri} for a single piece of data, referenced by id.
     * @param id The id of the term.
     * @return The Uri with the appended id.
     */
    public static Uri buildDataUriWithId(long id) {
        return ContentUris.withAppendedId(CONTENT_URI, id);
    }
}



Create a helper class to handle the SQL commands (in separate class file):


public class DataDbHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "data.db";
    private static final int DATABASE_VERSION = 1;

    public DataDbHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        /*
         * This String will contain a simple SQL statement that will create a table that will
         * cache our data.
         */
        final String SQL_CREATE_DATA_TABLE =

                "CREATE TABLE " + ExampleContract.DATA_TABLE + " (" +

                        /*
                         * Our contract did not explicitly declare a column called "_ID". However,
                         * it implements the interface "BaseColumns" which does have a field
                         * named "_ID". We use that here to designate our table's primary key.
                         */
                        ExampleContract._ID               + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        ExampleContract.COLUMN_NAME       + " VARCHAR, "                 +
                        ExampleContract.COLUMN_AGE + " VARCHAR" + ");";

        /*
         * After we've spelled out our SQLite table creation statement above, we actually execute
         * that SQL with the execSQL method of our SQLite database object.
         */
        db.execSQL(SQL_CREATE_DATA_TABLE);
    }

    /**
     * This example database is only a cache for online data, so its upgrade policy is simply to discard
     * the data and call through to onCreate to recreate the table. Note that this only fires if
     * you change the version number for your database (in our case, DATABASE_VERSION). It does NOT
     * depend on the version number for your application found in your app/build.gradle file. If
     * you want to update the schema without wiping data, commenting out the current body of this
     * method should be your top priority before modifying this method.
     *
     * @param sqLiteDatabase Database that is being upgraded
     * @param oldVersion     The old database version
     * @param newVersion     The new database version
     */

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + ExampleContract.DATA_TABLE);
        onCreate(db);
    }
}



To make use of this database use a Cursor

ud851-Sunshine-student\S07.03???