Is these a simple way to determine whether exist a record by id using Room?

Question

The following code is based the project https://github.com/enpassio/Databinding

I hope to determine whether exist a record by id, so I write the following code.

I think my code is not good, is these a simple way to determine whether exist a record by id using Room?

Can I directly a SQl and return Booean with @Dao? If so, how can I do?

Code

@Dao
interface ToyDao {

    @get:Query("SELECT * FROM toys")
    val allToys: LiveData<List<ToyEntry>>

    @Query("SELECT * FROM toys WHERE toyId = :id")
    fun getChosenToy(id: Int): LiveData<ToyEntry>

    ...
}



class ToyRepository private constructor(private val mDatabase: ToyDatabase, private val mExecutors: AppExecutors) {

    val toyList: LiveData<List<ToyEntry>>
        get() = mDatabase.toyDao().allToys

    fun getChosenToy(toyId: Int): LiveData<ToyEntry> {
        return mDatabase.toyDao().getChosenToy(toyId)
    }

    
    
    fun isExistRecord(toyId: Int):Boolean{
        val my=getChosenToy(toyId)
        if (my.value==null) return false else return true
    }

    ...
}

Answer 1

according to @Yarin's answer, codes below I test can simply work.

@Query("SELECT EXISTS (SELECT * FROM _table WHERE _id = :id)")
fun exists(id: Id): Boolean

---

But as I test, it seems not speed up with this method, maybe I'm not write in good way..

Environment

Emulator Pixel 2 API 28

100000+ Rows

Query 10000 times in 1372ms

@Query("SELECT EXISTS (SELECT * FROM _table WHERE _id = :id)")
fun exists(id: Long): Boolean

Query 10000 times in 1364ms

@Query("SELECT * FROM _table WHERE _id = :id")
fun getItem(id: Long): Item

---

UPDATE

You can use LIMIT as TOP in SQLite, and usually here uses List for multiple results ( if LIMIT is not 1 ).

@Query("SELECT * FROM _table WHERE _id = :id LIMIT 1")
fun getItem(id: Long): List<Item>

and it could be also Item because we know the result will be single.

@Query("SELECT * FROM _table WHERE _id = :id LIMIT 1")
fun getItem(id: Long): Item

Answer 2

you can check out this thread fastest-way-to-determine-if-record-exists

basically you have 2 options:

1. use the TOP 1 and it will look like - SELECT TOP 1 toys.id FROM toys WHERE toys.id = :id
2. use EXISTS or NO EXISTS and it will look like -

IF EXISTS (SELECT * FROM toys WHERE id = :id)
BEGIN
--do what you need if exists
END
ELSE
BEGIN
--do what needs to be done if not
END