Android Room - Select query with LIKE

问题:

I'm trying to make a query to search all objects whose names contain text:

@Query("SELECT * FROM hamster WHERE name LIKE %:arg0%")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Messages:

Error:no viable alternative at input 'SELECT * FROM hamster WHERE name LIKE %'
Error:There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (near "%": syntax error)
Error:Unused parameter: arg0

Also I'm trying:

@Query("SELECT * FROM hamster WHERE name LIKE '%:arg0%'")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Messages:

Error:Unused parameter: arg0

How to fix this?

回答1:

You should enclose the % characters in your input query - not in the query itself.

E.g. try this:

@Query("SELECT * FROM hamster WHERE name LIKE :arg0")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Then your String search value should look like:

search = "%fido%";
loadHamsters(search);

Furthermore, the binding parameter name should match the variable name, so rather than arg0 it should look like:

@Query("SELECT * FROM hamster WHERE name LIKE :search")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

回答2:

You can just concat using SQLite string concatenation.

@Query("SELECT * FROM hamster WHERE name LIKE '%' || :search || '%'")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

回答3:

Room only supports named bind parameter :name to avoid any confusion between the method parameters and the query bind parameters.

Room will automatically bind the parameters of the method into the bind arguments. This is done by matching the name of the parameters to the name of the bind arguments.

 @Query("SELECT * FROM user WHERE user_name LIKE :name AND last_name LIKE :last")
 public abstract List<User> findUsersByNameAndLastName(String name, String last);