Query a Sqlite Database Using an Array Android

Question

I have researched a handful of other forums with a similar topic and I have yet to find an answer to this frustrating issue. I am trying to use an array to check if a column in my database has one of the multiple values in the array. My cursor is as follows:

public Cursor notificationQuery(String geoIds) {
    Log.e("STRINGS", geoIds);
    return mDb.query(Constants.TABLE_POI_NAME,
            new String[]{Constants.TABLE_COLUMN_ID, Constants.TABLE_COLUMN_POI_NAME,
                    Constants.TABLE_COLUMN_LATITUDE, Constants.TABLE_COLUMN_LONGITUDE,
                    Constants.TABLE_COLUMN_GEO_ID},
            Constants.TABLE_COLUMN_GEO_ID + " IN (?)",
            new String[]{geoIds},
            null, null, null, null);
}

geoIds is currently an array of two values which has been converted into a string. The logged value of that string is below:

21007b0f-6b20-4eff-9a76-b412db8daa2e,26c695d6-6cb4-4c74-9933-281813a06fd9

Those are to separate Id values separated by a comma. When I test each one individually using "= ?" instead of "IN (?)" I get a proper match with the database and my cursor returns a value. However, when combined my cursor returns nothing when it should return two rows from the database. Please help me solve this issue! Thanks!

so you want to search into TABLE_POI_NAME and check if TABLE_COLUMN_GEO_ID has one of the values of geoIds am i right? — Panayiotis Irakleous
– Panayiotis Irakleous, Commented Jan 6, 2015 at 22:00
Yep that's what I'm looking to do. Both id's are confirmed to be in the column as well — Rich Luick
– Rich Luick, Commented Jan 6, 2015 at 22:10

Panayiotis Irakleous · Accepted Answer · 2015-01-06 22:20:54Z

2

Consider a function String makePlaceholders(int len) which returns len question-marks separated with commas, then:

public Cursor notificationQuery(String geoId1,String geoId2) {
    //assume we split this geoIds to 2 different values. you need to have 2 strings no 1
    String[] ids = { geoId1, geoId2 }; // do whatever is needed first depends on your inputs
    String query = "SELECT * FROM "+ Constants.TABLE_POI_NAME + " WHERE "+ 
    Constants.TABLE_COLUMN_GEO_ID +" IN (" + makePlaceholders(names.length) + ")";
    return mDb.rawQuery(query, ids);    // ids is the table above
}

Here is one implementation of makePlaceholders(int len):

String makePlaceholders(int len) {
    if (len < 1) {
        // It will lead to an invalid query anyway ..
        throw new RuntimeException("No placeholders");
    } else {
        StringBuilder sb = new StringBuilder(len * 2 - 1);
        sb.append("?");
        for (int i = 1; i < len; i++) {
            sb.append(",?");
        }
        return sb.toString();
    }
}

answered Jan 6, 2015 at 22:20

Panayiotis Irakleous

2,6961 gold badge25 silver badges36 bronze badges

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

3 Comments

Rich Luick Over a year ago

On a quick glance, that is pretty much exactly what I have going on behind the scenes with the string builder. I'll give it another shot when I'm back at my computer

Panayiotis Irakleous Over a year ago

@RichLuick ok i am glad that i help you. if this is solve your problem please mark it as answer and if you have any problem tell me and i will help you

Rich Luick Over a year ago

On closer inspection I see where this differs from my approach. And so far its working!

Stan Idesis · Accepted Answer · 2015-01-06 23:19:13Z

0

Or more simply, use the geoIds variable directly:

public Cursor notificationQuery(String geoIds) {
Log.e("STRINGS", geoIds);
return mDb.query(Constants.TABLE_POI_NAME,
        new String[]{Constants.TABLE_COLUMN_ID, Constants.TABLE_COLUMN_POI_NAME,
                Constants.TABLE_COLUMN_LATITUDE, Constants.TABLE_COLUMN_LONGITUDE,
                Constants.TABLE_COLUMN_GEO_ID},
        Constants.TABLE_COLUMN_GEO_ID + " IN (" + geoIds + ")",
        null, null, null, null, null);
}

This approach is less secure but will likely give you the result you expect.

answered Jan 6, 2015 at 23:19

Stan Idesis

611 silver badge6 bronze badges

1 Comment

Rich Luick Over a year ago

I beleive this was one of the methods I had tried before. It seems like it would be a good option but is for some reason not working for me.

Collectives™ on Stack Overflow

Query a Sqlite Database Using an Array Android

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related