Android - sqlite in clause using string values from arraylist?

Question

public void DBSearchCategory(String tableName) {
    // 1st way 
    String inClause = s1.ListViewCategory.toString();
    inClause = inClause.replace("[", "(");
    inClause = inClause.replace("]", ")");

   //        Cursor cursor = database.rawQuery("SELECT CATEGORY FROM " + tableName
  //                + " WHERE CATEGORY NOT IN " + inClause
 //                + " ORDER BY RANDOM() LIMIT 1 ", null);

    // 2nd way
    try {
        StringBuilder sb = new StringBuilder("");
        for (String param : s1.ListViewCategory) {
            sb.append(",").append('"').append(param).append('"');
        }
        params = sb.toString().substring(1); 
        Log.v("Tag", "params value is " + params);
    } catch (StringIndexOutOfBoundsException e) {

    }
    Cursor cursor = database.rawQuery("SELECT CATEGORY FROM " + tableName
                    + " WHERE CATEGORY NOT IN (?) "
                    + " ORDER BY RANDOM() LIMIT 1 ", new String[]{params});

    while (cursor.moveToNext()) {
        category = cursor.getString(cursor.getColumnIndex("CATEGORY"));
        s1.keyCategory = category;
    }
    cursor.close();
}

s1.ListViewCategory is a String type ArrayList in Singleton class s1, and it has values of categories: "game","country","city","subway","actor","pet" // In Database there are total 33 categories, and I want to exclude these 6 categories that are in s1.ListViewCategory
In rawQuery, I want to exclude categories that are in s1.ListViewCategory, so I tried 2 ways of cursor refering to these 2 stackoverflow questions: Android - sqlite in clause using string values from array? ///Android - sqlite in clause using values from array

I used WHERE and NOT IN statement to exclude these 6 categories

When I tried 2nd way cursor, I got no error. However, the Sql query did not work. It had to exclude categories that are in String[params], but it did not work. So I used log to see what param is and I got this

2020-01-09 09:16:47.233 8978-8978/com.kj.word V/Tag: params value is "game","country","city","subway","actor","pet"

When I tried 1st Cursor Category, I got error logcat:

Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
(no such column: game (code 1): , while compiling: SELECT CATEGORY FROM KeyWordDB WHERE CATEGORY 
NOT IN (game, country, city, subway, actor, pet) ORDER BY RANDOM() LIMIT 1)
#################################################################
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1008)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:573)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLite

I confirmed that there is a database, so I guess it is probably sql query problem ...

So my question is How can I fix 1st or 2nd cursor to exclude categories that are in s1.ListViewCateogry?

I've searched real hard, but I wasn't able to find answer... Ill be real grateful, if someone answers this question

forpas · Accepted Answer · 2020-01-09 11:44:08Z

1

Change the double quotes with single quotes inside the loop that constructs the comma delimited list:

for (String param : s1.ListViewCategory) {
    sb.append(",").append("'").append(param).append("'");
}
params = sb.toString().substring(1);

This code constructs a list like:

'game', 'country', 'city', 'subway', 'actor', 'pet'

If you use it as a parameter in the rawQuery() method then this list will be treated as a string literal and not a list of values.
So do this instead:

String sql = "SELECT CATEGORY FROM " + tableName
             + " WHERE CATEGORY NOT IN (?) "
             + " ORDER BY RANDOM() LIMIT 1 ";
sql = sql.replace("?", params);
Cursor cursor = database.rawQuery(sql, null);

Note that this method is prone to sql injection.

Another way is to create a list of ? placeholders instead of 1 placeholder and pass the list of values as an array of strings like this:

for (String param : s1.ListViewCategory) {
    sb.append(",?");
}
String[] array = ListViewCategory.toArray(new String[s1.ListViewCategory.size()]);

params = sb.toString().substring(1);
String sql = "SELECT CATEGORY FROM " + tableName
           + " WHERE CATEGORY NOT IN (@) "
           + " ORDER BY RANDOM() LIMIT 1 ";
sql = sql.replace("@", params);

Cursor cursor = database.rawQuery(sql, array);

edited Jan 9, 2020 at 11:44

answered Jan 9, 2020 at 7:56

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

forpas Over a year ago

Remopve the try/catch block to check if there is any error. Also what is the result of: Log.v("Tag", "params value is " + params);?

forpas Over a year ago

@Korea19800 see another safer way to do it in my edited answer.

Korea19800 Over a year ago

Thanks a lot! I will try and ask again if it has errors. Also I will select your answer if there is no error! Thanks a lot again

forpas Over a year ago

The statement uses NOT IN which means it excludes everything in the list (which must have at least 1 item, right?). So either it works or not. Did you try both ways?

forpas Over a year ago

I can't reproduce your issue. You will have to debug to check the sql statement right after: sql = sql.replace("?", params); and log its value if it is as it should be.

|

Collectives™ on Stack Overflow

Android - sqlite in clause using string values from arraylist?

1 Answer 1

17 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related