FileDocCategorySizeDatePackage
SQLiteQueryBuilder.javaAPI DocAndroid 5.1 API28749Thu Mar 12 22:22:10 GMT 2015android.database.sqlite

SQLiteQueryBuilder

public class SQLiteQueryBuilder extends Object
This is a convience class that helps build SQL queries to be sent to {@link SQLiteDatabase} objects.

Fields Summary
private static final String
TAG
private static final Pattern
sLimitPattern
private Map
mProjectionMap
private String
mTables
private StringBuilder
mWhereClause
private boolean
mDistinct
private SQLiteDatabase.CursorFactory
mFactory
private boolean
mStrict
Constructors Summary
public SQLiteQueryBuilder()


      
        mDistinct = false;
        mFactory = null;
Methods Summary
private static voidappendClause(java.lang.StringBuilder s, java.lang.String name, java.lang.String clause)

        if (!TextUtils.isEmpty(clause)) {
            s.append(name);
            s.append(clause);
        }
public static voidappendColumns(java.lang.StringBuilder s, java.lang.String[] columns)
Add the names that are non-null in columns to s, separating them with commas.

        int n = columns.length;

        for (int i = 0; i < n; i++) {
            String column = columns[i];

            if (column != null) {
                if (i > 0) {
                    s.append(", ");
                }
                s.append(column);
            }
        }
        s.append(' ");
public voidappendWhere(java.lang.CharSequence inWhere)
Append a chunk to the WHERE clause of the query. All chunks appended are surrounded by parenthesis and ANDed with the selection passed to {@link #query}. The final WHERE clause looks like: WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>)

param
inWhere the chunk of text to append to the WHERE clause.

        if (mWhereClause == null) {
            mWhereClause = new StringBuilder(inWhere.length() + 16);
        }
        if (mWhereClause.length() == 0) {
            mWhereClause.append('(");
        }
        mWhereClause.append(inWhere);
public voidappendWhereEscapeString(java.lang.String inWhere)
Append a chunk to the WHERE clause of the query. All chunks appended are surrounded by parenthesis and ANDed with the selection passed to {@link #query}. The final WHERE clause looks like: WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>)

param
inWhere the chunk of text to append to the WHERE clause. it will be escaped to avoid SQL injection attacks

        if (mWhereClause == null) {
            mWhereClause = new StringBuilder(inWhere.length() + 16);
        }
        if (mWhereClause.length() == 0) {
            mWhereClause.append('(");
        }
        DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere);
public java.lang.StringbuildQuery(java.lang.String[] projectionIn, java.lang.String selection, java.lang.String groupBy, java.lang.String having, java.lang.String sortOrder, java.lang.String limit)
Construct a SELECT statement suitable for use in a group of SELECT statements that will be joined through UNION operators in buildUnionQuery.

param
projectionIn 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.
param
selection 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.
param
groupBy 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.
param
having 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.
param
sortOrder 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.
param
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
return
the resulting SQL SELECT statement

        String[] projection = computeProjection(projectionIn);

        StringBuilder where = new StringBuilder();
        boolean hasBaseWhereClause = mWhereClause != null && mWhereClause.length() > 0;

        if (hasBaseWhereClause) {
            where.append(mWhereClause.toString());
            where.append(')");
        }

        // Tack on the user's selection, if present.
        if (selection != null && selection.length() > 0) {
            if (hasBaseWhereClause) {
                where.append(" AND ");
            }

            where.append('(");
            where.append(selection);
            where.append(')");
        }

        return buildQueryString(
                mDistinct, mTables, projection, where.toString(),
                groupBy, having, sortOrder, limit);
public java.lang.StringbuildQuery(java.lang.String[] projectionIn, java.lang.String selection, java.lang.String[] selectionArgs, java.lang.String groupBy, java.lang.String having, java.lang.String sortOrder, java.lang.String limit)

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 {@link #buildQuery(String[], String, String, String, String, String)} instead.

        return buildQuery(projectionIn, selection, groupBy, having, sortOrder, limit);
public static java.lang.StringbuildQueryString(boolean distinct, java.lang.String tables, java.lang.String[] columns, java.lang.String where, java.lang.String groupBy, java.lang.String having, java.lang.String orderBy, java.lang.String limit)
Build an SQL query string from the given clauses.

param
distinct true if you want each row to be unique, false otherwise.
param
tables The table names to compile the query against.
param
columns 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.
param
where 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.
param
groupBy 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.
param
having 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.
param
orderBy 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.
param
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
return
the SQL query string

        if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) {
            throw new IllegalArgumentException(
                    "HAVING clauses are only permitted when using a groupBy clause");
        }
        if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
            throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
        }

        StringBuilder query = new StringBuilder(120);

        query.append("SELECT ");
        if (distinct) {
            query.append("DISTINCT ");
        }
        if (columns != null && columns.length != 0) {
            appendColumns(query, columns);
        } else {
            query.append("* ");
        }
        query.append("FROM ");
        query.append(tables);
        appendClause(query, " WHERE ", where);
        appendClause(query, " GROUP BY ", groupBy);
        appendClause(query, " HAVING ", having);
        appendClause(query, " ORDER BY ", orderBy);
        appendClause(query, " LIMIT ", limit);

        return query.toString();
public java.lang.StringbuildUnionQuery(java.lang.String[] subQueries, java.lang.String sortOrder, java.lang.String limit)
Given a set of subqueries, all of which are SELECT statements, construct a query that returns the union of what those subqueries return.

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

        StringBuilder query = new StringBuilder(128);
        int subQueryCount = subQueries.length;
        String unionOperator = mDistinct ? " UNION " : " UNION ALL ";

        for (int i = 0; i < subQueryCount; i++) {
            if (i > 0) {
                query.append(unionOperator);
            }
            query.append(subQueries[i]);
        }
        appendClause(query, " ORDER BY ", sortOrder);
        appendClause(query, " LIMIT ", limit);
        return query.toString();
public java.lang.StringbuildUnionSubQuery(java.lang.String typeDiscriminatorColumn, java.lang.String[] unionColumns, java.util.Set columnsPresentInTable, int computedColumnsOffset, java.lang.String typeDiscriminatorValue, java.lang.String selection, java.lang.String groupBy, java.lang.String having)
Construct a SELECT statement suitable for use in a group of SELECT statements that will be joined through UNION operators in buildUnionQuery.

param
typeDiscriminatorColumn the name of the result column whose cells will contain the name of the table from which each row was drawn.
param
unionColumns 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.
param
columnsPresentInTable 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.
param
computedColumnsOffset 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"
param
typeDiscriminatorValue the value used for the type-discriminator column in this subquery
param
selection 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.
param
groupBy 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.
param
having 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
the resulting SQL SELECT statement

        int unionColumnsCount = unionColumns.length;
        String[] projectionIn = new String[unionColumnsCount];

        for (int i = 0; i < unionColumnsCount; i++) {
            String unionColumn = unionColumns[i];

            if (unionColumn.equals(typeDiscriminatorColumn)) {
                projectionIn[i] = "'" + typeDiscriminatorValue + "' AS "
                        + typeDiscriminatorColumn;
            } else if (i <= computedColumnsOffset
                       || columnsPresentInTable.contains(unionColumn)) {
                projectionIn[i] = unionColumn;
            } else {
                projectionIn[i] = "NULL AS " + unionColumn;
            }
        }
        return buildQuery(
                projectionIn, selection, groupBy, having,
                null /* sortOrder */,
                null /* limit */);
public java.lang.StringbuildUnionSubQuery(java.lang.String typeDiscriminatorColumn, java.lang.String[] unionColumns, java.util.Set columnsPresentInTable, int computedColumnsOffset, java.lang.String typeDiscriminatorValue, java.lang.String selection, java.lang.String[] selectionArgs, java.lang.String groupBy, java.lang.String having)

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 {@link #buildUnionSubQuery} instead.

        return buildUnionSubQuery(
                typeDiscriminatorColumn, unionColumns, columnsPresentInTable,
                computedColumnsOffset, typeDiscriminatorValue, selection,
                groupBy, having);
private java.lang.String[]computeProjection(java.lang.String[] projectionIn)

        if (projectionIn != null && projectionIn.length > 0) {
            if (mProjectionMap != null) {
                String[] projection = new String[projectionIn.length];
                int length = projectionIn.length;

                for (int i = 0; i < length; i++) {
                    String userColumn = projectionIn[i];
                    String column = mProjectionMap.get(userColumn);

                    if (column != null) {
                        projection[i] = column;
                        continue;
                    }

                    if (!mStrict &&
                            ( userColumn.contains(" AS ") || userColumn.contains(" as "))) {
                        /* A column alias already exist */
                        projection[i] = userColumn;
                        continue;
                    }

                    throw new IllegalArgumentException("Invalid column "
                            + projectionIn[i]);
                }
                return projection;
            } else {
                return projectionIn;
            }
        } else if (mProjectionMap != null) {
            // Return all columns in projection map.
            Set<Entry<String, String>> entrySet = mProjectionMap.entrySet();
            String[] projection = new String[entrySet.size()];
            Iterator<Entry<String, String>> entryIter = entrySet.iterator();
            int i = 0;

            while (entryIter.hasNext()) {
                Entry<String, String> entry = entryIter.next();

                // Don't include the _count column when people ask for no projection.
                if (entry.getKey().equals(BaseColumns._COUNT)) {
                    continue;
                }
                projection[i++] = entry.getValue();
            }
            return projection;
        }
        return null;
public java.lang.StringgetTables()
Returns the list of tables being queried

return
the list of tables being queried

        return mTables;
public android.database.Cursorquery(SQLiteDatabase db, java.lang.String[] projectionIn, java.lang.String selection, java.lang.String[] selectionArgs, java.lang.String groupBy, java.lang.String having, java.lang.String sortOrder)
Perform a query by combining all current settings and the information passed into this method.

param
db the database to query on
param
projectionIn 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.
param
selection 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.
param
selectionArgs 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.
param
groupBy 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.
param
having 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.
param
sortOrder 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
a cursor over the result set
see
android.content.ContentResolver#query(android.net.Uri, String[], String, String[], String)

        return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder,
                null /* limit */, null /* cancellationSignal */);
public android.database.Cursorquery(SQLiteDatabase db, java.lang.String[] projectionIn, java.lang.String selection, java.lang.String[] selectionArgs, java.lang.String groupBy, java.lang.String having, java.lang.String sortOrder, java.lang.String limit)
Perform a query by combining all current settings and the information passed into this method.

param
db the database to query on
param
projectionIn 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.
param
selection 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.
param
selectionArgs 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.
param
groupBy 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.
param
having 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.
param
sortOrder 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.
param
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
return
a cursor over the result set
see
android.content.ContentResolver#query(android.net.Uri, String[], String, String[], String)

        return query(db, projectionIn, selection, selectionArgs,
                groupBy, having, sortOrder, limit, null);
public android.database.Cursorquery(SQLiteDatabase db, java.lang.String[] projectionIn, java.lang.String selection, java.lang.String[] selectionArgs, java.lang.String groupBy, java.lang.String having, java.lang.String sortOrder, java.lang.String limit, android.os.CancellationSignal cancellationSignal)
Perform a query by combining all current settings and the information passed into this method.

param
db the database to query on
param
projectionIn 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.
param
selection 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.
param
selectionArgs 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.
param
groupBy 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.
param
having 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.
param
sortOrder 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.
param
limit Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
param
cancellationSignal A signal to cancel the operation in progress, or null if none. If the operation is canceled, then {@link OperationCanceledException} will be thrown when the query is executed.
return
a cursor over the result set
see
android.content.ContentResolver#query(android.net.Uri, String[], String, String[], String)

        if (mTables == null) {
            return null;
        }

        if (mStrict && selection != null && selection.length() > 0) {
            // Validate the user-supplied selection to detect syntactic anomalies
            // in the selection string that could indicate a SQL injection attempt.
            // The idea is to ensure that the selection clause is a valid SQL expression
            // by compiling it twice: once wrapped in parentheses and once as
            // originally specified. An attacker cannot create an expression that
            // would escape the SQL expression while maintaining balanced parentheses
            // in both the wrapped and original forms.
            String sqlForValidation = buildQuery(projectionIn, "(" + selection + ")", groupBy,
                    having, sortOrder, limit);
            validateQuerySql(db, sqlForValidation,
                    cancellationSignal); // will throw if query is invalid
        }

        String sql = buildQuery(
                projectionIn, selection, groupBy, having,
                sortOrder, limit);

        if (Log.isLoggable(TAG, Log.DEBUG)) {
            Log.d(TAG, "Performing query: " + sql);
        }
        return db.rawQueryWithFactory(
                mFactory, sql, selectionArgs,
                SQLiteDatabase.findEditTable(mTables),
                cancellationSignal); // will throw if query is invalid
public voidsetCursorFactory(SQLiteDatabase.CursorFactory factory)
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.

param
factory the factory to use.

        mFactory = factory;
public voidsetDistinct(boolean distinct)
Mark the query as DISTINCT.

param
distinct if true the query is DISTINCT, otherwise it isn't

        mDistinct = distinct;
public voidsetProjectionMap(java.util.Map columnMap)
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.

param
columnMap maps from the user column names to the database column names

        mProjectionMap = columnMap;
public voidsetStrict(boolean flag)
When set, the selection is verified against malicious arguments. When using this class to create a statement using {@link #buildQueryString(boolean, String, String[], String, String, String, String, 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 {@link #query(SQLiteDatabase, String[], String, String[], String, String, String)} or {@link #query(SQLiteDatabase, String[], String, String[], String, String, String, 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
By default, this value is false.

        mStrict = flag;
public voidsetTables(java.lang.String inTables)
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)")

param
inTables the list of tables to query on

        mTables = inTables;
private voidvalidateQuerySql(SQLiteDatabase db, java.lang.String sql, android.os.CancellationSignal cancellationSignal)
Verifies that a SQL SELECT statement is valid by compiling it. If the SQL statement is not valid, this method will throw a {@link SQLiteException}.

        db.getThreadSession().prepare(sql,
                db.getThreadDefaultConnectionFlags(true /*readOnly*/), cancellationSignal, null);