Example usage for android.database.sqlite SQLiteDatabase rawQuery

Introduction

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

Prototype

public Cursor rawQuery(String sql, String[] selectionArgs) 

Source Link

Document

Runs the provided SQL and returns a Cursor over the result set.

Usage

From source file:ru.gkpromtech.exhibition.db.DbHelper.java

public void applyUpdates(SQLiteDatabase db, JsonNode updates, boolean isStatic) throws Exception {

    JsonNode nodeRev = updates.get("revision");
    if (nodeRev == null)
        return;//from w ww .  j a  va 2 s  . c o m

    final String synchronous = getPragma(db, "synchronous");
    final String journalMode = getPragma(db, "journal_mode");

    db.rawQuery("PRAGMA synchronous = OFF", null);
    db.rawQuery("PRAGMA journal_mode = MEMORY", null);
    //  FK,  ?   ???    add-update
    db.execSQL("PRAGMA foreign_keys = OFF");

    SharedPreferences prefs = getPrefs();

    int langId = Profile.getInstance(mContext).getLangId();
    int currentRevision = prefs.getInt("revision", 0);
    ObjectMapper mapper = new ObjectMapper();
    int revision = nodeRev.asInt();
    ArrayNode nodeChanges = (ArrayNode) updates.get("changes");

    TypeReference<List<Change>> typeRef = new TypeReference<List<Change>>() {
    };
    List<Change> changes = mapper.readValue(nodeChanges.traverse(), typeRef);

    Map<Table, List<Integer>> deletedTableRowIds = new HashMap<>();

    try {
        db.beginTransaction();

        for (Change change : changes) {
            if (currentRevision > change.id) {
                Log.w("PPDB", "Skipping old change #" + change.id);
                continue;
            }

            boolean tr = change.entity.endsWith("_tr");
            String entityName = !tr ? change.entity : change.entity.substring(0, change.entity.length() - 3);

            Class<? extends Entity> entity = getEntityForTableName(entityName);
            if (entity == null) {
                Log.e("PPDB", "Cannot find entity for " + entityName);
                continue;
            }

            Table<? extends Entity> table = getTableFor(entity);
            if (table == null) {
                Log.e("PPDB", "Cannot find table for entity " + entityName);
                continue;
            }

            if (!tr) {
                if (change.data != null) {
                    switch (change.changetype) {
                    case Change.ADDED:
                        table.insert(db, change.data, SQLiteDatabase.CONFLICT_FAIL);
                        break;
                    case Change.UPDATED:
                        change.data.remove("id");
                        table.partialUpdate(db, change.rowid, change.data, SQLiteDatabase.CONFLICT_FAIL);
                        break;
                    }
                } else {
                    if (change.changetype == Change.DELETED) {
                        List<Integer> ids = deletedTableRowIds.get(table);
                        if (ids == null) {
                            ids = new ArrayList<>();
                            deletedTableRowIds.put(table, ids);
                        }
                        ids.add(change.rowid);
                    }
                }
            } else if (change.data != null) {
                int changeLangId = change.data.get("languageid").asInt();
                if (changeLangId != langId)
                    continue;
                change.data.remove("languageid");
                switch (change.changetype) {
                case Change.ADDED:
                case Change.UPDATED:
                    TableRef annotation = entity.getAnnotation(TableRef.class);
                    if (annotation == null) {
                        Log.e("PPDB", "Cannot get trid field for entity " + entityName);
                        continue;
                    }
                    String trIdName = annotation.trid();
                    JsonNode nodeTrId = change.data.get(trIdName);
                    if (nodeTrId == null) {
                        Log.e("PPDB", "Change data don't have a field [" + trIdName + "]: " + entityName);
                        continue;
                    }
                    int id = nodeTrId.asInt();
                    change.data.remove("id");
                    change.data.remove(trIdName);
                    table.partialUpdate(db, id, change.data, SQLiteDatabase.CONFLICT_FAIL);
                    break;
                }
            }
        }

        db.setTransactionSuccessful();

    } finally {
        db.endTransaction();
    }

    //   ??? ?, ? FK
    db.execSQL("PRAGMA foreign_keys = ON");

    try {
        db.beginTransaction();

        for (Map.Entry<Table, List<Integer>> entry : deletedTableRowIds.entrySet()) {
            Table table = entry.getKey();
            List<Integer> ids = entry.getValue();
            for (Integer id : ids)
                table.delete(db, id);
        }

        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

    if (synchronous != null)
        db.rawQuery("PRAGMA synchronous = " + synchronous, null);
    if (journalMode != null)
        db.rawQuery("PRAGMA journal_mode = " + journalMode, null);

    if (revision > currentRevision)
        prefs.edit().putInt("revision", revision).apply();

    if (isStatic)
        prefs.edit().putInt("jsonRevision", revision).apply();
}

---

From source file:com.concentricsky.android.khanacademy.util.OfflineVideoManager.java

public int getDownloadCountForTopic(SQLiteOpenHelper dbh, String topicId, int depth) {
    Log.d(LOG_TAG, "getDownloadCountForTopic");

    int result = 0;
    SQLiteDatabase db = dbh.getReadableDatabase();

    for (int i = 0; i < depth; ++i) {
        String sql = buildDownloadCountQuery(i);
        Cursor c = db.rawQuery(sql, new String[] { topicId });
        c.moveToFirst();//from www . j  a  va2  s . co  m
        result += c.getInt(0);
        Log.d(LOG_TAG, " result is " + result);
        c.close();
    }

    return result;
}

---

From source file:com.fitforbusiness.nafc.dashboard.DashBoardFragment.java

private void loadQualificationHours() {
    SQLiteDatabase sqLiteDatabase = null;
    try {/*w  ww  . j  a  va 2 s  .com*/
        sqLiteDatabase = DatabaseHelper.instance().getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("select sum(points) as total_hours from "
                + "trainer_profile_accreditation where deleted =0 and is_point=1", null);

        if (cursor.moveToFirst()) {
            totalCecHours.setText(
                    "Training Hours : " + (cursor.getString(cursor.getColumnIndex("total_hours")) != null
                            ? cursor.getString(cursor.getColumnIndex("total_hours"))
                            : ""));
        }
        cursor.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
    }
}

---

From source file:com.fitforbusiness.nafc.dashboard.DashBoardFragment.java

private void loadQualificationPoint() {
    SQLiteDatabase sqLiteDatabase = null;
    try {//from w  ww.j  a v a2  s. c  o m
        sqLiteDatabase = DatabaseHelper.instance().getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery("select sum(points) as total_points from "
                + "trainer_profile_accreditation where deleted =0 and is_point=0 " + " and "
                + Table.TrainerProfileAccreditation.COMPLETED_DATE + " >= " + "date(\'now\',\'-2 year\')",
                null);

        if (cursor.moveToFirst()) {
            totalCecPoints
                    .setText("CEC Points : " + (cursor.getString(cursor.getColumnIndex("total_points")) != null
                            ? cursor.getString(cursor.getColumnIndex("total_points"))
                            : ""));
        }
        cursor.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
    }

}

---

From source file:ru.gkpromtech.exhibition.db.Table.java

public <F extends Entity, S extends Entity> List<Pair<F, S>> selectLinked(Class<F> f, Class<S> s,
        String selection, String[] selectionArgs, String orderBy)
        throws InvalidClassException, IllegalAccessException, InstantiationException {

    if (mFks.length != 2)
        throw new InvalidClassException("Entity " + mEntityClass.getName() + " is not a link");

    List<Pair<F, S>> result = new ArrayList<>();
    FkInfo fk1;/*from ww w  .  ja  v  a 2s . c  o  m*/
    FkInfo fk2;

    if (mFks[0].entityClass.equals(f) && mFks[1].entityClass.equals(s)) {
        fk1 = mFks[0];
        fk2 = mFks[1];
    } else if (mFks[1].entityClass.equals(f) && mFks[0].entityClass.equals(s)) {
        //     ?   ?? ?
        fk1 = mFks[1];
        fk2 = mFks[0];
    } else {
        throw new InvalidClassException("Invalid classes passed as arguments");
    }

    Table<F> table1 = ((DbHelper) mSqlHelper).getTableFor(f);
    Table<S> table2 = ((DbHelper) mSqlHelper).getTableFor(s);

    StringBuilder query = new StringBuilder();
    for (String column : table1.mColumns) {
        query.append(",f.").append(column);
    }
    for (String column : table2.mColumns)
        query.append(",s.").append(column);
    query.replace(0, 1, "SELECT ");

    query.append("\nFROM ").append(mTableName).append(" t\nJOIN ").append(table1.mTableName).append(" f ON f.")
            .append(fk1.fkName).append(" = t.").append(fk1.fieldName).append("\nJOIN ")
            .append(table2.mTableName).append(" s ON s.").append(fk2.fkName).append(" = t.")
            .append(fk2.fieldName);
    if (selection != null)
        query.append("\nWHERE ").append(selection);
    if (orderBy != null)
        query.append("\nORDER BY ").append(orderBy);

    String queryString = query.toString();
    if (BuildConfig.DEBUG)
        Log.d("PP", queryString);

    SQLiteDatabase db = mSqlHelper.getReadableDatabase();
    Cursor cursor = db.rawQuery(queryString, selectionArgs);

    //noinspection TryFinallyCanBeTryWithResources
    try {
        while (cursor.moveToNext()) {
            F entity1 = f.newInstance();
            S entity2 = s.newInstance();
            for (int i = 0; i < table1.mFields.length; ++i)
                fillFieldValue(table1.mType[i], table1.mFields[i], entity1, cursor, i);
            for (int i = 0; i < table2.mFields.length; ++i)
                fillFieldValue(table2.mType[i], table2.mFields[i], entity2, cursor, table1.mFields.length + i);
            result.add(new Pair<>(entity1, entity2));
        }
    } finally {
        cursor.close();
        db.close();
    }

    return result;
}

---

From source file:ru.gkpromtech.exhibition.db.Table.java

public List<Pair<Entity[], T>> selectJoined(Join[] joins, String selection, String[] selectionArgs,
        String orderBy, String groupBy)
        throws InvalidClassException, IllegalAccessException, InstantiationException {

    List<Pair<Entity[], T>> result = new ArrayList<>();
    Table<? extends Entity>[] tables = new Table<?>[joins.length];

    StringBuilder query = new StringBuilder();
    for (int i = 0; i < joins.length; ++i) {
        tables[i] = ((DbHelper) mSqlHelper).getTableFor(joins[i].entity);
        for (String column : tables[i].mColumns) {
            query.append(",f").append(i).append(".").append(column);
        }//from  ww  w.ja  v a 2s .  com
    }
    for (String column : mColumns)
        query.append(",t.").append(column);
    query.replace(0, 1, "SELECT "); // first comma -> select

    query.append("\nFROM ").append(mTableName).append(" t");
    for (int i = 0; i < joins.length; ++i) {
        Join join = joins[i];
        query.append("\n");
        if (join.type != null)
            query.append(join.type).append(" ");
        query.append("JOIN ").append(tables[i].mTableName).append(" f").append(i).append(" ON ");
        if (join.customJoinOn != null) {
            query.append(join.customJoinOn);
        } else {
            query.append("f").append(i).append(".").append(join.entityRow).append(" = t.").append(join.row);
        }
    }

    if (selection != null)
        query.append("\nWHERE ").append(selection);
    if (groupBy != null)
        query.append("\nGROUP BY ").append(groupBy);
    if (orderBy != null)
        query.append("\nORDER BY ").append(orderBy);

    String queryString = query.toString();
    if (BuildConfig.DEBUG)
        Log.d("PP", queryString);

    SQLiteDatabase db = mSqlHelper.getReadableDatabase();
    Cursor cursor = db.rawQuery(queryString, selectionArgs);

    //noinspection TryFinallyCanBeTryWithResources
    try {
        while (cursor.moveToNext()) {
            int col = 0;
            Entity[] entities = new Entity[joins.length];
            for (int i = 0; i < joins.length; ++i) {
                Table<? extends Entity> table = tables[i];
                entities[i] = joins[i].entity.newInstance();
                for (int j = 0; j < table.mFields.length; ++j, ++col)
                    fillFieldValue(table.mType[j], table.mFields[j], entities[i], cursor, col);
            }

            T entity = mEntityClass.newInstance();
            for (int j = 0; j < mFields.length; ++j, ++col)
                fillFieldValue(mType[j], mFields[j], entity, cursor, col);
            result.add(new Pair<>(entities, entity));
        }
    } finally {
        cursor.close();
        db.close();
    }

    return result;
}

---

From source file:de.nware.app.hsDroid.provider.onlineService2Provider.java

/**
 * Prfen ob eine bestimmte Prfungsleistung schon eingetragen ist.
 * /* w w  w  . j  a va  2s. co  m*/
 * @param examnr
 *            Prfungsnummer
 * @param examdate
 *            Prfungsdatum
 * @return true, wenn erfolgreich
 */
public boolean examExists(String examnr, String examdate) {
    SQLiteDatabase mDb = mOpenHelper.getReadableDatabase();
    Cursor cursor = mDb.rawQuery(
            "select 1 from " + mOpenHelper.getTableName() + " where " + onlineService2Data.ExamsCol.EXAMNR
                    + "=? AND " + onlineService2Data.ExamsCol.EXAMDATE + "=?",
            new String[] { examnr, examdate });
    boolean exists = (cursor.getCount() > 0);
    cursor.close();
    return exists;
}

---

From source file:pl.selvin.android.syncframework.content.BaseContentProvider.java

public void onUpgradeDatabase(SQLiteDatabase db, int oldVersion, int newVersion) {
    Cursor c = db.rawQuery("select 'drop table ' || name || ';' from sqlite_master where type = 'table'", null);
    if (c.moveToFirst()) {
        do {/*from www.jav  a 2 s. co  m*/
            db.execSQL(c.getString(0));
        } while (c.moveToNext());
    }
    onCreateDataBase(db);
}

---

From source file:com.fitforbusiness.nafc.dashboard.DashBoardFragment.java

private RowData getImageName(String _id, int isGroup) {
    String query = "";

    if (isGroup == 0) {
        query = "select  * " + " from " + Table.Client.TABLE_NAME + " where " + Table.DELETED + " = 0 "
                + " and " + Table.Client.ID + " = " + _id;
    } else {//from   ww w .ja  va 2s  .com

        query = "select  * " + " from " + Table.Group.TABLE_NAME + " where " + Table.DELETED + " = 0 " + " and "
                + Table.Group.ID + " = " + _id;
    }
    SQLiteDatabase sqlDB = null;
    try {
        sqlDB = DatabaseHelper.instance().getReadableDatabase();

        Log.d("query is ", query);
        assert sqlDB != null;
        Cursor cursor = sqlDB.rawQuery(query, null);

        if (cursor.moveToFirst()) {

            String imageName = (cursor.getString(cursor.getColumnIndex(Table.Client.PHOTO_URL)));
            String personName;
            if (isGroup == 0) {
                personName = cursor.getString(cursor.getColumnIndex(Table.Client.FIRST_NAME)) + " "
                        + cursor.getString(cursor.getColumnIndex(Table.Client.LAST_NAME));
            } else {
                personName = cursor.getString(cursor.getColumnIndex(Table.Group.NAME));
            }

            rowData.setImageName(imageName);
            rowData.setPersonName(personName);
        }
        cursor.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
    }

    return rowData;
}

---

From source file:com.towson.wavyleaf.Sighting.java

protected boolean isDBEmpty() {
    DatabaseListJSONData m_dbListData = new DatabaseListJSONData(this);
    SQLiteDatabase db = m_dbListData.getWritableDatabase();

    Cursor cur = db.rawQuery("SELECT * FROM " + DatabaseConstants.TABLE_NAME, null);
    if (cur.moveToFirst())
        return false;
    else//from www  . j ava2  s .c om
        return true;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »