How to run query in SQLite database in android?

Question

In my SQLite database manager I can query this:

SELECT SUM(odometer) as odometer FROM tripmileagetable where date like '2012-07%';

this query returns me the total sum of the odometer colum from the table named 'tripmileagetable' of the month of july 2012, but I want to write this code in android query.

But I can't figure out how to establish this query in database.query() method, can any one help?

Answer 1

final Cursor cursor = db.rawQuery("SELECT SUM(odometer) as odometer FROM tripmileagetable where date like '2012-07%';", null);
int sum = 0;
if (cursor != null) {
    try {
        if (cursor.moveToFirst()) {
            sum = cursor.getInt(0);
        }
    } finally {
        cursor.close();
    }
}

Answer 2

It depends on how you plan on accessing the database in Android. You may try something like:

SQLiteDatabase db = this.getWritableDatabase();
String selectQuery = "select sum(odometer) as odometer from tripmileagetable where date like '2012-07%'";
Cursor cursor = db.rawQuery(selectQuery, null);

The above would be used if you're using an SQLiteOpenHelper class.

If you've created the database file yourself, you could do something like:

SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/com.package.name/databases/dbname.db", null, SQLiteDatabase.OPEN_READWRITE);
String selectQuery = "select sum(odometer) as odometer from tripmileagetable where date like '2012-07%'";
Cursor cursor = db.rawQuery(selectQuery, null);

Do some research on SQLiteDatase, Cursor, and SQLiteOpenHelper.

This example might help you:

https://github.com/nraboy/Spyfi/blob/master/Android/src/com/nraboy/spyfi/DataSource.java