Android: How to retrieve data from a sqlite database

Question

I have been studying the information and documentation for the android sdk for sqlite databases, and I am still a little stuck. I have a class for my data base that I retrieved from the example found here. This is my class:

package com.example.medtracker;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
    private static final String DATABASE_NAME = "meds.db";
    private String DATABASE_TABLE;
    private static final int DATABASE_VERSION = 1;

    // Index Key column
    public static final String KEY_ID = "_id";

    // Name of the column index of each column in DB
    public ArrayList<String> TABLE_KEYS = new ArrayList<String>();
    public ArrayList<String> TABLE_OPTIONS = new ArrayList<String>();
    public final String KEY_TIMESTAMP = "timeStamp";
    public final int TIMESTAMP_COLUMN = 1;

    // Create new database
    private String DATABASE_CREATE;

    // Variable to hold database instant
    private SQLiteDatabase db;

    // Database open/upgrade helper
    private myDBHelper dbHelper;

    public DBAdapter(Context context, String table, ArrayList<String> keys,
            ArrayList<String> options) {
        // Start initializing all of the variables
        DATABASE_TABLE = table;
        TABLE_KEYS = (ArrayList<String>) keys.clone();
        TABLE_OPTIONS = options;

        String keyString = "";
        for (int i = 0; TABLE_KEYS.size() > i; i++) {

            // Add commas to the options elements if there is a next value.
            if (i + 1 < TABLE_OPTIONS.size() && TABLE_OPTIONS.get(i) != null) {
                TABLE_OPTIONS.set(i, TABLE_OPTIONS.get(i) + ",");
            } else if (i + 1 == TABLE_OPTIONS.size()
                    && TABLE_OPTIONS.get(i) != null) {
                if (i + 1 < TABLE_KEYS.size()) {
                    TABLE_OPTIONS.set(i, TABLE_OPTIONS.get(i) + ",");
                } else {
                    TABLE_KEYS.set(i, TABLE_KEYS.get(i) + "");
                }
            } else if (i + 1 != TABLE_KEYS.size()) {
                TABLE_KEYS.set(i, TABLE_KEYS.get(i) + ",");
            } else {
                TABLE_KEYS.set(i, TABLE_KEYS.get(i) + "");
            }

            System.out.println(TABLE_OPTIONS.toString());
            System.out.println(TABLE_KEYS.toString());

            if (i + 1 <= TABLE_OPTIONS.size() && TABLE_OPTIONS.get(i) != null)
                keyString = keyString + " " + TABLE_KEYS.get(i) + " "
                        + TABLE_OPTIONS.get(i);
            else if (i + 1 > TABLE_OPTIONS.size()
                    || TABLE_OPTIONS.get(i) == null) {
                keyString = keyString + " " + TABLE_KEYS.get(i);
            }
        }

        // Create the database creation string.
        DATABASE_CREATE = "CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE + " ("
                + "_id" + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + KEY_TIMESTAMP + "," + keyString + ");";

        // Create a new Helper
        dbHelper = new myDBHelper(context, DATABASE_NAME, null,
                DATABASE_VERSION, DATABASE_TABLE, DATABASE_CREATE);
    }

    public DBAdapter open() throws SQLException {
        db = dbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        db.close();
    }

    public long insertEntry(ArrayList<String> key, ArrayList<String> value) {
        String timeStamp = new Timestamp(Calendar.getInstance()
                .getTimeInMillis()).toString();
        ContentValues contentValues = new ContentValues();
        for (int i = 0; key.size() > i; i++) {
            contentValues.put(key.get(i), value.get(i));
        }
        contentValues.put(KEY_TIMESTAMP, timeStamp);
        return db.insert(DATABASE_TABLE, null, contentValues);
    }

    public boolean removeEntry(long rowIndex) {
        return db.delete(DATABASE_TABLE, KEY_ID + "=" + rowIndex, null) > 0;
    }

    public Cursor getAllEntries(String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String sortBy, String sortOption) {
        return db.query(DATABASE_TABLE, columns, selection, selectionArgs,
                groupBy, having, sortBy + " " + sortOption);
    }

    public int updateEntry(long rowIndex, ArrayList<String> key,
            ArrayList<String> value) {
        String timeStamp = new Timestamp(Calendar.getInstance()
                .getTimeInMillis()).toString();
        String where = KEY_ID + "=" + rowIndex;
        ContentValues contentValues = new ContentValues();
        for (int i = 0; key.size() > i; i++) {
            contentValues.put(key.get(i), value.get(i));
        }
        contentValues.put(KEY_TIMESTAMP, timeStamp);
        return db.update(DATABASE_TABLE, contentValues, where, null);
    }

    public boolean clearTable() {
        return db.delete(DATABASE_TABLE, null, null) > 0;
    }

    private static class myDBHelper extends SQLiteOpenHelper {
        private String creationString;
        private String tableName;
        @SuppressWarnings("unused")
        SQLiteDatabase db;

        /**
         * Creates a myDBHelper object.
         * 
         * @param context
         *            The context where the access is needed
         * @param name
         *            Name of database file
         * @param factory
         *            A CursorFactory, or null to use default CursorFactory
         * @param version
         *            Database version
         * @param tableName
         *            Name of table within database
         * @param creationString
         *            SQL String used to create the database
         */
        public myDBHelper(Context context, String name, CursorFactory factory,
                int version, String tableName, String creationString) {
            super(context, name, factory, version);
            this.creationString = creationString;
            this.tableName = tableName;
        }

        /**
         * Creates the database table.
         * 
         * @param db
         *            The database used by this helper to create the table in
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(creationString);
        }

        /**
         * This method determines if the database needs to be updated or not.
         * 
         * @param db
         *            The database used by this helper
         * @param oldVersion
         *            The old database version
         * @param newVersion
         *            The new database version
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // Log the version upgrade
            Log.w("TaskDBAdapter", "Upgrading from version " + oldVersion
                    + " to " + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + tableName);
            onCreate(db);

        }

        /**
         * Creates tables when the database is opened if the tables need to be
         * created.
         * 
         * @param db
         *            The database used by this helper
         */
        @Override
        public void onOpen(SQLiteDatabase db) {
            db.execSQL(creationString);
        }

    }
}

I then made a class to add the object: public class AddMed extends Activity implements OnClickListener {

Button submit;
    EditText name, dossage;
    DBAdapter database;
    String names, amount;
    String total;
    ArrayList<String> btr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        // TODO Auto-generated method stub
        super.onCreate(savedInstanceState);
        setContentView(R.layout.addmed);
        submit = (Button) findViewById(R.id.bSubmit);
        name = (EditText) findViewById(R.id.etNameOfMed);
        dossage = (EditText) findViewById(R.id.etHowMuch);
    }

    @Override
    public void onClick(View arg0) {
        // TODO Auto-generated method stub
        switch (arg0.getId()) {
        case R.id.bSubmit:
            names = name.getText().toString();
            amount = dossage.getText().toString();
            total = names + amount;
            btr.add(total);
            database.open();
            database.insertEntry(btr, btr);
            database.close();
            break;
        }
    }

}

I then have another class with a text view where I tried making a new DBAdatper instance and using the getAllEntries method from the cursor but I am confused on the parameters. Can someone enlighten me? Thank You for your help.

Answer 1

From SQLiteDatabase documentation for query

public Cursor query (String table, String[] columns, String selection, String[]
selectionArgs, String groupBy, String having, String orderBy, String limit)

Added in API level 1

Query the given table, returning a Cursor over the result set.

Parameters

table       The table name to compile the query against.

columns     A list of which columns to return. Passing null will return all
            columns, which is discouraged to prevent reading data from storage
            that isn't going to be used.

selection   A filter declaring which rows to return, formatted as an SQL WHERE
            clause (excluding the WHERE itself). Passing null will return all rows for
            the given table.

selectionArgs   You may include ?s in selection, which will be replaced by the
                values from selectionArgs, in order that they appear in the
                selection. The values will be bound as Strings.

groupBy     A filter declaring how to group rows, formatted as an SQL GROUP BY 
            clause (excluding the GROUP BY itself). Passing null will cause the rows
            to not be grouped.

having      A filter declare which row groups to include in the cursor, if row
            grouping is being used, formatted as an SQL HAVING clause (excluding the
            HAVING itself). Passing null will cause all row groups to be included,
            and is required when row grouping is not being used.

orderBy     How to order the rows, formatted as an SQL ORDER BY clause (excluding
            the ORDER BY itself). Passing null will use the default sort order, which
            may be unordered.

limit       Limits the number of rows returned by the query, formatted as LIMIT
            clause. Passing null denotes no LIMIT clause.

Say you had a table called table1 with the three columns called x, y, z. And you wanted to do the following statement:

SELECT x,y FROM table1 WHERE x = 'squirrels' AND y = 'cats';

The table paramater is just the name of the table("table1"). The columns parameter would be the columns you're interested in getting the values for (in this case, the "x" and "y" columns). The selection parameter would be "x = ? AND y = ?". The selectionArgs parameter would be an array that contains "squirrels" and "cats". And the rest are pretty self explanatory if you know SQL.

Answer 2

It seems like the example you referenced is simply attempting to act as a wrapper around the SQLLiteDatabase class. The example lists what each parameter in getAllEntries is for. It seems like there are a lot of parameters, but several of them can just be null if you do not want to group or sort your result set. Here is a list of what each parameter does, taken from the SQLLiteDatabase API:

Answer 3

The tutorial you found delivers a class which is trying to do a lot automatically for you: great if you get it, but not so great if you don't.

I'd advise you to drop this example, which is quite complicated, and instead start from scratch on your own, with a more proper tutorial. http://www.vogella.com/ has a big collection of good tutorials, including the SQLite Database : SQLite