CODEWITHSUNDEEP
androidsqlwildcardandroid-room
aminakoy
aminakoy

Reputation: 423

Room Persistance Library - Query with WildCards

in my Room Dao class, I have the following query method:

@Query("SELECT * FROM word_table WHERE firstName LIKE :word")
fun findByName(word:String): DataSource.Factory<Int, Word>

I want also to retrieve the names with wildcards where the beginning and ending of the word can be anything. So, a query like "book", should also return the words "CarBook", "MathBook", "BooksForLearning", "TheBooksToTry". So, the query should catch words where the term "book" can be at any position. Is that possible with the SQL keyword LIKE or do I need to add other keywords ?

In some SQL queries, I have seen that the % is added at the beginning at ending (in my case, would that be "%" + :word + "%" ??), but I did not know how to do in Room.

Upvotes: 0

Views: 763

Answers (3)

Simon W
Simon W

Reputation: 51

The best place for me to add the wildcards is within the DAO, by inserting an "empty query", so you don't need to care about wildcards when calling the function in your activity.

Like:

@Query("") fun findByName(word:String):List<YouDataModel> {
return queryFindByName("%$word%")
}

@Query("SELECT * FROM word_table WHERE firstName LIKE :word") fun queryFindByName(word:String):List<YouDataModel>

Upvotes: 0

Wami ikechukwu
Wami ikechukwu

Reputation: 57

How to Use SQL Wide cards in ROOM Persistance Library (Android)

So this is how i was able to do achieve this.

Step One

In your DAO class

@Query("SELECT * FROM word_table WHERE firstName LIKE :word") fun findByName(word:String):List<YouDataModel>

NOTE: i am returning the result as a List, you can return it which ever way you want

Step Two

In your View Model class

var searchResult = MutableLiveData<List<YouDataModel>>()

fun findByName(word: String?): LiveData<List<YouDataModel>> { 
     viewModelScope.launch(Dispatchers.IO) {
         val result = adsRepo.findByName(word)
         searchResult .postValue(result)
     }
  return searchResult 
}

STEP THERE

In your Activity or Fragment

val viewModel = ViewModelProvider(this).get(YourModelClassName::class.java)

viewModel.searchResult("%${search.text}%").observe(viewLifecycleOwner, Observer{
                Log.d("TAG", "${it.size}")
})

NOTE: The SQL wildcard is passed in as a parameter in this view model function searchResult("%${search.text}%")

Upvotes: 1

Олег Казьмин
Олег Казьмин

Reputation: 136

This is a Full Text Search (FTS) feature that you must enable when declaring your tables. It is a SQLIte feature.

Here is a repo where you can find some info: https://github.com/android/architecture-components-samples/commit/c0d72f210241ff310bba13db9b8748081d6feab9

Here is an article that should guide you through the process: https://medium.com/@sienatime/enabling-sqlite-fts-in-room-2-1-75e17d0f0ff8

Good luck

Upvotes: 1

Related Questions