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 }