Added in API level 1

SQLiteQueryBuilder


open class SQLiteQueryBuilder
kotlin.Any
   ↳ android.database.sqlite.SQLiteQueryBuilder

This is a convenience class that helps build SQL queries to be sent to SQLiteDatabase objects.

This class is often used to compose a SQL query from client-supplied fragments. Best practice to protect against invalid or illegal SQL is to set the following:

Summary

Public constructors

Public methods
open static Unit

Add the names that are non-null in columns to s, separating them with commas.

open Unit

Append a chunk to the WHERE clause of the query.

open Unit

Append a chunk to the WHERE clause of the query.

open Unit

Add a standalone chunk to the WHERE clause of this query.

open String!
buildQuery(projectionIn: Array<String!>!, selection: String!, groupBy: String!, having: String!, sortOrder: String!, limit: String!)

Construct a SELECT statement suitable for use in a group of SELECT statements that will be joined through UNION operators in buildUnionQuery.

open String!
buildQuery(projectionIn: Array<String!>!, selection: String!, selectionArgs: Array<String!>!, groupBy: String!, having: String!, sortOrder: String!, limit: String!)

open static String!
buildQueryString(distinct: Boolean, tables: String!, columns: Array<String!>!, where: String!, groupBy: String!, having: String!, orderBy: String!, limit: String!)

Build an SQL query string from the given clauses.

open String!
buildUnionQuery(subQueries: Array<String!>!, sortOrder: String!, limit: String!)

Given a set of subqueries, all of which are SELECT statements, construct a query that returns the union of what those subqueries return.

open String!
buildUnionSubQuery(typeDiscriminatorColumn: String!, unionColumns: Array<String!>!, columnsPresentInTable: MutableSet<String!>!, computedColumnsOffset: Int, typeDiscriminatorValue: String!, selection: String!, groupBy: String!, having: String!)

Construct a SELECT statement suitable for use in a group of SELECT statements that will be joined through UNION operators in buildUnionQuery.

open String!
buildUnionSubQuery(typeDiscriminatorColumn: String!, unionColumns: Array<String!>!, columnsPresentInTable: MutableSet<String!>!, computedColumnsOffset: Int, typeDiscriminatorValue: String!, selection: String!, selectionArgs: Array<String!>!, groupBy: String!, having: String!)

open Int
delete(db: SQLiteDatabase, selection: String?, selectionArgs: Array<String!>?)

Perform a delete by combining all current settings and the information passed into this method.

open SQLiteDatabase.CursorFactory?

Gets the cursor factory to be used for the query, as last configured by setCursorFactory(android.database.sqlite.SQLiteDatabase.CursorFactory).

open MutableCollection<Pattern!>?

Gets the projection greylist for the query, as last configured by setProjectionGreylist.

open MutableMap<String!, String!>?

Gets the projection map for the query, as last configured by setProjectionMap(java.util.Map).

open String?

Returns the list of tables being queried

open Long

Perform an insert by combining all current settings and the information passed into this method.

open Boolean

Get if the query is marked as DISTINCT, as last configured by setDistinct(boolean).

open Boolean

Get if the query is marked as strict, as last configured by setStrict(boolean).

open Boolean

Get if the query is marked as strict, as last configured by setStrictColumns(boolean).

open Boolean

Get if the query is marked as strict, as last configured by setStrictGrammar(boolean).

open Cursor!
query(db: SQLiteDatabase!, projectionIn: Array<String!>!, selection: String!, selectionArgs: Array<String!>!, groupBy: String!, having: String!, sortOrder: String!)

Perform a query by combining all current settings and the information passed into this method.

open Cursor!
query(db: SQLiteDatabase!, projectionIn: Array<String!>!, selection: String!, selectionArgs: Array<String!>!, groupBy: String!, having: String!, sortOrder: String!, limit: String!)

Perform a query by combining all current settings and the information passed into this method.

open Cursor!
query(db: SQLiteDatabase!, projectionIn: Array<String!>!, selection: String!, selectionArgs: Array<String!>!, groupBy: String!, having: String!, sortOrder: String!, limit: String!, cancellationSignal: CancellationSignal!)

Perform a query by combining all current settings and the information passed into this method.

open Unit

Sets the cursor factory to be used for the query.

open Unit
setDistinct(distinct: Boolean)

Mark the query as DISTINCT.

open Unit

Sets a projection greylist of columns that will be allowed through, even when setStrict(boolean) is enabled.

open Unit

Sets the projection map for the query.

open Unit
setStrict(strict: Boolean)

When set, the selection is verified against malicious arguments.

open Unit
setStrictColumns(strictColumns: Boolean)

When enabled, verify that all projections and ContentValues only contain valid columns as defined by setProjectionMap(java.util.Map).

open Unit
setStrictGrammar(strictGrammar: Boolean)

When enabled, verify that all untrusted SQL conforms to a restricted SQL grammar.

open Unit
setTables(inTables: String?)

Sets the list of tables to query.

open Int
update(db: SQLiteDatabase, values: ContentValues, selection: String?, selectionArgs: Array<String!>?)

Perform an update by combining all current settings and the information passed into this method.

Public constructors

SQLiteQueryBuilder

Added in API level 1
SQLiteQueryBuilder()

Public methods

appendColumns

Added in API level 1
open static fun appendColumns(
    s: StringBuilder!,
    columns: Array<String!>!
): Unit

Add the names that are non-null in columns to s, separating them with commas.

appendWhere

Added in API level 1
open fun appendWhere(inWhere: CharSequence): Unit

Append a chunk to the WHERE clause of the query. All chunks appended are surrounded by parenthesis and ANDed with the selection passed to #query. The final WHERE clause looks like:

WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>)

Parameters
inWhere CharSequence: the chunk of text to append to the WHERE clause. This value cannot be null.

appendWhereEscapeString

Added in API level 1
open fun appendWhereEscapeString(inWhere: String): Unit

Append a chunk to the WHERE clause of the query. All chunks appended are surrounded by parenthesis and ANDed with the selection passed to #query. The final WHERE clause looks like:

WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>)

Parameters
inWhere String: the chunk of text to append to the WHERE clause. it will be escaped to avoid SQL injection attacks This value cannot be null.

appendWhereStandalone

Added in API level 29
open fun appendWhereStandalone(inWhere: CharSequence): Unit

Add a standalone chunk to the WHERE clause of this query.

This method differs from appendWhere(java.lang.CharSequence) in that it automatically appends AND to any existing WHERE clause already under construction before appending the given standalone expression wrapped in parentheses.

Parameters
inWhere CharSequence: the standalone expression to append to the WHERE clause. It will be wrapped in parentheses when it's appended. This value cannot be null.

buildQuery

Added in API level 11
open fun buildQuery(
    projectionIn: Array<String!>!,
    selection: String!,
    groupBy: String!,
    having: String!,
    sortOrder: String!,
    limit: String!
): String!

Construct a SELECT statement suitable for use in a group of SELECT statements that will be joined through UNION operators in buildUnionQuery.

Parameters
projectionIn Array<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 URL.
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.
sortOrder 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.
Return
String! the resulting SQL SELECT statement

buildQuery

Added in API level 1
Deprecated in API level 15
open fun buildQuery(
    projectionIn: Array<String!>!,
    selection: String!,
    selectionArgs: Array<String!>!,
    groupBy: String!,
    having: String!,
    sortOrder: String!,
    limit: String!
): String!

Deprecated: This method's signature is misleading since no SQL parameter substitution is carried out. The selection arguments parameter does not get used at all. To avoid confusion, call buildQuery(java.lang.String[],java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String) instead.

buildQueryString

Added in API level 1
open static fun buildQueryString(
    distinct: Boolean,
    tables: String!,
    columns: Array<String!>!,
    where: String!,
    groupBy: String!,
    having: String!,
    orderBy: String!,
    limit: String!
): String!

Build an SQL query string from the given clauses.

Parameters
distinct Boolean: true if you want each row to be unique, false otherwise.
tables String!: The table names to compile the query against.
columns Array<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.
where 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 URL.
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.
Return
String! the SQL query string

buildUnionQuery

Added in API level 1
open fun buildUnionQuery(
    subQueries: Array<String!>!,
    sortOrder: String!,
    limit: String!
): String!

Given a set of subqueries, all of which are SELECT statements, construct a query that returns the union of what those subqueries return.

Parameters
subQueries Array<String!>!: an array of SQL SELECT statements, all of which must have the same columns as the same positions in their results
sortOrder 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!: The limit clause, which applies to the entire union result set
Return
String! the resulting SQL SELECT statement

buildUnionSubQuery

Added in API level 11
open fun buildUnionSubQuery(
    typeDiscriminatorColumn: String!,
    unionColumns: Array<String!>!,
    columnsPresentInTable: MutableSet<String!>!,
    computedColumnsOffset: Int,
    typeDiscriminatorValue: String!,
    selection: String!,
    groupBy: String!,
    having: String!
): String!

Construct a SELECT statement suitable for use in a group of SELECT statements that will be joined through UNION operators in buildUnionQuery.

Parameters
typeDiscriminatorColumn String!: the name of the result column whose cells will contain the name of the table from which each row was drawn.
unionColumns Array<String!>!: the names of the columns to appear in the result. This may include columns that do not appear in the table this SELECT is querying (i.e. mTables), but that do appear in one of the other tables in the UNION query that we are constructing.
columnsPresentInTable MutableSet<String!>!: a Set of the names of the columns that appear in this table (i.e. in the table whose name is mTables). Since columns in unionColumns include columns that appear only in other tables, we use this array to distinguish which ones actually are present. Other columns will have NULL values for results from this subquery.
computedColumnsOffset Int: all columns in unionColumns before this index are included under the assumption that they're computed and therefore won't appear in columnsPresentInTable, e.g. "date * 1000 as normalized_date"
typeDiscriminatorValue String!: the value used for the type-discriminator column in this subquery
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 URL.
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.
Return
String! the resulting SQL SELECT statement

buildUnionSubQuery

Added in API level 1
Deprecated in API level 15
open fun buildUnionSubQuery(
    typeDiscriminatorColumn: String!,
    unionColumns: Array<String!>!,
    columnsPresentInTable: MutableSet<String!>!,
    computedColumnsOffset: Int,
    typeDiscriminatorValue: String!,
    selection: String!,
    selectionArgs: Array<String!>!,
    groupBy: String!,
    having: String!
): String!

Deprecated: This method's signature is misleading since no SQL parameter substitution is carried out. The selection arguments parameter does not get used at all. To avoid confusion, call #buildUnionSubQuery instead.

delete

Added in API level 29
open fun delete(
    db: SQLiteDatabase,
    selection: String?,
    selectionArgs: Array<String!>?
): Int

Perform a delete by combining all current settings and the information passed into this method.

Parameters
db SQLiteDatabase: the database to delete on This value cannot be null.
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 URL.
selectionArgs Array<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. This value may be null.
Return
Int the number of rows deleted

getCursorFactory

Added in API level 29
open fun getCursorFactory(): SQLiteDatabase.CursorFactory?

Gets the cursor factory to be used for the query, as last configured by setCursorFactory(android.database.sqlite.SQLiteDatabase.CursorFactory).

Return
SQLiteDatabase.CursorFactory? This value may be null.

getProjectionGreylist

Added in API level 30
open fun getProjectionGreylist(): MutableCollection<Pattern!>?

Gets the projection greylist for the query, as last configured by setProjectionGreylist.

Return
MutableCollection<Pattern!>? This value may be null.

getProjectionMap

Added in API level 29
open fun getProjectionMap(): MutableMap<String!, String!>?

Gets the projection map for the query, as last configured by setProjectionMap(java.util.Map).

Return
MutableMap<String!, String!>? This value may be null.

getTables

Added in API level 1
open fun getTables(): String?

Returns the list of tables being queried

Return
String? the list of tables being queried This value may be null.

insert

Added in API level 30
open fun insert(
    db: SQLiteDatabase,
    values: ContentValues
): Long

Perform an insert by combining all current settings and the information passed into this method.

Parameters
db SQLiteDatabase: the database to insert on This value cannot be null.
values ContentValues: This value cannot be null.
Return
Long the row ID of the newly inserted row, or -1 if an error occurred

isDistinct

Added in API level 29
open fun isDistinct(): Boolean

Get if the query is marked as DISTINCT, as last configured by setDistinct(boolean).

isStrict

Added in API level 29
open fun isStrict(): Boolean

Get if the query is marked as strict, as last configured by setStrict(boolean).

isStrictColumns

Added in API level 30
open fun isStrictColumns(): Boolean

Get if the query is marked as strict, as last configured by setStrictColumns(boolean).

isStrictGrammar

Added in API level 30
open fun isStrictGrammar(): Boolean

Get if the query is marked as strict, as last configured by setStrictGrammar(boolean).

query

Added in API level 1
open fun query(
    db: SQLiteDatabase!,
    projectionIn: Array<String!>!,
    selection: String!,
    selectionArgs: Array<String!>!,
    groupBy: String!,
    having: String!,
    sortOrder: String!
): Cursor!

Perform a query by combining all current settings and the information passed into this method.

Parameters
db SQLiteDatabase!: the database to query on
projectionIn Array<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 URL.
selectionArgs Array<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.
sortOrder 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.
Return
Cursor! a cursor over the result set

query

Added in API level 1
open fun query(
    db: SQLiteDatabase!,
    projectionIn: Array<String!>!,
    selection: String!,
    selectionArgs: Array<String!>!,
    groupBy: String!,
    having: String!,
    sortOrder: String!,
    limit: String!
): Cursor!

Perform a query by combining all current settings and the information passed into this method.

Parameters
db SQLiteDatabase!: the database to query on
projectionIn Array<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 URL.
selectionArgs Array<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.
sortOrder 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.
Return
Cursor! a cursor over the result set

query

Added in API level 16
open fun query(
    db: SQLiteDatabase!,
    projectionIn: Array<String!>!,
    selection: String!,
    selectionArgs: Array<String!>!,
    groupBy: String!,
    having: String!,
    sortOrder: String!,
    limit: String!,
    cancellationSignal: CancellationSignal!
): Cursor!

Perform a query by combining all current settings and the information passed into this method.

Parameters
db SQLiteDatabase!: the database to query on
projectionIn Array<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 URL.
selectionArgs Array<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.
sortOrder 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.
cancellationSignal CancellationSignal!: A signal to cancel the operation in progress, or null if none. If the operation is canceled, then OperationCanceledException will be thrown when the query is executed.
Return
Cursor! a cursor over the result set

setCursorFactory

Added in API level 1
open fun setCursorFactory(factory: SQLiteDatabase.CursorFactory?): Unit

Sets the cursor factory to be used for the query. You can use one factory for all queries on a database but it is normally easier to specify the factory when doing this query.

Parameters
factory SQLiteDatabase.CursorFactory?: the factory to use. This value may be null.

setDistinct

Added in API level 1
open fun setDistinct(distinct: Boolean): Unit

Mark the query as DISTINCT.

Parameters
distinct Boolean: if true the query is DISTINCT, otherwise it isn't

setProjectionGreylist

Added in API level 30
open fun setProjectionGreylist(projectionGreylist: MutableCollection<Pattern!>?): Unit

Sets a projection greylist of columns that will be allowed through, even when setStrict(boolean) is enabled. This provides a way for abusive custom columns like COUNT(*) to continue working.

Parameters
projectionGreylist MutableCollection<Pattern!>?: This value may be null.

setProjectionMap

Added in API level 1
open fun setProjectionMap(columnMap: MutableMap<String!, String!>?): Unit

Sets the projection map for the query. The projection map maps from column names that the caller passes into query to database column names. This is useful for renaming columns as well as disambiguating column names when doing joins. For example you could map "name" to "people.name". If a projection map is set it must contain all column names the user may request, even if the key and value are the same.

Parameters
columnMap MutableMap<String!, String!>?: maps from the user column names to the database column names This value may be null.

setStrict

Added in API level 14
open fun setStrict(strict: Boolean): Unit

When set, the selection is verified against malicious arguments. When using this class to create a statement using buildQueryString(boolean,java.lang.String,java.lang.String[],java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String), non-numeric limits will raise an exception. If a projection map is specified, fields not in that map will be ignored. If this class is used to execute the statement directly using query(android.database.sqlite.SQLiteDatabase,java.lang.String[],java.lang.String,java.lang.String[],java.lang.String,java.lang.String,java.lang.String) or query(android.database.sqlite.SQLiteDatabase,java.lang.String[],java.lang.String,java.lang.String[],java.lang.String,java.lang.String,java.lang.String,java.lang.String), additionally also parenthesis escaping selection are caught. To summarize: To get maximum protection against malicious third party apps (for example content provider consumers), make sure to do the following:

  • Set this value to true
  • Use a projection map
  • Use one of the query overloads instead of getting the statement as a sql string

This feature is disabled by default on each newly constructed SQLiteQueryBuilder and needs to be manually enabled.

setStrictColumns

Added in API level 30
open fun setStrictColumns(strictColumns: Boolean): Unit

When enabled, verify that all projections and ContentValues only contain valid columns as defined by setProjectionMap(java.util.Map).

This enforcement applies to insert, #query, and update operations. Any enforcement failures will throw an IllegalArgumentException.

This feature is disabled by default on each newly constructed SQLiteQueryBuilder and needs to be manually enabled.

setStrictGrammar

Added in API level 30
open fun setStrictGrammar(strictGrammar: Boolean): Unit

When enabled, verify that all untrusted SQL conforms to a restricted SQL grammar. Here are the restrictions applied:

  • In WHERE and HAVING clauses: subqueries, raising, and windowing terms are rejected.
  • In GROUP BY clauses: only valid columns are allowed.
  • In ORDER BY clauses: only valid columns, collation, and ordering terms are allowed.
  • In LIMIT clauses: only numerical values and offset terms are allowed.
All column references must be valid as defined by setProjectionMap(java.util.Map).

This enforcement applies to #query, update and delete operations. This enforcement does not apply to trusted inputs, such as those provided by appendWhere. Any enforcement failures will throw an IllegalArgumentException.

This feature is disabled by default on each newly constructed SQLiteQueryBuilder and needs to be manually enabled.

setTables

Added in API level 1
open fun setTables(inTables: String?): Unit

Sets the list of tables to query. Multiple tables can be specified to perform a join. For example: setTables("foo, bar") setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)")

Parameters
inTables String?: the list of tables to query on This value may be null.

update

Added in API level 29
open fun update(
    db: SQLiteDatabase,
    values: ContentValues,
    selection: String?,
    selectionArgs: Array<String!>?
): Int

Perform an update by combining all current settings and the information passed into this method.

Parameters
db SQLiteDatabase: the database to update on This value cannot be null.
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 URL.
selectionArgs Array<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. This value may be null.
values ContentValues: This value cannot be null.
Return
Int the number of rows updated