How to get multiple rows in SQLite at a time in Android?

Question

I want to get multiple column from SQLite database for example I have the data with ids 1, 2, 3, 4, 5. Now in a SQLite database I want to get 2, 5 id values in Android:

    String selectQuery = "SELECT  * FROM " + Table_STOCK + " 
          WHERE " + KEY_ID + " = '" + id + "'" ;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

From the query above I can only get one at a time with one id. I want to get multiple columns with multiple ids.

Please add more details in your question. And please search for answer before you are posting one. I think the problem you are facing is common. — Reaz Murshed, Apr 18 '18 at 06:34
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the [ask] page for help clarifying this question. — ADM, Apr 18 '18 at 06:35
this is very basics of SQL, you should learn before starting using it. You can use `WHERE id in (2,5)` instead of `WHERE id = 2` — Vladyslav Matviienko, Apr 18 '18 at 06:42
please take some effort yourself. We shouldn't have to do entire task instead of you. — Vladyslav Matviienko, Apr 18 '18 at 06:49

Björn Kechel · Answer 1 · 2018-04-18T07:29:11.527

You can use the keyword IN for this:

String commaSeparatedIds = "1, 2, 3, 4, 5";

String selectQuery = "SELECT  * FROM " + Table_STOCK + " 
                  WHERE " + KEY_ID + " IN (" + commaSeparatedIds + ")" ;

And here is a small helper method to create a commaSeparatedString from a List of Ids without additional Libraries:

private String toCommaSeparatedString(List<Integer> list) {
    if (list.size() > 0) {
        StringBuilder nameBuilder = new StringBuilder();
        for (Integer item : list) {
            nameBuilder.append(item).append(", ");
        }
        nameBuilder.deleteCharAt(nameBuilder.length() - 1);
        nameBuilder.deleteCharAt(nameBuilder.length() - 1);
        return nameBuilder.toString();
    } else {
        return "";
    }
}

Even Simpler with the apache commons libraries as explained here: Java: join array of primitives with separator (But beware that these sometimes cause problems with Android <4.4)

score 1 · Answer 2 · edited Apr 18 '18 at 06:51

1

Here is the code, you have to use to get multiple records

  selectQuery = "SELECT  * FROM " + Table_STOCK + " WHERE " + KEY_ID + "='" + id2+ "' AND " + KEY_ID + " = '" + id5";

There are multiple ways to fetch the data using condition.

edited Apr 18 '18 at 06:51

Jibin Mathews

1,129
1
10
23

answered Apr 18 '18 at 06:45

Praveen Rawat

314
1
3
14

But for that you have to be confirm that how many rows you have to fetch – Rishabh Saxena Apr 18 '18 at 06:57
1

Yes , it should be predefined about how many rows he should fetch. – Praveen Rawat Apr 18 '18 at 07:04
Check out my solution and if you find it dynamic then please upvote :) – Rishabh Saxena Apr 18 '18 at 07:06
1

I already mentioned , there are many ways to fetch . But in question he not mention dynamic , he knows how many rows he had to fetch. – Praveen Rawat Apr 18 '18 at 07:09
2

Ok but the answer should always be dynamic so that if in future anyone needs solution it should be beneficial for everyone. – Rishabh Saxena Apr 18 '18 at 07:11
Thanks @ShaifaliRajput – Rishabh Saxena Apr 18 '18 at 07:21

score 0 · Answer 3 · answered Apr 18 '18 at 06:59

You can just add ids in array list and call this method, rest this method will take care of everything, its a dynamic approach because here you can pass as much id you can. Just Edit the Object with your own data model.

public ArrayList<Object> getContent(ArrayList<String> list) {

        ArrayList<Object> objList = new ArrayList<>();
        if(list!=null && !list.isEmpty()){
            SQLiteDatabase db = getReadableDatabase();

            //Check for list items
            for(int i =0;i<list.size();i++) {

                Cursor cursor = db.query(Table_STOCK, null,
                        KEY_ID + " = ?",
                        new String[]{list.get(i)}, null, null, null, null);


                Object obj=null;

                if (cursor != null) {
                    cursor.moveToFirst();

                    // Set your object properties extracting from cursor
                    obj = new Object();
                    obj.setColumn1Property(cursor.getString(cursor.getColumnIndex(Table_STOCK.COLUMN_1)));
                    obj.setColumn2Property(cursor.getString(cursor.getColumnIndex(Table_STOCK.COLUMN_2)));
                }
                if (cursor != null) {
                    cursor.close();
                }
                //Add extracted object in your list.
            objList.add(obj);

            }
        }
        return objList;
        //Return rows list from your database. 
    }

How to get multiple rows in SQLite at a time in Android?

3 Answers3