SQLiteQueryBuilder.java revision f4072fcc14ec44072d31d7beeb4524550bead531
1/* 2 * Copyright (C) 2006 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 17package android.database.sqlite; 18 19import android.database.Cursor; 20import android.database.DatabaseUtils; 21import android.provider.BaseColumns; 22import android.text.TextUtils; 23import android.util.Log; 24 25import java.util.Iterator; 26import java.util.Map; 27import java.util.Set; 28import java.util.Map.Entry; 29import java.util.regex.Pattern; 30 31/** 32 * This is a convience class that helps build SQL queries to be sent to 33 * {@link SQLiteDatabase} objects. 34 */ 35public class SQLiteQueryBuilder 36{ 37 private static final String TAG = "SQLiteQueryBuilder"; 38 private static final Pattern sLimitPattern = 39 Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?"); 40 41 private Map<String, String> mProjectionMap = null; 42 private String mTables = ""; 43 private StringBuilder mWhereClause = null; // lazily created 44 private boolean mDistinct; 45 private SQLiteDatabase.CursorFactory mFactory; 46 private boolean mStrictProjectionMap; 47 48 public SQLiteQueryBuilder() { 49 mDistinct = false; 50 mFactory = null; 51 } 52 53 /** 54 * Mark the query as DISTINCT. 55 * 56 * @param distinct if true the query is DISTINCT, otherwise it isn't 57 */ 58 public void setDistinct(boolean distinct) { 59 mDistinct = distinct; 60 } 61 62 /** 63 * Returns the list of tables being queried 64 * 65 * @return the list of tables being queried 66 */ 67 public String getTables() { 68 return mTables; 69 } 70 71 /** 72 * Sets the list of tables to query. Multiple tables can be specified to perform a join. 73 * For example: 74 * setTables("foo, bar") 75 * setTables("foo LEFT OUTER JOIN bar ON (foo.id = bar.foo_id)") 76 * 77 * @param inTables the list of tables to query on 78 */ 79 public void setTables(String inTables) { 80 mTables = inTables; 81 } 82 83 /** 84 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded 85 * by parenthesis and ANDed with the selection passed to {@link #query}. The final 86 * WHERE clause looks like: 87 * 88 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) 89 * 90 * @param inWhere the chunk of text to append to the WHERE clause. 91 */ 92 public void appendWhere(CharSequence inWhere) { 93 if (mWhereClause == null) { 94 mWhereClause = new StringBuilder(inWhere.length() + 16); 95 } 96 if (mWhereClause.length() == 0) { 97 mWhereClause.append('('); 98 } 99 mWhereClause.append(inWhere); 100 } 101 102 /** 103 * Append a chunk to the WHERE clause of the query. All chunks appended are surrounded 104 * by parenthesis and ANDed with the selection passed to {@link #query}. The final 105 * WHERE clause looks like: 106 * 107 * WHERE (<append chunk 1><append chunk2>) AND (<query() selection parameter>) 108 * 109 * @param inWhere the chunk of text to append to the WHERE clause. it will be escaped 110 * to avoid SQL injection attacks 111 */ 112 public void appendWhereEscapeString(String inWhere) { 113 if (mWhereClause == null) { 114 mWhereClause = new StringBuilder(inWhere.length() + 16); 115 } 116 if (mWhereClause.length() == 0) { 117 mWhereClause.append('('); 118 } 119 DatabaseUtils.appendEscapedSQLString(mWhereClause, inWhere); 120 } 121 122 /** 123 * Sets the projection map for the query. The projection map maps 124 * from column names that the caller passes into query to database 125 * column names. This is useful for renaming columns as well as 126 * disambiguating column names when doing joins. For example you 127 * could map "name" to "people.name". If a projection map is set 128 * it must contain all column names the user may request, even if 129 * the key and value are the same. 130 * 131 * @param columnMap maps from the user column names to the database column names 132 */ 133 public void setProjectionMap(Map<String, String> columnMap) { 134 mProjectionMap = columnMap; 135 } 136 137 /** 138 * Sets the cursor factory to be used for the query. You can use 139 * one factory for all queries on a database but it is normally 140 * easier to specify the factory when doing this query. @param 141 * factory the factor to use 142 */ 143 public void setCursorFactory(SQLiteDatabase.CursorFactory factory) { 144 mFactory = factory; 145 } 146 147 /** 148 * @hide 149 */ 150 public void setStrictProjectionMap(boolean flag) { 151 mStrictProjectionMap = flag; 152 } 153 154 /** 155 * Build an SQL query string from the given clauses. 156 * 157 * @param distinct true if you want each row to be unique, false otherwise. 158 * @param tables The table names to compile the query against. 159 * @param columns A list of which columns to return. Passing null will 160 * return all columns, which is discouraged to prevent reading 161 * data from storage that isn't going to be used. 162 * @param where A filter declaring which rows to return, formatted as an SQL 163 * WHERE clause (excluding the WHERE itself). Passing null will 164 * return all rows for the given URL. 165 * @param groupBy A filter declaring how to group rows, formatted as an SQL 166 * GROUP BY clause (excluding the GROUP BY itself). Passing null 167 * will cause the rows to not be grouped. 168 * @param having A filter declare which row groups to include in the cursor, 169 * if row grouping is being used, formatted as an SQL HAVING 170 * clause (excluding the HAVING itself). Passing null will cause 171 * all row groups to be included, and is required when row 172 * grouping is not being used. 173 * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause 174 * (excluding the ORDER BY itself). Passing null will use the 175 * default sort order, which may be unordered. 176 * @param limit Limits the number of rows returned by the query, 177 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 178 * @return the SQL query string 179 */ 180 public static String buildQueryString( 181 boolean distinct, String tables, String[] columns, String where, 182 String groupBy, String having, String orderBy, String limit) { 183 if (TextUtils.isEmpty(groupBy) && !TextUtils.isEmpty(having)) { 184 throw new IllegalArgumentException( 185 "HAVING clauses are only permitted when using a groupBy clause"); 186 } 187 if (!TextUtils.isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) { 188 throw new IllegalArgumentException("invalid LIMIT clauses:" + limit); 189 } 190 191 StringBuilder query = new StringBuilder(120); 192 193 query.append("SELECT "); 194 if (distinct) { 195 query.append("DISTINCT "); 196 } 197 if (columns != null && columns.length != 0) { 198 appendColumns(query, columns); 199 } else { 200 query.append("* "); 201 } 202 query.append("FROM "); 203 query.append(tables); 204 appendClause(query, " WHERE ", where); 205 appendClause(query, " GROUP BY ", groupBy); 206 appendClause(query, " HAVING ", having); 207 appendClause(query, " ORDER BY ", orderBy); 208 appendClause(query, " LIMIT ", limit); 209 210 return query.toString(); 211 } 212 213 private static void appendClause(StringBuilder s, String name, String clause) { 214 if (!TextUtils.isEmpty(clause)) { 215 s.append(name); 216 s.append(clause); 217 } 218 } 219 220 private static void appendClauseEscapeClause(StringBuilder s, String name, String clause) { 221 if (!TextUtils.isEmpty(clause)) { 222 s.append(name); 223 DatabaseUtils.appendEscapedSQLString(s, clause); 224 } 225 } 226 227 /** 228 * Add the names that are non-null in columns to s, separating 229 * them with commas. 230 */ 231 public static void appendColumns(StringBuilder s, String[] columns) { 232 int n = columns.length; 233 234 for (int i = 0; i < n; i++) { 235 String column = columns[i]; 236 237 if (column != null) { 238 if (i > 0) { 239 s.append(", "); 240 } 241 s.append(column); 242 } 243 } 244 s.append(' '); 245 } 246 247 /** 248 * Perform a query by combining all current settings and the 249 * information passed into this method. 250 * 251 * @param db the database to query on 252 * @param projectionIn A list of which columns to return. Passing 253 * null will return all columns, which is discouraged to prevent 254 * reading data from storage that isn't going to be used. 255 * @param selection A filter declaring which rows to return, 256 * formatted as an SQL WHERE clause (excluding the WHERE 257 * itself). Passing null will return all rows for the given URL. 258 * @param selectionArgs You may include ?s in selection, which 259 * will be replaced by the values from selectionArgs, in order 260 * that they appear in the selection. The values will be bound 261 * as Strings. 262 * @param groupBy A filter declaring how to group rows, formatted 263 * as an SQL GROUP BY clause (excluding the GROUP BY 264 * itself). Passing null will cause the rows to not be grouped. 265 * @param having A filter declare which row groups to include in 266 * the cursor, if row grouping is being used, formatted as an 267 * SQL HAVING clause (excluding the HAVING itself). Passing 268 * null will cause all row groups to be included, and is 269 * required when row grouping is not being used. 270 * @param sortOrder How to order the rows, formatted as an SQL 271 * ORDER BY clause (excluding the ORDER BY itself). Passing null 272 * will use the default sort order, which may be unordered. 273 * @return a cursor over the result set 274 * @see android.content.ContentResolver#query(android.net.Uri, String[], 275 * String, String[], String) 276 */ 277 public Cursor query(SQLiteDatabase db, String[] projectionIn, 278 String selection, String[] selectionArgs, String groupBy, 279 String having, String sortOrder) { 280 return query(db, projectionIn, selection, selectionArgs, groupBy, having, sortOrder, 281 null /* limit */); 282 } 283 284 /** 285 * Perform a query by combining all current settings and the 286 * information passed into this method. 287 * 288 * @param db the database to query on 289 * @param projectionIn A list of which columns to return. Passing 290 * null will return all columns, which is discouraged to prevent 291 * reading data from storage that isn't going to be used. 292 * @param selection A filter declaring which rows to return, 293 * formatted as an SQL WHERE clause (excluding the WHERE 294 * itself). Passing null will return all rows for the given URL. 295 * @param selectionArgs You may include ?s in selection, which 296 * will be replaced by the values from selectionArgs, in order 297 * that they appear in the selection. The values will be bound 298 * as Strings. 299 * @param groupBy A filter declaring how to group rows, formatted 300 * as an SQL GROUP BY clause (excluding the GROUP BY 301 * itself). Passing null will cause the rows to not be grouped. 302 * @param having A filter declare which row groups to include in 303 * the cursor, if row grouping is being used, formatted as an 304 * SQL HAVING clause (excluding the HAVING itself). Passing 305 * null will cause all row groups to be included, and is 306 * required when row grouping is not being used. 307 * @param sortOrder How to order the rows, formatted as an SQL 308 * ORDER BY clause (excluding the ORDER BY itself). Passing null 309 * will use the default sort order, which may be unordered. 310 * @param limit Limits the number of rows returned by the query, 311 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 312 * @return a cursor over the result set 313 * @see android.content.ContentResolver#query(android.net.Uri, String[], 314 * String, String[], String) 315 */ 316 public Cursor query(SQLiteDatabase db, String[] projectionIn, 317 String selection, String[] selectionArgs, String groupBy, 318 String having, String sortOrder, String limit) { 319 if (mTables == null) { 320 return null; 321 } 322 323 String sql = buildQuery( 324 projectionIn, selection, groupBy, having, 325 sortOrder, limit); 326 327 if (Log.isLoggable(TAG, Log.DEBUG)) { 328 Log.d(TAG, "Performing query: " + sql); 329 } 330 return db.rawQueryWithFactory( 331 mFactory, sql, selectionArgs, 332 SQLiteDatabase.findEditTable(mTables)); 333 } 334 335 /** 336 * Construct a SELECT statement suitable for use in a group of 337 * SELECT statements that will be joined through UNION operators 338 * in buildUnionQuery. 339 * 340 * @param projectionIn A list of which columns to return. Passing 341 * null will return all columns, which is discouraged to 342 * prevent reading data from storage that isn't going to be 343 * used. 344 * @param selection A filter declaring which rows to return, 345 * formatted as an SQL WHERE clause (excluding the WHERE 346 * itself). Passing null will return all rows for the given 347 * URL. 348 * @param groupBy A filter declaring how to group rows, formatted 349 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 350 * Passing null will cause the rows to not be grouped. 351 * @param having A filter declare which row groups to include in 352 * the cursor, if row grouping is being used, formatted as an 353 * SQL HAVING clause (excluding the HAVING itself). Passing 354 * null will cause all row groups to be included, and is 355 * required when row grouping is not being used. 356 * @param sortOrder How to order the rows, formatted as an SQL 357 * ORDER BY clause (excluding the ORDER BY itself). Passing null 358 * will use the default sort order, which may be unordered. 359 * @param limit Limits the number of rows returned by the query, 360 * formatted as LIMIT clause. Passing null denotes no LIMIT clause. 361 * @return the resulting SQL SELECT statement 362 */ 363 public String buildQuery( 364 String[] projectionIn, String selection, String groupBy, 365 String having, String sortOrder, String limit) { 366 String[] projection = computeProjection(projectionIn); 367 368 StringBuilder where = new StringBuilder(); 369 boolean hasBaseWhereClause = mWhereClause != null && mWhereClause.length() > 0; 370 371 if (hasBaseWhereClause) { 372 where.append(mWhereClause.toString()); 373 where.append(')'); 374 } 375 376 // Tack on the user's selection, if present. 377 if (selection != null && selection.length() > 0) { 378 if (hasBaseWhereClause) { 379 where.append(" AND "); 380 } 381 382 where.append('('); 383 where.append(selection); 384 where.append(')'); 385 } 386 387 return buildQueryString( 388 mDistinct, mTables, projection, where.toString(), 389 groupBy, having, sortOrder, limit); 390 } 391 392 /** 393 * @deprecated This method's signature is misleading since no SQL parameter 394 * substitution is carried out. The selection arguments parameter does not get 395 * used at all. To avoid confusion, call 396 * {@link #buildQuery(String[], String, String, String, String, String)} instead. 397 */ 398 @Deprecated 399 public String buildQuery( 400 String[] projectionIn, String selection, String[] selectionArgs, 401 String groupBy, String having, String sortOrder, String limit) { 402 return buildQuery(projectionIn, selection, groupBy, having, sortOrder, limit); 403 } 404 405 /** 406 * Construct a SELECT statement suitable for use in a group of 407 * SELECT statements that will be joined through UNION operators 408 * in buildUnionQuery. 409 * 410 * @param typeDiscriminatorColumn the name of the result column 411 * whose cells will contain the name of the table from which 412 * each row was drawn. 413 * @param unionColumns the names of the columns to appear in the 414 * result. This may include columns that do not appear in the 415 * table this SELECT is querying (i.e. mTables), but that do 416 * appear in one of the other tables in the UNION query that we 417 * are constructing. 418 * @param columnsPresentInTable a Set of the names of the columns 419 * that appear in this table (i.e. in the table whose name is 420 * mTables). Since columns in unionColumns include columns that 421 * appear only in other tables, we use this array to distinguish 422 * which ones actually are present. Other columns will have 423 * NULL values for results from this subquery. 424 * @param computedColumnsOffset all columns in unionColumns before 425 * this index are included under the assumption that they're 426 * computed and therefore won't appear in columnsPresentInTable, 427 * e.g. "date * 1000 as normalized_date" 428 * @param typeDiscriminatorValue the value used for the 429 * type-discriminator column in this subquery 430 * @param selection A filter declaring which rows to return, 431 * formatted as an SQL WHERE clause (excluding the WHERE 432 * itself). Passing null will return all rows for the given 433 * URL. 434 * @param groupBy A filter declaring how to group rows, formatted 435 * as an SQL GROUP BY clause (excluding the GROUP BY itself). 436 * Passing null will cause the rows to not be grouped. 437 * @param having A filter declare which row groups to include in 438 * the cursor, if row grouping is being used, formatted as an 439 * SQL HAVING clause (excluding the HAVING itself). Passing 440 * null will cause all row groups to be included, and is 441 * required when row grouping is not being used. 442 * @return the resulting SQL SELECT statement 443 */ 444 public String buildUnionSubQuery( 445 String typeDiscriminatorColumn, 446 String[] unionColumns, 447 Set<String> columnsPresentInTable, 448 int computedColumnsOffset, 449 String typeDiscriminatorValue, 450 String selection, 451 String groupBy, 452 String having) { 453 int unionColumnsCount = unionColumns.length; 454 String[] projectionIn = new String[unionColumnsCount]; 455 456 for (int i = 0; i < unionColumnsCount; i++) { 457 String unionColumn = unionColumns[i]; 458 459 if (unionColumn.equals(typeDiscriminatorColumn)) { 460 projectionIn[i] = "'" + typeDiscriminatorValue + "' AS " 461 + typeDiscriminatorColumn; 462 } else if (i <= computedColumnsOffset 463 || columnsPresentInTable.contains(unionColumn)) { 464 projectionIn[i] = unionColumn; 465 } else { 466 projectionIn[i] = "NULL AS " + unionColumn; 467 } 468 } 469 return buildQuery( 470 projectionIn, selection, groupBy, having, 471 null /* sortOrder */, 472 null /* limit */); 473 } 474 475 /** 476 * @deprecated This method's signature is misleading since no SQL parameter 477 * substitution is carried out. The selection arguments parameter does not get 478 * used at all. To avoid confusion, call 479 * {@link #buildUnionSubQuery} 480 * instead. 481 */ 482 @Deprecated 483 public String buildUnionSubQuery( 484 String typeDiscriminatorColumn, 485 String[] unionColumns, 486 Set<String> columnsPresentInTable, 487 int computedColumnsOffset, 488 String typeDiscriminatorValue, 489 String selection, 490 String[] selectionArgs, 491 String groupBy, 492 String having) { 493 return buildUnionSubQuery( 494 typeDiscriminatorColumn, unionColumns, columnsPresentInTable, 495 computedColumnsOffset, typeDiscriminatorValue, selection, 496 groupBy, having); 497 } 498 499 /** 500 * Given a set of subqueries, all of which are SELECT statements, 501 * construct a query that returns the union of what those 502 * subqueries return. 503 * @param subQueries an array of SQL SELECT statements, all of 504 * which must have the same columns as the same positions in 505 * their results 506 * @param sortOrder How to order the rows, formatted as an SQL 507 * ORDER BY clause (excluding the ORDER BY itself). Passing 508 * null will use the default sort order, which may be unordered. 509 * @param limit The limit clause, which applies to the entire union result set 510 * 511 * @return the resulting SQL SELECT statement 512 */ 513 public String buildUnionQuery(String[] subQueries, String sortOrder, String limit) { 514 StringBuilder query = new StringBuilder(128); 515 int subQueryCount = subQueries.length; 516 String unionOperator = mDistinct ? " UNION " : " UNION ALL "; 517 518 for (int i = 0; i < subQueryCount; i++) { 519 if (i > 0) { 520 query.append(unionOperator); 521 } 522 query.append(subQueries[i]); 523 } 524 appendClause(query, " ORDER BY ", sortOrder); 525 appendClause(query, " LIMIT ", limit); 526 return query.toString(); 527 } 528 529 private String[] computeProjection(String[] projectionIn) { 530 if (projectionIn != null && projectionIn.length > 0) { 531 if (mProjectionMap != null) { 532 String[] projection = new String[projectionIn.length]; 533 int length = projectionIn.length; 534 535 for (int i = 0; i < length; i++) { 536 String userColumn = projectionIn[i]; 537 String column = mProjectionMap.get(userColumn); 538 539 if (column != null) { 540 projection[i] = column; 541 continue; 542 } 543 544 if (!mStrictProjectionMap && 545 ( userColumn.contains(" AS ") || userColumn.contains(" as "))) { 546 /* A column alias already exist */ 547 projection[i] = userColumn; 548 continue; 549 } 550 551 throw new IllegalArgumentException("Invalid column " 552 + projectionIn[i]); 553 } 554 return projection; 555 } else { 556 return projectionIn; 557 } 558 } else if (mProjectionMap != null) { 559 // Return all columns in projection map. 560 Set<Entry<String, String>> entrySet = mProjectionMap.entrySet(); 561 String[] projection = new String[entrySet.size()]; 562 Iterator<Entry<String, String>> entryIter = entrySet.iterator(); 563 int i = 0; 564 565 while (entryIter.hasNext()) { 566 Entry<String, String> entry = entryIter.next(); 567 568 // Don't include the _count column when people ask for no projection. 569 if (entry.getKey().equals(BaseColumns._COUNT)) { 570 continue; 571 } 572 projection[i++] = entry.getValue(); 573 } 574 return projection; 575 } 576 return null; 577 } 578} 579