How to pass column name as a parameter in room database?

user_14643842 0
Viewed 1342

I am using roomdatabase and I have a lot of categories to query.

@Query("SELECT * FROM Table_name WHERE sweets = 1")
LiveData<List<Item>> getAllSweets();

@Query("SELECT * FROM Table_name WHERE drinks = 1")
LiveData<List<Item>> getAllDrinks();

So the question is, it is possible to make one universal query and pass column name as parameter? For example:

@Query("SELECT * FROM Table_name WHERE :columnname = drinks AND :columnname = 1")
LiveData<List<Item>> getAllDrinks(String drinks);

I know that I refer to the columnName but If columnName is type int it shouldn't be?

LiveData<List<Item>> getAllDrinks(int drinks)
android-room android-sqlite android java
1 Answers

Short Answer: You can't do that because SQLite doesn't allow that, check here.

Long Answer:

There are a couple of drawbacks in your model:

  1. Database Design Model: Not that a good database design in terms of data normalization; why? You have a column for sweets, another for drinks, maybe one for meals.. More columns, means more data storage, more null-able fields in records, and you need a database method for querying each column separately (like what your question about)
  2. To have a variable column name can be error-prone: what if you pass in a column that doesn't exist in database.. This is one of the features that Room provides to you over abstract SQL queries where Room provides you a compile-time errors for wrong column names while normal SQLite raises them at Runtime.

What I encourage you to do:

Create a single column that indicates the type of the food; for efficiency you can create it of type int, and map each integer value to a certain type of food:

1 >> goes for >> sweats 
2 >> goes for >> drinks
and so on..

Now you have a single column (not multiple of them as in your model) Now you can have a single DAO method accessing any type (which you were asking for)

@Query("SELECT * FROM Table_name WHERE :foodtype = type")
LiveData<List<Item>> getAllDrinks(int type) // add the type

Note: Also for readability you can use Enum's for your fields and make Room type conversion.

user_98516080
Related