Geek asks and answers

How to run a query in a 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 amount of the odometer column from the table named "tripmileagetable" of the month of July 2012, but I want to write this code in the android query.

But I can’t figure out how to set this query in the database.query () method, can anyone help?

+5
source share
5 answers
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();
    }
}
+16
source

It depends on how you plan to access the database in Android. You can 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 will be used if you use the SQLiteOpenHelper class.

If you created the database file yourself, you can 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);

SQLiteDatase, Cursor SQLiteOpenHelper.

:

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

+2

rawQuery:

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

, . . SQLite Administrator .

, . , , ( eclipse ). .

0

Android SQLite DBs. , , ( !).

, Java, SQLiteOpenHelper. onCreate() onUpdate(). , , , .

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

, Java, .

To write a database, you must use getWritableDatabase (), and to read it you must use getReadableDatabase (). Both of them return an SQLiteDatabase object and ultimately throw a SQLiteException. In particular, two SQLiteDatabase methods are available for querying your database: 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 arguments (?).

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

0
source

More articles:


All Articles