diff options
author | Trevor Johns <trevorjohns@google.com> | 2013-06-28 18:19:57 -0700 |
---|---|---|
committer | Trevor Johns <trevorjohns@google.com> | 2013-07-18 19:51:02 -0700 |
commit | 8b253b7ed02c066dfac1852e9522456cd50d5a36 (patch) | |
tree | d983eb8e74d6faacec8872a8732d544b458a5fa5 /common | |
parent | 56280e0e9c5ad29823f5196590674a529dabcb8d (diff) | |
download | android-8b253b7ed02c066dfac1852e9522456cd50d5a36.tar.gz |
Move SelectionBuilder to common.
Change-Id: I044a3edcc7d862b08a05b56594a8bd77c77ace10
Diffstat (limited to 'common')
-rw-r--r-- | common/src/com/example/android/common/db/SelectionBuilder.java | 358 |
1 files changed, 358 insertions, 0 deletions
diff --git a/common/src/com/example/android/common/db/SelectionBuilder.java b/common/src/com/example/android/common/db/SelectionBuilder.java new file mode 100644 index 00000000..51d8cc37 --- /dev/null +++ b/common/src/com/example/android/common/db/SelectionBuilder.java @@ -0,0 +1,358 @@ +/* + * Copyright 2013 The Android Open Source Project + * + * Licensed under the Apache License, Version 2.0 (the "License"); + * you may not use this file except in compliance with the License. + * You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +/* + * Modifications: + * -Imported from AOSP frameworks/base/core/java/com/android/internal/content + * -Changed package name + */ + +package com.example.android.common.db; + +import android.content.ContentValues; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.text.TextUtils; +import android.util.Log; + +import com.google.common.collect.Lists; +import com.google.common.collect.Maps; + +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collections; +import java.util.Map; + +/** + * Helper for building selection clauses for {@link SQLiteDatabase}. + * + * <p>This class provides a convenient frontend for working with SQL. Instead of composing statements + * manually using string concatenation, method calls are used to construct the statement one + * clause at a time. These methods can be chained together. + * + * <p>If multiple where() statements are provided, they're combined using {@code AND}. + * + * <p>Example: + * + * <pre> + * SelectionBuilder builder = new SelectionBuilder(); + * Cursor c = builder.table(FeedContract.Entry.TABLE_NAME) // String TABLE_NAME = "entry" + * .where(FeedContract.Entry._ID + "=?", id); // String _ID = "_ID" + * .query(db, projection, sortOrder) + * + * </pre> + * + * <p>In this example, the table name and filters ({@code WHERE} clauses) are both explicitly + * specified via method call. SelectionBuilder takes care of issuing a "query" command to the + * database, and returns the resulting {@link Cursor} object. + * + * <p>Inner {@code JOIN}s can be accomplished using the mapToTable() function. The map() function + * can be used to create new columns based on arbitrary (SQL-based) criteria. In advanced usage, + * entire subqueries can be passed into the map() function. + * + * <p>Advanced example: + * + * <pre> + * // String SESSIONS_JOIN_BLOCKS_ROOMS = "sessions " + * // + "LEFT OUTER JOIN blocks ON sessions.block_id=blocks.block_id " + * // + "LEFT OUTER JOIN rooms ON sessions.room_id=rooms.room_id"; + * + * // String Subquery.BLOCK_NUM_STARRED_SESSIONS = + * // "(SELECT COUNT(1) FROM " + * // + Tables.SESSIONS + " WHERE " + Qualified.SESSIONS_BLOCK_ID + "=" + * // + Qualified.BLOCKS_BLOCK_ID + " AND " + Qualified.SESSIONS_STARRED + "=1)"; + * + * String Subqery.BLOCK_SESSIONS_COUNT = + * Cursor c = builder.table(Tables.SESSIONS_JOIN_BLOCKS_ROOMS) + * .map(Blocks.NUM_STARRED_SESSIONS, Subquery.BLOCK_NUM_STARRED_SESSIONS) + * .mapToTable(Sessions._ID, Tables.SESSIONS) + * .mapToTable(Sessions.SESSION_ID, Tables.SESSIONS) + * .mapToTable(Sessions.BLOCK_ID, Tables.SESSIONS) + * .mapToTable(Sessions.ROOM_ID, Tables.SESSIONS) + * .where(Qualified.SESSIONS_BLOCK_ID + "=?", blockId); + * </pre> + * + * <p>In this example, we have two different types of {@code JOIN}s: a left outer join using a + * modified table name (since this class doesn't directly support these), and an inner join using + * the mapToTable() function. The map() function is used to insert a count based on specific + * criteria, executed as a sub-query. + * + * This class is <em>not</em> thread safe. + */ +public class SelectionBuilder { + private static final String TAG = "basicsyncadapter"; + + private String mTable = null; + private Map<String, String> mProjectionMap = Maps.newHashMap(); + private StringBuilder mSelection = new StringBuilder(); + private ArrayList<String> mSelectionArgs = Lists.newArrayList(); + + /** + * Reset any internal state, allowing this builder to be recycled. + * + * <p>Calling this method is more efficient than creating a new SelectionBuilder object. + * + * @return Fluent interface + */ + public SelectionBuilder reset() { + mTable = null; + mSelection.setLength(0); + mSelectionArgs.clear(); + return this; + } + + /** + * Append the given selection clause to the internal state. Each clause is + * surrounded with parenthesis and combined using {@code AND}. + * + * <p>In the most basic usage, simply provide a selection in SQL {@code WHERE} statement format. + * + * <p>Example: + * + * <pre> + * .where("blog_posts.category = 'PROGRAMMING'); + * </pre> + * + * <p>User input should never be directly supplied as as part of the selection statement. + * Instead, use positional parameters in your selection statement, then pass the user input + * in via the selectionArgs parameter. This prevents SQL escape characters in user input from + * causing unwanted side effects. (Failure to follow this convention may have security + * implications.) + * + * <p>Positional parameters are specified using the '?' character. + * + * <p>Example: + * <pre> + * .where("blog_posts.title contains ?, userSearchString); + * </pre> + * + * @param selection SQL where statement + * @param selectionArgs Values to substitute for positional parameters ('?' characters in + * {@code selection} statement. Will be automatically escaped. + * @return Fluent interface + */ + public SelectionBuilder where(String selection, String... selectionArgs) { + if (TextUtils.isEmpty(selection)) { + if (selectionArgs != null && selectionArgs.length > 0) { + throw new IllegalArgumentException( + "Valid selection required when including arguments="); + } + + // Shortcut when clause is empty + return this; + } + + if (mSelection.length() > 0) { + mSelection.append(" AND "); + } + + mSelection.append("(").append(selection).append(")"); + if (selectionArgs != null) { + Collections.addAll(mSelectionArgs, selectionArgs); + } + + return this; + } + + /** + * Table name to use for SQL {@code FROM} statement. + * + * <p>This method may only be called once. If multiple tables are required, concatenate them + * in SQL-format (typically comma-separated). + * + * <p>If you need to do advanced {@code JOIN}s, they can also be specified here. + * + * See also: mapToTable() + * + * @param table Table name + * @return Fluent interface + */ + public SelectionBuilder table(String table) { + mTable = table; + return this; + } + + /** + * Verify that a table name has been supplied using table(). + * + * @throws IllegalStateException if table not set + */ + private void assertTable() { + if (mTable == null) { + throw new IllegalStateException("Table not specified"); + } + } + + /** + * Perform an inner join. + * + * <p>Map columns from a secondary table onto the current result set. References to the column + * specified in {@code column} will be replaced with {@code table.column} in the SQL {@code + * SELECT} clause. + * + * @param column Column name to join on. Must be the same in both tables. + * @param table Secondary table to join. + * @return Fluent interface + */ + public SelectionBuilder mapToTable(String column, String table) { + mProjectionMap.put(column, table + "." + column); + return this; + } + + /** + * Create a new column based on custom criteria (such as aggregate functions). + * + * <p>This adds a new column to the result set, based upon custom criteria in SQL format. This + * is equivalent to the SQL statement: {@code SELECT toClause AS fromColumn} + * + * <p>This method is useful for executing SQL sub-queries. + * + * @param fromColumn Name of column for mapping + * @param toClause SQL string representing data to be mapped + * @return Fluent interface + */ + public SelectionBuilder map(String fromColumn, String toClause) { + mProjectionMap.put(fromColumn, toClause + " AS " + fromColumn); + return this; + } + + /** + * Return selection string based on current internal state. + * + * @return Current selection as a SQL statement + * @see #getSelectionArgs() + */ + public String getSelection() { + return mSelection.toString(); + + } + + /** + * Return selection arguments based on current internal state. + * + * @see #getSelection() + */ + public String[] getSelectionArgs() { + return mSelectionArgs.toArray(new String[mSelectionArgs.size()]); + } + + /** + * Process user-supplied projection (column list). + * + * <p>In cases where a column is mapped to another data source (either another table, or an + * SQL sub-query), the column name will be replaced with a more specific, SQL-compatible + * representation. + * + * Assumes that incoming columns are non-null. + * + * <p>See also: map(), mapToTable() + * + * @param columns User supplied projection (column list). + */ + private void mapColumns(String[] columns) { + for (int i = 0; i < columns.length; i++) { + final String target = mProjectionMap.get(columns[i]); + if (target != null) { + columns[i] = target; + } + } + } + + /** + * Return a description of this builder's state. Does NOT output SQL. + * + * @return Human-readable internal state + */ + @Override + public String toString() { + return "SelectionBuilder[table=" + mTable + ", selection=" + getSelection() + + ", selectionArgs=" + Arrays.toString(getSelectionArgs()) + "]"; + } + + /** + * Execute query (SQL {@code SELECT}) against specified database. + * + * <p>Using a null projection (column list) is not supported. + * + * @param db Database to query. + * @param columns Database projection (column list) to return, must be non-NULL. + * @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. + * @return A {@link Cursor} object, which is positioned before the first entry. Note that + * {@link Cursor}s are not synchronized, see the documentation for more details. + */ + public Cursor query(SQLiteDatabase db, String[] columns, String orderBy) { + return query(db, columns, null, null, orderBy, null); + } + + /** + * Execute query ({@code SELECT}) against database. + * + * <p>Using a null projection (column list) is not supported. + * + * @param db Database to query. + * @param columns Database projection (column list) to return, must be non-null. + * @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 A {@link Cursor} object, which is positioned before the first entry. Note that + * {@link Cursor}s are not synchronized, see the documentation for more details. + */ + public Cursor query(SQLiteDatabase db, String[] columns, String groupBy, + String having, String orderBy, String limit) { + assertTable(); + if (columns != null) mapColumns(columns); + Log.v(TAG, "query(columns=" + Arrays.toString(columns) + ") " + this); + return db.query(mTable, columns, getSelection(), getSelectionArgs(), groupBy, having, + orderBy, limit); + } + + /** + * Execute an {@code UPDATE} against database. + * + * @param db Database to query. + * @param values A map from column names to new column values. null is a valid value that will + * be translated to NULL + * @return The number of rows affected. + */ + public int update(SQLiteDatabase db, ContentValues values) { + assertTable(); + Log.v(TAG, "update() " + this); + return db.update(mTable, values, getSelection(), getSelectionArgs()); + } + + /** + * Execute {@code DELETE} against database. + * + * @param db Database to query. + * @return The number of rows affected. + */ + public int delete(SQLiteDatabase db) { + assertTable(); + Log.v(TAG, "delete() " + this); + return db.delete(mTable, getSelection(), getSelectionArgs()); + } +} |