SQLiteDatabase.query method

Where clause and args work together to form the WHERE statement of the SQL query. So say you looking to express

WHERE Column1 = 'value1' AND Column2 = 'value2'

I am using the query method of SQLiteDatabase. How do I use the query method?

Then your whereClause and whereArgs will be as follows

String whereClause = "Column1 =? AND Column2 =?";
String[] whereArgs = new String[]{"value1", "value2"};

I tried this:

Cursor cursor = sqLiteDatabase.query(
tableName, tableColumns, whereClause, whereArgs, groupBy, having, orderBy);

If you want to select all table columns, i believe a null string passed to tableColumns will suffice.

tableColumns - columns parameter is constructed as follows.

String[] columns = new String[]{KEY_ID, KEY_CONTENT};

If we need to get all the fields, how should the column parameter to be constructed. Do we need to include all the Field Names in String array?

How do I properly use the query method?

tableColumns

and here is a good tutorial for database.

• null for all columns as in SELECT * FROM ...
ynamic, e.g. "column1=?" -> see whereArgs

whereClause

the others

• the part you put after WHERE without that keyword, e.g. "column1 > 5"
• just like whereClause the statement after the keyword or null if you don't use it.
• should include ? for things that are dynamic, e.g. "column1=?" -> see whereArgs

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");

whereArgs

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);

---

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

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;--'

the others

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)

whereArgs

This is a more general answer meant to be a quick reference for future viewers.

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

Example

SQLiteDatabase db = helper.getReadableDatabase();


String table = "table2";
String[] columns = {"column1", "column3"};
String selection = "column3 =?";
String[] selectionArgs = {"apple"};
String groupBy = null;
String having = null;
String orderBy = "column3 DESC";
String limit = "10";


Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

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");

Explanation from the documentation

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);

---

• table String: The table name to compile the query against.

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

• columns String: A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data

  Unsafe: String whereClause = "column1='" + value + "'";
  from storage that isn't going to be used.

Safe: String whereClause = "column1=?";
• selection String: A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing

  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;--'
  

  null will return all rows for the given table.

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)

I had a problem with mime types and where making tests for few file types. It looks like each browser sends it's variation of a mime type for a specific file. I was trying to upload mp3 and zip files with open source php class, that what I have found:

• Firefox (mp3): audio/mpeg
used.
• Firefox (zip): application/zip
• Chrome (mp3): audio/mp3
• orderBy String: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
• Chrome (zip): application/octet-stream
• limit String: Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

if your SQL query is like this

SELECT col-1, col-2 FROM tableName WHERE col-1=apple,col-2=mango
GROUPBY col-3 HAVING Count(col-4) > 5  ORDERBY col-2 DESC LIMIT 15;

Then for query() method, we can do as:-

String table = "tableName";
String[] columns = {"col-1", "col-2"};
String selection = "col-1 =? AND col-2=?";
String[] selectionArgs = {"apple","mango"};
String groupBy =col-3;
String having =" COUNT(col-4) > 5";
String orderBy = "col-2 DESC";
String limit = "15";


query(tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);