CODEWITHSUNDEEP
androidandroid-sqliteandroid-cursorandroid-database
Deno Agüero
Deno Agüero

Reputation: 599

Android - Efficient way using the Database Cursor

I've implemented a sqlite database in my application and I'm using the Android Cursor. I've written a database class with e.g. the database name and the table and column names. Here I also have various methods, like the following:

public Cursor getCorrectQuestions(int topic) {
    SQLiteDatabase db = getReadableDatabase();
    Cursor questionCursor = db.rawQuery(
            "Select * FROM Result, Question WHERE Result.qid = Question._id AND correct = 1 AND topic = " + topic,
            null);
    questionCursor.moveToFirst();
    return questionCursor;
}

public Cursor getExamQuestions() {
    SQLiteDatabase db = getReadableDatabase();
    Cursor questionCursor = db.rawQuery("Select * FROM Question WHERE topic = 7", null);
    questionCursor.moveToFirst();
    return questionCursor;
}

public Cursor getAnswerItems(String id) {
    SQLiteDatabase db = getReadableDatabase();
    Cursor answerCursor = db.rawQuery(
            "Select * FROM Answer, Question WHERE Question._id = " + id + " AND Question._id = Answer.qid", null);
    answerCursor.moveToFirst();
    return answerCursor;
}

public Cursor getUserResults(String qid) {
    SQLiteDatabase db = getReadableDatabase();
    Cursor userResultsCursor = db.rawQuery("SELECT result FROM Result, Answer WHERE Result.qid = " + qid, null);
    userResultsCursor.moveToFirst();
    return userResultsCursor;
}

In the QuizActivity which has 3 cursors (answerCursor, questionCursor, userResultCursor) I call these methods. My question is: is it necessary to create a SQLiteDatabase Object in every method or is it possible to define this once in my database constructor? And do I need 3 different cursors in my activity or is there a better way to handle this?

Upvotes: 0

Views: 193

Answers (1)

happydude
happydude

Reputation: 3889

Assuming the methods you have written are part of a SQLiteOpenHelper, you are not really creating 3 database objects. Only the first call to getReadableDatabase() actually creates a database object, and subsequent calls reuse the same object over again.

You also need to make a new Cursor for each query you perform, as they cannot be edited after creation. In this sense, there is no way to simplify what you have already done.

As far as improvements to your code, there are a few things you can look at:

  • Consider putting your database in a ContentProvider and accessing it via URI's. This will require more upfront work, but will make it much easier if you want to share your database with other apps or sync your data to a server in the future.

  • Leave the cursor in its default position (don't call moveToFirst()). That way when the caller receives the cursor, it can use the following code to start iterating cursor rows without performing any further checks:

    while (cursor.moveToNext()) {
    // extract data
}

    This is because the cursor returned from a query is initially positioned before the first row of data, so if the cursor is empty then the code inside the while loop simply never executes at all.

Upvotes: 2

Related Questions