to top
BasicSyncAdapter / src / com.example.android.common / db /

SelectionBuilder.java

1
/*
2
 * Copyright 2013 The Android Open Source Project
3
 *
4
 * Licensed under the Apache License, Version 2.0 (the "License");
5
 * you may not use this file except in compliance with the License.
6
 * You may obtain a copy of the License at
7
 *
8
 *      http://www.apache.org/licenses/LICENSE-2.0
9
 *
10
 * Unless required by applicable law or agreed to in writing, software
11
 * distributed under the License is distributed on an "AS IS" BASIS,
12
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
 * See the License for the specific language governing permissions and
14
 * limitations under the License.
15
 */
16
 
17
/*
18
 * Modifications:
19
 * -Imported from AOSP frameworks/base/core/java/com/android/internal/content
20
 * -Changed package name
21
 */
22
 
23
package com.example.android.common.db;
24
 
25
import android.content.ContentValues;
26
import android.database.Cursor;
27
import android.database.sqlite.SQLiteDatabase;
28
import android.text.TextUtils;
29
import android.util.Log;
30
 
31
import java.util.ArrayList;
32
import java.util.Arrays;
33
import java.util.Collections;
34
import java.util.HashMap;
35
import java.util.Map;
36
 
37
/**
38
 * Helper for building selection clauses for {@link SQLiteDatabase}.
39
 *
40
 * <p>This class provides a convenient frontend for working with SQL. Instead of composing statements
41
 * manually using string concatenation, method calls are used to construct the statement one
42
 * clause at a time. These methods can be chained together.
43
 *
44
 * <p>If multiple where() statements are provided, they're combined using {@code AND}.
45
 *
46
 * <p>Example:
47
 *
48
 * <pre>
49
 *     SelectionBuilder builder = new SelectionBuilder();
50
 *     Cursor c = builder.table(FeedContract.Entry.TABLE_NAME)       // String TABLE_NAME = "entry"
51
 *                       .where(FeedContract.Entry._ID + "=?", id);  // String _ID = "_ID"
52
 *                       .query(db, projection, sortOrder)
53
 *
54
 * </pre>
55
 *
56
 * <p>In this example, the table name and filters ({@code WHERE} clauses) are both explicitly
57
 * specified via method call. SelectionBuilder takes care of issuing a "query" command to the
58
 * database, and returns the resulting {@link Cursor} object.
59
 *
60
 * <p>Inner {@code JOIN}s can be accomplished using the mapToTable() function. The map() function
61
 * can be used to create new columns based on arbitrary (SQL-based) criteria. In advanced usage,
62
 * entire subqueries can be passed into the map() function.
63
 *
64
 * <p>Advanced example:
65
 *
66
 * <pre>
67
 *     // String SESSIONS_JOIN_BLOCKS_ROOMS = "sessions "
68
 *     //        + "LEFT OUTER JOIN blocks ON sessions.block_id=blocks.block_id "
69
 *     //        + "LEFT OUTER JOIN rooms ON sessions.room_id=rooms.room_id";
70
 *
71
 *     // String Subquery.BLOCK_NUM_STARRED_SESSIONS =
72
 *     //       "(SELECT COUNT(1) FROM "
73
 *     //        + Tables.SESSIONS + " WHERE " + Qualified.SESSIONS_BLOCK_ID + "="
74
 *     //        + Qualified.BLOCKS_BLOCK_ID + " AND " + Qualified.SESSIONS_STARRED + "=1)";
75
 *
76
 *     String Subqery.BLOCK_SESSIONS_COUNT =
77
 *     Cursor c = builder.table(Tables.SESSIONS_JOIN_BLOCKS_ROOMS)
78
 *               .map(Blocks.NUM_STARRED_SESSIONS, Subquery.BLOCK_NUM_STARRED_SESSIONS)
79
 *               .mapToTable(Sessions._ID, Tables.SESSIONS)
80
 *               .mapToTable(Sessions.SESSION_ID, Tables.SESSIONS)
81
 *               .mapToTable(Sessions.BLOCK_ID, Tables.SESSIONS)
82
 *               .mapToTable(Sessions.ROOM_ID, Tables.SESSIONS)
83
 *               .where(Qualified.SESSIONS_BLOCK_ID + "=?", blockId);
84
 * </pre>
85
 *
86
 * <p>In this example, we have two different types of {@code JOIN}s: a left outer join using a
87
 * modified table name (since this class doesn't directly support these), and an inner join using
88
 * the mapToTable() function. The map() function is used to insert a count based on specific
89
 * criteria, executed as a sub-query.
90
 *
91
 * This class is <em>not</em> thread safe.
92
 */
93
public class SelectionBuilder {
94
    private static final String TAG = "basicsyncadapter";
95
 
96
    private String mTable = null;
97
    private Map<String, String> mProjectionMap = new HashMap<String, String>();
98
    private StringBuilder mSelection = new StringBuilder();
99
    private ArrayList<String> mSelectionArgs = new ArrayList<String>();
100
 
101
    /**
102
     * Reset any internal state, allowing this builder to be recycled.
103
     *
104
     * <p>Calling this method is more efficient than creating a new SelectionBuilder object.
105
     *
106
     * @return Fluent interface
107
     */
108
    public SelectionBuilder reset() {
109
        mTable = null;
110
        mSelection.setLength(0);
111
        mSelectionArgs.clear();
112
        return this;
113
    }
114
 
115
    /**
116
     * Append the given selection clause to the internal state. Each clause is
117
     * surrounded with parenthesis and combined using {@code AND}.
118
     *
119
     * <p>In the most basic usage, simply provide a selection in SQL {@code WHERE} statement format.
120
     *
121
     * <p>Example:
122
     *
123
     * <pre>
124
     *     .where("blog_posts.category = 'PROGRAMMING');
125
     * </pre>
126
     *
127
     * <p>User input should never be directly supplied as as part of the selection statement.
128
     * Instead, use positional parameters in your selection statement, then pass the user input
129
     * in via the selectionArgs parameter. This prevents SQL escape characters in user input from
130
     * causing unwanted side effects. (Failure to follow this convention may have security
131
     * implications.)
132
     *
133
     * <p>Positional parameters are specified using the '?' character.
134
     *
135
     * <p>Example:
136
     * <pre>
137
     *     .where("blog_posts.title contains ?, userSearchString);
138
     * </pre>
139
     *
140
     * @param selection SQL where statement
141
     * @param selectionArgs Values to substitute for positional parameters ('?' characters in
142
     *                      {@code selection} statement. Will be automatically escaped.
143
     * @return Fluent interface
144
     */
145
    public SelectionBuilder where(String selection, String... selectionArgs) {
146
        if (TextUtils.isEmpty(selection)) {
147
            if (selectionArgs != null && selectionArgs.length > 0) {
148
                throw new IllegalArgumentException(
149
                        "Valid selection required when including arguments=");
150
            }
151
 
152
            // Shortcut when clause is empty
153
            return this;
154
        }
155
 
156
        if (mSelection.length() > 0) {
157
            mSelection.append(" AND ");
158
        }
159
 
160
        mSelection.append("(").append(selection).append(")");
161
        if (selectionArgs != null) {
162
            Collections.addAll(mSelectionArgs, selectionArgs);
163
        }
164
 
165
        return this;
166
    }
167
 
168
    /**
169
     * Table name to use for SQL {@code FROM} statement.
170
     *
171
     * <p>This method may only be called once. If multiple tables are required, concatenate them
172
     * in SQL-format (typically comma-separated).
173
     *
174
     * <p>If you need to do advanced {@code JOIN}s, they can also be specified here.
175
     *
176
     * See also: mapToTable()
177
     *
178
     * @param table Table name
179
     * @return Fluent interface
180
     */
181
    public SelectionBuilder table(String table) {
182
        mTable = table;
183
        return this;
184
    }
185
 
186
    /**
187
     * Verify that a table name has been supplied using table().
188
     *
189
     * @throws IllegalStateException if table not set
190
     */
191
    private void assertTable() {
192
        if (mTable == null) {
193
            throw new IllegalStateException("Table not specified");
194
        }
195
    }
196
 
197
    /**
198
     * Perform an inner join.
199
     *
200
     * <p>Map columns from a secondary table onto the current result set. References to the column
201
     * specified in {@code column} will be replaced with {@code table.column} in the SQL {@code
202
     * SELECT} clause.
203
     *
204
     * @param column Column name to join on. Must be the same in both tables.
205
     * @param table Secondary table to join.
206
     * @return Fluent interface
207
     */
208
    public SelectionBuilder mapToTable(String column, String table) {
209
        mProjectionMap.put(column, table + "." + column);
210
        return this;
211
    }
212
 
213
    /**
214
     * Create a new column based on custom criteria (such as aggregate functions).
215
     *
216
     * <p>This adds a new column to the result set, based upon custom criteria in SQL format. This
217
     * is equivalent to the SQL statement: {@code SELECT toClause AS fromColumn}
218
     *
219
     * <p>This method is useful for executing SQL sub-queries.
220
     *
221
     * @param fromColumn Name of column for mapping
222
     * @param toClause SQL string representing data to be mapped
223
     * @return Fluent interface
224
     */
225
    public SelectionBuilder map(String fromColumn, String toClause) {
226
        mProjectionMap.put(fromColumn, toClause + " AS " + fromColumn);
227
        return this;
228
    }
229
 
230
    /**
231
     * Return selection string based on current internal state.
232
     *
233
     * @return Current selection as a SQL statement
234
     * @see #getSelectionArgs()
235
     */
236
    public String getSelection() {
237
        return mSelection.toString();
238
 
239
    }
240
 
241
    /**
242
     * Return selection arguments based on current internal state.
243
     *
244
     * @see #getSelection()
245
     */
246
    public String[] getSelectionArgs() {
247
        return mSelectionArgs.toArray(new String[mSelectionArgs.size()]);
248
    }
249
 
250
    /**
251
     * Process user-supplied projection (column list).
252
     *
253
     * <p>In cases where a column is mapped to another data source (either another table, or an
254
     * SQL sub-query), the column name will be replaced with a more specific, SQL-compatible
255
     * representation.
256
     *
257
     * Assumes that incoming columns are non-null.
258
     *
259
     * <p>See also: map(), mapToTable()
260
     *
261
     * @param columns User supplied projection (column list).
262
     */
263
    private void mapColumns(String[] columns) {
264
        for (int i = 0; i < columns.length; i++) {
265
            final String target = mProjectionMap.get(columns[i]);
266
            if (target != null) {
267
                columns[i] = target;
268
            }
269
        }
270
    }
271
 
272
    /**
273
     * Return a description of this builder's state. Does NOT output SQL.
274
     *
275
     * @return Human-readable internal state
276
     */
277
    @Override
278
    public String toString() {
279
        return "SelectionBuilder[table=" + mTable + ", selection=" + getSelection()
280
                + ", selectionArgs=" + Arrays.toString(getSelectionArgs()) + "]";
281
    }
282
 
283
    /**
284
     * Execute query (SQL {@code SELECT}) against specified database.
285
     *
286
     * <p>Using a null projection (column list) is not supported.
287
     *
288
     * @param db Database to query.
289
     * @param columns Database projection (column list) to return, must be non-NULL.
290
     * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the
291
     *                ORDER BY itself). Passing null will use the default sort order, which may be
292
     *                unordered.
293
     * @return A {@link Cursor} object, which is positioned before the first entry. Note that
294
     *         {@link Cursor}s are not synchronized, see the documentation for more details.
295
     */
296
    public Cursor query(SQLiteDatabase db, String[] columns, String orderBy) {
297
        return query(db, columns, null, null, orderBy, null);
298
    }
299
 
300
    /**
301
     * Execute query ({@code SELECT}) against database.
302
     *
303
     * <p>Using a null projection (column list) is not supported.
304
     *
305
     * @param db Database to query.
306
     * @param columns Database projection (column list) to return, must be non-null.
307
     * @param groupBy A filter declaring how to group rows, formatted as an SQL GROUP BY clause
308
     *                (excluding the GROUP BY itself). Passing null will cause the rows to not be
309
     *                grouped.
310
     * @param having A filter declare which row groups to include in the cursor, if row grouping is
311
     *               being used, formatted as an SQL HAVING clause (excluding the HAVING itself).
312
     *               Passing null will cause all row groups to be included, and is required when
313
     *               row grouping is not being used.
314
     * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause (excluding the
315
     *                ORDER BY itself). Passing null will use the default sort order, which may be
316
     *                unordered.
317
     * @param limit Limits the number of rows returned by the query, formatted as LIMIT clause.
318
     *              Passing null denotes no LIMIT clause.
319
     * @return A {@link Cursor} object, which is positioned before the first entry. Note that
320
     *         {@link Cursor}s are not synchronized, see the documentation for more details.
321
     */
322
    public Cursor query(SQLiteDatabase db, String[] columns, String groupBy,
323
                        String having, String orderBy, String limit) {
324
        assertTable();
325
        if (columns != null) mapColumns(columns);
326
        Log.v(TAG, "query(columns=" + Arrays.toString(columns) + ") " + this);
327
        return db.query(mTable, columns, getSelection(), getSelectionArgs(), groupBy, having,
328
                orderBy, limit);
329
    }
330
 
331
    /**
332
     * Execute an {@code UPDATE} against database.
333
     *
334
     * @param db Database to query.
335
     * @param values A map from column names to new column values. null is a valid value that will
336
     *               be translated to NULL
337
     * @return The number of rows affected.
338
     */
339
    public int update(SQLiteDatabase db, ContentValues values) {
340
        assertTable();
341
        Log.v(TAG, "update() " + this);
342
        return db.update(mTable, values, getSelection(), getSelectionArgs());
343
    }
344
 
345
    /**
346
     * Execute {@code DELETE} against database.
347
     *
348
     * @param db Database to query.
349
     * @return The number of rows affected.
350
     */
351
    public int delete(SQLiteDatabase db) {
352
        assertTable();
353
        Log.v(TAG, "delete() " + this);
354
        return db.delete(mTable, getSelection(), getSelectionArgs());
355
    }
356
}