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:com.odoo.orm.OModel.java

public List<ODataRow> query(String sql, String[] args, Boolean closeConnection) {
    List<ODataRow> records = new ArrayList<ODataRow>();
    SQLiteDatabase db = getReadableDatabase();
    Cursor cr = db.rawQuery(sql, args);
    if (cr.moveToFirst()) {
        do {// w  ww .  j  a  v  a  2  s  .  c o  m
            ODataRow row = createRowFromCursor(cr);
            if (mWithFunctionalColumns) {
                for (OColumn col : mFunctionalColumns) {
                    if (!col.canFunctionalStore()) {
                        row.put(col.getName(), getFunctionalMethodValue(col, row));
                    }
                }
            }
            records.add(row);
        } while (cr.moveToNext());
    }
    cr.close();
    if (closeConnection)
        db.close();
    return records;
}

From source file:com.cryart.sabbathschool.util.SSCore.java

public boolean downloadIfNeeded() {
    if (quarterlyForLanguageExists()) {
        return true;
    }// w w w  .j a  v a 2  s  .c o  m

    InputStream is;
    String json;
    Cursor c;

    SQLiteDatabase db = this.getReadableDatabase();

    try {
        DefaultHttpClient httpClient = new DefaultHttpClient();
        HttpGet httpGet = new HttpGet("https://s3-us-west-2.amazonaws.com/com.cryart.sabbathschool/latest_"
                + LANGUAGE + ".json?" + String.valueOf(System.currentTimeMillis()));
        HttpResponse httpResponse = httpClient.execute(httpGet);
        HttpEntity httpEntity = httpResponse.getEntity();
        is = httpEntity.getContent();
    } catch (UnsupportedEncodingException e) {
        return false;
    } catch (ClientProtocolException e) {
        return false;
    } catch (IOException e) {
        return false;
    }

    try {
        BufferedReader reader = new BufferedReader(new InputStreamReader(is, "UTF-8"), 8);
        StringBuilder sb = new StringBuilder();
        String line = null;
        while ((line = reader.readLine()) != null) {
            sb.append(line + "n");
        }
        is.close();
        json = sb.toString();
    } catch (Exception e) {
        return false;
    }

    try {
        JSONObject ss_quarterly = new JSONObject(json);
        String ss_quarter_id = ss_quarterly.getString("quarter_id");
        String ss_quarter_name = ss_quarterly.getString("quarter_name");
        String ss_quarter_image = "";
        String ss_quarter_lang = ss_quarterly.getString("quarter_lang");
        JSONArray ss_lessons = ss_quarterly.getJSONArray("quarter_lessons");

        if (ss_quarterly.has("quarter_image")) {
            ss_quarter_image = ss_quarterly.getString("quarter_image");
        }

        c = db.rawQuery("SELECT COUNT(1) FROM ss_quarters " + "WHERE quarter_id = ? AND quarter_lang = ?",
                new String[] { ss_quarter_id, LANGUAGE });
        c.moveToFirst();
        int quarter_count = c.getInt(0);
        c.close();
        if (quarter_count > 0) {
            return true;
        }

        db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("quarter_id", ss_quarter_id);
        values.put("quarter_name", ss_quarter_name);
        values.put("quarter_image", ss_quarter_image);
        values.put("quarter_lang", ss_quarter_lang);

        long ss_quarter_serial = db.insert("ss_quarters", null, values);

        for (int i = 0; i < ss_lessons.length(); i++) {
            JSONObject ss_lesson = ss_lessons.getJSONObject(i);
            String ss_lesson_name = ss_lesson.getString("lesson_name");
            String ss_lesson_image = ss_lesson.getString("lesson_image");
            String ss_lesson_date_text = ss_lesson.getString("lesson_date_text");
            JSONArray ss_days = ss_lesson.getJSONArray("lesson_days");

            values = new ContentValues();
            values.put("lesson_name", ss_lesson_name);
            values.put("lesson_image", ss_lesson_image);
            values.put("lesson_date_text", ss_lesson_date_text);
            values.put("lesson_quarter_serial", ss_quarter_serial);

            long ss_lesson_serial = db.insert("ss_lessons", null, values);

            for (int j = 0; j < ss_days.length(); j++) {
                JSONObject ss_day = ss_days.getJSONObject(j);
                String ss_day_date = ss_day.getString("day_date");
                String ss_day_name = ss_day.getString("day_name");
                String ss_day_text = ss_day.getString("day_text");
                String ss_day_comments = "";
                String ss_day_highlights = "";
                String ss_day_date_text = ss_day.getString("day_date_text");
                String ss_day_verses = ss_day.getString("day_verses");

                values = new ContentValues();
                values.put("day_date", ss_day_date);
                values.put("day_name", ss_day_name);
                values.put("day_text", ss_day_text);
                values.put("day_comments", ss_day_comments);
                values.put("day_highlights", ss_day_highlights);
                values.put("day_date_text", ss_day_date_text);
                values.put("day_verses", ss_day_verses);
                values.put("day_lesson_serial", ss_lesson_serial);

                db.insert("ss_days", null, values);
            }
        }

        return true;
    } catch (JSONException e) {
        return false;
    }
}

From source file:org.ttrssreader.controllers.DBHelper.java

Set<Label> getLabelsForArticle(int articleId) {
    if (!isDBAvailable())
        return new HashSet<>();

    // @formatter:off
    String sql = "SELECT f._id, f.title, 0 checked FROM " + TABLE_FEEDS + " f " + "     WHERE f._id <= -11 AND"
            + "     NOT EXISTS (SELECT * FROM " + TABLE_ARTICLES2LABELS
            + " a2l where f._id = a2l.labelId AND a2l.articleId = " + articleId + ")" + " UNION"
            + " SELECT f._id, f.title, 1 checked FROM " + TABLE_FEEDS + " f, " + TABLE_ARTICLES2LABELS + " a2l "
            + "     WHERE f._id <= -11 AND f._id = a2l.labelId AND a2l.articleId = " + articleId;
    // @formatter:on

    SQLiteDatabase db = getOpenHelper().getReadableDatabase();
    readLock(true);/*from   w  w  w  .  j  a v a 2 s .  c  o  m*/
    Cursor c = null;
    try {
        c = db.rawQuery(sql, null);
        Set<Label> ret = new HashSet<>(c.getCount());
        while (c.moveToNext()) {
            Label label = new Label();
            label.id = c.getInt(0);
            label.caption = c.getString(1);
            label.checked = c.getInt(2) == 1;
            ret.add(label);
        }
        return ret;

    } finally {
        if (c != null && !c.isClosed())
            c.close();
        readLock(false);
    }
}

From source file:org.ttrssreader.controllers.DBHelper.java

/**
 * get remote files for given article//from  w w  w.  ja  va2 s  .  c om
 *
 * @param articleId article, which remote files should be found
 * @return collection of remote file objects from DB or {@code null}
 */
public Collection<RemoteFile> getRemoteFiles(int articleId) {
    if (!isDBAvailable())
        return null;

    ArrayList<RemoteFile> rfs = null;
    SQLiteDatabase db = getOpenHelper().getReadableDatabase();
    readLock(true);
    Cursor c = null;
    try {
        // @formatter:off
        c = db.rawQuery(" SELECT r.*" + " FROM " + TABLE_REMOTEFILES + " r," + TABLE_REMOTEFILE2ARTICLE + " m, "
                + TABLE_ARTICLES + " a" + " WHERE m.remotefileId=r.id" + "   AND m.articleId=a._id"
                + "   AND a._id=?", new String[] { String.valueOf(articleId) });
        // @formatter:on

        rfs = new ArrayList<>(c.getCount());

        while (c.moveToNext()) {
            rfs.add(handleRemoteFileCursor(c));
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (c != null && !c.isClosed())
            c.close();
        readLock(false);
    }
    return rfs;
}

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

/**
 * Return all the columns in the given table, including any metadata columns.
 * This does a direct query against the database and is suitable for accessing
 * non-managed tables. It does not access any metadata and therefore will not
 * report non-unit-of-retention (grouping) columns.
 * /*from w w w .  j  a  va 2 s.  c  om*/
 * @param db
 * @param tableId
 * @return
 */
public String[] getAllColumnNames(SQLiteDatabase db, String tableId) {
    Cursor cursor = db.rawQuery("SELECT * FROM " + tableId + " LIMIT 1", null);
    String[] colNames = cursor.getColumnNames();

    return colNames;
}

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

/**
 * Perform a raw query with bind parameters.
 * //from  ww  w. ja  v a  2 s . c om
 * @param db
 * @param sql
 * @param selectionArgs
 * @return
 */
public Cursor rawQuery(SQLiteDatabase db, String sql, String[] selectionArgs) {
    Cursor c = db.rawQuery(sql, selectionArgs);
    return c;
}

From source file:org.ttrssreader.controllers.DBHelper.java

/**
 * get remote files for given articles/* w  w w  .j a  v  a 2 s  .c  o m*/
 *
 * @param whereClause the WHERE clause to apply when selecting.
 * @param whereArgs   You may include ?s in the where clause, which
 *                    will be replaced by the values from whereArgs. The values
 *                    will be bound as Strings.
 * @param uniqOnly    if set to {@code true}, then only remote files, which are referenced by given articles only
 *                    will be
 *                    returned, otherwise all remote files referenced by given articles will be found (even those,
 *                    which are
 *                    referenced also by some other articles)
 * @return collection of remote file objects from DB or {@code null}
 */
private Collection<RemoteFile> getRemoteFilesForArticles(String whereClause, String[] whereArgs,
        boolean uniqOnly) {
    if (!isDBAvailable())
        return null;

    ArrayList<RemoteFile> rfs = null;
    StringBuilder uniqRestriction = new StringBuilder();
    String[] queryArgs = whereArgs;

    if (uniqOnly) {
        // @formatter:off
        uniqRestriction.append(" AND m.remotefileId NOT IN (").append("   SELECT remotefileId")
                .append("     FROM ").append(TABLE_REMOTEFILE2ARTICLE)
                .append("           WHERE remotefileId IN (").append("       SELECT remotefileId")
                .append("         FROM ").append(TABLE_REMOTEFILE2ARTICLE)
                .append("         WHERE articleId IN (").append("           SELECT _id")
                .append("             FROM ").append(TABLE_ARTICLES).append("             WHERE ")
                .append(whereClause).append("           )").append("         GROUP BY remotefileId)")
                .append("       AND articleId NOT IN (").append("         SELECT _id")
                .append("           FROM ").append(TABLE_ARTICLES).append("           WHERE ")
                .append(whereClause).append("       )").append("   GROUP by remotefileId)");
        // @formatter:on

        // because we are using whereClause twice in uniqRestriction, then we should also extend queryArgs,
        // which will be used in query
        if (whereArgs != null) {
            int initialLength = whereArgs.length;
            queryArgs = new String[initialLength * 3];
            for (int i = 0; i < 3; i++) {
                System.arraycopy(whereArgs, 0, queryArgs, i * initialLength, initialLength);
            }
        }
    }

    StringBuilder query = new StringBuilder();
    // @formatter:off
    query.append(" SELECT r.*").append("   FROM ").append(TABLE_REMOTEFILES + " r,")
            .append(TABLE_REMOTEFILE2ARTICLE + " m, ").append(TABLE_ARTICLES + " a")
            .append("   WHERE m.remotefileId=r.id").append("     AND m.articleId=a._id")
            .append("     AND a._id IN (").append("       SELECT _id FROM ").append(TABLE_ARTICLES)
            .append("       WHERE ").append(whereClause).append("     )").append(uniqRestriction)
            .append("   GROUP BY r.id");
    // @formatter:on

    SQLiteDatabase db = getOpenHelper().getReadableDatabase();
    readLock(true);
    Cursor c = null;
    try {
        long time = System.currentTimeMillis();
        c = db.rawQuery(query.toString(), queryArgs);

        rfs = new ArrayList<>();

        while (c.moveToNext()) {
            rfs.add(handleRemoteFileCursor(c));
        }
        Log.d(TAG, "Query in getRemoteFilesForArticles took " + (System.currentTimeMillis() - time)
                + "ms... (remotefiles: " + rfs.size() + ")");

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (c != null && !c.isClosed())
            c.close();
        readLock(false);
    }
    return rfs;
}

From source file:com.flowzr.budget.holo.export.flowzr.FlowzrSyncEngine.java

public static <T extends Object> void pushUpdate(String tableName, Class<T> clazz)
        throws ClientProtocolException, IOException, JSONException, Exception {
    SQLiteDatabase db2 = dba.db();
    Cursor cursorCursor;//  w  w  w.  j  ava 2  s .  co m
    String sql;
    long total;

    sql = "select count(*) from " + tableName + " where updated_on<=0 or remote_key is null or (updated_on > "
            + last_sync_ts;
    if (!tableName.equals(DatabaseHelper.BUDGET_TABLE)) {
        sql = sql + " and updated_on<" + startTimestamp + ")";
    } else {
        sql = sql + ")";
    }

    cursorCursor = db.rawQuery(sql, null);
    cursorCursor.moveToFirst();
    total = cursorCursor.getLong(0);

    sql = "select * from " + tableName + " where updated_on<=0 or remote_key is null or (updated_on > "
            + last_sync_ts;
    if (!tableName.equals(DatabaseHelper.BUDGET_TABLE)) {
        sql = sql + " and updated_on<" + startTimestamp + ")";
    } else {
        sql = sql + ")";
    }

    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) {
        Log.i("flowzr", "pushing " + tableName);
        do {
            if (i % 10 == 0) {
                //notifyUser(context.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;
            Log.e("flowzr", resp);
        }
        if (isCanceled) {
            Log.i("flowzr", "sync canceled!");
            return;
        }
    }
}

From source file:net.survivalpad.android.entity.Article.java

/**
 * ./*  ww w. ja v a 2s .c  o  m*/
 *
 * @param db
 * @param disasterTypeList ??DisasterType?
 * @param keyword          ?????null
 * @param out              ???
 */
public void find(SQLiteDatabase db, List<DisasterType> disasterTypeList, String keyword, List<Article> out) {

    // ???????0???SQL
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT").append(" _id, parent_id, language, title, author_id, abstraction,")
            .append(" image_filename, like_count, source, source_url, created, updated")
            .append(" FROM articles").append(" WHERE").append(" 1 = 0");

    // DisasterType??
    if (disasterTypeList.size() > 0) {
        boolean firstFlg = true;
        StringBuffer articleDisasterTypesWhereClause = new StringBuffer();
        for (DisasterType type : disasterTypeList) {
            if (!firstFlg) {
                articleDisasterTypesWhereClause.append(" OR ");
            }
            articleDisasterTypesWhereClause.append("disastertype_id = ").append(type.getId());
            firstFlg = false;
        }

        String subQuery = " SELECT article_id FROM articles_disastertypes WHERE "
                + articleDisasterTypesWhereClause.toString();

        sql.append(" OR _id IN (").append(subQuery).append(")");
    }

    // ??
    if (keyword != null) {
        sql.append(" AND (").append(" title LIKE '%" + keyword + "%'")
                .append(" OR abstraction LIKE '%" + keyword + "%'");

        String subQuery = " SELECT article_id FROM columns WHERE" + " description LIKE '%" + keyword + "%'";

        sql.append(" OR _id IN (").append(subQuery).append(")").append(" )");
    }

    if (BuildConfig.DEBUG) {
        Log.d(TAG, sql.toString());
    }

    Cursor cursor = db.rawQuery(sql.toString(), new String[] {});
    while (cursor.moveToNext()) {
        Article article = new Article();
        article.read(cursor);
        out.add(article);
    }
}

From source file:com.zetaDevelopment.phonegap.plugin.sqlitePlugin.SQLitePlugin.java

/**
 * Executes a batch request and sends the results via sendJavascriptCB().
 *
 * @param dbname//from   w ww.  ja v a2s  . co m
 *            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(Locale.getDefault()).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 {
                        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 + "');");
    }
}