CODEWITHSUNDEEP
javaandroidsqlite
Reyjohn
Reyjohn

Reputation: 2734

How to run query in SQLite database in android?

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?

Upvotes: 6

Views: 34825

Answers (5)

Paolo Rovelli
Paolo Rovelli

Reputation: 9684

Android fully supports SQLite DBs. All the DBs you create inside your app will be accessible from all the classes of your apps (NOT outside your app!).

First of all, you should create a Java class that extends SQLiteOpenHelper. Here, you have to override the onCreate() and onUpdate() methods. As one can suppose, the first is called when the DB is created, while the latter is called when the DB is modified. 

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.database.Cursor;
import android.content.ContentValues;


/**
 * Class DatabaseHelper.
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    //Database parameters:
    private static final String DATABASE_NAME = "dbname";  // the name of the DB!
    private static final int DATABASE_VERSION = 2;
    private static final String DATABASE_TABLE_NAME = "tripmileagetable";  // the name of the table!

    //Table attributes:
    private static final String DATABASE_TABLE_ATTR_ID = "id";  // attr1
    private static final String DATABASE_TABLE_ATTR_ODOMETER = "odometer";  // attr2
    private static final String DATABASE_TABLE_ATTR_DATE = "date";  // attr3


    /**
     * Class constructor.
     *
     * @param context  the context.
     */
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        String create = "CREATE TABLE " + DATABASE_TABLE_NAME + " (" +
                        DATABASE_TABLE_ATTR_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        DATABASE_TABLE_ATTR_ODOMETER + " INTEGER, " +
                        DATABASE_TABLE_ATTR_DATE + " TEXT);";

        db.execSQL( create );
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_NAME);
        onCreate(db);
    }
}

Now, inside the new Java class, you can create custom methods to query your DB.

To write the DB you should use getWritableDatabase(), while to read it you should use getReadableDatabase(). Both of them return an SQLiteDatabase object, and eventually throw an SQLiteException. In particular, to query your DB, there are two SQLiteDatabase methods available: rawQuery and query (both return a Cursor object).

/**
 * Get the sum of the odometer of a particular month and year.
 *
 * @param year  the year.
 * @param month  the month.
 * @return the sum of the odometer of the year and the month.
 */
public int sumOdometer(Integer year, Integer month) {
    //Date composition:
    String date = year.toString() + "-" + month.toString() + "%";

    //SQL query:
    String query = "SELECT SUM(" + DATABASE_TABLE_ATTR_ODOMETER + ") AS " + DATABASE_TABLE_ATTR_ODOMETER + 
                   " FROM " + DATABASE_TABLE_NAME +
                   " WHERE " + DATABASE_TABLE_ATTR_DATE + "LIKE ?";

    //Execute the SQL query:
    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = db.rawQuery(query, new String [] {date});

    int sum = 0;
    if( cursor.moveToFirst() ) {  // moves the cursor to the first row in the result set...
        sum = cursor.getInt( cursor.getColumnIndex(DATABASE_TABLE_ATTR_ODOMETER) );
    }

    //Close the Cursor:
    cursor.close();

    return sum;
}

Note that SQLite automatically puts single quotes (') around the arguments (?).

You can find a good tutorial here: http://www.codeproject.com/Articles/119293/Using-SQLite-Database-with-Android

Upvotes: 1

leindraw
leindraw

Reputation: 1

your field is not create on your table Database, check out the field on your Table. because I have seen that problem on mine. I use SQLite Administrator to see my Database.

or if you didn't have it, look on your source code of create Database. make sure your source code is true, then you can delete your old database on file explorer (using eclipse I mean). and you can run again with new Database.

Upvotes: 0

Nic Raboy
Nic Raboy

Reputation: 3153

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

Upvotes: 2

biegleux
biegleux

Reputation: 13247

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();
    }
}

Upvotes: 18

unnamedhorse
unnamedhorse

Reputation: 1823

You can use the rawQuery method:

Cursor c = database.rawQuery("SELECT SUM(odometer) as odometer FROM tripmileagetable where date like '2012-07%'", null);

Upvotes: 0

Related Questions