Example usage for android.database.sqlite SQLiteDatabase rawQuery

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

  1. HOME
  2. Java
  3. android
  4. android.database.sqlite.*
  5. SQLiteDatabase
  6. 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:holidayiq.com.geofenced.geofence.GeofenceTransitionsIntentService.java

public static void SendNotificationForEntry(Context mContext, String geoId, String geoName,
        String notificationStringTitle, String notificationStringContent, String notificationStringDeeplink,
        String objType, Context context, String eventType, boolean isBanner) {
    String currentTime = String.valueOf(System.currentTimeMillis());
    if (notificationStringTitle != null) {
        notificationStringTitle = notificationStringTitle.replace("$name$", geoName);
        notificationStringContent = notificationStringContent.replace("$name$", geoName);
        notificationStringDeeplink = notificationStringDeeplink.replace("$name$", Underscored(geoName));
        notificationStringDeeplink = notificationStringDeeplink.replace("$id$", geoId);
        Calendar c = new GregorianCalendar();
        c.set(Calendar.HOUR_OF_DAY, 6);
        c.set(Calendar.MINUTE, 0);
        c.set(Calendar.SECOND, 0);
        Date d1 = c.getTime();//from ww  w  .  j a v  a 2s .co m
        boolean isNotification = false;
        File dbFile = mContext.getDatabasePath("hiq_in_app.sqlite");
        SQLiteDatabase inAppDb = SQLiteDatabase.openDatabase(dbFile.getPath(), null,
                SQLiteDatabase.OPEN_READWRITE);
        if (geoName.equalsIgnoreCase("home")) {
            String lastTriggeredHome = HIQSharedPrefrence.getString("lastTriggeredHome", mContext);
            if (lastTriggeredHome == null) {
                sendNotification(notificationStringTitle, notificationStringContent, notificationStringDeeplink,
                        context, isBanner);
                HIQSharedPrefrence.putString("lastTriggeredHome", System.currentTimeMillis() + "", mContext);
                isNotification = true;
            } else {
                long previouslastTriggeredHome = Long
                        .parseLong(HIQSharedPrefrence.getString("lastTriggeredHome", mContext));
                if (previouslastTriggeredHome < d1.getTime()) {
                    sendNotification(notificationStringTitle, notificationStringContent,
                            notificationStringDeeplink, context, isBanner);
                    HIQSharedPrefrence.putString("lastTriggeredHome", System.currentTimeMillis() + "",
                            mContext);
                    isNotification = true;
                }
            }

        } else if (objType.equalsIgnoreCase("Hotel")) {
            String lastTriggeredHome = HIQSharedPrefrence.getString("lastTriggeredHotel", mContext);
            if (lastTriggeredHome == null) {
                sendNotification(notificationStringTitle, notificationStringContent, notificationStringDeeplink,
                        context, isBanner);
                HIQSharedPrefrence.putString("lastTriggeredHotel", System.currentTimeMillis() + "", mContext);
                isNotification = true;
            } else {
                long previouslastTriggeredHome = Long
                        .parseLong(HIQSharedPrefrence.getString("lastTriggeredHotel", mContext));
                if (previouslastTriggeredHome < d1.getTime()) {
                    sendNotification(notificationStringTitle, notificationStringContent,
                            notificationStringDeeplink, context, isBanner);
                    HIQSharedPrefrence.putString("lastTriggeredHotel", System.currentTimeMillis() + "",
                            mContext);
                    isNotification = true;
                }
            }
        } else if (objType.equalsIgnoreCase("SS")) {
            Cursor res = null;
            try {
                String lastNotificationTriggerd = null;
                res = inAppDb.rawQuery("Select * from " + GeoDbHelper.SIGHT_SEEING_TABLE_NAME + " where "
                        + GeoDbHelper.SIGHT_SEEING_COULUMN_ID + " = " + geoId, null);
                if (res != null) {
                    if (res.moveToFirst()) {
                        lastNotificationTriggerd = res.getString(res.getColumnIndex("lastTimeStamp"));
                    }
                }
                if (lastNotificationTriggerd == null) {
                    sendNotification(notificationStringTitle, notificationStringContent,
                            notificationStringDeeplink, context, isBanner);
                    isNotification = true;
                    Cursor cur = inAppDb.rawQuery("update " + GeoDbHelper.SIGHT_SEEING_TABLE_NAME
                            + " SET lastTimeStamp = '" + currentTime + "' where "
                            + GeoDbHelper.SIGHT_SEEING_COULUMN_ID + " = " + geoId, null);
                    cur.moveToFirst();
                    cur.close();
                } else {
                    long time = Long.parseLong(lastNotificationTriggerd);
                    if (time < d1.getTime()) {
                        sendNotification(notificationStringTitle, notificationStringContent,
                                notificationStringDeeplink, context, isBanner);
                        isNotification = true;
                        Cursor cur = inAppDb.rawQuery("update " + GeoDbHelper.SIGHT_SEEING_TABLE_NAME
                                + " SET lastTimeStamp = '" + currentTime + "' where "
                                + GeoDbHelper.SIGHT_SEEING_COULUMN_ID + " = " + geoId, null);
                        cur.moveToFirst();
                        cur.close();
                    }
                }

            } catch (Exception e) {
                e.printStackTrace();
                ExceptionUtils.logException(e);
            } finally {
                if (res != null && !res.isClosed()) {
                    res.close();
                }
                res = null;
                if (inAppDb != null && inAppDb.isOpen()) {
                    inAppDb.close();
                }
                inAppDb = null;
            }
        } else if (objType.equalsIgnoreCase("Destination")) {
            Cursor res = null;
            try {
                String lastNotificationTriggerd = null;
                res = inAppDb.rawQuery("Select * from " + GeoDbHelper.DESTINATION_TABLE_NAME + " where "
                        + GeoDbHelper.DESTINATION_COLUMN_ID + " = " + geoId, null);
                if (res != null) {
                    try {
                        if (res.moveToFirst()) {
                            lastNotificationTriggerd = res.getString(res.getColumnIndex("lastTimeStamp"));
                        }
                    } catch (Exception e) {

                    }
                }
                if (lastNotificationTriggerd == null) {
                    sendNotification(notificationStringTitle, notificationStringContent,
                            notificationStringDeeplink, context, isBanner);
                    isNotification = true;
                    Cursor cur = inAppDb.rawQuery("update " + GeoDbHelper.DESTINATION_TABLE_NAME
                            + " SET lastTimeStamp = '" + currentTime + "' where "
                            + GeoDbHelper.DESTINATION_COLUMN_ID + " = " + geoId, null);
                    cur.moveToFirst();
                    cur.close();
                } else {
                    long time = Long.parseLong(lastNotificationTriggerd);
                    if (time < d1.getTime()) {
                        sendNotification(notificationStringTitle, notificationStringContent,
                                notificationStringDeeplink, context, isBanner);
                        isNotification = true;
                        Cursor cur = inAppDb.rawQuery("update " + GeoDbHelper.DESTINATION_TABLE_NAME
                                + " SET lastTimeStamp = '" + currentTime + "' where "
                                + GeoDbHelper.DESTINATION_COLUMN_ID + " = " + geoId, null);
                        cur.moveToFirst();
                        cur.close();
                    }
                }

            } catch (Exception e) {
                ExceptionUtils.logException(e);
            } finally {
                if (res != null && !res.isClosed()) {
                    res.close();
                }
                res = null;
                if (inAppDb != null && inAppDb.isOpen()) {
                    inAppDb.close();
                }
                inAppDb = null;
            }
        }
        HashMap<String, Object> oMap = new HashMap<String, Object>();
        oMap.put("Type", eventType);
        oMap.put("Name", geoName);
        oMap.put("ID", geoId);
        oMap.put("Object Type", objType);
        oMap.put("Date", new Date());

        if (isNotification) {
        }
    }

    // HIQUtil.sendEventToGAFromObject("GeoFenceEvent", "GeoFenceEvent in Android", oMap);

}

From source file:com.digicorp.plugin.sqlitePlugin.SQLitePlugin.java

/**
 * Executes a batch request and sends the results via sendJavascriptCB().
 *
 * @param dbname/*from   w  w  w  . j a v a2 s  . com*/
 *            The name of the database.
 *
 * @param queryarr
 *            Array of query strings
 *
 * @param jsonparams
 *            Array of JSON query parameters
 *
 * @param queryIDs
 *            Array of query ids
 *
 * @param tx_id
 *            Transaction id
 *
 */
private void executeSqlBatch(String dbname, String[] queryarr, JSONArray[] jsonparams, String[] queryIDs,
        String tx_id) {
    SQLiteDatabase mydb = this.getDatabase(dbname);

    if (mydb == null)
        return;

    try {
        mydb.beginTransaction();

        String query = "";
        String query_id = "";
        int len = queryarr.length;

        for (int i = 0; i < len; i++) {
            query = queryarr[i];
            query_id = queryIDs[i];
            if (query.toLowerCase().startsWith("insert") && jsonparams != null) {
                SQLiteStatement myStatement = mydb.compileStatement(query);
                for (int j = 0; j < jsonparams[i].length(); j++) {
                    if (jsonparams[i].get(j) instanceof Float || jsonparams[i].get(j) instanceof Double) {
                        myStatement.bindDouble(j + 1, jsonparams[i].getDouble(j));
                    } else if (jsonparams[i].get(j) instanceof Number) {
                        myStatement.bindLong(j + 1, jsonparams[i].getLong(j));
                    } else if (jsonparams[i].isNull(j)) {
                        myStatement.bindNull(j + 1);
                    } else {
                        myStatement.bindString(j + 1, jsonparams[i].getString(j));
                    }
                }
                long insertId = myStatement.executeInsert();

                String result = "{'insertId':'" + insertId + "'}";
                this.sendJavascriptCB("window.SQLitePluginTransactionCB.queryCompleteCallback('" + tx_id + "','"
                        + query_id + "', " + result + ");");
            } else {
                String[] params = null;

                if (jsonparams != null) {
                    params = new String[jsonparams[i].length()];

                    for (int j = 0; j < jsonparams[i].length(); j++) {
                        if (jsonparams[i].isNull(j))
                            params[j] = "";
                        else
                            params[j] = jsonparams[i].getString(j);
                    }
                }

                Cursor myCursor = mydb.rawQuery(query, params);

                if (query_id.length() > 0)
                    this.processResults(myCursor, query_id, tx_id);

                myCursor.close();
            }
        }
        mydb.setTransactionSuccessful();
    } catch (SQLiteException ex) {
        ex.printStackTrace();
        Log.v("executeSqlBatch", "SQLitePlugin.executeSql(): Error=" + ex.getMessage());
        this.sendJavascriptCB("window.SQLitePluginTransactionCB.txErrorCallback('" + tx_id + "', '"
                + ex.getMessage() + "');");
    } catch (JSONException ex) {
        ex.printStackTrace();
        Log.v("executeSqlBatch", "SQLitePlugin.executeSql(): Error=" + ex.getMessage());
        this.sendJavascriptCB("window.SQLitePluginTransactionCB.txErrorCallback('" + tx_id + "', '"
                + ex.getMessage() + "');");
    } finally {
        mydb.endTransaction();
        Log.v("executeSqlBatch", tx_id);
        this.sendJavascriptCB("window.SQLitePluginTransactionCB.txCompleteCallback('" + tx_id + "');");
    }
}

From source file:com.openerp.orm.ORM.java

/**
 * Count.//from w  w w.  j a v a2s .  c o  m
 * 
 * Returns number of row in local database. Zero (0) if there are no any row
 * related to where clause.
 * 
 * @param dbHelper
 *            the database helper
 * @param where
 *            the where clause conditions
 * @param whereArgs
 *            the where args
 * @return the int (number of row in database)
 */
public int count(BaseDBHelper dbHelper, String[] where, String[] whereArgs) {
    SQLiteDatabase db = getWritableDatabase();
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT count(*) as total FROM ");
    sql.append(modelToTable(dbHelper.getModelName()));
    sql.append(" WHERE ");
    if (where != null && where.length > 0) {

        for (String whr : where) {
            if (whr.contains(".")) {
                String[] datas = whr.split("\\.");
                String table = datas[0];
                String rel_id = table + "_id";
                String fetch_id = modelToTable(dbHelper.getModelName()) + "_id";
                String rel_table = modelToTable(dbHelper.getModelName()) + "_" + table + "_rel";
                String subQue = "id in (SELECT " + fetch_id + " FROM " + rel_table + " WHERE " + rel_id
                        + " = ?) ";
                sql.append(subQue);
                sql.append(" ");

            } else {
                sql.append(whr);
                sql.append(" ");
            }

        }
        sql.append(" and oea_name = '" + user_name + "'");
    } else {
        sql.append(" oea_name = '" + user_name + "'");
    }
    Cursor cursor = db.rawQuery(sql.toString(), whereArgs);
    cursor.moveToFirst();
    int count = cursor.getInt(0);
    db.close();
    cursor.close();
    return count;

}

From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java

/**
 * Return the health of a data table. The health can be one of
 * <ul>/*from w  w w. j  a va  2 s  . c  om*/
 * <li>TABLE_HEALTH_IS_CLEAN = 0</li>
 * <li>TABLE_HEALTH_HAS_CONFLICTS = 1</li>
 * <li>TABLE_HEALTH_HAS_CHECKPOINTS = 2</li>
 * <li>TABLE_HEALTH_HAS_CHECKPOINTS_AND_CONFLICTS = 3</li>
 * <ul>
 * 
 * @param db
 * @param tableId
 * @return
 */
public int getTableHealth(SQLiteDatabase db, String tableId) {
    StringBuilder b = new StringBuilder();
    b.append("SELECT SUM(case when _savepoint_type is null then 1 else 0 end) as checkpoints,")
            .append("SUM(case when _conflict_type is not null then 1 else 0 end) as conflicts from \"")
            .append(tableId).append("\"");

    Cursor c = null;
    try {
        c = db.rawQuery(b.toString(), null);
        int idxCheckpoints = c.getColumnIndex("checkpoints");
        int idxConflicts = c.getColumnIndex("conflicts");
        c.moveToFirst();
        Integer checkpoints = ODKDatabaseUtils.get().getIndexAsType(c, Integer.class, idxCheckpoints);
        Integer conflicts = ODKDatabaseUtils.get().getIndexAsType(c, Integer.class, idxConflicts);
        c.close();

        int outcome = TABLE_HEALTH_IS_CLEAN;
        if (checkpoints != null && checkpoints != 0) {
            outcome += TABLE_HEALTH_HAS_CHECKPOINTS;
        }
        if (conflicts != null && conflicts != 0) {
            outcome += TABLE_HEALTH_HAS_CONFLICTS;
        }
        return outcome;
    } finally {
        if (c != null && !c.isClosed()) {
            c.close();
        }
    }
}

From source file:ru.orangesoftware.financisto2.export.flowzr.FlowzrSyncEngine.java

private <T extends MyEntity> void pushUpdate(String tableName, Class<T> clazz)
        throws ClientProtocolException, IOException, JSONException, Exception {
    SQLiteDatabase db2 = dba.db();
    Cursor cursorCursor;/*from ww w  . jav a 2 s  .c  o  m*/
    String sql;
    long total;

    sql = "select count(*) from " + tableName + " where updated_on<0 or (updated_on > "
            + FlowzrSyncOptions.last_sync_ts + " and updated_on<" + options.startTimestamp + ")";
    cursorCursor = db.rawQuery(sql, null);
    cursorCursor.moveToFirst();
    total = cursorCursor.getLong(0);
    sql = "select * from " + tableName + " where updated_on<0 or (updated_on > "
            + FlowzrSyncOptions.last_sync_ts + " and updated_on<" + options.startTimestamp + ")";

    if (tableName.equals(DatabaseHelper.TRANSACTION_TABLE)) {
        sql += " order by  parent_id asc,_id asc";
    } else if (tableName.equals(DatabaseHelper.BUDGET_TABLE)) {
        sql += " order by  parent_budget_id asc";
    } else if (!tableName.equals("currency_exchange_rate")) {
        sql += " order by  _id asc";
    }

    cursorCursor = db2.rawQuery(sql, null);
    JSONArray resultSet = new JSONArray();

    int i = 0;
    if (cursorCursor.moveToFirst() && isCanceled != true) {
        do {
            if (i % 10 == 0) {
                flowzrSyncActivity.notifyUser(
                        flowzrSyncActivity.getString(R.string.flowzr_sync_sending) + " " + tableName,
                        (int) (Math.round(i * 100 / total)));
            }
            resultSet.put(cursorToDict(tableName, cursorCursor));
            i++;
            if (i % MAX_PUSH_SIZE == 0) {
                String resp = makeRequest(tableName, resultSet.toString());
                resultSet = new JSONArray();
                if (resp.equals(FLOWZR_MSG_NET_ERROR)) {
                    isCanceled = true;
                }
                if (isCanceled) {
                    return;
                }
            }
        } while (cursorCursor.moveToNext());
    }
    cursorCursor.close();
    if (i % MAX_PUSH_SIZE != 0) {
        String resp = makeRequest(tableName, resultSet.toString());
        if (resp.equals(FLOWZR_MSG_NET_ERROR)) {
            isCanceled = true;
        }
        if (isCanceled) {
            return;
        }
    }
}

From source file:com.hybris.mobile.lib.commerce.provider.CatalogProvider.java

@Override
public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
    SQLiteDatabase sqLiteDatabase = mDatabaseHelper.getWritableDatabase();
    String tableName;/* w  w w  .j a  v  a2 s  .c  o  m*/
    String where;
    String order = "";
    Bundle bundleSyncAdapter = new Bundle();
    String lastPathSegment = uri.getLastPathSegment();

    if (StringUtils.isNotBlank(sortOrder)) {
        order = " ORDER BY " + sortOrder;
    }

    switch (URI_MATCHER.match(uri)) {

    // Getting the content for a group (list of simple data)
    case CatalogContract.Provider.CODE_GROUP_ID:
        tableName = CatalogContract.DataBaseDataSimple.TABLE_NAME;
        where = CatalogContract.DataBaseDataLinkGroup.TABLE_NAME + "."
                + CatalogContract.DataBaseDataLinkGroup.ATT_GROUP_ID + "='" + lastPathSegment + "'";

        // Limit for the query on the sync adapter
        String currentPage = uri.getQueryParameter(CatalogContract.Provider.QUERY_PARAM_CURRENT_PAGE);
        String pageSize = uri.getQueryParameter(CatalogContract.Provider.QUERY_PARAM_PAGE_SIZE);

        // Bundle information for the syncing part
        bundleSyncAdapter.putString(CatalogSyncConstants.SYNC_PARAM_GROUP_ID, lastPathSegment);

        if (StringUtils.isNotBlank(currentPage) && StringUtils.isNotBlank(pageSize)) {
            bundleSyncAdapter.putInt(CatalogSyncConstants.SYNC_PARAM_CURRENT_PAGE,
                    Integer.valueOf(currentPage));
            bundleSyncAdapter.putInt(CatalogSyncConstants.SYNC_PARAM_PAGE_SIZE, Integer.valueOf(pageSize));
        }

        break;

    // Getting a specific data detail
    case CatalogContract.Provider.CODE_DATA_ID:
    case CatalogContract.Provider.CODE_DATA_DETAILS_ID:
        tableName = CatalogContract.DataBaseDataDetails.TABLE_NAME;
        where = CatalogContract.DataBaseDataDetails.TABLE_NAME + "."
                + CatalogContract.DataBaseDataDetails.ATT_DATA_ID + "='" + lastPathSegment + "'";

        // Bundle information for the syncing part
        bundleSyncAdapter.putString(CatalogSyncConstants.SYNC_PARAM_DATA_ID, lastPathSegment);

        // We don't load the variants for a specific data
        bundleSyncAdapter.putBoolean(CatalogSyncConstants.SYNC_PARAM_LOAD_VARIANTS, false);
        break;

    default:
        Log.e(TAG, "URI not recognized" + uri.toString());
        throw new IllegalArgumentException("URI not recognized" + uri.toString());

    }

    // We do the query by joining the data to the group
    Cursor cursor = sqLiteDatabase.rawQuery("SELECT * FROM " + tableName + " INNER JOIN "
            + CatalogContract.DataBaseDataLinkGroup.TABLE_NAME + " ON " + tableName + "."
            + CatalogContract.DataBaseData.ATT_DATA_ID + "=" + CatalogContract.DataBaseDataLinkGroup.TABLE_NAME
            + "." + CatalogContract.DataBaseDataLinkGroup.ATT_DATA_ID + " WHERE " + where + order, null);

    // Register the cursor to watch the uri for changes
    cursor.setNotificationUri(getContext().getContentResolver(), uri);

    // Existing data
    if (cursor.getCount() > 0) {
        // TODO - For now we check if one the items is out-of-sync and we sync all of them if this is the case
        // Future - Check every out-of-date items and sync them
        cursor.moveToLast();
        int status = cursor.getInt(cursor.getColumnIndex(CatalogContract.DataBaseData.ATT_STATUS));
        cursor.moveToFirst();

        // Data expired, we request a sync
        if (status == CatalogContract.SyncStatus.OUTOFDATE.getValue()) {
            Log.i(TAG, "Data for " + uri.toString() + " is out-of-date, requesting a sync");
            requestSync(bundleSyncAdapter);

            // TODO - the uptodate/outofdate should be done in the sync adapter
            // We up-to-date all the data in case the sync does not return any results (we base our out of sync on the last item of the cursor)
            if (URI_MATCHER.match(uri) == CatalogContract.Provider.CODE_GROUP_ID) {
                updateInternalDataSyncStatus(cursor, tableName, SyncStatus.UPTODATE);
            }
        }
        // Data updated, we invalidate the data
        else {
            Log.i(TAG, "Data for " + uri.toString() + " is up-of-date, invalidating it");
            updateInternalDataSyncStatus(cursor, tableName, SyncStatus.OUTOFDATE);
        }

    }
    // No data found, we request a sync if it's not already up-to-date
    else {
        boolean triggerSyncAdapter;

        switch (URI_MATCHER.match(uri)) {

        // Saving the sync info for the group
        case CatalogContract.Provider.CODE_GROUP_ID:
            triggerSyncAdapter = updateTrackSyncStatus(CatalogContract.Provider.getUriSyncGroup(authority),
                    CatalogContract.DataBaseSyncStatusGroup.ATT_GROUP_ID,
                    CatalogContract.DataBaseSyncStatusGroup.TABLE_NAME, lastPathSegment);

            break;

        // Saving the sync info for the data
        case CatalogContract.Provider.CODE_DATA_ID:
            triggerSyncAdapter = updateTrackSyncStatus(CatalogContract.Provider.getUriData(authority),
                    CatalogContract.DataBaseData.ATT_DATA_ID, CatalogContract.DataBaseDataSimple.TABLE_NAME,
                    lastPathSegment);
            break;

        // Saving the sync info for the data details
        case CatalogContract.Provider.CODE_DATA_DETAILS_ID:
            triggerSyncAdapter = updateTrackSyncStatus(CatalogContract.Provider.getUriDataDetails(authority),
                    CatalogContract.DataBaseData.ATT_DATA_ID, CatalogContract.DataBaseDataDetails.TABLE_NAME,
                    lastPathSegment);
            break;

        default:
            Log.e(TAG, "URI not recognized" + uri.toString());
            throw new IllegalArgumentException("URI not recognized" + uri.toString());

        }

        // Trigger the sync adapter
        if (triggerSyncAdapter) {
            Log.i(TAG, "No data found for " + uri.toString() + " and data out-of-date, requesting a sync");
            requestSync(bundleSyncAdapter);
        } else {
            Log.i(TAG, "No data found for " + uri.toString() + " and data up-to-date");
        }

    }

    return cursor;
}

From source file:org.mariotaku.twidere.util.Utils.java

public static boolean isFiltered(final SQLiteDatabase database, final String text_plain, final String text_html,
        final String screen_name, final String source) {
    if (database == null)
        return false;
    final StringBuilder builder = new StringBuilder();
    final String[] selection_args = new String[] { text_plain, text_html, screen_name, source };
    builder.append("SELECT NULL WHERE");
    builder.append("(SELECT 1 IN (SELECT ? LIKE '%'||" + TABLE_FILTERED_KEYWORDS + "." + Filters.TEXT
            + "||'%' FROM " + TABLE_FILTERED_KEYWORDS + "))");
    builder.append(" OR ");
    builder.append("(SELECT 1 IN (SELECT ? LIKE '%<a href=\"%'||" + TABLE_FILTERED_LINKS + "." + Filters.TEXT
            + "||'%\">%' FROM " + TABLE_FILTERED_LINKS + "))");
    builder.append(" OR ");
    builder.append("(SELECT ? IN (SELECT " + Filters.TEXT + " FROM " + TABLE_FILTERED_USERS + "))");
    builder.append(" OR ");
    builder.append("(SELECT 1 IN (SELECT ? LIKE '%>'||" + TABLE_FILTERED_SOURCES + "." + Filters.TEXT
            + "||'</a>%' FROM " + TABLE_FILTERED_SOURCES + "))");
    final Cursor cur = database.rawQuery(builder.toString(), selection_args);
    if (cur == null)
        return false;
    try {//from w w w  .  j  a  v  a  2s.  c  o  m
        return cur.getCount() > 0;
    } finally {
        cur.close();
    }
}

From source file:org.path.common.android.utilities.ODKDatabaseUtils.java

/**
 * Get a {@link UserTable} for this table based on the given where clause. All
 * columns from the table are returned.//from   w  w w .ja v a 2s.  c o m
 * <p>
 * SELECT * FROM table WHERE whereClause GROUP BY groupBy[]s HAVING
 * havingClause ORDER BY orderbyElement orderByDirection
 * <p>
 * If any of the clause parts are omitted (null), then the appropriate
 * simplified SQL statement is constructed.
 * 
 * @param db
 * @param appName
 * @param tableId
 * @param columnDefns
 * @param whereClause
 *          the whereClause for the selection, beginning with "WHERE". Must
 *          include "?" instead of actual values, which are instead passed in
 *          the selectionArgs.
 * @param selectionArgs
 *          an array of string values for bind parameters
 * @param groupBy
 *          an array of elementKeys
 * @param having
 * @param orderByElementKey
 *          elementKey to order the results by
 * @param orderByDirection
 *          either "ASC" or "DESC"
 * @return
 */
public UserTable rawSqlQuery(SQLiteDatabase db, String appName, String tableId,
        ArrayList<ColumnDefinition> columnDefns, String whereClause, String[] selectionArgs, String[] groupBy,
        String having, String orderByElementKey, String orderByDirection) {
    Cursor c = null;
    try {
        StringBuilder s = new StringBuilder();
        s.append("SELECT * FROM \"").append(tableId).append("\" ");
        if (whereClause != null && whereClause.length() != 0) {
            s.append(" WHERE ").append(whereClause);
        }
        if (groupBy != null && groupBy.length != 0) {
            s.append(" GROUP BY ");
            boolean first = true;
            for (String elementKey : groupBy) {
                if (!first) {
                    s.append(", ");
                }
                first = false;
                s.append(elementKey);
            }
            if (having != null && having.length() != 0) {
                s.append(" HAVING ").append(having);
            }
        }
        if (orderByElementKey != null && orderByElementKey.length() != 0) {
            s.append(" ORDER BY ").append(orderByElementKey);
            if (orderByDirection != null && orderByDirection.length() != 0) {
                s.append(" ").append(orderByDirection);
            } else {
                s.append(" ASC");
            }
        }
        String sqlQuery = s.toString();
        c = db.rawQuery(sqlQuery, selectionArgs);
        UserTable table = new UserTable(c, appName, tableId, columnDefns, whereClause, selectionArgs, groupBy,
                having, orderByElementKey, orderByDirection);
        return table;
    } finally {
        if (c != null && !c.isClosed()) {
            c.close();
        }
    }
}

From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java

/**
 * Get a {@link UserTable} for this table based on the given where clause. All
 * columns from the table are returned.//  ww  w .j a va2s. c om
 * <p>
 * SELECT * FROM table WHERE whereClause GROUP BY groupBy[]s HAVING
 * havingClause ORDER BY orderbyElement orderByDirection
 * <p>
 * If any of the clause parts are omitted (null), then the appropriate
 * simplified SQL statement is constructed.
 * 
 * @param db
 * @param appName
 * @param tableId
 * @param columnDefns
 * @param whereClause
 *          the whereClause for the selection, beginning with "WHERE". Must
 *          include "?" instead of actual values, which are instead passed in
 *          the selectionArgs.
 * @param selectionArgs
 *          an array of string values for bind parameters
 * @param groupBy
 *          an array of elementKeys
 * @param having
 * @param orderByElementKey
 *          elementKey to order the results by
 * @param orderByDirection
 *          either "ASC" or "DESC"
 * @return
 */
public UserTable rawSqlQuery(SQLiteDatabase db, String appName, String tableId,
        ArrayList<ColumnDefinition> columnDefns, String whereClause, String[] selectionArgs, String[] groupBy,
        String having, String orderByElementKey, String orderByDirection) {
    Cursor c = null;
    try {
        StringBuilder s = new StringBuilder();
        s.append("SELECT * FROM \"").append(tableId).append("\" ");
        if (whereClause != null && whereClause.length() != 0) {
            s.append(" WHERE ").append(whereClause);
        }
        if (groupBy != null && groupBy.length != 0) {
            s.append(" GROUP BY ");
            boolean first = true;
            for (String elementKey : groupBy) {
                if (!first) {
                    s.append(", ");
                }
                first = false;
                s.append(elementKey);
            }
            if (having != null && having.length() != 0) {
                s.append(" HAVING ").append(having);
            }
        }
        if (orderByElementKey != null && orderByElementKey.length() != 0) {
            s.append(" ORDER BY ").append(orderByElementKey);
            if (orderByDirection != null && orderByDirection.length() != 0) {
                s.append(" ").append(orderByDirection);
            } else {
                s.append(" DESC");
            }
        }
        String sqlQuery = s.toString();
        c = db.rawQuery(sqlQuery, selectionArgs);
        UserTable table = new UserTable(c, appName, tableId, columnDefns, whereClause, selectionArgs, groupBy,
                having, orderByElementKey, orderByDirection);
        return table;
    } finally {
        if (c != null && !c.isClosed()) {
            c.close();
        }
    }
}

From source file:com.rener.sea.DBHelper.java

public List<Person> getAllPersons() {
    SQLiteDatabase db = getReadableDatabase();
    //        Cursor cursor = db.query(DBSchema.TABLE_PERSON, new String[]{DBSchema.PERSON_ID},
    //                null, null, null, null, DBSchema.PERSON_FIRST_NAME + " COLLATE NOCASE", null);
    Cursor cursor = db.rawQuery("SELECT " + DBSchema.PERSON_ID + ", " + DBSchema.PERSON_FIRST_NAME + ", "
            + DBSchema.PERSON_MIDDLE_INITIAL + ", " + DBSchema.PERSON_LAST_NAME1 + ", "
            + DBSchema.PERSON_LAST_NAME2 + " " + "FROM " + DBSchema.TABLE_PERSON + " WHERE " + DBSchema.STATUS
            + " !=? AND " + DBSchema.PERSON_ID + " NOT IN (SELECT " + DBSchema.USER_PERSON_ID + " FROM "
            + DBSchema.TABLE_USERS + ") " + "ORDER BY " + DBSchema.PERSON_FIRST_NAME + " COLLATE NOCASE",
            new String[] { String.valueOf(-1) });
    ArrayList<Person> persons;
    persons = new ArrayList<>();
    //        Log.i(this.toString(), "Cursor " + cursor);
    //        Log.i(this.toString(), "Cursor count " + cursor.getCount());
    if ((cursor != null) && (cursor.getCount() > 0)) {
        //            Log.i(this.toString(), "Inside if");
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
            persons.add(new Person(cursor.getLong(0), cursor.getString(1),
                    (cursor.isNull(2) ? "" : cursor.getString(2)), cursor.getString(3),
                    (cursor.isNull(4) ? "" : cursor.getString(4)), this));
            //                Log.i(this.toString(), "People created " + cursor.getLong(0));
        }//  ww w.ja  v  a2 s .co  m

        db.close();
        cursor.close();

    }
    //        Log.i(this.toString(), "persons not found");
    return persons;

}