Links
Android Sites
Language
Results
Loading...
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
}