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"
.....
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
)
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>>
}
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.
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.
Used to get the common rown from two tables. and the conditions applied are same as Union.
Used to get the result that is not in the other result set.
SELECT columns_names FROM table1 EXCEPT SELECT column_name FROM table2
Used to combind the result from two or more tables based on the common column value between them.
Types -
- Inner
- Outer
- Left
- 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 |
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 |
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 |
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 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 |