Android – SQLiteDatabase.query

query method gives you access to the data of a SQLiteDatabase. You specify what you want to return from a table, it is like SQL select, and it returns back a Cursor. You can specify table name, columns, selection, groupBy, having, orderBy and limit. The syntax is:

query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, 
        String having, String orderBy,String limit)
tableString: The table name to compile the query against.
columnsString: 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.
selectionString: 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.
selectionArgsString: 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.
groupByString: 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.
havingString: 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.
orderByString: 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.
limitString: Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.

Cursor provides access to the query results and allows us to go through the results row by row. The cursor maintains a position and we should position it to a specific row, also in the beginning using moveToNext. Some other methods are: moveToPrevious, moveToFirst, moveToLast and moveToPosition.

I use SQLiteHelper class, read this post, in the following examples. Query needs at least the two following arguments, table and columns.

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

To access the result from the cursor we need to use a while loop with cursor.moveToNext as the condition, and then get each column with cursor’s get methods, getString, getInt etc. Cursor’s get method needs the column index, starting from zero. The column position corresponds to the query, for example _ID is 0, COL_TITLE is 1 etc.

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

Now with the column’s data you can do whatever you need to do. I will create a new object and then added to a list and then return it for further use.

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

order query

If you want to order the result:

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

For multiple column order and reverse order for each column:

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

Filter query

To filter a query we need to use the 3rd and 4th argument, selection and selectionArgs arguments. Selection argument is a string which needs a column name, an operator and a ? as position holder.

String selection = GameEntry.COL_TITLE + " = ? ";

For the above example you need to create a string array with the value:

String[] selectionArgs = {"Some Title"};

You can have more than one condition using AND and OR:

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

Common operators:

Query SQLite Database, <span>Android – SQLiteDatabase.query</span>, KandZ

LIKE allows us to do a string pattern match.

  • mytext% – start with mytext and the rest we do not care
  • %mytext – ends with mytext
  • %mytext% – mytext in the middle.

You can read more here.

98440cookie-checkAndroid – SQLiteDatabase.queryAndroid – SQLiteDatabase.query

Related posts

One Thought to “Android – SQLiteDatabase.query

  1. […] Android – SQLiteDatabase.query […]

Comments are closed.