SQLiteDatabase.query method

Question

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

I tried this:

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

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?

Tufa

Try a simple approach like this – Archduchy

I know that method. But I am trying to learn how to implement query method instead of rawQuery. – Tufa

Answer 1

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)

Sension

Where does Limit/offset fall into in this... group by? having? orderby? – Filly

@Filly there are several versions that have a limit parameter, e.g. developer.android.com/reference/android/database/sqlite/… it's all just simple text concatenation in the end so you can put e.g. "some_column LIMIT 10" into orderBy and it would still work – Sension

Any option here to join 2 tables? – Sketchbook

@VijayKumbhoje you should be able put e.g. table1 CROSS JOIN table2 as the tablename. But there is a point where I would look at rawquery: https://mcmap.net/q/175664/-rawquery-query-selectionargs/995891 – Sension

but I am not using rawquery, What should I do in this case – Sketchbook

@VijayKumbhoje whatever you feel more comfortable with / looks cleaner in the end. The query methods are merely adding a few keywords like SELECT and FROM to the arguments (see source) then do a rawQuery with the resulting query string. If your query does not fit well into the available arguments of query, just write the query string yourself. – Sension

Answer 2

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

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

Explanation from the documentation

• table String: The table name to compile the query against.
• columns String: A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
• selection String: A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
• selectionArgs String: You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
• groupBy String: A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
• having String: A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
• 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.
• limit String: Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

Adhern

Answer 3

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'

Then your whereClause and whereArgs will be as follows

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

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

Pennell

don't enclose ? in ', the ' is added automatically if required – Sension

Answer 4

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

Campanulate

Answer 5

db.query(
        TABLE_NAME,
        new String[] { TABLE_ROW_ID, TABLE_ROW_ONE, TABLE_ROW_TWO },
        TABLE_ROW_ID + "=" + rowID,
        null, null, null, null, null
);

TABLE_ROW_ID + "=" + rowID, here = is the where clause. To select all values you will have to give all column names:

or you can use a raw query like this 
db.rawQuery("SELECT * FROM permissions_table WHERE name = 'Comics' ", null);

and here is a good tutorial for database.

Temporize