Sqlitedatabase.Query Method

SQLiteDatabase.query method

tableColumns

• null for all columns as in SELECT * FROM ...
• new String[] { "column1", "column2", ... } for specific columns as in SELECT column1, column2 FROM ... - you can also put complex expressions here:
  
  new String[] { "(SELECT max(column1) FROM table1) AS max" } would give you a column named max holding the max value of column1

whereClause

• the part you put after WHERE without that keyword, e.g. "column1 > 5"
• should include ? for things that are dynamic, e.g. "column1=?" -> see whereArgs

whereArgs

• specify the content that fills each ? in whereClause in the order they appear

the others

• just like whereClause the statement after the keyword or null if you don't use it.

Example

String[] tableColumns = new String[] {
    "column1",
    "(SELECT max(column1) FROM table2) AS max"
};
String whereClause = "column1 = ? OR column1 = ?";
String[] whereArgs = new String[] {
    "value1",
    "value2"
};
String orderBy = "column1";
Cursor c = sqLiteDatabase.query("table1", tableColumns, whereClause, whereArgs,
        null, null, orderBy);

// since we have a named column we can do
int idx = c.getColumnIndex("max");

is equivalent to the following raw query

String queryString =
    "SELECT column1, (SELECT max(column1) FROM table1) AS max FROM table1 " +
    "WHERE column1 = ? OR column1 = ? ORDER BY column1";
sqLiteDatabase.rawQuery(queryString, whereArgs);

---

By using the Where/Bind -Args version you get automatically escaped values and you don't have to worry if input-data contains '.

Unsafe: String whereClause = "column1='" + value + "'";

Safe: String whereClause = "column1=?";

because if value contains a ' your statement either breaks and you get exceptions or does unintended things, for example value = "XYZ'; DROP TABLE table1;--" might even drop your table since the statement would become two statements and a comment:

SELECT * FROM table1 where column1='XYZ'; DROP TABLE table1;--'

using the args version XYZ'; DROP TABLE table1;-- would be escaped to 'XYZ''; DROP TABLE table1;--' and would only be treated as a value. Even if the ' is not intended to do bad things it is still quite common that people have it in their names or use it in texts, filenames, passwords etc. So always use the args version. (It is okay to build int and other primitives directly into whereClause though)

SQLiteDatabase query method

If you want the record corresponding to a particular String as its title, (say, title="lunch"), you do the query as follows:

cursor=db.query(TABLE_RECIPES_NAME, TABLE_RECIPE_COLUMNS,
                 "title=?", new String[]{"lunch"}, null, null, null);

OR

cursor=db.query(TABLE_RECIPES_NAME, TABLE_RECIPE_COLUMNS,
                     RECIPE_COLUMN_TITLE+"=?", new String[]{"lunch"}, null, null, null);

This will query the table TABLE_RECIPES_NAME and return all columns specified by TABLE_RECIPE_COLUMNS , corresponding to the record where "title" is "lunch".

How can I use this query with the query method (SQLiteDatabase.query)?

Your issue is that the query method expects a table as it then generates SQL as per

SELECT your_columns FROM the_table; 

As there is no table it issues the Invalid Table exception.

You have to provide something that will satisfy the FROM clause, it cannot be nothing.

You can get around this in a few ways, which I guess is what the homework is trying to get you to ascertain/explore.

Fix 1

You could supply one of the tables that exist e.g. use

csr = db.query(null,new String[]{sq_child_mismatches,sq_friend_mismatches},null,null,null,null,null,"1");

• Note the 8th parameter, this LIMITs the number of rows generated to one as there would be a row generated for each row in the fake table.

Fix 2

or as FROM can be a subquery (see diagram) you could use a subquery e.g. one that you have

So you could use :-

    csr = db.query(
            sq_child_mismatches, //<<<<<<<<<< the fake subquery
            new String[]{
                    sq_child_mismatches,
                    sq_friend_mismatches
            },
            null    ,null,null,null,null
    );

• In this case, as the fake subquery returns a single row/result, there is no need for the LIMIT clause.

sqlLiteDatabase.query() for INNER JOIN

You can put the join in the table variable.

String table = "tblLocal local " +
    "inner join tblPlaces place " +
    "on place._id = local._id";

See the IOSched app from Google for an example. Take a look at the provider package and the SelectionBuilder class.

A SQLiteQueryBuilder is used to construct the query string, and all it does is concatenate the table variable to the rest of the query. See https://android.googlesource.com/platform/frameworks/base/+/refs/heads/master/core/java/android/database/sqlite/SQLiteQueryBuilder.java#201

Android SQLite Database : How to query for a specific data?

I assume DATABASE_TABLE is supposed to be a variable:

String query = "select * from " + DATABASE_TABLE + " where title=\""+ arg +"\"";

---

Also consider using parameterized queries to protect yourself from SQL Injection Attacks.

String query = "select * from " + DATABASE_TABLE + " where title=?";
Cursor c = ourDatabase.rawQuery(query, new String[] {arg});

Or use the built-in methods like SQLiteDatabase.query()

---

Addition

Revised code, still errors.

You should always post the LogCat errors along with the relevant code when your app crashes, but I can see an error right away. Cursors can hold more than record or be empty, you must tell the Cursor which row you want to read from and check if this row exists. To do this, simply add:

if (c.moveToFirst()) { /* return true if row exist, false if it doesn't */
    resulttwo = resulttwo + c.getString(iName);
}

If you want to read more than one row, use a loop:

while (c.moveToFirst()) { 
    resulttwo = resulttwo + c.getString(iName);
}

Also consider using a StringBuilder directly or just the += operator.

Usage of SQLiteDatabase.query()

I have found answer to my question. modified stack_veds code as below.

String clause = ""; 

for (int i = 0; i < checkedItems.size(); i++) {

           String sales_id = checkedItems.get(i); 
            if(i == 0)
            clause += "SALES_ID = '" + sales_id "'";
            else
            clause += " OR SALES_ID = '" + sales_id "'";
    }

Replace || with OR

AND

Cursor c = database.query("TABLE_NAME", COLUMN_NAMES, clause, null,
                null, null, null);

How to run query in SQLite database in android?

final Cursor cursor = db.rawQuery("SELECT SUM(odometer) as odometer FROM tripmileagetable where date like '2012-07%';", null);
int sum = 0;
if (cursor != null) {
    try {
        if (cursor.moveToFirst()) {
            sum = cursor.getInt(0);
        }
    } finally {
        cursor.close();
    }
}

Multiple OrderBy in SQLiteDatabase.Query Method

From SQLite docs:

If a SELECT statement that returns more than one row does not have an
ORDER BY clause, the order in which the rows are returned is
undefined. Or, if a SELECT statement does have an ORDER BY clause,
then the list of expressions attached to the ORDER BY determine the
order in which rows are returned to the user. Rows are first sorted
based on the results of evaluating the left-most expression in the
ORDER BY list, then ties are broken by evaluating the second left-most
expression and so on. The order in which two rows for which all ORDER
BY expressions evaluate to equal values are returned is undefined.
Each ORDER BY expression may be optionally followed by one of the
keywords ASC (smaller values are returned first) or DESC (larger
values are returned first). If neither ASC or DESC are specified, rows
are sorted in ascending (smaller values first) order by default.

Answer 1: Result set will be sorted in ascending order.

Answer 2:

String orderBy = FLD_CEEID + " ASC, " + FLD_CEMID + " ASC";
db.query(false, CEXP_TABLE, new String[] {KEY_CEID, FLD_CETID, FLD_CEEID, FLD_CEMID,
        FLD_CEAMT, FLD_CESEL}, FLD_CETID + " = " + TID, null,
        null, null, orderBy, null);

---

Related Topics