Instead of writing multiple query i refer pass negative value to limit clause. Because if there is change in query i have to update the both query which is more error prone.

Official doc -> If the LIMIT expression evaluates to a negative value, then there is no upper bound on the number of rows returned. you can find it here https://sqlite.org/lang_select.html and read the limit clause section.

So I would do somthing like this,

@Query("SELECT * FROM playlist " +

    "WHERE playlist_title LIKE '% :playlistTitle %' " +

    "GROUP BY playlist_title " +

    "ORDER BY playlist_title " +

    "LIMIT :limit")

List<IPlaylist> searchPlaylists(String playlistTitle, int limit);

and pass negative when you don't want to apply filter.

return playlistDao.searchPlaylists(title, limit.isPresent() ? limit.get() : -1)

It's working in my case.

Updated [21 Dec 2018]

In case If you are using kotlin use default value.

@JvmOverloads

@Query("SELECT * FROM playlist " +

        "WHERE playlist_title LIKE '% :playlistTitle %' " +

        "GROUP BY playlist_title " +

        "ORDER BY playlist_title " +

        "LIMIT :limit")

fun searchPlaylists(playlistTitle: String, limit: Int = -1): List<IPlaylist>

@JvmOverloads to make it compatiable with Java. It generate two separate methods for Java.

There is no something like optional parameter in Room, but there is a @RawQuery annotation where you can pass query as a String so you can build your SQL query in the runtime. I think this will work for you.

Here is the example from the Offical documentation:

@Dao

 interface RawDao {

     @RawQuery

     User getUser(String query);

 }

And here is how you can use it:

User user = rawDao.getUser("SELECT * FROM User WHERE id = 3 LIMIT 1");

Important: RawQuery methods must return a non-void type

Important: This is available in Room 1.1.0-alpha3

Use SupportSQLiteQuery.

https://developer.android.com/reference/android/arch/persistence/db/SupportSQLiteQuery

Latest release 1.1.1 now uses SupportSQLiteQuery.

A query with typed bindings. It is better to use this API instead of
rawQuery(String, String) because it allows binding type safe
parameters.

@Dao

     interface RawDao {

         @RawQuery(observedEntities = User.class)

         LiveData<List<User>> getUsers(SupportSQLiteQuery query);

     }

Usage:

     LiveData<List<User>> liveUsers = rawDao.getUsers( new 

SimpleSQLiteQuery("SELECT * FROM User ORDER BY name DESC"));

Update your gradle to 1.1.1

implementation 'android.arch.persistence.room:runtime:1.1.1'

implementation 'android.arch.lifecycle:extensions:1.1.1'

annotationProcessor "android.arch.persistence.room:compiler:1.1.1"

Note: if you upgrade to 1.1.1, and are using String instead of SupportSQLiteQuery,

you will get the error:

RawQuery does not allow passing a string anymore. Please use
android.arch.persistence.db.SupportSQLiteQuery.

Using SupportSQLiteQuery as above will solve the problem.

Room supports @RawQuery annotation to construct queries at run-time.

Step 1 : Make DAO method

Mark the DAO method with @RawQuery annotation instead of normal @RawQuery.

@Dao

interface BooksDao{

    @RawQuery

    List<Book> getBooks(SupportSQLiteQuery query);

}

Step 2 : Construct the query

Room uses prepared statements for security and compile time verification. Therefore, while constructing queries, we need to store query string and bind parameters separately.

In this example, I use the variable queryString for query string and args for bind parameters.

^{(Please note that I used text editor to write code. Therefore there may be typo or simple syntax errors. If you find anything please let me know in the comments or edit the post.)}

// Query string

String queryString = new String();



// List of bind parameters

List<Object> args = new ArrayList();



boolean containsCondition = false;



// Beginning of query string

queryString += "SELECT * FROM BOOKS";



// Optional parts are added to query string and to args upon here



if(!authorName.isEmpty()){

    queryString += " WHERE";

    queryString += " author_name LIKE ?%";

    args.add(authorName);

    containsCondition = true;

}



if(fromDate!=null){



    if (containsCondition) {

        queryString += " AND";

    } else {

        queryString += " WHERE";

        containsCondition = true;

    }



    queryString += " publication_date AFTER ?";

    args.add(fromDate.getTime());

}



if(toDate!=null){



    if (containsCondition) {

        queryString += " AND";

    } else {

        queryString += " WHERE";

        containsCondition = true;

    }



    queryString += " publication_date BEFORE ?";

    args.add(toDate.getTime());

}



// End of query string

queryString += ";";

Step 3 : Perform query

SimpleSQLiteQuery query = new SimpleSQLiteQuery(queryString, args.toArray());

List<Book> result = booksDao.getBooks(query);

Notes

• Like normal Query, RawQuery supports returning raw cursors, entities, POJOs and POJOs with embedded fields


• 
  RawQuery supports relations

make it more simple.i will show you example using where clause using two variable.Do 

like this



  @Query("SELECT * FROM Student WHERE stdName1= :myname AND stdId1=:myid")

List<Student> fetchAllData(String myname,int myid);

stdName1 and stdId1 are column names

@Anderson K & @Juanky Soriano,
I'm agree with @CommonsWare,

There some Limitation in Room Library, then also We can write fully dynamic query on Room Database by using the @query() of Support SQLite Database

String mQuery = "SELECT * FROM foobar WHERE columnName1 IN ('value_1','value_2') and columnName2 In('value_3','value_4')";



AppDatabase appDatabase = Room.databaseBuilder(getApplicationContext(),

        AppDatabase.class, "database-name").build();



Cursor mCursor = AppDatabase.getAppDatabase(context).getOpenHelper().getReadableDatabase().query(myQuery);

Now you can convert cursor row wise data to your POJO class.