using room database with android, kotlin

roomdatabase.md

Integrate to Project

App level gradle

.....

apply plugin: 'kotlin-kapt'

.....

    // Room components
    implementation "androidx.room:room-runtime:2.1.0"
    kapt "androidx.room:room-compiler:2.1.0"

.....

Creating Entity

Entity is like the table in the database. for each table you will need the promary key and other columns. all those things are defined here.

import androidx.annotation.NonNull
import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "word_table")
data class Word(
    @PrimaryKey
    @NonNull
    @ColumnInfo(name = "word")
    val mWord: String
)

Define the DAO

the Database Access Object is the interface where you define the operations for a entity.

import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query

@Dao
interface WordDao {

    @Insert
    fun insert(word: Word)

    @Query("DELETE FROM word_table")
    fun deleteAll()

    @Query("SELECT * FROM word_table ORDER BY word ASC")
    fun getAllWord(): LiveData<List<Word>>
}

Create the Database

Now as we are done with entity and DAO we can combine those and create the database.

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(entities = [Word::class], version = 1)
abstract class WordRoomDatabase : RoomDatabase() {
    abstract fun wordDao(): WordDao
}

the entities as well as DAOs are defined here. after this we need to create object for this class and then use it to perform database operations.

private lateinit var roomDatabase: WordRoomDatabase
private lateinit var dao: WordDao

roomDatabase = Room.databaseBuilder(
    applicationContext,
    WordRoomDatabase::class.java,
    "word_database.db"
).build()

dao = roomDatabase.wordDao()

//to insert into databas
Thread(
    Runnable {
        dao.insert(Word("Test word"))
    }
).start()

//to get data from database
Handler().postDelayed(
    {
        dao.getAllWord().observe(
            this,
            Observer<List<Word>> { t ->
                //your code here
            }
        )
    },
    3000
)

You should be creating the singleton for the database and use in the app. the above code is done inside the activity just for demo. Evey database operation has to be done outside of the main thread.

SQL Concepts

Union

This is used to combine result of two statement into a single result, provided -

• The number and the order of the columns must be the same in both queries
• The data types of the corresponding columns must be the same or compatible.

By default union removes the duplicate rown but if you want the duplicate rows too use union_all.

Intersect

Used to get the common rown from two tables. and the conditions applied are same as Union.

Except

Used to get the result that is not in the other result set.

SELECT columns_names FROM table1 EXCEPT SELECT column_name FROM table2

Join

Used to combind the result from two or more tables based on the common column value between them.

Types -

1. Inner
2. Outer
3. Left
4. Right

Example Table -

Student

Roll Number	Name	Address	Phone	Age
1	Harsh	Delhi	xxxxxxxxxx	18
2	Pratik	Bihar	xxxxxxxxxx	19
3	Riyanka	Siliguri	xxxxxxxxxx	20
4	Deep	Ramnagar	xxxxxxxxxx	18
5	Saptarhi	Kolkata	xxxxxxxxxx	19
6	Dhanraj	Barabajar	xxxxxxxxxx	20
7	Rohit	Belurghat	xxxxxxxxxx	18
8	Niraj	Alipore	xxxxxxxxxx	19

Student Course

Course Id	Roll Number
1	1
2	2
2	3
3	4
1	5
4	9
5	10
4	11

Inner

Shows the data which is common between both the tables.

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Course Id	Name	Age
1	Harsh	18
2	Pratik	19
2	Riyanka	20
3	Deep	18
1	Saptarhi	19

Left Join

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join.

If there is no matching rows then the result set will be null.

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
LEFT JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Name	Course Id
Harsh	1
Pratik	2
Riyanka	2
Deep	3
Saptarhi	1
Dhanraj	null
Rohit	null
Niraj	null

Right Join

This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join.

If there is no matching rows then the result set will be null.

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
RIGHT JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Name	Course Id
Harsh	1
Pratik	2
Riyanka	2
Deep	3
Saptarhi	1
null	4
null	5
null	4

Full Join

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
FULL JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Name	Course Id
Harsh	1
Pratik	2
Riyanka	2
Deep	3
Saptarhi	1
null	4
null	5
null	4
Dhanraj	null
Rohit	null
Niraj	null

Android Project with room implementation

SQL Queries CheatSheet 1

SQL Queries CheatSheet 2

SQL Queries CheatSheet 3