2

I am trying to create the following Query using SupportSQLiteQueryBuilder : 

SELECT * FROM tablename WHERE favorite=1 ORDER BY col1

I used the following code to build this query:


public static SupportSQLiteQuery getSortedQuery(String sortByColname, boolean fav){
    SupportSQLiteQueryBuilder builder=
            SupportSQLiteQueryBuilder
                    .builder(DbUtils.Names.TABLE_NAME)
                    .columns(new String[]{Names.COL1,Names.COL2,Names.COL3,Names.COL4,Names.COL5,Names.COL_FAVORITE})
                    .orderBy(sortByColname) ;
    if(fav){
        builder.selection(Names.COL_FAVORITE,new String[]{"1"}  );
    }

    SupportSQLiteQuery query= builder.create();
    //Log.e("DB_UTILS", "getSortedQuery:query="+query.getSql());
    return  query;
}

But on checking the unit test , i found that following is the sql query that this method is generating :

SELECT col1,col2,col3,col4,col5 favorite  FROM tablename WHERE favorite ORDER BY col1

  1. Why The arguement is not getting passed into this statement? why only favourite got generated and favourite=1 ?

  2. Is There a way to generate * in the query? writing all the colnames instead of * is painful

ansh sachdeva
  • 1,220
  • 1
  • 15
  • 32
  • 2
    `selection` should take something like `someColumn = ?`, and yes, you can pass `*` to `columns` method – pskink Jun 26 '19 at 20:27
  • 1
    1. Try replacing `Names.COL_FAVORITE` with `Names.COL_FAVORITE = ?`. 2. Try replacing `.columns(new String[]{Names.COL1,Names.COL2,Names.COL3,Names.COL4,Names.COL5,Names.COL_FAVORITE})` with `.columns(new String[]{"*"})` – CommonsWare Jun 26 '19 at 20:27
  • @CommonsWare 2nd part was right but replacing `COL_FAVOURITE` with `COL_FAVOURITE + " = ?"` gave the following result `SELECT * FROM tea WHERE favorite = ? ORDER BY name` – ansh sachdeva Jun 26 '19 at 20:32
  • and whats wrong with your query: `"SELECT * FROM tea WHERE favorite = ? ORDER BY name"`? – pskink Jun 26 '19 at 20:35
  • @pskink `favourite =1` should be in the desired resultant query . i am trying to query for all results where a boolean `favourite` is true – ansh sachdeva Jun 26 '19 at 20:38
  • 2
    each time you have `?` in your selection it will be replaced with the corresponding parameter taken from `String[]` array – pskink Jun 26 '19 at 20:40

1 Answers1

1

To be honest, I would use a raw query for this, with different SQL strings based on fav. But, if you really want to use SupportSQLiteQueryBuilder...

With regards to item 1, typically positional parameters are evaluated at runtime, which is what the SupportSQLiteQueryBuilder API is geared towards. So, you could:

  • Replace Names.COL_FAVORITE with Names.COL_FAVORITE = ?, which will then use your new String[]{"1"} at runtime
  • Replace Names.COL_FAVORITE with Names.COL_FAVORITE = 1, which will ignore your new String[]{"1"} at runtime

With regards to item 2, replace .columns(new String[]{Names.COL1,Names.COL2,Names.COL3,Names.COL4,Names.COL5,Names.COL_FAVORITE}) with .columns(new String[]{"*"}).

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • ohh the runtime, i didn't considered that. probably that's why my test showed `?` because i was running this function on the jdk using Junit4 test. Thanks for the explanation. Although, is there a way to like, look at the arguments being passed? i see `query.getArgCount()` function , but not `query.getArgs()` – ansh sachdeva Jun 26 '19 at 20:53
  • Hey @anshsachdeva How to try the accepted answer I tried by doing queryBuilder.selection(DataTeaNames.COL_FAVORITE+"= ?", new String[]{"1"}); but didn't worked. do you know how to make this method work: public SupportSQLiteQueryBuilder selection(String selection, Object[] bindArgs) { mSelection = selection; mBindArgs = bindArgs; return this; } – Rahul Matte Aug 07 '20 at 09:39