Example usage for android.database.sqlite SQLiteDatabase query

List of usage examples for android.database.sqlite SQLiteDatabase query

  1. HOME
  2. Java
  3. android
  4. android.database.sqlite.*
  5. SQLiteDatabase
  6. query

Introduction

In this page you can find the example usage for android.database.sqlite SQLiteDatabase query.

Prototype

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

Source Link

Document

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

Usage

From source file:org.frc836.database.DB.java

public long getWheelTypeIDFromName(String type, SQLiteDatabase db) {

    String[] projection = { WHEEL_TYPE_LU_Entry.COLUMN_NAME_ID };
    String[] where = { type };//from w ww. j  a va 2  s  .  c o m
    Cursor c = db.query(WHEEL_TYPE_LU_Entry.TABLE_NAME, projection, // select
            WHEEL_TYPE_LU_Entry.COLUMN_NAME_WHEEL_TYPE_DESC + " LIKE ?", where, // EventName
            null, // don't group
            null, // don't filter
            null, // don't order
            "0,1"); // limit to 1
    long ret = -1;
    try {
        c.moveToFirst();
        ret = c.getLong(c.getColumnIndexOrThrow(WHEEL_TYPE_LU_Entry.COLUMN_NAME_ID));
    } finally {
        if (c != null)
            c.close();
    }
    return ret;
}

From source file:org.frc836.database.DB.java

public long getConfigIDFromName(String config, SQLiteDatabase db) {

    String[] projection = { CONFIGURATION_LU_Entry.COLUMN_NAME_ID };
    String[] where = { config };//from  w  w w . j a  v a2s.co m
    Cursor c = db.query(CONFIGURATION_LU_Entry.TABLE_NAME, projection, // select
            CONFIGURATION_LU_Entry.COLUMN_NAME_CONFIGURATION_DESC + " LIKE ?", where, // EventName
            null, // don't group
            null, // don't filter
            null, // don't order
            "0,1"); // limit to 1
    long ret = -1;
    try {
        c.moveToFirst();
        ret = c.getLong(c.getColumnIndexOrThrow(CONFIGURATION_LU_Entry.COLUMN_NAME_ID));
    } finally {
        if (c != null)
            c.close();
    }
    return ret;
}

From source file:org.frc836.database.DB.java

public long getEventIDFromName(String eventName, SQLiteDatabase db) {

    String[] projection = { EVENT_LU_Entry.COLUMN_NAME_ID };
    String[] where = { eventName };
    Cursor c = db.query(EVENT_LU_Entry.TABLE_NAME, // from the event_lu
            // table
            projection, // select
            EVENT_LU_Entry.COLUMN_NAME_EVENT_NAME + " LIKE ?", // where
            // event_name
            // ==
            where, // EventName
            null, // don't group
            null, // don't filter
            null, // don't order
            "0,1"); // limit to 1
    long ret = -1;
    try {//from   ww w. j  av a2  s  . c o  m
        c.moveToFirst();
        ret = c.getLong(c.getColumnIndexOrThrow(EVENT_LU_Entry.COLUMN_NAME_ID));
    } finally {
        if (c != null)
            c.close();
    }

    return ret;
}

From source file:org.frc836.database.DB.java

private void insertOrUpdate(String table, String nullColumnHack, ContentValues values, String idColumnName,
        String whereClause, String[] whereArgs) {
    synchronized (ScoutingDBHelper.lock) {
        SQLiteDatabase db = ScoutingDBHelper.getInstance().getWritableDatabase();

        String[] projection = { idColumnName };

        Cursor c = db.query(table, projection, whereClause, whereArgs, null, null, null, "0,1");
        try {//from   w  ww. ja v a 2s  .  c  om
            if (c.moveToFirst()) {
                String[] id = { c.getString(c.getColumnIndexOrThrow(idColumnName)) };
                values.put(COLUMN_NAME_TIMESTAMP, dateParser.format(new Date()));
                db.update(table, values, idColumnName + "=?", id);
            } else {
                db.insert(table, nullColumnHack, values);
            }
        } finally {
            if (c != null)
                c.close();
            ScoutingDBHelper.getInstance().close();
        }
    }
}

From source file:org.frc836.database.DB.java

public long getPosIDFromName(String position, SQLiteDatabase db) {

    String[] projection = { POSITION_LU_Entry.COLUMN_NAME_ID };
    String[] where = { position };
    Cursor c = db.query(POSITION_LU_Entry.TABLE_NAME, // from the event_lu
            // table
            projection, // select
            POSITION_LU_Entry.COLUMN_NAME_POSITION + " LIKE ?", // where
            // event_name
            // ==
            where, // EventName
            null, // don't group
            null, // don't filter
            null, // don't order
            "0,1"); // limit to 1
    long ret = -1;
    try {/* w  w w. j av a 2  s  .  co m*/
        c.moveToFirst();
        ret = c.getLong(c.getColumnIndexOrThrow(POSITION_LU_Entry.COLUMN_NAME_ID));
    } finally {
        if (c != null)
            c.close();
    }

    return ret;
}

From source file:com.raspi.chatapp.util.storage.MessageHistory.java

public MessageArrayContent[] getMessages(String buddyId, int amount, int offset, boolean reverse) {
    //Log.d("DATABASE", "Getting messages");
    SQLiteDatabase db = mDbHelper.getReadableDatabase();
    String[] columns = new String[] { MessageHistoryContract.MessageEntry.COLUMN_NAME_BUDDY_ID,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_TYPE,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_CONTENT,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_URL,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_STATUS,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_TIMESTAMP,
            MessageHistoryContract.MessageEntry._ID,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_OTHERS_ID };
    Cursor messages = db.query(buddyId, columns, null, null, null, null,
            MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_TIMESTAMP + " DESC", offset + "," + amount);

    if (reverse)/*w w w  .  j a  v  a 2  s .  c o m*/
        messages.moveToFirst();
    else
        messages.moveToLast();
    int messageCount = messages.getCount();
    MessageArrayContent[] result = new MessageArrayContent[messageCount];
    int i = 0;
    if (messages.getCount() > 0)
        do {
            String from = messages.getString(0);
            SharedPreferences preferences = context.getSharedPreferences(Constants.PREFERENCES, 0);
            String me = preferences.getString(Constants.USERNAME, "");
            String type = messages.getString(1);
            String content = messages.getString(2);
            String url = messages.getString(3);
            int progress = 0;
            String status = messages.getString(4);
            long time = messages.getLong(5);
            long _ID = messages.getLong(6);
            long othersId = messages.getLong(7);
            switch (type) {
            case (MessageHistory.TYPE_TEXT):
                result[i] = new TextMessage(!me.equals(from), content, time, status, _ID, othersId);
                //            if (((TextMessage) result[i]).left)
                //              updateMessageStatus(from, _ID, STATUS_READ);
                break;
            case (MessageHistory.TYPE_IMAGE):
                try {
                    JSONArray contentJSON = new JSONArray(content);
                    result[i] = new ImageMessage(!me.equals(from), //left
                            contentJSON.getString(0), //File
                            contentJSON.getString(1), //description
                            url, //url
                            progress, //progress
                            time, //timeStamp
                            status, //status
                            _ID, //_ID
                            buddyId, //buddyID
                            othersId); //othersId
                } catch (Exception e) {
                    e.printStackTrace();
                }
                break;
            }
            i++;
        } while (messages.move((reverse) ? 1 : -1));
    db.close();
    messages.close();
    return result;
}

From source file:com.raspi.chatapp.util.storage.MessageHistory.java

public MessageArrayContent getLastMessage(String buddyId) {
    MessageArrayContent mac = null;/* ww w .  j av  a  2 s.  c  o  m*/
    SQLiteDatabase db = mDbHelper.getReadableDatabase();

    try {
        String[] columns = new String[] { MessageHistoryContract.MessageEntry.COLUMN_NAME_BUDDY_ID,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_TYPE,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_CONTENT,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_STATUS,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_TIMESTAMP,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_URL,
                MessageHistoryContract.MessageEntry._ID,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_OTHERS_ID };
        Cursor lastMessage = db.query(buddyId, columns, null, null, null, null,
                MessageHistoryContract.MessageEntry.COLUMN_NAME_MESSAGE_TIMESTAMP + " DESC", "1");
        lastMessage.moveToFirst();
        if (lastMessage.getCount() != 0 && lastMessage.moveToFirst()) {
            String type = lastMessage.getString(1);
            SharedPreferences preferences = context.getSharedPreferences(Constants.PREFERENCES, 0);
            String me = preferences.getString(Constants.USERNAME, "");
            boolean sent = me.equals(lastMessage.getString(0));

            if (TYPE_TEXT.equals(type)) {
                mac = new TextMessage(!sent, lastMessage.getString(2), lastMessage.getLong(4),
                        lastMessage.getString(3), lastMessage.getLong(6), lastMessage.getLong(7));
            } else if (TYPE_IMAGE.equals(type)) {
                JSONArray contentJSON = new JSONArray(lastMessage.getString(2));
                mac = new ImageMessage(!sent, contentJSON.getString(0), contentJSON.getString(1),
                        lastMessage.getString(5), lastMessage.getInt(6), lastMessage.getLong(4),
                        lastMessage.getString(3), lastMessage.getLong(6), lastMessage.getString(1),
                        lastMessage.getLong(7));
            }
        }
    } catch (Exception e) {

    } finally {
        db.close();
    }
    return mac;
}

From source file:org.frc836.database.DBSyncService.java

private void processConfig(JSONArray config) {
    try {//w w  w  . j av a 2s  .  c  o m
        for (int i = 0; i < config.length(); i++) {
            JSONObject row = config.getJSONObject(i);
            Action action = Action.UPDATE;
            if (row.getInt(CONFIGURATION_LU_Entry.COLUMN_NAME_INVALID) != 0) {
                action = Action.DELETE;
            }
            ContentValues vals = new ContentValues();
            vals.put(CONFIGURATION_LU_Entry.COLUMN_NAME_ID, row.getInt(CONFIGURATION_LU_Entry.COLUMN_NAME_ID));
            vals.put(CONFIGURATION_LU_Entry.COLUMN_NAME_CONFIGURATION_DESC,
                    row.getString(CONFIGURATION_LU_Entry.COLUMN_NAME_CONFIGURATION_DESC));
            vals.put(CONFIGURATION_LU_Entry.COLUMN_NAME_TIMESTAMP, DB.dateParser
                    .format(new Date(row.getLong(CONFIGURATION_LU_Entry.COLUMN_NAME_TIMESTAMP) * 1000)));

            // check if this entry exists already
            String[] projection = { CONFIGURATION_LU_Entry.COLUMN_NAME_CONFIGURATION_DESC };
            String[] where = { vals.getAsString(CONFIGURATION_LU_Entry.COLUMN_NAME_ID) };
            synchronized (ScoutingDBHelper.lock) {

                SQLiteDatabase db = ScoutingDBHelper.getInstance().getWritableDatabase();

                Cursor c = db.query(CONFIGURATION_LU_Entry.TABLE_NAME, projection, // select
                        CONFIGURATION_LU_Entry.COLUMN_NAME_ID + "=?", where, null, // don't
                        // group
                        null, // don't filter
                        null, // don't order
                        "0,1"); // limit to 1
                try {
                    if (!c.moveToFirst()) {
                        if (action == Action.UPDATE)
                            action = Action.INSERT;
                        else if (action == Action.DELETE)
                            action = Action.NOTHING;
                    }

                    switch (action) {
                    case UPDATE:
                        db.update(CONFIGURATION_LU_Entry.TABLE_NAME, vals,
                                CONFIGURATION_LU_Entry.COLUMN_NAME_ID + " = ?", where);
                        break;
                    case INSERT:
                        db.insert(CONFIGURATION_LU_Entry.TABLE_NAME, null, vals);
                        break;
                    case DELETE:
                        db.delete(CONFIGURATION_LU_Entry.TABLE_NAME,
                                CONFIGURATION_LU_Entry.COLUMN_NAME_ID + " = ?", where);
                        break;
                    default:
                    }
                } finally {
                    if (c != null)
                        c.close();
                    ScoutingDBHelper.getInstance().close();
                }
            }
        }
    } catch (JSONException e) {
        // TODO handle error
    }
}

From source file:org.frc836.database.DBSyncService.java

private void processPits(JSONArray pits) {
    // TODO could be abstracted further
    try {/*from  w w w .j av  a  2 s .  co  m*/
        for (int i = 0; i < pits.length(); i++) {
            JSONObject row = pits.getJSONObject(i);
            Action action = Action.UPDATE;
            if (row.getInt(PitStats.COLUMN_NAME_INVALID) != 0) {
                action = Action.DELETE;
            }
            ContentValues vals = PitStats.getNewPitStats().jsonToCV(row);

            // check if this entry exists already
            String[] projection = { PitStats.COLUMN_NAME_ID, PitStats.COLUMN_NAME_INVALID };
            String[] where = { vals.getAsString(PitStats.COLUMN_NAME_TEAM_ID) };

            synchronized (ScoutingDBHelper.lock) {

                SQLiteDatabase db = ScoutingDBHelper.getInstance().getWritableDatabase();

                Cursor c = db.query(PitStats.TABLE_NAME, projection, // select
                        PitStats.COLUMN_NAME_TEAM_ID + "=?", where, null, // don't
                        // group
                        null, // don't filter
                        null, // don't order
                        "0,1"); // limit to 1
                try {
                    if (!c.moveToFirst()) {
                        if (action == Action.UPDATE)
                            action = Action.INSERT;
                        else if (action == Action.DELETE)
                            action = Action.NOTHING;
                    } else {
                        int invalid = c.getInt(c.getColumnIndexOrThrow(PitStats.COLUMN_NAME_INVALID));
                        if (invalid > 0) // Current entry has not been sent
                                         // to server, don't overwrite
                            action = Action.NOTHING;
                    }

                    switch (action) {
                    case UPDATE:
                        db.update(PitStats.TABLE_NAME, vals, PitStats.COLUMN_NAME_TEAM_ID + " = ?", where);
                        break;
                    case INSERT:
                        db.insert(PitStats.TABLE_NAME, null, vals);
                        break;
                    case DELETE:
                        db.delete(PitStats.TABLE_NAME, PitStats.COLUMN_NAME_TEAM_ID + " = ?", where);
                        break;
                    default:
                    }
                } finally {
                    if (c != null)
                        c.close();
                    ScoutingDBHelper.getInstance().close();
                }
            }
        }
    } catch (JSONException e) {
        // TODO handle error
    }
}

From source file:org.frc836.database.DBSyncService.java

private void processNotes(JSONArray notes) {
    try {/*from   ww  w.ja  va 2s. c o m*/
        for (int i = 0; i < notes.length(); i++) {
            JSONObject row = notes.getJSONObject(i);
            Action action = Action.UPDATE;
            if (row.getInt(NOTES_OPTIONS_Entry.COLUMN_NAME_INVALID) != 0) {
                action = Action.DELETE;
            }
            ContentValues vals = new ContentValues();
            vals.put(NOTES_OPTIONS_Entry.COLUMN_NAME_ID, row.getInt(NOTES_OPTIONS_Entry.COLUMN_NAME_ID));
            vals.put(NOTES_OPTIONS_Entry.COLUMN_NAME_OPTION_TEXT,
                    row.getString(NOTES_OPTIONS_Entry.COLUMN_NAME_OPTION_TEXT));
            vals.put(NOTES_OPTIONS_Entry.COLUMN_NAME_TIMESTAMP, DB.dateParser
                    .format(new Date(row.getLong(NOTES_OPTIONS_Entry.COLUMN_NAME_TIMESTAMP) * 1000)));

            // check if this entry exists already
            String[] projection = { NOTES_OPTIONS_Entry.COLUMN_NAME_OPTION_TEXT };
            String[] where = { vals.getAsString(NOTES_OPTIONS_Entry.COLUMN_NAME_ID) };

            synchronized (ScoutingDBHelper.lock) {

                SQLiteDatabase db = ScoutingDBHelper.getInstance().getWritableDatabase();

                Cursor c = db.query(NOTES_OPTIONS_Entry.TABLE_NAME, projection, // select
                        NOTES_OPTIONS_Entry.COLUMN_NAME_ID + "=?", where, null, // don't
                        // group
                        null, // don't filter
                        null, // don't order
                        "0,1"); // limit to 1
                try {
                    if (!c.moveToFirst()) {
                        if (action == Action.UPDATE)
                            action = Action.INSERT;
                        else if (action == Action.DELETE)
                            action = Action.NOTHING;
                    }

                    switch (action) {
                    case UPDATE:
                        db.update(NOTES_OPTIONS_Entry.TABLE_NAME, vals,
                                NOTES_OPTIONS_Entry.COLUMN_NAME_ID + " = ?", where);
                        break;
                    case INSERT:
                        db.insert(NOTES_OPTIONS_Entry.TABLE_NAME, null, vals);
                        break;
                    case DELETE:
                        db.delete(NOTES_OPTIONS_Entry.TABLE_NAME, NOTES_OPTIONS_Entry.COLUMN_NAME_ID + " = ?",
                                where);
                        break;
                    default:
                    }
                } finally {
                    if (c != null)
                        c.close();
                    ScoutingDBHelper.getInstance().close();
                }
            }
        }
    } catch (JSONException e) {
        // TODO handle error
    }
}