Simple Local Database using Room

by Andhika Yuana

Hola!

Andhika Yuana

Co-Founder @ ajaro.id

Lead Android Dev. @ qiscus

andhikayuana@gmail.com

085-741-851-967

Outside Programmer World

Old School SQLite

public class PostsDatabaseHelper extends SQLiteOpenHelper {
    // Database Info
    private static final String DATABASE_NAME = "postsDatabase";
    private static final int DATABASE_VERSION = 1;

    // Table Names
    private static final String TABLE_POSTS = "posts";
    private static final String TABLE_USERS = "users";

    // Post Table Columns
    private static final String KEY_POST_ID = "id";
    private static final String KEY_POST_USER_ID_FK = "userId";
    private static final String KEY_POST_TEXT = "text";

    // User Table Columns
    private static final String KEY_USER_ID = "id";
    private static final String KEY_USER_NAME = "userName";
    private static final String KEY_USER_PROFILE_PICTURE_URL = "profilePictureUrl";

    private static PostsDatabaseHelper sInstance;

    public static synchronized PostsDatabaseHelper getInstance(Context context) {
        // Use the application context, which will ensure that you 
        // don't accidentally leak an Activity's context.
        // See this article for more information: http://bit.ly/6LRzfx
        if (sInstance == null) {
            sInstance = new PostsDatabaseHelper(context.getApplicationContext());
        }
        return sInstance;
    }

    /**
     * Constructor should be private to prevent direct instantiation.
     * Make a call to the static method "getInstance()" instead.
     */
    private PostsDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    // Called when the database connection is being configured.
    // Configure database settings for things like foreign key support, write-ahead logging, etc.
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    // Called when the database is created for the FIRST time.
    // If a database already exists on disk with the same DATABASE_NAME, this method will NOT be called.
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_POSTS_TABLE = "CREATE TABLE " + TABLE_POSTS +
                "(" +
                    KEY_POST_ID + " INTEGER PRIMARY KEY," + // Define a primary key
                    KEY_POST_USER_ID_FK + " INTEGER REFERENCES " + TABLE_USERS + "," + // Define a foreign key
                    KEY_POST_TEXT + " TEXT" +
                ")";

        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS +
                "(" +
                    KEY_USER_ID + " INTEGER PRIMARY KEY," +
                    KEY_USER_NAME + " TEXT," +
                    KEY_USER_PROFILE_PICTURE_URL + " TEXT" +
                ")";

        db.execSQL(CREATE_POSTS_TABLE);
        db.execSQL(CREATE_USERS_TABLE);
    }
    
...
// Called when the database needs to be upgraded.
    // This method will only be called if a database already exists on disk with the same DATABASE_NAME,
    // but the DATABASE_VERSION is different than the version of the database that exists on disk.
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion != newVersion) {
            // Simplest implementation is to drop all old tables and recreate them
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_POSTS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
            onCreate(db);
        }
    }

    // Insert a post into the database
    public void addPost(Post post) {
        // Create and/or open the database for writing
        SQLiteDatabase db = getWritableDatabase();

        // It's a good idea to wrap our insert in a transaction. This helps with performance and ensures
        // consistency of the database.
        db.beginTransaction();
        try {
            // The user might already exist in the database (i.e. the same user created multiple posts).
            long userId = addOrUpdateUser(post.user);

            ContentValues values = new ContentValues();
            values.put(KEY_POST_USER_ID_FK, userId);
            values.put(KEY_POST_TEXT, post.text);

            // Notice how we haven't specified the primary key. SQLite auto increments the primary key column.
            db.insertOrThrow(TABLE_POSTS, null, values);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to add post to database");
        } finally {
            db.endTransaction();
        }
    }

    // Insert or update a user in the database
    // Since SQLite doesn't support "upsert" we need to fall back on an attempt to UPDATE (in case the
    // user already exists) optionally followed by an INSERT (in case the user does not already exist).
    // Unfortunately, there is a bug with the insertOnConflict method
    // (https://code.google.com/p/android/issues/detail?id=13045) so we need to fall back to the more
    // verbose option of querying for the user's primary key if we did an update.
    public long addOrUpdateUser(User user) {
        // The database connection is cached so it's not expensive to call getWriteableDatabase() multiple times.
        SQLiteDatabase db = getWritableDatabase();
        long userId = -1;

        db.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            values.put(KEY_USER_NAME, user.userName);
            values.put(KEY_USER_PROFILE_PICTURE_URL, user.profilePictureUrl);

            // First try to update the user in case the user already exists in the database
            // This assumes userNames are unique
            int rows = db.update(TABLE_USERS, values, KEY_USER_NAME + "= ?", new String[]{user.userName});

            // Check if update succeeded
            if (rows == 1) {
                // Get the primary key of the user we just updated
                String usersSelectQuery = String.format("SELECT %s FROM %s WHERE %s = ?",
                        KEY_USER_ID, TABLE_USERS, KEY_USER_NAME);
                Cursor cursor = db.rawQuery(usersSelectQuery, new String[]{String.valueOf(user.userName)});
                try {
                    if (cursor.moveToFirst()) {
                        userId = cursor.getInt(0);
                        db.setTransactionSuccessful();
                    }
                } finally {
                    if (cursor != null && !cursor.isClosed()) {
                        cursor.close();
                    }
                }
            } else {
                // user with this userName did not already exist, so insert new user
                userId = db.insertOrThrow(TABLE_USERS, null, values);
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to add or update user");
        } finally {
            db.endTransaction();
        }
        return userId;
    }

    // Get all posts in the database
    public List<Post> getAllPosts() {
        List<Post> posts = new ArrayList<>();

        // SELECT * FROM POSTS
        // LEFT OUTER JOIN USERS
        // ON POSTS.KEY_POST_USER_ID_FK = USERS.KEY_USER_ID
        String POSTS_SELECT_QUERY =
                String.format("SELECT * FROM %s LEFT OUTER JOIN %s ON %s.%s = %s.%s",
                        TABLE_POSTS,
                        TABLE_USERS,
                        TABLE_POSTS, KEY_POST_USER_ID_FK,
                        TABLE_USERS, KEY_USER_ID);

        // "getReadableDatabase()" and "getWriteableDatabase()" return the same object (except under low
        // disk space scenarios)
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(POSTS_SELECT_QUERY, null);
        try {
            if (cursor.moveToFirst()) {
                do {
                    User newUser = new User();
                    newUser.userName = cursor.getString(cursor.getColumnIndex(KEY_USER_NAME));
                    newUser.profilePictureUrl = cursor.getString(cursor.getColumnIndex(KEY_USER_PROFILE_PICTURE_URL));

                    Post newPost = new Post();
                    newPost.text = cursor.getString(cursor.getColumnIndex(KEY_POST_TEXT));
                    newPost.user = newUser;
                    posts.add(newPost);
                } while(cursor.moveToNext());
            }
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to get posts from database");
        } finally {
            if (cursor != null && !cursor.isClosed()) {
                cursor.close();
            }
        }
        return posts;
    }

    // Update the user's profile picture url
    public int updateUserProfilePicture(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_USER_PROFILE_PICTURE_URL, user.profilePictureUrl);

        // Updating profile picture url for user with that userName
        return db.update(TABLE_USERS, values, KEY_USER_NAME + " = ?",
                new String[] { String.valueOf(user.userName) });
    }

    // Delete all posts and users in the database
    public void deleteAllPostsAndUsers() {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        try {
            // Order of deletions is important when foreign key relationships exist.
            db.delete(TABLE_POSTS, null, null);
            db.delete(TABLE_USERS, null, null);
            db.setTransactionSuccessful();
        } catch (Exception e) {
            Log.d(TAG, "Error while trying to delete all posts and users");
        } finally {
            db.endTransaction();
        }
    }    
}

Major Problem with SQLite

  • There is no compile-time verification of raw SQL queries.

  • As your schema changes you need to update the affected SQL queries manually.

  • You need to use lots of boilerplate code to convert between SQL queries and Java data objects.

Room ?

This one ?

No!

Room: a SQLite object mapping library

Why we should use Room ?

Simple!

How to use ?

dependencies {
    ...

    def room_version = "1.1.1"

    implementation "android.arch.persistence.room:runtime:$room_version"
    kapt "android.arch.persistence.room:compiler:$room_version"

    // optional - RxJava support for Room
    implementation "android.arch.persistence.room:rxjava2:$room_version"
}
defaultConfig {
    ...

    kapt {
        arguments {
            arg("room.schemaLocation", "$projectDir/schemas".toString())
        }
    }

}
build.gradle
build.gradle

Room Architecture Diagram

Database

Contains the database holder and serves as the main access point for the underlying connection to your app's persisted, relational data.

Database


@Database(entities = arrayOf(ProductEntity::class), version = 1)
abstract class BelanjaDatabase : RoomDatabase() {

    abstract fun productDao(): ProductDao
}

Entity

Represents a table within the database.

Entity

@Entity(tableName = "products")
data class ProductEntity(
        @ColumnInfo(name = "name") var name: String,
        @ColumnInfo(name = "price") var price: Int,
        @ColumnInfo(name = "image") var image: String
) {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int = 0
}

DAO

DAO (Data Access Object), contains the methods used for accessing the database.

DAO

@Dao
interface ProductDao {

    @Query("SELECT * FROM products")
    fun getProductsWithRx(): Flowable<List<ProductEntity>>

    @Query("SELECT * FROM products")
    fun getProducts(): List<ProductEntity>

    @Query("SELECT * FROM products WHERE id = :id")
    fun getProductWithRx(id: Int): Single<ProductEntity>

    @Query("SELECT * FROM products WHERE id = :id")
    fun getProduct(id: Int): ProductEntity

    @Insert(onConflict = REPLACE)
    fun insert(productEntity: ProductEntity)

    @Insert(onConflict = REPLACE)
    fun insert(vararg productEntity: ProductEntity)

    @Update(onConflict = REPLACE)
    fun update(productEntity: ProductEntity)

    @Delete
    fun delete(productEntity: ProductEntity)
}

Action!


//create database instance
val db = Room.databaseBuilder(this, BelanjaDatabase::class.java, "belanja_db").build()

//insert product
val product = ProductEntity("Sepatu Mahal", 300000, "https://anu.com/sepatu.jpg")
db.productDao().insert(product)

//insert product using vararg
db.productDao().insert(
        ProductEntity("Kaos Libran", 200000, "https://anu.com/kaos.jpg"),
        ProductEntity("Celana Mahal", 500000, "https://anu.com/celana.jpg"),
        ProductEntity("Baju Batik", 600000, "https://anu.com/baju-batik.jpg")
)

//get all product
db.productDao().getProducts()

Action!


//get product by id 1
val product1 = db.productDao().getProduct(1)

//update product id 1
product1.name = "Sepatu Harga Terjangkau"
product1.price = 100000
product1.image = "https://anu.com/ini-sepatu-terjangkau.jpg"
db.productDao().update(product1)

//delete product
val product3 = db.productDao().getProduct(3)
db.productDao().delete(product3)

Action!


//get all product using rx and map to list of name
db.productDao().getProductsWithRx()
                .flatMap { Flowable.fromIterable(it) }
                .map { it.name }
                .toList()

What's this ?

java.lang.IllegalStateException: 
Cannot access database on the main thread since it may potentially lock the UI for a long period of time

Solved or not ?


//create database instance
val db = Room.databaseBuilder(this, BelanjaDatabase::class.java, "belanja_db")
        .allowMainThreadQueries()
        .build()

// .allowMainThreadQueries()
// for allow db operation in Main Thread, but not recommended in production
.allowMainThreadQueries() will cause...

Note

Room doesn't support database access on the main thread unless you've called .allowMainThreadQueries() on the builder because it might lock the UI for a long period of time.

 

Asynchronous queries—queries that return instances of LiveData or Flowable—are exempt from this rule because they asynchronously run the query on a background thread when needed.

API Reference

Any Questions ?

Simple Local Database using Room

By Andhika Yuana

Simple Local Database using Room

Amikom Business Park Meetup

  • 987