CODEWITHSUNDEEP
android-room
nLL
nLL

Reputation: 5672

Android Room Query cannot use same variable more than once

I am trying to convert this raw query to Query.

Raw Query:

SELECT * from numbers WHERE number=$numberToSearch OR number LIKE '%$numberToSearch%' OR number LIKE '%$numberToSearch' OR number LIKE '$numberToSearch%'

I started with simple one and created this Query

 @Query("SELECT * from numbers WHERE number=:numberToSearch OR number LIKE :numberToSearch")

However, room things that my query needs to variables and creates exception. I can of course provide :numberToSearch few times as variable but that doe not sound right.

Is there any way writing a Query that works?

Upvotes: 1

Views: 63

Answers (1)

musooff
musooff

Reputation: 6882

You should enclose your like query with % before passing as a parameter.

So correct representation of what you original query is goes as follows

val numberToSearch = 123
val numberToSearchLike = `%$numberToSearch%`
// You have to call with two parameters as parameters are not same
searchNumber(numberToSearch, numberToSearchLike)

And your DAO should look like this

@Query("SELECT * from numbers WHERE number=:numberToSearch OR number LIKE :numberToSearchLike")
fun searchNumber(numberToSearch:Int, numberToSearchLike: String)

Thats actually the way your do LIKE operations in Room

P.S.

In fact you don't have to have WHERE number=:numberToSearch as your LIKE operator will return the same thing. So your query is same as saying

@Query("SELECT * from numbers WHERE number LIKE :numberToSearchLike")

Upvotes: 1

Related Questions