CODEWITHSUNDEEP
javaandroidsqlite
Kelvin Chan
Kelvin Chan

Reputation: 3

retrieve multiple rows form android sqlite

I am going to fetch some rows with specific tag in android Sqlite. The problems are

  1. I got 15 with cursor.getCount(); but 13 rows retrieved.
  2. the 13 rows just repeat last row data

here is my code 

public ArrayList<HashMap<String, String>> getTodo(String tag) {
    ArrayList<HashMap<String,String>> arList = new ArrayList<HashMap<String,String>>();
    HashMap<String, String> todo = new HashMap<String, String>();
    String selectQuery = "SELECT * FROM " + TABLE_NAME + " WHERE "
                + COLUMN_TAG + " = ?";
    String[] args={tag};

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, args);
    // Move to first row
    cursor.moveToFirst();
    System.out.println(cursor.getCount());
    if (cursor.getCount() > 0) {
        do {
            todo.put(COLUMN_ID, cursor.getString(cursor.getColumnIndex(COLUMN_ID)));
            todo.put(COLUMN_FROM, cursor.getString(cursor.getColumnIndex(COLUMN_FROM)));
            todo.put(COLUMN_TO, cursor.getString(cursor.getColumnIndex(COLUMN_TO)));
            todo.put(COLUMN_TITLE, cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            todo.put(COLUMN_TAG, cursor.getString(cursor.getColumnIndex(COLUMN_TAG)));

            arList.add(todo);
        } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();
    // return todo
    return arList;
}

Please help. Thanks

Upvotes: 0

Views: 6059

Answers (2)

stealthjong
stealthjong

Reputation: 11093

It's better to make the todo's you want to acquire into objects

public class Todo {
    public String id, from, to, title, tag;

    //setters and getters (not necessary)
}

public ArrayList<MyData> getTodo(String tag) {
    ArrayList<MyData> arList = new ArrayList<MyData>();
    String selectQuery = "SELECT * FROM " + TABLE_NAME + " WHERE "
            + COLUMN_TAG + " = ?";
    String[] args={tag};


    try {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, args);
        // Move to first row
        if (!cursor.moveToFirst())
            return arList;
        do {
            Todo todo = new Todo();
            todo.id = cursor.getString(cursor.getColumnIndex(COLUMN_ID));
            todo.from = cursor.getString(cursor.getColumnIndex(COLUMN_FROM));
            todo.to = cursor.getString(cursor.getColumnIndex(COLUMN_TO));
            todo.title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE))
            todo.tag = cursor.getString(cursor.getColumnIndex(COLUMN_TAG));
            arList.add(todo);
        }  while(cursor.moveToNext());
        cursor.close();
        db.close();
        return arList;
    }
    finally {
        cursor.close();
        db.close();
    }
}

You keep putting new strings to an already created hashmap, but you keep using the same keys, and therefore, many entries get replaced. The Todo object keeps your code readable. You should also check if the cursor can jump to the first entry (which makes a getCount call unnecessary)

Upvotes: 2

CL.
CL.

Reputation: 180020

You are using the same todo object for all rows, so you end up with lots of references to the same object.

Create a new one for each row in the loop:

Cursor cursor = db.rawQuery(selectQuery, args);
try {
    while (cursor.moveToNext()) {
        HashMap<String, String> todo = new HashMap<String, String>();
        todo.put(...);
        ...
        arList.add(todo);
    }
finally {
    cursor.close();
}

Upvotes: 2

Related Questions